Exercices: Bases de données relationnelles et SQL

Un lycée gère ses élèves avec la relation ELEVE, dont la clef primaire est num.

1. Donner le nombre d'attributs et le nombre d'enregistrements de cette relation, puis écrire son schéma relationnel.

2. Indiquer un domaine possible pour l'attribut num et un domaine possible pour l'attribut classe.

3. On exécute la requête suivante.

INSERT INTO ELEVE VALUES (3, 'Roux', 'Paul', 'TG2');

Expliquer pourquoi le système de gestion de bases de données refuse cette insertion.

1. Écrire une requête SQL qui renvoie les noms des élèves de la classe TG1, puis donner son résultat.

Un cinéma de quartier gère la relation FILM.

La durée est exprimée en minutes. Donner le résultat de chacune des requêtes suivantes.

1. SELECT titre FROM FILM WHERE annee = 2018;

2. SELECT titre, duree FROM FILM WHERE realisateur = 'Dubois' ORDER BY duree;

3. SELECT COUNT(*) FROM FILM WHERE annee >= 2018;

4. SELECT AVG(duree) FROM FILM;

5. Écrire une requête qui renvoie les titres des films dont la durée est strictement supérieure à 100 minutes, puis donner son résultat.

Une papeterie utilise la relation PRODUIT suivante (prix en euros).

On exécute successivement les trois requêtes suivantes.

UPDATE PRODUIT SET prix = 1.50 WHERE nom = 'Stylo';

DELETE FROM PRODUIT WHERE stock < 30;

INSERT INTO PRODUIT VALUES (4, 'Règle', 0.90, 60);

1. Donner le contenu de la relation PRODUIT après chacune de ces requêtes.

2. Écrire une requête qui augmente de 10 unités le stock du produit de référence 1.

3. Indiquer, parmi UPDATE, DELETE, INSERT et SELECT, celles qui modifient le contenu de la base.

Une médiathèque utilise une base de données composée de trois relations.

LIVRE(id_livre, titre, auteur)

ADHERENT(id_adh, nom, ville)

EMPRUNT(id_adh, id_livre, date_emprunt)

1. Identifier les clefs étrangères de la relation EMPRUNT et les relations auxquelles elles font référence.

2. Écrire une requête qui renvoie les noms des adhérents ayant emprunté le livre « Germinal », puis donner son résultat trié par ordre alphabétique.

3. Écrire une requête qui renvoie, sans doublon et par ordre alphabétique, les titres des livres empruntés par des adhérents habitant Lyon, puis donner son résultat.

4. Donner le nombre de lignes renvoyées par la requête de la question 3 si l'on supprime le mot-clef DISTINCT.

5. Écrire une requête qui renvoie le nombre total d'emprunts, puis donner son résultat.

Une boutique en ligne stocke ses commandes dans une unique relation.

COMMANDE(num_cmd, client, adresse_client, produit, prix)

1. Repérer les redondances présentes dans cette relation.

2. Pour chacune des situations suivantes, décrire l'anomalie rencontrée.

   a) Mise à jour : le client Martin déménage.

   b) Insertion : on souhaite enregistrer un nouveau client qui n'a encore passé aucune commande.

   c) Suppression : on supprime la commande 103.

3. Proposer un schéma relationnel à trois relations, CLIENT, PRODUIT et COMMANDE, qui supprime ces anomalies. Souligner les clefs primaires et indiquer les clefs étrangères.

4. Indiquer, dans le nouveau schéma, ce que devient la mise à jour de l'adresse de Martin.

Un lycée stocke les notes de ses élèves dans deux relations.

ELEVE(id, nom, classe)

NOTE(id_eleve, matiere, valeur)

Pour chacune des questions suivantes, écrire la requête SQL demandée, puis donner son résultat.

1. Calculer la moyenne des notes de NSI de l'ensemble des élèves. Arrondir au centième.

2. Donner le nom et la note de NSI des élèves de la classe TG1, par ordre décroissant de note.

3. Déterminer le nombre de notes strictement supérieures à 12.

4. Déterminer la meilleure note de mathématiques.

5. Calculer la moyenne des notes de NSI des élèves de la classe TG1.

Une médiathèque gère ses prêts avec les relations suivantes. L'attribut date_retour vaut NULL tant que le livre n'a pas été rendu, et le test se fait avec IS NULL.

LIVRE(id_livre, titre, auteur)

ADHERENT(id_adh, nom, ville)

EMPRUNT(id_adh, id_livre, date_emprunt, date_retour)

1. Écrire une requête qui renvoie le nom de l'adhérent et le titre du livre pour chaque emprunt non rendu, puis donner son résultat trié par nom.

2. Écrire une requête qui renvoie, sans doublon, les noms des adhérents ayant emprunté au moins un livre de Zola, puis donner son résultat trié par ordre alphabétique.

3. Écrire une requête qui enregistre le retour du livre 2 par l'adhérent 2 le 2025-04-01, puis donner le nombre d'emprunts non rendus après cette mise à jour.

4. On exécute INSERT INTO EMPRUNT VALUES (4, 1, '2025-04-02', NULL);. Expliquer pourquoi un système de gestion de bases de données qui vérifie l'intégrité référentielle refuse cette insertion.

Un réseau régional de trains TER est décrit par deux relations. L'attribut duree_min est la durée du trajet en minutes. Les attributs gare_depart et gare_arrivee sont des clefs étrangères qui référencent GARE.

GARE(id_gare, nom, ville)

TRAJET(id_trajet, gare_depart, gare_arrivee, duree_min)

1. Expliquer pourquoi la relation GARE doit être utilisée deux fois dans une requête qui affiche le nom de la gare de départ et le nom de la gare d'arrivée de chaque trajet.

2. Écrire une requête qui renvoie, pour chaque trajet dont la durée est strictement inférieure à 40 minutes, le nom de la gare de départ, le nom de la gare d'arrivée et la durée. On utilisera deux alias de la relation GARE. Donner le résultat par ordre croissant de durée.

3. Donner le résultat de SELECT SUM(duree_min) FROM TRAJET WHERE id_trajet <= 3;.

4. Les trajets 1, 2 et 3 forment un itinéraire de Dijon-Ville à Mâcon-Ville avec arrêts. Comparer sa durée totale à celle du trajet direct 5.

5. Écrire une requête qui supprime le trajet 4, puis donner le nombre de lignes de la relation TRAJET après cette suppression.

Un site de réservation de places de concert enregistre ses données dans un système de gestion de bases de données. La relation PLACE contient notamment une ligne dont l'attribut stock vaut 1 pour la référence 7.

Deux clients, Alice et Bob, cherchent à réserver cette dernière place au même moment. Le programme de chacun exécute les trois étapes suivantes.

1. Lire la valeur de stock pour la référence 7.

2. Si cette valeur est supérieure à 0, calculer stock - 1.

3. Écrire la nouvelle valeur dans la base.

Les opérations s'entrelacent ainsi : Alice exécute l'étape 1, Bob exécute l'étape 1, Alice exécute les étapes 2 et 3, puis Bob exécute les étapes 2 et 3.

1. Donner la valeur de stock à la fin de l'exécution et le nombre de places réellement vendues. Expliquer le problème rencontré.

2. Citer le service d'un système de gestion de bases de données qui a pour rôle d'éviter ce problème.

3. On propose la requête unique suivante.

UPDATE PLACE SET stock = stock - 1 WHERE ref = 7 AND stock > 0;

Expliquer pourquoi son exécution par un système de gestion de bases de données qui garantit l'absence d'accès concurrents conflictuels résout le problème.

1. La relation CLIENT du site contient les attributs nom, prenom, email, date_naissance, adresse, numero_securite_sociale et historique_navigation. Le seul usage prévu est l'envoi des billets par courriel.

   a) Indiquer les attributs qui ne sont pas nécessaires à cet usage.

   b) Justifier ce choix par un principe de minimisation des données personnelles prévu par le RGPD.