Réaliser les manipulations suivantes dans le fichier fournit en répondant dans la section correspondante.

• Le fichier doit pouvoir être exécuté plusieurs fois sans erreurs
• Portez une attention particulière à l'utilisation judicieuse des mécanismes et fonctions MariaDB

Aéroport

Créer la base de données airport et les tables représentées dans le modèle relationnel ci-dessous.

• Ajouter les colonnes, clés primaires et clés étrangères pour respecter les relations identifiées selon les contraintes suivantes:

  • Un pays est identifié par son code ISO 3166-1 alpha-3
  • Chaque pilote possède une numéro d'employé qui augmente à chaque embauche
  • Une route est identifiée via un code IATA et une séquence de 4 chiffres
  • Chaque vol est identifié via un numéro unique auto-incrément
  • On peut associer un pilote à un vol, en indiquant si c'était le copilote ou non.
  • De façon sporadique, les vols sont audités pour vérifier la conformités de quelques critères. Chaque critère est noté de 1 à 4: médiocre, insatisfaisant, satisfaisant, au-delà des attentes
  • Un avion est identifié par un code CAA
  • Créer une lookup table pour encapsuler les valeurs possibles de manufacturiers d'avion
  • Un entretien effectué sur un avion est identifié par un numéro unique auto-incrément

• Respecter les règles d'intégrité suivantes

  • Un vol possède un pilote, un copilote et ceux-ci ne peuvent pas être la même personne
  • Lorsqu'un avion est supprimé ses entretiens le sont aussi
  • On peut annuler l'inspection d'un vol en la supprimant
  • Autrement, toutes les autres relations doivent être conservées

Vous pouvez télécharger le diagramme et l'ouvrir avec Draw.io pour y ajouter vos annotations

Insérer des données de départ dans chaque table en couvrant différentes relations possibles, ex: Avions avec 0 vols, 1 vol, Plusieurs vols

Assurez-vous de respecter le nom des colonnes présentées dans l'exemple de format du résultat

Récupérer les pilotes qui ont plus de temps de vol que la moyenne

• calculer combien de temps de plus

+-------+-------------+----------+
| pilot | flight time | diff     |
+-------+-------------+----------+
| alice | hh:mm:ss    | hh:mm:ss |
...
+-------+-------------+----------+

Récupérer les pilotes/copilotes en indiquant

• le nombres de vols total
• le nombre de vols en tant que pilote
• le nombre de vols en tant que copilote
• donner une valeur par défaut aux données nulles
• trier en ordre décroissant de nombre de vols: total, pilote, copilote

+---------+---------+-------+---------+
| name    | flights | pilot | copilot |
+---------+---------+-------+---------+
| alice   |       6 |     3 |       3 |
| bob     |       2 |     1 |       1 |
| charlie |       0 |     0 |       0 |
...
+---------+---------+-------+---------+

Récupérer, pour chaque possibilité de pays, le nombres de routes et de vols qui y passent

• donner une valeur par défaut aux données nulles
• trier en ordre d'origine et vols

+--------+-------------+---------+--------+
| origin | destination | flights | routes |
+--------+-------------+---------+--------+
| can    | can         |       6 |      1 |
| can    | usa         |       4 |      2 |
| usa    | usa         |       2 |      2 |
| can    | gbr         |       0 |      0 |
| gbr    | can         |       0 |      0 |
| gbr    | gbr         |       0 |      0 |
| gbr    | usa         |       0 |      0 |
| usa    | can         |       0 |      0 |
| usa    | gbr         |       0 |      0 |
+--------+-------------+---------+--------+

Récupérer les pilotes/copilotes en calculant le nombre de routes et de vols passant par leur pays d'origine

• donner une valeur par défaut aux données nulles
• trier par vols décroissant

+---------+--------+--------------+
| pilot   | routes | home flights |
+---------+--------+--------------+
| dave    |      3 |            6 |
| bob     |      1 |            1 |
| charlie |      0 |            0 |
...
+---------+--------+--------------+

Récupérer quel(s) modèle(s) d'avion passent le plus de temps en maintenance

• formater l'affichage de l'avion avec le manufacturier et le modèle
• trier par manufacturier et modèle

+------------+---------------------+
| plane      | days in maintenance |
+------------+---------------------+
| boeing 737 |                 123 |
...
+------------+---------------------+

Récupérer un résumé des vols

• la route selon le format Code IATA : origine -> destination
• l'avion en combinant le manufacturier, le modèle et le CAA
• identifier le pilote* avec une étoile et le copilote par leurs noms
• l'audit qui calcule la moyenne d'une inspection et affiche le commentaire(si présent sinon N/A)
• trier par route et départ

+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+
| route                | departure           | duration | delay    | plane             | pilots      | audit                                 |
+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+
| ABC1234 : can -> can | 2020-04-15 14:10:00 | 06:40:00 | 00:00:00 | boeing 737 C-FPQR | alice, bob* | 3.6 : cold food, missing toilet paper |
| ABC1234 : can -> can | 2021-01-01 08:00:00 | 02:20:00 | 00:00:00 | airbus 380 C-FASW | alice*      | 4.0 : N/A                             |
| ABC1234 : can -> can | 2021-01-02 08:10:00 | 02:30:00 | 00:00:00 | airbus 380 C-FASW | bob, alice* | NULL                                  |
| ABC1234 : can -> can | 2021-02-01 09:05:00 | 02:22:00 | 04:44:00 | airbus 380 C-FASW | NULL        | NULL                                  |
| ABC1234 : can -> can | 2021-02-02 09:20:00 | 02:20:00 | 04:40:00 | airbus 380 C-FASW | NULL        | NULL                                  |
| DEF9876 : usa -> usa | 2021-01-03 13:20:00 | 05:10:00 | 01:07:00 | boeing 747 C-FIER | alice       | NULL                                  |
| DEF9876 : usa -> usa | 2021-02-03 14:22:00 | 05:20:00 | 10:40:00 | boeing 747 C-FIER | NULL        | NULL                                  |
| XYZ1289 : can -> usa | 2021-01-05 09:15:00 | 07:35:00 | 03:34:00 | airbus 380 C-FASW | NULL        | NULL                                  |
| XYZ1289 : can -> usa | 2023-11-09 09:00:00 | 07:25:00 | 00:00:00 | airbus 380 C-FASW | alice       | NULL                                  |
| XYZ1289 : can -> usa | 2023-11-11 09:10:00 | 07:30:00 | 00:00:00 | airbus 380 C-FASW | NULL        | NULL                                  |
+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+

Récupérer le sommaire des heures de vols par modèle d'avion de chaque manufacturier

• donner une valeur par défaut aux données nulles
• Exploiter la mécanique rollup pour calculer les super-aggrégats
  • Formater les sous-totaux par manufacturer avec >
  • Indiquer le grand total en MAJUSCULES

+--------------+-------------+
| plane        | flight time |
+--------------+-------------+
| airbus 380   | 32:02:00    |
| > airbus     | 32:02:00    |
| boeing 737   | 06:40:00    |
| boeing 747   | 10:30:00    |
| > boeing     | 17:10:00    |
| > bombardier | 00:00:00    |
| > bombardier | 00:00:00    |
| TOTAL        | 49:12:00    |
+--------------+-------------+

Proposez une statistique intéressante à calculer et effectuer la requête correspondante

• Vous devez combiner l'information d'au moins 2 tables

Remise

24 novembre, 8h AM via LÉA

Envoyer uniquement le fichier .sql fournis contenant vos requêtes

• Inscrire votre nom en commentaire

Critères d'évaluation

Laboratoire 3 - 5% / 41.5

Nom: ____________________________________