Exercice 3 - Bases de données relationnelles et S… · Bac Numérique et Sciences Informatiques Amérique du Nord 2025 (jour 2)

Exercice 3 (8 points)

Cet exercice porte sur les bases de données, la programmation en Python, la récursivité et les algorithmes de parcours de graphes.

Partie A

Dans cette partie, on pourra utiliser les clauses du langage SQL pour:

construire des requêtes d'interrogation à l'aide de SELECT, FROM, WHERE (avec les opérateurs logiques AND, OR), JOIN... ON;

construire des requêtes d'insertion et de mise à jour à l'aide de UPDATE, INSERT, DELETE;

affiner les recherches à l'aide de DISTINCT, ORDER BY.

La compagnie aérienne AirInfo souhaite utiliser un système de gestion de bases de données relationnelles afin de l'aider à gérer les informations dont elle dispose sur les aéroports desservis, les vols proposés, les passagers et les réservations effectuées par ces passagers.

Elle crée donc la base de données compagnie_aerienne, constituée de quatre relations. Le schéma relationnel de cette base de données est le suivant:

aeroport(id_aeroport: TEXT, ville: TEXT, pays: TEXT)

vol(id_vol: TEXT, aeroport_dep: TEXT, aeroport_arr: TEXT, distance: INT)

passager(id_passager: INT, nom: TEXT, prenom: TEXT, ville: TEXT, d_totale: INT)

reservation(id_vol: TEXT, id_passager: INT)

Une clé primaire de la relation vol est l'attribut id_vol. Les autres clés primaires et étrangères ne sont pas précisées.

Les quatre tables ci-après constituent, en l'état, la base de données compagnie_aerienne complète.

Les valeurs des champs distance et d_totale intervenant respectivement dans les tables vol et passager sont exprimées en milliers de kilomètres.

1.

Expliquer pourquoi l'attribut id_vol ne peut pas être une clé primaire de la table reservation.

2.

Proposer une clé primaire pour la table reservation.

3.

Expliquer le rôle d'une clé étrangère dans une relation.

4.

Écrire le résultat renvoyé par la requête SQL suivante:

SELECT id_vol

FROM vol

WHERE aeroport_arr = 'CDG';

5.

Écrire une requête SQL qui donne les noms des villes qui sont destination d'un vol au départ de l'aéroport CDG.

La compagnie AirInfo envisage de récompenser la fidélité de ses usagers en tenant compte de la distance totale qu'ils parcourent sur leurs lignes. Ainsi, à chaque nouvelle réservation, la table passager doit être mise à jour: si l'usager avait déjà parcouru une distance totale $d_{\text{totale}}$, puis réserve un vol d'une distance $d_{\text{vol}}$, l'attribut d_totale est modifié en prenant pour nouvelle valeur $d_{\text{totale}} + d_{\text{vol}}$.

6.

La passagère d'identifiant 5 a déjà parcouru 10 milliers de kilomètres. Elle réserve un vol de Washington (IAD) à Paris (CDG), d'une distance de 6 milliers de kilomètres.

Écrire la requête permettant de mettre à jour la table passager.

La compagnie aérienne offre maintenant la possibilité de relier Paris CDG à Montréal YUL (Canada) par un vol de 6 milliers de kilomètres. Afin de prendre en compte ce nouveau trajet dans la base de données, on souhaite l'ajouter dans la table vol dont la clé primaire est id_vol. On propose, de manière incorrecte, la requête d'insertion ci-après:

INSERT INTO vol

VALUES ('AI0256', 'CDG', 'YUL', 6);

7.

Proposer, en justifiant, une correction relative à l'erreur commise.