Bases de données avec MySQL

TD n°4
Concevoir une base de données à partir d'un problème

Exercice 1 : Création de la base de données

Nous allons aujourd'hui créer une base de données à partir d'un problème réel. J'exposerais donc le
problème et vous construirez la base de données correspondante en veillant à ce qu'elle soit
cohérente et que les informations soient le moins redondantes possibles.
Voici le problème :
La compagnie aérienne Delta China, basée à Shanghai, dispose d'une « flotte » (un
ensemble) d'avions. Ceux-ci sont immatriculés par un code à 6 chiffres ou lettres et on stocke
également des commentaires sur chaque avion et le nombre d'heure de vol effectué. De plus chaque
avion a un modèle particulier. En effet, la compagnie a acheté auprès de Boeing et de Airbus des
avions de lignes de plusieurs modèles différents pour ses moyens et longs vols. Les modèles d'avion
définissent le nombre de place pour les passagers en classe Economy et en classe Business, ainsi
que le nombre de personnel naviguant qui doit s'occuper de l'avion lorsqu'il est en vol. De plus,
chaque modèle d'avion dispose d'un périmètre d'action, à cause du carburant, qui lui donne une
distance maximale de vol (en kilomètre).
Cette société souhaite gérer correctement ses avions et surtout ses vols. Un vol est un
voyage d'un avion particulier entre Shanghai et une destination (on suppose que l'on considère
uniquement des allers-retours). Chaque destination est connue de la compagnie aérienne et on
connaît le nom de la grande ville la plus proche, le nom de l'aéroport desservi et surtout le nombre
de kilomètre entre Shanghai et cet aéroport.
Chaque vol est opéré par un équipage qui comprend 2 pilotes et autant de personnel
naviguant que nécessaire au type d'avion concerné par le vol. Pilotes et personnel naviguant sont
des salariés de Delta China et la compagnie dispose de leur nom, leur prénom et stocke aussi leur
salaire. Pour les pilotes, on stocke aussi le nombre d'heures de vols qu'ils ont effectué. Et pour le
personnel naviguant on indique aussi sa fonction. Il est important de noter que l'équipage d'un avion
peut changer au cours du temps, donc à chaque vol sont équipage. Un vol a aussi une date de départ
et une durée de vol exprimée en minutes. Enfin, on veut indiquer si le vol est moyen ou un long
courrier.
Enfin la compagnie veut émettre des billets à ses clients. Elle veut donc stocker, dans sa base
de données, le nom et prénom de ses passagers ainsi que leur numéro de passeport (une série de 9
chiffre et lettre) et leur nationalité. Chaque client peut donc acheter un billet pour un vol donné
(c'est toujours des billets aller-retour). Ce billet a un prix et un code (une série de 7 chiffres et
lettres) pas forcément unique. Enfin, chaque passager a des bagages, qui ont un certain poids (en
kilo) et qui dépendent directement du billets émis pour le vol donné.
Créez une base de données pour modéliser cette situation. Je vous conseille de commencer
sur le papier. Vous pouvez ainsi commencer par écrire sur papier le schéma en intention de la base.
Pour ce faire, vous pouvez employer la méthode du graphe des dépendances fonctionnelles ou
mieux les schémas entités – association vu en cours. Vous prendrez garde à bien créer une clé
primaire pour chaque table de votre base et à bien indiquer les clés étrangères dans chaque table.
Enfin vous pouvez remplir avec des données de votre choix les tables, juste ce qu'il faut pour
tester les requêtes de l'exercice suivant.
Exercice 2 : Quelques requêtes à faire sur cette base
Muni de cette base de données, essayez d'écrire les requêtes (on a pas les données de la base
donc on pourra pas trop vérifier) qui répondent aux questions ou demandes suivantes :
1. Combien de vol a effectué, cette année, le pilote XXX.
2. Un vol (choisissez le numéro) vient de faire son aller-retour, actualisez le nombre d'heures
de vol du pilote XXX et de l'avion AAA concerné.
3. Quelle est la liste des destinations du passager YYY l'année dernière ?
4. Vérifier que tous les vols peuvent atteindre leur destination, donc que le type d'appareil pour
voler suffisamment loin pour une destination donnée ?
5. Vérifier que les vols embarques le nombre nécessaire (ni moins ni plus) de personnel
naviguant par rapport au type d'avion donné. Vérifier aussi que l'équipage comprend
exactement 2 pilotes.
6. Le poids des bagages du passager YYY pour un vol donné (vous choisissez le numéro).
7. Calculer le poids moyens des bagages des passagers.
8. Calculer le pourcentage des passagers sans bagage.
9. Le salaire moyen d'un équipage pour un vol donné.
10. La liste de toutes les destinations d'un avion AAA donné.