Exercices corrigés : modélisation, algèbre et SQL

Partie 1, Modélisation, du MCD au MLD

Ces exercices reprennent Du MCD au modèle relationnel. Pour chaque énoncé, identifier d'abord les entités, puis les associations entre elles et leurs cardinalités.

La faute de cette partie est presque toujours la même : ranger dans une entité un attribut qui appartient à l'association, ou traduire une association N-M par une clé étrangère au lieu d'une table. Les cinq corrigés reviennent dessus sous des habillages différents. Un modèle se discute plus qu'il ne se juge : les corrigés restent repliés, et un cadre de calcul fait d'abord compter ce que le MLD doit contenir.

Exercice 1, Bibliothèque

Une bibliothèque gère des livres, des auteurs et des emprunts.

• Un auteur peut avoir écrit plusieurs livres, et un livre a un seul auteur principal.
• Un lecteur peut emprunter plusieurs livres, et un livre peut être emprunté par plusieurs lecteurs (à des dates différentes).
• Pour chaque emprunt, la bibliothèque enregistre la date d'emprunt et la date de retour prévue.

1. Identifier les entités et leurs attributs.
2. Identifier les associations et leurs cardinalités.
3. Proposer un MCD (notation textuelle).
4. Déduire le MLD correspondant.

Exercice 2, Entreprise et projets

Une entreprise souhaite modéliser ses salariés, départements et projets.

• Chaque salarié appartient à un seul département.
• Un salarié peut travailler sur plusieurs projets.
• Un projet peut impliquer plusieurs salariés.
• L'entreprise veut aussi connaître le rôle de chaque salarié sur un projet (par exemple développeur, chef de projet).

1. Proposer le MCD.
2. Déduire le MLD.
3. Quelle est la nature de l'association Salarié-Projet ? Comment se traduit-elle en MLD ?

Exercice 3, Plateforme vidéo

Modéliser une plateforme de vidéos en ligne avec les règles suivantes :

• Un utilisateur peut poster plusieurs vidéos.
• Un utilisateur peut commenter plusieurs vidéos.
• Chaque commentaire est lié à une vidéo et à un utilisateur.
• Une vidéo peut recevoir plusieurs commentaires.
• La plateforme veut aussi enregistrer les likes (un utilisateur peut liker plusieurs vidéos).

1. Proposer le MCD.
2. Déduire le MLD.

Exercice 4, Hôtel

Un hôtel souhaite gérer ses chambres, ses clients et ses réservations.

• Un client peut effectuer plusieurs réservations.
• Une réservation porte sur une seule chambre.
• Une chambre a un type (simple, double, suite) et un tarif par nuit.
• L'hôtel enregistre pour chaque réservation la date d'arrivée et la date de départ.

1. Identifier les entités et leurs attributs.
2. Identifier les associations et leurs cardinalités.
3. Proposer le MLD.

Exercice 5, Réseau social

Un réseau social permet à des utilisateurs de se suivre mutuellement et de publier des posts.

• Un utilisateur peut suivre plusieurs autres utilisateurs (un abonnement).
• Un utilisateur peut publier plusieurs posts.
• Un post peut recevoir des likes d'autres utilisateurs.
• Le réseau veut savoir qui suit qui et depuis quand.

1. Identifier les entités et les associations (attention : l'association « suit » est réflexive).
2. Proposer le MLD.

Partie 2, Créer, modifier et interroger en SQL

La modélisation étant faite, la suite l'exécute : les mêmes entités deviennent des tables, puis des résultats. Ces exercices reprennent Les requêtes SQL et, pour les comptes et les moyennes des exercices 8 et 9, le début de SQL avancé.

Chaque vérification part de la base du module, celle des chapitres sur l'algèbre et sur le SQL, puis la modifie comme le dit son libellé : un étudiant de plus, une note changée, un cours qui ouvre. Le bouton du schéma, en tête de l'éditeur, en montre les tables. Quand l'énoncé demande un ordre, l'ordre des lignes compte ; sinon, il est libre.

Schéma de référence :

Ce que la base contient : dix étudiants, sept cours, vingt-cinq inscriptions dont deux sans note. Une requête sur les inscriptions qui rend plus de vingt-cinq lignes a dupliqué quelque chose ; une requête qui en rend moins a filtré quelque chose, volontairement ou non.

Exercice 6, Mise en place du schéma

Créer, dans une base vide, une version réduite du schéma, puis la remplir. Trois tables :

• Etudiant : idEtudiant, clé primaire ; nom et prenom, obligatoires ; dateNaissance.
• Cours : idCours, clé primaire ; intitule, obligatoire ; volumeHoraire.
• Inscription : idEtudiant, idCours et note. La clé primaire est le couple. Supprimer un étudiant supprime ses inscriptions ; supprimer un cours qui a des inscrits est refusé.

Les données à insérer :

Sous MySQL, l'identifiant s'écrirait INT PRIMARY KEY AUTO_INCREMENT, le nom VARCHAR(50) et la note DECIMAL(4,2). La page exécute SQLite, où INTEGER PRIMARY KEY s'incrémente seul ; elle signale un AUTO_INCREMENT au lieu de l'exécuter.

Exercice 7, Filtrage sur les notes

Exercice 8, Comptage par cours

Le second critère du ORDER BY départage les ex æquo : sans lui, deux cours à cinq inscrits sortent dans un ordre que rien ne fixe, et qui change d'un moteur à l'autre.

Exercice 9, Agrégats et filtrage de groupes

Sur la base du cours, Reseaux a deux inscrits mais 10.75 de moyenne : les deux conditions ne font pas double emploi, et la deuxième vérification montre un cours écarté par chacune. L'exercice Filtrer les groupes, sur la page Exercices, revient sur le partage entre WHERE et HAVING.

Exercice 10, Ce qui n'existe pas

Une question en « aucun » ne se répond pas en filtrant : les lignes cherchées sont précisément celles qui manquent. La requête doit construire l'absence.

Si la sous-requête rendait une seule valeur NULL, NOT IN ne rendrait plus aucune ligne, sans le moindre message. Ici la clé primaire interdit qu'idCours soit nulle, sous MySQL comme dans la page ; l'exercice 30 montre une colonne qui, elle, contient des NULL. Sans le filtre sur la ville, la requête cherche les cours sans aucun inscrit, et ne rend rien sur la base du cours : un résultat vide est une réponse, pas une panne. L'exercice Aucun inscrit de Paris, sur la page Exercices, pose la même question en variant les données.

Exercice 11, UPDATE et DELETE

Sur la base du module, dont la table Inscription déclare ON DELETE CASCADE vers Etudiant :

1. L'étudiante Sophie Dupond, d'identifiant 3, a changé de nom : elle s'appelle désormais Dupont. Mettre à jour la table.
2. L'étudiant Tom Durand, d'identifiant 4, quitte l'école. Le supprimer ainsi que toutes ses inscriptions.
3. À quelle condition ses inscriptions disparaîtront-elles sans être supprimées une par une ?

La question 3 tient au moteur : une contrainte écrite dans le CREATE TABLE n'agit que s'il la vérifie. MySQL vérifie toujours les clés étrangères de ses tables InnoDB, et la page les a activées pour faire comme lui. Le bloc suivant supprime Sofia Petit, qui a une seule inscription, puis compte les inscriptions restantes.

L'exercice Renommer et retirer, sur la clé, sur la page Exercices, refait ce geste sur deux autres étudiants.

Exercice 12, ALTER TABLE

Sur la base du module :

1. Ajouter une colonne mention de type VARCHAR(20) à la table Inscription, avec la valeur par défaut 'Passable'.
2. Ajouter une contrainte UNIQUE sur la colonne email de la table Etudiant.
3. Supprimer la colonne dateNaissance de la table Etudiant.

La base du module déclare déjà email unique : la question 2 suppose une table qui ne l'est pas encore, et se traite après le bloc, qui vérifie les deux autres.

La question 2. Sous MySQL, une contrainte s'ajoute par ALTER TABLE et se nomme, pour pouvoir la retirer plus tard par DROP CONSTRAINT uq_email :

SQLite, le moteur de la page, ne sait pas ajouter de contrainte à une table existante : il écrit la même règle par un index unique, CREATE UNIQUE INDEX uq_email ON Etudiant(email), que MySQL accepte aussi. Dans les deux écritures, le moteur contrôle d'abord l'existant, et refuse la contrainte si deux lignes se répètent déjà : une contrainte ne se déclare jamais sans avoir cherché les doublons qu'elle va interdire. Plusieurs NULL ne se comptent pas comme des doublons, et les deux étudiants de la base sans email passent.

Exercice 13, LIKE, BETWEEN et IS NULL

Trois requêtes sur le schéma de référence : les noms qui commencent par une lettre, un intervalle de volumes horaires, les notes non saisies.

Les exercices L'email absent et Bornes incluses, sur la page Exercices, reprennent les deux derniers pièges.

Partie 3, Algèbre relationnelle

Exprimer les requêtes suivantes en utilisant les opérateurs σ (sélection), π (projection), ⨝ (jointure), ∪ (union), − (différence), ÷ (division).

Ces exercices reprennent L'algèbre relationnelle et ses arbres. SQL dit ce qu'on veut, l'algèbre dit dans quel ordre l'obtenir, ce que la partie 4 dessinera ensuite.

Les symboles s'écrivent avec la barre placée au-dessus de la zone de saisie ; au clavier, et, ou, non, <=, >= et le tiret conviennent aussi. Une table écrite dans un cas prend la place de la table du cours de même nom, les autres restent. À chaque vérification, l'arbre de l'expression se dessine, et chaque nœud se clique.

Exercice 14, Projection + sélection

L'exercice Sélection puis projection, sur la page Exercices, revient sur cet ordre.

Exercice 15, Sélection sur une table liée

Exercice 16, Sélection avec condition numérique

Exercice 17, Division relationnelle

L'exercice 27 écrit la même division en SQL. L'exercice Division par tous, sur la page Exercices, en pose une autre.

Exercice 18, Union

L'union est ensembliste : un étudiant inscrit aux deux cours n'y figure qu'une fois. SQL la traduit par UNION, qui empile deux SELECT rendant les mêmes colonnes et retire les doublons.

L'étape suivante fait le compte de l'union de travers, à trouver.

Exercice 19, Différence

En SQL, EXCEPT traduit − de la même façon que UNION traduit ∪. SQLite, PostgreSQL et SQL Server le connaissent, et MySQL depuis sa version 8.0.31.

L'exercice Différence pour dire « sans », sur la page Exercices, s'exerce au même opérateur.

Partie 4, Arbres algébriques

Représenter chaque requête SQL sous forme d'arbre algébrique. Rappel : les feuilles sont les tables, les nœuds internes sont les opérations, et la racine est le résultat final. L'arbre se lit de bas en haut.

Ces exercices reprennent, eux aussi, L'algèbre relationnelle et ses arbres. Sur un arbre, la taille de chaque résultat intermédiaire devient visible, et donc le coût. Les deux premiers arbres se vérifient : l'expression écrite dessine le sien. Les trois suivants sortent de ce que le moteur calcule, ou demandent de juger un coût : ils gardent un corrigé replié.

Exercice 20, Arbre simple (sélection + projection)

L'exercice Sélection puis projection, sur la page Exercices, pose le même arbre sur une autre table.

Exercice 21, Arbre avec LIKE

      π_{nom}
         |
  σ_{nom LIKE 'M%'}
         |
      Etudiant

Exercice 22, Arbre avec BETWEEN

Exercice 23, Arbre avec jointure et optimisation

Représenter en arbre la requête suivante, puis proposer une version optimisée :

Exercice 24, Arbre avec agrégation

              π_{intitule, nbInscrits}
                        |
              σ_{COUNT(*) >= 4}    <-- HAVING
                        |
        γ_{idCours, intitule; COUNT(*)}   <-- GROUP BY + agrégat
                        |
         ⨝ (Cours.idCours = Inscription.idCours)
                /               \
             Cours           Inscription

Partie 5, Requêtes complexes et imbriquées

Les quatre parties précédentes portaient sur une seule interrogation à la fois. Celle-ci en emboîte, comme le fait le chapitre SQL avancé : une requête a besoin d'un résultat qu'une autre requête calcule. Avant d'écrire une sous-requête, dire ce qu'elle rend, une valeur, une colonne ou une table. Une valeur se compare avec = ou < ; une colonne permet IN ; EXISTS, lui, accepte n'importe quelle sous-requête reliée à la ligne extérieure, et c'est la forme à préférer dès qu'une négation entre en jeu.

Exercice 25, Jointures multiples

Le schéma des employés et de leurs projets, dans la base du module :

Exercice 26, Sous-requêtes imbriquées

Sur la base du cours, deux étudiantes sont nées après la date, Dupond Sophie et Nguyen Elsa, et leurs cours sont six sur sept : seul Statistiques manque. La requête rend les cours qui ont au moins un tel inscrit ; ceux qui n'ont que de tels inscrits demanderaient une négation, comme à l'exercice suivant.

Exercice 27, Double négation (tous les cours)

Cette double négation est la traduction directe de la division relationnelle de l'exercice 17.

L'exercice Inscrit à tous, sur la page Exercices, reprend cette double négation.

Exercice 28, Analyse statistique

Trois questions, une requête pour chacune : la note maximale de chaque cours ; les cours dont la moyenne est inférieure à la moyenne générale de toutes les notes ; l'étudiant qui a la meilleure note de toute la base.

Les deux moyennes ne coïncident que si tous les cours ont le même nombre de notes ; sur la base, Mathematiques en pèse cinq et Statistiques une seule, son second inscrit n'étant pas noté. L'étape suivante refait le calcul de travers.

L'exercice Au-dessus de la moyenne, sur la page Exercices, compare lui aussi chaque ligne à une valeur que calcule une sous-requête.

Exercice 29, Sous-requête corrélée

Sans le ORDER BY, l'ordre des lignes n'est fixé par rien, et MySQL ne rend pas le même que la page.

L'exercice Le projet principal de chacun, sur la page Exercices, pose la même question sur les employés et leurs projets.

Exercice 30, EXISTS vs IN

IN, EXISTS et la jointure avec DISTINCT rendent ici les mêmes cinq étudiants. Les deux premières ne parcourent qu'Etudiant, et ne peuvent pas produire de doublon ; la troisième le peut.

Les performances se valent le plus souvent : l'optimiseur réécrit les deux formes, et sur cet exercice MySQL exécute IN et EXISTS selon le même plan, que EXPLAIN affiche (voir Exploiter une base). EXISTS sert à vérifier une existence, JOIN quand des colonnes de la table jointe sont nécessaires.

Le piège, en négatif : tant que la question est positive, IN et EXISTS donnent le même résultat ; la négation les sépare. Les trois requêtes suivantes cherchent les inscriptions dont la note ne figure pas parmi celles du cours 2, dont un inscrit n'est pas encore noté.

Elle ne rend aucune ligne, sans erreur ni avertissement. La sous-requête rend quatre notes, 16.5, 14.5, 10.5 et un NULL : note NOT IN (16.5, 14.5, 10.5, NULL) demande d'affirmer que la note diffère d'une valeur inconnue, la condition vaut inconnu, et WHERE ne garde que ce qui est vrai.

Vingt lignes cette fois : le passage de zéro à vingt tient à une seule valeur absente. Une requête juste pendant des mois devient silencieusement vide le jour où une saisie est laissée en blanc. NOT EXISTS résiste, parce qu'il teste l'existence d'une ligne, pas l'égalité avec une valeur :

Vingt-deux lignes : les vingt précédentes, plus les deux inscriptions dont la note est elle-même absente, qu'aucune note du cours 2 n'égale.