Pgsql - Reservation De Vols

Absolument ! Puisque vous souhaitez explorer des fonctionnalités avancées spécifiques à PostgreSQL, cet exercice portera sur la gestion d’un système de réservation de vols. Nous allons intégrer des triggers, des fonctions en PL/pgSQL et d’autres concepts avancés.

Attention : Cet exercice est conçu pour PostgreSQL et certaines fonctionnalités mentionnées (comme PL/pgSQL) ne sont pas directement disponibles dans SQLite. Assurez-vous d’avoir un environnement PostgreSQL configuré pour le réaliser.

1. Modèle Logique des Données (MLD) en ER Diagram

erDiagram
    PASSAGER ||--o{ RESERVATION : effectue
    VOL ||--o{ RESERVATION : concerne
    AEROPORT ||--o{ VOL : est_origine
    AEROPORT ||--o{ VOL : est_destination
    COMPAGNIE_AERIENNE ||--o{ VOL : est_opere_par
    RESERVATION {
        INTEGER id_reservation SERIAL PRIMARY KEY
        INTEGER id_passager INTEGER REFERENCES PASSAGER(id_passager)
        INTEGER id_vol INTEGER REFERENCES VOL(id_vol)
        DATE date_reservation DATE NOT NULL DEFAULT CURRENT_DATE
        INTEGER nombre_passagers INTEGER NOT NULL CHECK (nombre_passagers > 0)
        VARCHAR statut VARCHAR(20) NOT NULL DEFAULT 'Confirmée'
    }
    PASSAGER {
        INTEGER id_passager SERIAL PRIMARY KEY
        VARCHAR nom VARCHAR(100) NOT NULL
        VARCHAR prenom VARCHAR(100) NOT NULL
        VARCHAR email VARCHAR(100) UNIQUE NOT NULL
        VARCHAR telephone VARCHAR(20)
        DATE date_naissance DATE
    }
    VOL {
        INTEGER id_vol SERIAL PRIMARY KEY
        INTEGER id_aeroport_depart INTEGER REFERENCES AEROPORT(id_aeroport)
        INTEGER id_aeroport_arrivee INTEGER REFERENCES AEROPORT(id_aeroport)
        INTEGER id_compagnie INTEGER REFERENCES COMPAGNIE_AERIENNE(id_compagnie)
        TIMESTAMP heure_depart TIMESTAMP WITHOUT TIME ZONE NOT NULL
        TIMESTAMP heure_arrivee TIMESTAMP WITHOUT TIME ZONE NOT NULL
        INTEGER capacite INTEGER NOT NULL CHECK (capacite > 0)
        NUMERIC prix NUMERIC(10, 2) NOT NULL CHECK (prix > 0)
    }
    AEROPORT {
        INTEGER id_aeroport SERIAL PRIMARY KEY
        VARCHAR code_iata VARCHAR(3) UNIQUE NOT NULL
        VARCHAR nom_aeroport VARCHAR(200) NOT NULL
        VARCHAR ville VARCHAR(100) NOT NULL
        VARCHAR pays VARCHAR(100) NOT NULL
    }
    COMPAGNIE_AERIENNE {
        INTEGER id_compagnie SERIAL PRIMARY KEY
        VARCHAR nom_compagnie VARCHAR(200) UNIQUE NOT NULL
        VARCHAR code_icao VARCHAR(3) UNIQUE
    }

2. Création de la Base de Données et des Tables (À faire par vous - en PostgreSQL)

Créez une nouvelle base de données nommée reservations_vols dans PostgreSQL et exécutez les instructions SQL pour créer les tables basées sur le diagramme ER ci-dessus. Assurez-vous de définir les clés primaires, les clés étrangères et les contraintes (NOT NULL, UNIQUE, CHECK) correctement. Utilisez SERIAL pour les colonnes d’auto-incrémentation.

3. Insertion de Données (Instructions et Données)

Insérez les données suivantes dans les tables que vous avez créées.

Table COMPAGNIE_AERIENNE:

nom_compagnie	code_icao
Air Sénégal	CSE
Transavia	TVF
Brussels Airlines	BEL

Table AEROPORT:

code_iata	nom_aeroport	ville	pays
DSS	Aéroport International Blaise Diagne	Diass	Sénégal
CDG	Aéroport Paris-Charles de Gaulle	Paris	France
BRU	Aéroport de Bruxelles-National	Bruxelles	Belgique
AMS	Aéroport d’Amsterdam-Schiphol	Amsterdam	Pays-Bas

Table PASSAGER:

nom	prenom	email	telephone	date_naissance
Diouf	Khadim	[adresse e-mail supprimée]	77 777 77 77	1985-05-10
Dubois	Sophie	[adresse e-mail supprimée]	06 06 06 06 06	1992-11-22
Janssens	Pieter	[adresse e-mail supprimée]	02 222 22 22	1988-03-01

Table VOL:

id_aeroport_depart	id_aeroport_arrivee	id_compagnie	heure_depart	heure_arrivee	capacite	prix
1	2	1	2025-04-15 10:00:00	2025-04-15 16:00:00	150	350.00
2	3	2	2025-04-20 08:00:00	2025-04-20 09:30:00	180	120.50
3	4	3	2025-05-01 14:00:00	2025-05-01 15:15:00	100	180.75
1	3	1	2025-05-10 18:00:00	2025-05-11 00:30:00	120	420.20

Table RESERVATION:

id_passager	id_vol	nombre_passagers
1	1	2
2	2	1
3	3	1

Instructions pour l’insertion (À faire par vous - en PostgreSQL) :

Utilisez l’instruction INSERT INTO pour ajouter ces données dans les tables correspondantes.

4. Fonctionnalités Avancées (À faire par vous - en PostgreSQL)

a) Triggers:

Trigger pour la mise à jour de la capacité restante d’un vol :
- Créez un trigger qui s’exécute AFTER INSERT sur la table RESERVATION.
- Ce trigger doit décrémenter la capacite de la table VOL correspondant à la réservation, en fonction du nombre_passagers réservés.
- Créez un trigger similaire qui s’exécute AFTER DELETE sur la table RESERVATION pour incrémenter la capacite du vol.
- Créez un trigger AFTER UPDATE sur RESERVATION qui ajuste la capacite du vol en tenant compte de l’ancienne et de la nouvelle valeur de nombre_passagers.
Trigger pour empêcher la surréservation :
- Créez un trigger qui s’exécute BEFORE INSERT sur la table RESERVATION.
- Ce trigger doit vérifier si le nombre de passagers à réserver (NEW.nombre_passagers) ne dépasse pas la capacité restante du vol (VOL.capacite - (somme des nombre_passagers des réservations existantes pour ce vol)).
- Si la réservation entraîne une surréservation, le trigger doit empêcher l’insertion (en lançant une exception).

b) Fonctions en PL/pgSQL:

Fonction pour obtenir le nombre total de réservations pour un vol donné :
- Créez une fonction en PL/pgSQL qui prend en entrée l’id_vol et renvoie le nombre total de passagers réservés pour ce vol.
Fonction pour calculer le prix total d’une réservation :
- Créez une fonction en PL/pgSQL qui prend en entrée l’id_reservation et renvoie le prix total de la réservation (prix du vol * nombre de passagers).
Fonction pour vérifier si un passager a déjà une réservation pour un vol à une date donnée :
- Créez une fonction en PL/pgSQL qui prend en entrée l’id_passager, l’id_vol et une date_vol (extraite de VOL.heure_depart) et renvoie TRUE si le passager a déjà une réservation pour ce vol à cette date, FALSE sinon.

c) Vues:

Vue affichant les détails des vols avec les noms des aéroports de départ et d’arrivée, et le nom de la compagnie aérienne :
- Créez une vue qui joint les tables VOL, AEROPORT (deux fois avec des alias), et COMPAGNIE_AERIENNE pour afficher des informations conviviales sur les vols.
Vue affichant les réservations avec les noms et prénoms des passagers, les détails du vol (aéroports, heures), et le nombre de passagers :
- Créez une vue qui joint les tables RESERVATION, PASSAGER, et la vue créée à l’étape 6 pour fournir une vue complète des réservations.

d) Index:

Créez des index pertinents pour optimiser les requêtes courantes, par exemple sur les clés étrangères (id_passager, id_vol), les colonnes de recherche fréquentes (email dans PASSAGER, code_iata dans AEROPORT), et les colonnes utilisées dans les clauses WHERE des requêtes que vous anticiperez.

e) Transactions:

Écrivez un bloc de code (en utilisant un client SQL comme psql) qui effectue les opérations suivantes dans une seule transaction :
- Insérer un nouveau passager.
- Créer une nouvelle réservation pour ce passager sur un vol existant.
- Mettre à jour le statut de la réservation à ‘Confirmée’.
- Simulez une erreur après l’insertion du passager mais avant la création de la réservation et observez si le ROLLBACK fonctionne correctement.

f) Gestion des erreurs et exceptions dans PL/pgSQL:

Modifiez la fonction de vérification de surréservation (question 2) pour lancer une exception personnalisée avec un message informatif en cas de tentative de surréservation.

g) Sécurité (Concepts de base):

Créez un nouvel utilisateur (rôle) PostgreSQL avec des droits de lecture seule sur les tables VOL et AEROPORT.
Accordez à cet utilisateur les droits d’exécution sur les fonctions PL/pgSQL que vous avez créées pour obtenir des informations sur les vols.

5. Requêtes SQL Avancées (Beaucoup ! - en PostgreSQL)

Maintenant, exécutez une série de requêtes SQL avancées pour interroger votre base de données, en tirant parti des fonctionnalités que vous avez implémentées.

Affichez tous les détails des vols (en utilisant la vue créée).
Affichez toutes les réservations avec les noms des passagers et les détails des vols (en utilisant la vue créée).
Trouvez le nombre total de passagers réservés pour le vol avec id_vol = 1 (en utilisant la fonction PL/pgSQL).
Calculez le prix total de la réservation avec id_reservation = 1 (en utilisant la fonction PL/pgSQL).
Vérifiez si le passager avec id_passager = 1 a une réservation pour le vol avec id_vol = 1 le 2025-04-15 (en utilisant la fonction PL/pgSQL).
Affichez les vols avec leur capacité restante (calculée à partir des réservations).
Trouvez les vols qui sont complets ou presque complets (par exemple, plus de 80% de la capacité réservée).
Affichez les passagers qui ont effectué plus d’une réservation.
Trouvez les aéroports qui sont à la fois des points de départ et d’arrivée de vols.
Affichez les compagnies aériennes et le nombre total de vols qu’elles opèrent.
Trouvez les passagers nés avant une certaine date (par exemple, 1990-01-01).
Affichez les vols dont la durée est supérieure à 3 heures.
Trouvez les réservations effectuées à une date spécifique.
Affichez les vols triés par prix croissant.
Trouvez le vol le moins cher et le vol le plus cher.
Affichez le nombre moyen de passagers par réservation.
Trouvez les passagers dont le numéro de téléphone est renseigné.
Affichez les vols pour une destination spécifique (par exemple, ‘Paris’).
Trouvez les vols opérés par une compagnie aérienne spécifique (par exemple, ‘Air Sénégal’).
Affichez les réservations avec le statut ‘Confirmée’.
Tentez d’insérer une réservation qui entraînerait une surréservation (pour tester votre trigger).
Mettez à jour le nombre de passagers d’une réservation pour voir si le trigger de mise à jour de la capacité fonctionne.
Supprimez une réservation et vérifiez si la capacité du vol correspondant est correctement mise à jour.
Utilisez EXPLAIN sur certaines de vos requêtes pour voir le plan d’exécution et comment les index sont utilisés (ou non).
Écrivez une requête qui utilise une fonction d’agrégation avec une clause FILTER pour compter le nombre de réservations avec plus de deux passagers pour un vol donné.
Utilisez une expression de table commune (CTE) pour trouver les vols avec le nombre moyen de passagers par réservation le plus élevé.
Écrivez une requête qui utilise une fonction de fenêtre pour attribuer un rang aux vols en fonction de leur prix au sein de chaque compagnie aérienne.
Trouvez les passagers qui ont des réservations sur des vols opérés par plus d’une compagnie aérienne.
Affichez les aéroports qui n’ont aucun vol au départ.
Affichez les compagnies aériennes qui n’ont aucun vol à destination d’un aéroport spécifique.

Et ainsi de suite… Continuez à explorer les possibilités offertes par PostgreSQL ! N’hésitez pas à imaginer d’autres scénarios et à écrire des requêtes pour les résoudre. Cet exercice devrait vous donner une solide base pour travailler avec PostgreSQL et ses fonctionnalités avancées. Bonne exploration !

Al AMINE

Explorer

pgsql - reservation de vols

Pgsql - Reservation De Vols

Graph View