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 :
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) :
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)