Géographie

Problème

On s'intéresse à un ensemble de villes reliées entre elles par un réseau routier. Certaines liaisons entre deux villes sont à sens unique et d'autres à double sens. Chaque ville est caractérisée par son nom, sa population et son appartenance à un département et une région. On considérera par la suite, et pour simplifier la présentation que le "réseau routier" se compose des villes représentées dans le schéma suivant :

Réseau routier base de données Géographie

Les chiffres en rouge représentent les distances entre villes.

Dictionnaire des données

Mnémonique Libellé
Ville Nom de la ville
Population Nombre d'habitants de la ville
NumDept Numéro du département dans lequel la ville se trouve
NomDept Nom du département dans lequel la ville se trouve
NumRegion Numéro de la région dans laquelle la ville se trouve
NomRegion Nom de la région dans laquelle la ville se trouve
Distance Distance, en kilomètres, entre 2 villes


Pour modéliser la distance entre 2 villes, l'attribut "Ville" sera amené à jouer deux rôles :

1- Celui de la ville de départ : attribut "VilleDepart"
2- Celui de la ville d'arrivée  : attribut "VilleArrivee"

Dans le cas d'une liaison à double sens entre les 2 villes, la même ville sera successivement considérée comme ville de départ puis ville d'arrivée. Dans notre cas, par exemple B-C et C-B ou B-D et D-B.

Si la liaison entre les deux villes est à sens unique, dans notre exemple E-B ou E-F, une des 2 villes ne jouera qu'un seul des 2 rôles : Ville de départ ou Ville d'arrivée, mais pourra jouer les deux rôles si elle est l'objet d'une liaison à double sens avec une autre ville : par exemple B.

Schéma des données

Pour un complément d'information sur la démarche de construction du schéma des données se reporter, dans ces pages, à la rubrique Conception BD.

1-DF simples

Ville -> Population, Numdept, Nomdept, Numregion, Nomregion
Numdept -> Nomdept, Numregion, Nomregion
Numregion -> Nomregion

2-Attributs non classés et DF composées

Les attributs VilleDepart, VilleArrivee, Distance n'ont pas été classés dans l'étape précédente, ils interviennent dans une DF composée qui est la suivante :

VilleDepart, VilleArrivee-> Distance, Population, Numdept, Nomdept, Numregion, Nomregion

3-Elimination des transitivités

Ville -> Population, Numdept
Numdept -> Nomdept, Numregion
Numregion -> Nomregion
VilleDepart, VilleArrivee-> Distance

4-Construction des entités et associations

VILLES(Ville, Population, Numdept)
DEPARTEMENT(Numdept, Nomdept, Numregion)
REGION(Numregion, Nomregion)
DISTANCES(VilleDepart, VilleArrivee, Distance)

5-Mise en relation des entités et associations

Entité source Entité but Relation sur l'attribut Type de relation
DEPARTEMENT VILLE Numdept 1-n
REGION DEPARTEMENT Numregion 1-n
VILLES DISTANCES Ville / VilleDepart 1-n
VILLES DISTANCES Ville / VilleArrivee 1-n

6-Schéma des données

La structure de données "optimale" est la suivante (les clés sont en caractères gras) :

Schéma des données base de données Géographie

Dans ce schéma, les relations entre la table "Distances" et la table "Villes" ne sont pas représentées, car elles dépendent des traitements, et donc des différents rôles que l'attribut Ville va jouer en fonction des questions posées.

Contenu de la table "Distances"

VilleDepart VilleArrivee Distance
A E 50
A F 60
B C 50
B D 60
C B 50
D B 60
D E 30
E A 50
E B 60
E D 30
E F 100
F A 60

Interrogations

Pour visualiser le contenu de chaque table de la base de données, taper la commande :
SELECT * FROM Nom_de_ table;
dans un cadre comportant le bouton "Exécuter".

Question 1
Afficher, par région et département, le nombre de villes de plus de 100000 habitants. (Réponse)

Question 2
Afficher la liste de toutes les liaisons à sens unique entre deux villes pour lesquelles la population de l'une des 2 villes est inférieure à 80000 habitants. (Réponse)

Question 3
Afficher la liste des villes, distantes de 2 tronçons routiers, et la distance qui les sépare. (Réponse)

Question 4
Afficher la liste des villes, qui peuvent être distantes de 4 tronçons routiers (il peut en effet y avoir des liaisons plus courtes en 2 ou 3 tronçons), et la distance qui les sépare. (Réponse)

Question 5
Afficher le nombre d'habitants par région et département. On supposera que la population d'une région se résume à celle des villes qui font partie du réseau routier. (Réponse)

Question 6
Donner le nom de la région qui possède le plus long réseau routier et le nombre de kilomètres de ce réseau.
Cette question doit être traitée de manière "statique" car elle implique la création de tables de travail. (Réponse)