Le contexte

Dans le cadre de cet exercice, vous disposez d'une base de données simulant la gestion d'un aéroport, incluant des informations sur les vols, les avions, les compagnies aériennes, et les passagers. La base de données comprend les tables suivantes :

• t_vol : contient des informations sur les vols, tels que l'ID du vol (pk_vol), l'ID de l'avion (fk_avion), la destination, la date et l'heure de départ.

• t_avion : contient des détails sur les avions, y compris l'ID de l'avion (pk_avion), le modèle, la capacité, et l'ID de la compagnie aérienne (fk_compagnie_aerienne).

• t_compagnie_aerienne : inclut les informations sur les compagnies aériennes, telles que l'ID de la compagnie (pk_compagnie_aerienne), le nom et le pays d'origine.

• t_passager : liste les passagers avec leur ID (pk_passager), nom, prénom, et nationalité.

• tr_vol_passager : une table de relation N à N entre t_vol et t_passager, incluant l'ID du vol (fk_vol) et l'ID du passager (fk_passager).

Travail à réaliser

1. Exécuter le script SQL suivant pour créer la base de données :

   CREATE SCHEMA `db_aviation` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   
   -- Création de la table des compagnies aériennes
   CREATE TABLE t_compagnie_aerienne (
       pk_compagnie_aerienne INT PRIMARY KEY,
       nom VARCHAR(255),
       pays_origine VARCHAR(255)
   );
   
   -- Création de la table des avions
   CREATE TABLE t_avion (
       pk_avion INT PRIMARY KEY,
       modele VARCHAR(255),
       capacite INT,
       fk_compagnie_aerienne INT,
       FOREIGN KEY (fk_compagnie_aerienne) REFERENCES t_compagnie_aerienne(pk_compagnie_aerienne)
   );
   
   -- Création de la table des vols
   CREATE TABLE t_vol (
       pk_vol INT PRIMARY KEY,
       destination VARCHAR(255),
       date_depart DATE,
       heure_depart TIME,
       fk_avion INT,
       FOREIGN KEY (fk_avion) REFERENCES t_avion(pk_avion)
   );
   
   -- Création de la table des passagers
   CREATE TABLE t_passager (
       pk_passager INT PRIMARY KEY,
       nom VARCHAR(255),
       prenom VARCHAR(255),
       nationalite VARCHAR(255)
   );
   
   -- Création de la table de relation entre vols et passagers
   CREATE TABLE tr_vol_passager (
       fk_vol INT,
       fk_passager INT,
       PRIMARY KEY (fk_vol, fk_passager),
       FOREIGN KEY (fk_vol) REFERENCES t_vol(pk_vol),
       FOREIGN KEY (fk_passager) REFERENCES t_passager(pk_passager)
   );
   
   -- Insertion dans la table des compagnies aériennes
   INSERT INTO t_compagnie_aerienne (pk_compagnie_aerienne, nom, pays_origine) VALUES
   (1, 'Air France', 'France'),
   (2, 'Lufthansa', 'Allemagne'),
   (3, 'Swiss Air', 'Suisse');
   
   -- Insertion dans la table des avions
   INSERT INTO t_avion (pk_avion, modele, capacite, fk_compagnie_aerienne) VALUES
   (1, 'Airbus A320', 180, 1),
   (2, 'Boeing 747', 366, 2),
   (3, 'Airbus A380', 853, 3);
   
   -- Insertion dans la table des vols
   INSERT INTO t_vol (pk_vol, destination, date_depart, heure_depart, fk_avion) VALUES
   (1, 'Tokyo', '2024-07-01', '13:00', 1),
   (2, 'New York', '2024-07-02', '09:00', 2),
   (3, 'Londres', '2024-07-03', '16:00', 3),
   (4, 'Porto', '2024-08-03', '12:00', 1),
   (5, 'Londres', '2024-07-06', '16:00', 2);
   
   -- Insertion dans la table des passagers
   INSERT INTO t_passager (pk_passager, nom, prenom, nationalite) VALUES
   (1, 'Dupont', 'Jean', 'Français'),
   (2, 'Müller', 'Fritz', 'Allemand'),
   (3, 'Rossi', 'Luca', 'Italien');
   
   -- Insertion dans la table de relation entre vols et passagers
   INSERT INTO tr_vol_passager (fk_vol, fk_passager) VALUES
   (1, 1),
   (1, 2),
   (2, 2),
   (2, 3),
   (3, 1),
   (3, 3);

2. Réaliser un reverse engineering pour obtenir le schéma ER de la base de données

3. Réaliser les requêtes simples suivantes pour obtenir les résultats ci-dessous :
   1. Sélectionnez tous les vols partant avant le 2 juillet 2024 inclus, en affichant leur destination et la date de départ.

      destination	date_depart
      Tokyo	2024-07-01
      New York	2024-07-02

   2. Sélectionnez tous les avions de la compagnie aérienne 'Air France'.

      modele	capacite
      Airbus A320	180

   3. Affichez les destinations uniques desservies par les vols enregistrés dans la base de données.

      destination
      Tokyo
      New York
      Londres
      Porto

   4. Comptez le nombre total de vols prévus pour partir après le 3 juillet 2024.

      nbr_vols
      2

4. Réaliser les requêtes de filtrage et tri suivantes pour obtenir les résultats ci-dessous :
   1. Obtenez la liste des avions de type Airbus, triée par capacité décroissante.

      modele	capacite
      Airbus A380	853
      Airbus A320	180

   2. Listez les compagnies aériennes dont le nom contient le mot ‘Air’

      nom	pays_origine
      Air France	France
      Swiss Air	Suisse

   3. Affichez les 3 premiers vols à partir en ordre de heure de départ.

      destination	date_depart	heure_depart
      New York	2024-07-02	09:00:00
      Porto	2024-07-04	12:00:00
      Tokyo	2024-07-01	13:00:00

   4. Sélectionnez les vols à destination de 'Londres' et triez-les par date de départ, du plus futur au plus ancien.

      destination	date_depart	heure_depart
      Londres	2024-07-06	16:00:00
      Londres	2024-07-03	16:00:00

5. Réaliser les requêtes de jointure suivantes pour obtenir les résultats ci-dessous :
   1. Affichez le nom des passagers et le nom de la compagnie aérienne pour tous les vols à destination de Tokyo.

      nom	prenom	compagnie_aerienne
      Dupont	Jean	Air France
      Müller	Fritz	Air France

   2. Affichez les noms des passagers et leurs destinations de vol pour tous les vols partant le 1er juillet 2024.

      nom	prenom	destination
      Dupont	Jean	Tokyo
      Müller	Fritz	Tokyo

   3. Pour chaque vol à destination de 'Londres', listez le modèle de l'avion et la capacité de celui-ci.

      destination	modele	capacite
      Londres	Airbus A380	853
      Londres	Boeing 747	366

   4. Identifiez les passagers qui voyagent sur des vols opérés par 'Lufthansa', en affichant leur nom, prénom, et la destination du vol.

      nom	prenom	destination
      Müller	Fritz	New York
      Rossi	Luca	New York

   5. Trouvez tous les vols n'ayant pas de passagers inscrits et affichez leur destination et la date de départ.

      destination	date_depart
      Porto	2024-08-03
      Londres	2024-07-06