# Rapport de projet module 106

## Introduction
> Ce rapport documente la réalisation du projet du module 106, axé sur l'administration et la gestion de bases de données relationnelles. 
> L'objectif était de concevoir, implémenter et administrer la base de données de l'aéroport fictif **SkyConnect**.

## Base de données
### Conception de la base de données
> Dans un premier temps, nous avons réalisé la conception de la base de données sur Looping, en nous basant sur le contexte défini dans le cahier des charges.
> Lors de la phase de validation, le professeur a validé notre schéma initial, puis nous a transmis une nouvelle structure de base de données pleinement compatible avec les données à intégrer.

### Schema MCD et MLD
|MCD|![MCD](annexes/mcd.png)|
|---|---|
|MLD|![MLD](annexes/mld.png)|

### Création de la base de données 
> La base de données a été créée à partir du fichier Looping que le professeur nous a donné.
> J'ai simplement copié le script LDD généré par Looping et je l'ai collé dans le terminal connecté à la base de données.

### Chargement des données

> Pour réaliser ce projet nous avons fait recours à docker pour disposer d'un environnement d'exécution pour MySQL. 
> J'ai utilisé le conteneur docker MySQL lancé avec la commande `docker run mysql`.
>
> La prochaine étape est de copier les données dans le repertoire de confiance de MySQL : 
> ```docker cp C:\Users\%username%\Downloads\exports_tsv_final\* db:/var/lib/mysql-files/```
> 
> Maintenant que les données sont prêtes on va pouvoir commencer l'import des données dans un ordre précis pour ne pas avoir de problème avec les clés étrangères. Les tables qui n'ont aucune clé étrangère seront toujours les premières à être importées.


##### Importation des données dans la table `t_passager`
```sql 
LOAD DATA INFILE '/var/lib/mysql-files/t_passager.tsv' 
INTO TABLE  t_passager 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS;
```
```sql
Query OK, 36095 rows affected (0.106 sec)
Records: 36095  Deleted: 0  Skipped: 0  Warnings: 0
```

#### Importation des données dans la trable `t_aeroport`
> REMARQUE : 
> La dernière ligne de cette requete permet de :
>  1. Créer une variable sur `code_iata`.
>  2. Assigner la valeur NULL si la valeur string est égale à "NULL".
>
> Ce qui va permettre de pouvoir importer une donnée avec 4 char alors que la colonne `code_iata` n'accepte que 3 char.
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_aeroport.tsv' 
INTO TABLE t_aeroport 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES 
(aeroport_id, @code_iata, code_icao, nom)SET code_iata = NULLIF(@code_iata, 'null');
```
```sql
Query OK, 9854 rows affected (0.067 sec)
Records: 9854  Deleted: 0  Skipped: 0  Warnings: 0
```

##### Importation des données dans la trable `t_compagnie`
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_compagnie.tsv' 
INTO TABLE t_compagnie 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;
```
```sql
Query OK, 110 rows affected (0.004 sec)
Records: 110  Deleted: 0  Skipped: 0  Warnings: 0
```

##### Importation des données dans la trable `t_type_avion`
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_type_avion.tsv' 
INTO TABLE t_type_avion 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;
```
```sql
Query OK, 342 rows affected (0.013 sec)
Records: 342  Deleted: 0  Skipped: 0  Warnings: 0
```

##### Importation des données dans la trable `t_avion`
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_avion.tsv' 
INTO TABLE t_avion 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;
```
```sql
Query OK, 3950 rows affected (0.025 sec)
Records: 3950  Deleted: 0  Skipped: 0  Warnings: 0
```

##### Importation des données dans la trable `t_programme_vol`
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_programme_vol.tsv' 
INTO TABLE t_programme_vol 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;
```
```sql
Query OK, 9614 rows affected (0.158 sec)
Records: 9614  Deleted: 0  Skipped: 0  Warnings: 0
```

##### Importation des données dans la trable `t_vol`
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_vol.tsv' 
INTO TABLE  t_vol 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS;
```
```sql
Query OK, 321911 rows affected (2.249 sec)
Records: 321911  Deleted: 0  Skipped: 0  Warnings: 0
```

##### Importation des données dans la trable `t_reservation`
```sql
LOAD DATA INFILE '/var/lib/mysql-files/t_reservation.tsv' 
INTO TABLE t_reservation 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;
```
```sql
Query OK, 5455883 rows affected (29.077 sec)
Records: 5455883  Deleted: 0  Skipped: 0  Warnings: 0
```


## Requêtes

##### Requête 1
```sql
SELECT *
FROM t_avion
WHERE compagnie_fk = 60
ORDER BY capacite DESC
LIMIT 2;
```

```
+----------+----------+--------------+---------------+
| avion_id | capacite | compagnie_fk | type_avion_fk |
+----------+----------+--------------+---------------+
|       11 |      380 |           60 |            89 |
|       20 |      380 |           60 |            89 |
+----------+----------+--------------+---------------+
2 rows in set (0.006 sec)
```

##### Requête 2
```sql
SELECT *
FROM t_vol
ORDER BY heure_depart DESC
LIMIT 3;
```

```
+--------+--------------+---------------+----------+--------------+---------------------+--------------------+---------------+
| vol_id | heure_depart | heure_arrivee | avion_fk | compagnie_fk | aeroport_arrivee_fk | aeroport_depart_fk | numero_vol_fk |
+--------+--------------+---------------+----------+--------------+---------------------+--------------------+---------------+
|    680 | 23:59:00     | 13:09:00      |      352 |           16 |                3913 |               5615 | CH2311        |
|    955 | 23:59:00     | 15:17:00      |     4024 |           22 |                2016 |              12744 | DA6839        |
|   4292 | 23:59:00     | 01:17:00      |     3630 |           99 |                1549 |              10840 | TU4730        |
+--------+--------------+---------------+----------+--------------+---------------------+--------------------+---------------+
3 rows in set (0.118 sec)
```

##### Requête 3
```sql
SELECT compagnie_fk, COUNT(*) AS nombre_de_vols
FROM t_vol
GROUP BY compagnie_fk
ORDER BY nombre_de_vols DESC;
```

```
+--------------+----------------+
| compagnie_fk | nombre_de_vols |
+--------------+----------------+
|           76 |           4769 |
|           12 |           3779 |
|          111 |           3772 |
|          105 |           3748 |
|           80 |           3727 |
|           74 |           3673 |
|           99 |           3653 |
|           87 |           3627 |
|           13 |           3592 |
|           55 |           3589 |
|          ... |            ... |
+--------------+----------------+
110 rows in set (0.116 sec)
```

##### Requête 4 
```sql
SELECT
    P.nom,
    P.prenom,
    P.numero_passeport,
    A.nom AS nom_aeroport_depart
FROM
    t_passager AS P
JOIN
    t_reservation AS R ON P.passager_id = R.passager_fk
JOIN
    t_vol AS V ON R.vol_fk = V.vol_id
JOIN
    t_aeroport AS A ON V.aeroport_depart_fk = A.aeroport_id;
```

```
+----------+------------+------------------+---------------------+
| nom      | prenom     | numero_passeport | nom_aeroport_depart |
+----------+------------+------------------+---------------------+
| Rod      | Evans      | P106826          | A.T.D. YILMA INTL   |
| Michael  | Hague      | P107373          | A.T.D. YILMA INTL   |
| Rita     | Tushingham | P104848          | A.T.D. YILMA INTL   |
| Michael  | Anthony    | P108677          | A.T.D. YILMA INTL   |
| Carolina | Chiappetta | P128793          | A.T.D. YILMA INTL   |
| Mike     | Hollis     | P119464          | A.T.D. YILMA INTL   |
| Zack     | Hopkins    | P137755          | A.T.D. YILMA INTL   |
| Eric     | Bledsoe    | P139017          | A.T.D. YILMA INTL   |
| Darren   | Clarke     | P132289          | A.T.D. YILMA INTL   |
| Daniela  | Preisley   | P137518          | A.T.D. YILMA INTL   |
| ...      | ...        | ...              | ...                 |
+----------+------------+------------------+---------------------+
5455883 rows in set (16.492 sec)
```

##### Requête 5 
```sql
SELECT vol_fk, AVG(prix) AS prix_moyen
FROM t_reservation
GROUP BY vol_fk
ORDER BY prix_moyen ASC;
```

```
+--------+------------+
| vol_fk | prix_moyen |
+--------+------------+
| 127444 | 162.421489 |
| 336974 | 162.713235 |
| 291125 | 165.013415 |
| 156280 | 170.002195 |
| 403524 | 172.109118 |
| 393692 | 173.468621 |
| 213748 | 174.344444 |
| 190835 | 175.164750 |
| 249577 | 176.198372 |
|  33249 | 176.622667 |
|    ... |        ... |
+--------+------------+
42714 rows in set (1.633 sec)
```

##### Requête 6 
```sql
SELECT
    A.nom AS nom_aeroport,
    C.nom AS nom_compagnie
FROM
    t_aeroport AS A
LEFT JOIN
    t_compagnie AS C ON A.aeroport_id = C.aeroport_fk;
```

```
+---------------------------+-------------------+
| nom_aeroport              | nom_compagnie     |
+---------------------------+-------------------+
| A CORUNA                  | Spain Airlines    |
| A R S SPORT STRIP         | NULL              |
| A. BOUSSOUF AIN BOUCHEKIF | NULL              |
| A.T.D. YILMA INTL         | Ethiopia Airlines |
| A-306                     | Korea Airlines    |
| A-511 AAF                 | NULL              |
| AALBORG                   | Denmark Airlines  |
| AARHUS                    | NULL              |
| AARS                      | NULL              |
| AASIAAT                   | NULL              |
| ...                       | ...               |
+---------------------------+-------------------+
9854 rows in set (0.010 sec)
```

##### Requête 7 
```sql
SELECT
    C.nom,
    COUNT(A.avion_id) AS nombre_avions
FROM
    t_compagnie AS C
LEFT JOIN
    t_avion AS A ON C.compagnie_id = A.compagnie_fk
GROUP BY
    C.compagnie_id, C.nom
ORDER BY
    nombre_avions DESC; 
```

```
+----------------------+---------------+
| nom                  | nombre_avions |
+----------------------+---------------+
| Australia Airlines   |           466 |
| Croatia Airlines     |           450 |
| Czech Airlines       |           448 |
| Guadeloupe Airlines  |           443 |
| Laos Airlines        |           439 |
| Peru Airlines        |           438 |
| Greece Airlines      |           436 |
| Ghana Airlines       |           430 |
| Isla De Pascua Airli |           400 |
| Gibraltar Airlines   |             0 |
| ...                  |           ... |
+----------------------+---------------+
110 rows in set (0.023 sec)
```

##### Requête 8 
```sql
SELECT compagnie_fk, COUNT(*) AS nombre_vols
FROM t_vol
GROUP BY compagnie_fk
HAVING nombre_vols >= 1500
ORDER BY nombre_vols DESC
LIMIT 5;
```

```
+--------------+-------------+
| compagnie_fk | nombre_vols |
+--------------+-------------+
|           76 |        4769 |
|           12 |        3779 |
|          111 |        3772 |
|          105 |        3748 |
|           80 |        3727 |
+--------------+-------------+
5 rows in set (0.089 sec)
```

##### Requête 9 
```sql
SELECT
    C.nom AS nom_compagnie,
    A.nom AS nom_aeroport_depart,
    COUNT(*) AS nombre_vols
FROM
    t_vol AS V
JOIN
    t_compagnie AS C ON V.compagnie_fk = C.compagnie_id
JOIN
    t_aeroport AS A ON V.aeroport_depart_fk = A.aeroport_id
GROUP BY
    C.nom, A.nom
ORDER BY
    nom_compagnie ASC,
    nombre_vols DESC;
```

```
+----------------------+---------------------+-------------+
| nom_compagnie        | nom_aeroport_depart | nombre_vols |
+----------------------+---------------------+-------------+
| Afghanistan Airlines | TOMBOUCTOU          |          84 |
| Afghanistan Airlines | MESSLA              |          62 |
| Afghanistan Airlines | ASHEVILLE REGL      |          55 |
| Afghanistan Airlines | NEW MINAMIDAITO     |          54 |
| Afghanistan Airlines | PLAGE BLANCHE       |          51 |
| Afghanistan Airlines | FT BRIDGER          |          51 |
| Afghanistan Airlines | VREDENDAL           |          50 |
| Afghanistan Airlines | MC QUESTEN          |          49 |
| Afghanistan Airlines | CASEMENT AB         |          48 |
| Afghanistan Airlines | SHAWNEE MUN         |          48 |
| ...                  | ...                 |         ... |
+----------------------+---------------------+-------------+
9555 rows in set (0.755 sec)
```

##### Requête 10 
```sql
SELECT
    C.nom AS nom_compagnie,
    TA.nom AS nom_type_avion,
    SUM(A.capacite) AS capacite_totale
FROM
    t_avion AS A
JOIN
    t_compagnie AS C ON A.compagnie_fk = C.compagnie_id
JOIN
    t_type_avion AS TA ON A.type_avion_fk = TA.type_avion_id
GROUP BY
    C.nom, TA.nom
ORDER BY
    capacite_totale DESC;
```

```
+--------------------+------------------------+-----------------+
| nom_compagnie      | nom_type_avion         | capacite_totale |
+--------------------+------------------------+-----------------+
| Australia Airlines | Douglas DC-2           |            8372 |
| Australia Airlines | Junkers W 34           |            5796 |
| Australia Airlines | Piper PA-28            |            5796 |
| Australia Airlines | Antonow An-22          |            5796 |
| Croatia Airlines   | Short S.23             |            5460 |
| Australia Airlines | Rolladen Schneider LS8 |            5152 |
| Australia Airlines | Boeing 757             |            5152 |
| Australia Airlines | Lockheed L-1011        |            5152 |
| Australia Airlines | Schempp-Hirth Discus   |            5152 |
| Australia Airlines | Junkers Ju 52/3m       |            5152 |
| ...                | ...                    |             ... |
+--------------------+------------------------+-----------------+
900 rows in set (0.012 sec)
```

## Index

> Comme nous avons vu en classe les index sur les `primary key`, les `foreign key` et les `unique` sont crées automatiquement, nous n'avons donc pas besoin de les créer sur ces types de champ.

##### Création index 1
```sql
CREATE INDEX idx_vol_heure_depart ON t_vol(heure_depart);
```

> Voici les résultats avant et après :
> Avant : `835 rows in set (0.043 sec)` 
> Après : `835 rows in set (0.004 sec)`

##### Création index 2
```sql
CREATE INDEX idx_vol_heure_depart ON t_vol(heure_depart);
```

> Voici les résultats avant et après :
> Avant : `4115 rows in set (0.354 sec)`
> Après : `4115 rows in set (0.265 sec)`

> Ici, l'index est le même car dans les deux `WHERE`, `companie_fk` revient une fois et `heure_depart` revient aux deux endroits. Il y a l'index automatique sur `companie_fk`, `heure_depart` est donc la seule colonne à indexer.


## Utilisateurs et rôles

### Administrateur

##### Creation du rôle
```sql
CREATE ROLE 'role_administrateur'@'localhost';
```

##### Assignation des permissions
```sql
GRANT ALL PRIVILEGES ON db_aeroport.* TO 'role_administrateur'@'localhost' WITH GRANT OPTION;
```

##### Création de l'utilisateur
```sql
CREATE USER 'administrateur'@'localhost' IDENTIFIED BY 'administrateur';
```

##### Assignation du rôle à l'utilisateur
```sql
GRANT 'role_admininistateur'@'localhost' TO 'administrateur'@'localhost';
```

### Gestionnaire de vols

##### Creation du rôle
```sql
CREATE ROLE 'role_gestionnaire_vols'@'localhost';
```

##### Assignation des permissions
```sql
GRANT SELECT, INSERT, UPDATE ON db_aeroport.t_vol TO 'role_gestionnaire_vols'@'localhost';
GRANT SELECT, INSERT, UPDATE ON db_aeroport.t_programme_vol TO 'role_gestionnaire_vols'@'localhost';
GRANT SELECT ON db_aeroport.t_avion TO 'role_gestionnaire_vols'@'localhost';
```

##### Création de l'utilisateur
```sql
CREATE USER 'gestionnaire_vols'@'localhost' IDENTIFIED BY 'gestionnaire_vols';
```

##### Assignation du rôle à l'utilisateur
```sql
GRANT 'role_gestionnaire_vols'@'localhost' TO 'gestionnaire_vols'@'localhost';
```

### Agent de réservation

##### Creation du rôle
```sql
CREATE ROLE 'role_agent_reservation'@'localhost';
```

##### Assignation des permissions
```sql
GRANT SELECT, UPDATE, INSERT ON db_aeroport.t_passager TO 'role_agent_reservation'@'localhost';
GRANT INSERT, UPDATE, INSERT ON db_aeroport.t_reservation TO 'role_agent_reservation'@'localhost';
GRANT SELECT ON db_aeroport.t_vol TO 'role_agent_reservation'@'localhost';
```

##### Création de l'utilisateur
```sql
CREATE USER 'agent_reservation'@'localhost' IDENTIFIED BY 'agent_reservation';
```

##### Assignation du rôle à l'utilisateur
```sql
GRANT 'role_agent_reservation'@'localhost' TO 'agent_reservation'@'localhost';
```

### Agent de comptoir

##### Creation du rôle
```sql
CREATE ROLE 'role_agent_comptoir'@'localhost';
```

##### Assignation des permissions
```sql
GRANT SELECT ON db_aeroport.t_reservation TO 'role_agent_comptoir'@'localhost';
GRANT SELECT ON db_aeroport.t_passager TO 'role_agent_comptoir'@'localhost';
GRANT SELECT ON db_aeroport.t_vol TO 'role_agent_comptoir'@'localhost';
GRANT UPDATE (siege) ON db_aeroport.t_reservation TO 'role_agent_comptoir'@'localhost';
```

##### Création de l'utilisateur
```sql
CREATE USER 'agent_comptoir'@'localhost' IDENTIFIED BY 'agent_comptoir';
```

##### Assignation du rôle à l'utilisateur
```sql
GRANT 'role_agent_comptoir'@'localhost' TO 'agent_comptoir'@'localhost';
```

### Analyste 

##### Creation du rôle
```sql
CREATE ROLE 'role_analyste'@'localhost';
```

##### Assignation des permissions
```sql
GRANT SELECT ON db_aeroport.* TO 'role_analyste'@'localhost';
```

##### Création de l'utilisateur
```sql
CREATE USER 'analyste'@'localhost' IDENTIFIED BY 'analyste';
```

##### Assignation du rôle à l'utilisateur
```sql
GRANT 'role_analyste'@'localhost' TO 'usr_analyste'@'localhost';
```

## Transactions

##### Cas 1
```sql
START TRANSACTION;

DELETE FROM t_reservation
WHERE vol_fk = 25;

DELETE FROM t_vol
WHERE vol_id = 25;

COMMIT;
```

> On commance par supprimer le vol, puis la reservation et on `COMMIT;` les changements;

##### Cas 2
```sql
START TRANSACTION;

SELECT COUNT(*) INTO @seat_taken
FROM t_reservation
WHERE vol_fk = 10 AND siege = '12A';

IF @seat_taken = 0 THEN
    INSERT INTO t_reservation (passager_fk, vol_fk, siege, prix)
    VALUES (5, 10, '12A', 250.00);
    COMMIT;
ELSE
    ROLLBACK;
END IF;
```

> On lance une transaction pour controller si le siège 12A pour le vol 10 est déjà pris, si il n'est pas pris on met 0 dans la variable `@seat_taken` sinon on met 1.
> Si le siège est vide (@seat_taken = 0) alors on y ajoute un passager et on `COMMIT;`, sinon on `ROLLBACK;`

## Prompts IA

J'ai utilisé l'ia pour faire deux prompt : 
 - Le premier pour controller l'orthographe
 - Le 2e noté ici

### Prompt 1
```
reformule ça sur un ton technique

Nous avons d'abord fait la conception de la base de données sur looping à partir du contexte donné dans le cdc. 
Puis au moment de la validation par le professeur, il a d'abord validé la base de données et nous a donné une autre base de données qui est parfaitement compatible avec les données a ajouter.
```

```
Voici une version plus **technique et professionnelle** de ton texte :

> Dans un premier temps, nous avons réalisé la conception de la base de données sur Looping, en nous basant sur le contexte défini dans le cahier des charges.
> Lors de la phase de validation, le professeur a validé notre schéma initial, puis nous a transmis une nouvelle structure de base de données, optimisée et pleinement compatible avec les données à intégrer.

Souhaites-tu que je l’adapte pour un **rapport de projet** (style académique) ou plutôt pour une **présentation technique** (style plus synthétique et orienté résultats) ?
```