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.

classDiagram class pilots { name varchar[100] nn birthday date nn } class countries { name varchar[100] nn } class routes { } class flights { departure datetime nn duration time nn delay time nn = 0 } class flights_pilots { is_copilot boolean nn = false } class audits { pilot tinyint nn copilot tinyint nn crew tinyint nn food tinyint nn toilets tinyint nn comment varchar[1000] } class planes { manufacturer varchar[100] nn model varchar[100] nn } class lookup { } class maintenances { start date nn end date description varchar[1000] nn } pilots "*" --> "1" countries routes "*" --> "1" countries: origin routes "*" --> "1" countries: destination flights_pilots "*" --> "1" pilots flights_pilots "0..2" --> "1" flights flights "*" --> "1" routes flights "*" --> "1" planes audits "0..1" --> "1" flights maintenances "*" --> "1" planes planes "*" --> "1" lookup

Serveur

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 retirer l'inspection d'un vol en la supprimant
- Autrement, toutes les autres relations sont immuables

Compléter les insertions des données de départ si nécessaire

Récupération

Les pilotes, ayant des vols en tant que pilote, qui ont plus de temps de vol que la moyenne

flight time, le temps de vol total
diff, indique combien de temps de plus
Trier par temps de vol décroissant

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

Tous les pilotes en calculant

flights, le nombres de vols total
pilot, le nombre de vols en tant que pilote
copilot, le nombre de vols en tant que copilote
Donner une valeur par défaut aux données nulles
Trier en ordre décroissant de flights, pilot, copilot

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

Pour chaque possibilité de pays, calculer

Pour chaque combinaison, en origine ou destination
- flights, le nombres de vols
- routes, le nombre de routes
- pilots, le nombre de pilotes dont la nationalité correspond
Donner une valeur par défaut aux données nulles
Trier en ordre d'origine croissant et flights décroissant

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

Résumé des vols

route, formatte Code IATA : origine -> destination
plane, combine le manufacturier, le modèle et le CAA
pilots, identifier le pilote* avec une étoile et le copilote par leurs noms
audit, 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                                  |
+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+

Sommaire des heures de vols par modèle d'avion de tous les 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    |
+--------------+-------------+

Remise

Énoncé de travail sur LÉA

Envoyer uniquement le fichier .sql fournis contenant vos requêtes
Inscrire votre nom en commentaire
AVANT la date limite de remise, vous devez passer me voir pour recevoir votre rétroaction

James Hoffman

Base de données 1