Exercices corrigés

Entités et attributs :

• Auteur : idAuteur, nom, prenom
• Livre : idLivre, titre, genre, annee
• Lecteur : idLecteur, nom, prenom, email

Relations :

• Auteur -(1,1)- Ecrit -(0,N)- Livre → un auteur écrit 0 à N livres, un livre a exactement 1 auteur
• Lecteur -(0,N)- Emprunt -(0,N)- Livre → relation N-M avec attribut (dateEmprunt, dateRetour)

MCD :

MLD :

Pourquoi Emprunt a une clé primaire composite ? Un même livre peut être emprunté plusieurs fois (par des lecteurs différents). La clé (idLivre, idLecteur) n'est pas suffisante si on veut autoriser plusieurs emprunts du même livre par le même lecteur. Dans ce cas, on ajouterait dateEmprunt à la clé primaire.

MCD :

MLD :

Explication :

• La relation Département-Salarié est 1-N : elle se traduit par une clé étrangère idDept dans la table Salarie.
• La relation Salarié-Projet est N-M : elle génère une table de liaison TravailleSur.
• L'attribut role appartient à la relation (pas à une entité) : il va dans la table de liaison.

MCD :

[Utilisateur] --(1,N)-- <Poste> --(1,1)-- [Video]
     |                                        |
  <Like>                               <Commente>
     |                                        |
(0,N)                                       (1,N)
                                              |
                                       [Commentaire]
                                        idCom
                                        contenu
                                        dateCom

MLD :

Explication :

• Video porte la clé idUser car un utilisateur poste une vidéo (relation 1-N).
• Commentaire porte deux clés étrangères : vers l'auteur du commentaire et vers la vidéo concernée.
• Like est une table de liaison N-M entre Utilisateur et Video.

Entités et attributs :

• Client : idClient, nom, prenom, email, telephone
• Chambre : idChambre, numero, type, tarifNuit
• Reservation : idReservation, dateArrivee, dateDepart

Relations :

• Client -(1,N)- Effectue -(1,1)- Reservation → un client peut avoir plusieurs réservations, une réservation appartient à un seul client
• Chambre -(1,N)- Concerne -(1,1)- Reservation → une chambre peut être réservée plusieurs fois (à des dates différentes), une réservation porte sur une chambre

MLD :

Pourquoi Reservation est une entité à part et non une table de liaison ? Parce que la relation Client-Chambre n'est pas directe : une réservation a ses propres attributs (dateArrivee, dateDepart, idReservation) et une identité propre. Elle se traduit par une entité, pas seulement une table de liaison.

Entités :

• Utilisateur : idUser, pseudo, email, dateInscription
• Post : idPost, contenu, datePublication

Relations :

• Suit : Utilisateur -(0,N)- suit -(0,N)- Utilisateur → relation réflexive N-M (un utilisateur peut en suivre plusieurs, et être suivi par plusieurs)
• Publie : Utilisateur -(1,N)- publie -(1,1)- Post → relation 1-N
• Like : Utilisateur -(0,N)- like -(0,N)- Post → relation N-M

MLD :

Point clé, relation réflexive : La table Abonnement référence deux fois Utilisateur avec deux noms différents (idSuiveur = celui qui suit, idSuivi = celui qui est suivi). Les deux colonnes pointent vers la même table mais ont des rôles distincts.

-- Création des tables
CREATE TABLE Etudiant (
  idEtudiant   INT PRIMARY KEY AUTO_INCREMENT,
  nom          VARCHAR(50) NOT NULL,
  prenom       VARCHAR(50) NOT NULL,
  dateNaissance DATE
);

CREATE TABLE Cours (
  idCours      INT PRIMARY KEY AUTO_INCREMENT,
  intitule     VARCHAR(100) NOT NULL,
  volumeHoraire INT CHECK (volumeHoraire > 0)
);

CREATE TABLE Inscription (
  idEtudiant INT,
  idCours    INT,
  note       DECIMAL(4,2),
  PRIMARY KEY (idEtudiant, idCours),
  FOREIGN KEY (idEtudiant) REFERENCES Etudiant(idEtudiant) ON DELETE CASCADE,
  FOREIGN KEY (idCours)    REFERENCES Cours(idCours)       ON DELETE RESTRICT
);

-- Insertion des données
INSERT INTO Etudiant (nom, prenom, dateNaissance) VALUES
  ('Durand', 'Claire', '2001-03-25'),
  ('Martin', 'Lucas',  '2000-07-14'),
  ('Lefevre', 'Sophie', '2002-11-02'),
  ('Bernard', 'Tom',   '2001-09-10');

INSERT INTO Cours (intitule, volumeHoraire) VALUES
  ('Mathematiques', 30),
  ('Physique',      24),
  ('Informatique',  36);

INSERT INTO Inscription VALUES
  (1, 1, 14.5),
  (1, 3, 16.0),
  (2, 1, 11.0),
  (2, 2,  9.5),
  (3, 2, 13.0),
  (4, 3, 12.0);

-- Etudiants inscrits à "Mathematiques"
SELECT e.nom, e.prenom
FROM Etudiant e
JOIN Inscription i ON e.idEtudiant = i.idEtudiant
JOIN Cours c       ON i.idCours    = c.idCours
WHERE c.intitule = 'Mathematiques';

Résultat attendu :

Explication : On crée les tables avec leurs contraintes (ON DELETE CASCADE supprime les inscriptions si un étudiant est supprimé). Deux jointures successives permettent de relier Etudiant → Inscription → Cours, puis on filtre sur l'intitulé.

Résultat attendu :

Explication : Le WHERE filtre les lignes de la table Inscription avant le tri. Le ORDER BY ... DESC place les meilleures notes en premier.

Résultat attendu :

Explication : Le GROUP BY c.intitule regroupe les inscriptions par cours. COUNT(*) compte les lignes de chaque groupe. Sans JOIN, on n'aurait pas accès à intitule.

Résultat attendu :

Explication :

• WHERE ne peut pas filtrer sur AVG() car les agrégats ne sont pas encore calculés à ce stade.
• HAVING s'applique après le GROUP BY et peut utiliser des fonctions d'agrégation.
• Physique est exclu : sa moyenne est (9.5 + 13.0) / 2 = 11.25, soit inférieure à 12 mais supérieure à 11, vérifiez avec vos données exactes.

Solution 1, avec NOT IN :

Solution 2, avec LEFT JOIN (recommandée) :

Explication : La LEFT JOIN conserve tous les cours, même ceux sans inscription. Pour ces derniers, i.idCours vaut NULL. Filtrer sur IS NULL isole les cours sans inscrits.

Cette approche est préférable à NOT IN car elle gère correctement les NULL et est généralement plus performante sur de grandes tables.

Points importants :

• Le WHERE dans UPDATE est indispensable, sans lui, tous les étudiants seraient renommés.
• ON DELETE CASCADE sur la clé étrangère idEtudiant dans Inscription garantit que les inscriptions orphelines sont supprimées automatiquement lorsqu'un étudiant est supprimé.
• Préférez toujours filtrer sur la clé primaire (idEtudiant) en production pour éviter les ambiguïtés sur les noms en double.

-- 1. Ajout de la colonne mention dans Inscription
ALTER TABLE Inscription
ADD COLUMN mention VARCHAR(20) DEFAULT 'Passable';

-- 2. Ajout de la contrainte UNIQUE sur email
--    (on suppose que la colonne email existe déjà)
ALTER TABLE Etudiant
ADD CONSTRAINT uq_email UNIQUE (email);

-- 3. Suppression de la colonne dateNaissance
ALTER TABLE Etudiant
DROP COLUMN dateNaissance;

Points importants :

• ADD COLUMN ajoute une colonne avec ses contraintes (ici DEFAULT).
• ADD CONSTRAINT nomme explicitement la contrainte pour pouvoir la supprimer plus tard avec DROP CONSTRAINT uq_email.
• DROP COLUMN est irréversible, les données de cette colonne sont perdues. Toujours sauvegarder avant.

Points importants :

• LIKE 'D%' : le % remplace n'importe quelle suite de caractères. _ remplace un seul caractère.
• BETWEEN 25 AND 40 est inclusif : équivalent à >= 25 AND <= 40.
• note = NULL ne fonctionne pas, il faut toujours utiliser IS NULL ou IS NOT NULL pour les valeurs nulles.

π_{nom, prenom}(
  σ_{intitule = 'Mathematiques'}(
    Etudiant ⨝_{Etudiant.idEtudiant = Inscription.idEtudiant}
    Inscription ⨝_{Inscription.idCours = Cours.idCours}
    Cours
  )
)

Lecture de l'expression (intérieur → extérieur) :

1. Les trois tables sont jointes sur leurs clés communes.
2. On filtre les lignes où intitule = 'Mathematiques'.
3. On ne garde que les colonnes nom et prenom.

Traduction SQL :

π_{intitule}(
  σ_{nom = 'Durand'}(
    Etudiant ⨝ Inscription ⨝ Cours
  )
)

Note : Si plusieurs étudiants ont le nom "Durand", tous leurs cours seront affichés. En pratique, on préférerait filtrer sur idEtudiant pour lever l'ambiguïté.

Traduction SQL :

π_{nom, prenom}(
  σ_{note < 10}(
    Etudiant ⨝_{Etudiant.idEtudiant = Inscription.idEtudiant} Inscription
  )
)

Ici, il n'est pas nécessaire de joindre Cours car on ne filtre pas sur un attribut du cours.

Traduction SQL :

π_{nom, prenom}(
  Etudiant ⨝ (
    π_{idEtudiant, idCours}(Inscription) ÷ π_{idCours}(Cours)
  )
)

Explication de la division :

π_{idEtudiant, idCours}(Inscription) ÷ π_{idCours}(Cours) retourne les idEtudiant pour lesquels il existe une inscription pour chaque idCours de Cours.

Traduction SQL (avec NOT EXISTS) :

On cherche les étudiants pour lesquels il n'existe pas de cours auquel ils ne seraient pas inscrits.

π_{idEtudiant}(σ_{intitule = 'Mathematiques'}(Inscription ⨝ Cours))
∪
π_{idEtudiant}(σ_{intitule = 'Physique'}(Inscription ⨝ Cours))

Propriété de l'union : L'union supprime les doublons. Un étudiant inscrit aux deux cours n'apparaîtra qu'une fois dans le résultat.

Traduction SQL :

Alternative avec IN :

π_{idEtudiant}(σ_{intitule = 'Mathematiques'}(Inscription ⨝ Cours))
−
π_{idEtudiant}(σ_{intitule = 'Physique'}(Inscription ⨝ Cours))

Lecture : On prend l'ensemble des étudiants en Maths, et on lui soustrait l'ensemble des étudiants en Physique. Seuls ceux qui ne sont que en Maths restent.

Traduction SQL :

Alternative avec NOT IN (compatible MySQL qui ne supporte pas EXCEPT) :

         π_{nom, prenom}
                |
    σ_{dateNaissance < '2002-01-01'}
                |
           Etudiant

Lecture (bas → haut) :

1. Etudiant, on part de la table entière.
2. σ_{dateNaissance < '2002-01-01'}, on filtre les étudiants nés avant 2002.
3. π_{nom, prenom}, on ne garde que les colonnes nom et prénom.

Pourquoi ce sens ? La sélection avant la projection est obligatoire ici : si on projetait d'abord, la colonne dateNaissance disparaîtrait et la sélection serait impossible.

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

Note : LIKE 'M%' en algèbre relationnelle s'écrit avec la même notation de sélection. La condition reste une condition sur les attributs de la relation.

      π_{intitule}
            |
  σ_{volumeHoraire >= 20 ∧ volumeHoraire <= 40}
            |
          Cours

Équivalence : BETWEEN 20 AND 40 est équivalent à >= 20 AND <= 40. En algèbre relationnelle, on utilise le symbole ∧ (et logique) pour combiner les conditions dans σ.

Arbre non optimisé :

              π_{nom, intitule}
                     |
          σ_{intitule = 'Informatique'}
                     |
         ⨝ (Inscription.idCours = Cours.idCours)
                /              \
⨝ (Etudiant.idEtudiant = Inscription.idEtudiant)   Cours
           /         \
       Etudiant   Inscription

Arbre optimisé (sélection poussée vers les feuilles) :

              π_{nom, intitule}
                     |
         ⨝ (Inscription.idCours = Cours.idCours)
                /                     \
⨝ (Etudiant.idEtudiant = Inscription.idEtudiant)   σ_{intitule = 'Informatique'}
           /         \                                           |
       Etudiant   Inscription                                  Cours

Pourquoi l'optimisation est meilleure ? En filtrant Cours sur intitule = 'Informatique' avant la jointure, on réduit la taille de la table Cours à 1 ligne. Les jointures suivantes portent sur un ensemble beaucoup plus petit, ce qui accélère l'exécution.

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

Notation : On utilise γ (gamma) pour représenter l'opération d'agrégation (GROUP BY + fonction d'agrégation). La syntaxe est γ_{colonnes de groupe ; agrégats}.

Ordre de lecture (bas → haut) :

1. Jointure Cours × Inscription sur les clés communes.
2. Agrégation par intitule avec calcul du COUNT(*).
3. Filtrage des groupes (HAVING) : on ne garde que ceux avec au moins 2 inscrits.
4. Projection sur les colonnes utiles.

Explication : On remonte la chaîne de jointures depuis l'employé jusqu'au client via la table de liaison TravailleSur. Le DISTINCT évite les doublons : un employé peut travailler sur plusieurs projets parisiens mais ne doit apparaître qu'une fois.

Alternative avec sous-requête :

Lecture de l'intérieur vers l'extérieur :

1. La sous-requête la plus interne sélectionne les idEtudiant des étudiants nés après 2001.
2. La sous-requête intermédiaire trouve les idCours auxquels ces étudiants sont inscrits.
3. La requête externe récupère les intitulés de ces cours.

Attention : Cette requête retourne les cours qui ont AU MOINS UN inscrit né après 2001. Pour retourner les cours suivis UNIQUEMENT par des étudiants nés après 2001 (sans aucun étudiant né avant), la requête serait plus complexe.

Solution avec NOT EXISTS (portable, gère les NULL) :

Solution avec EXCEPT (PostgreSQL, SQL Server) :

Logique de la double négation :

"Un étudiant est inscrit à tous les cours" équivaut à : "Il n'existe aucun cours auquel cet étudiant n'est pas inscrit"

Cette formulation en double négation (NOT EXISTS + cours NOT IN inscriptions) est la traduction directe de la division relationnelle en SQL.

1. Note maximale par cours :

2. Cours dont la moyenne est inférieure à la moyenne générale :

La sous-requête (SELECT AVG(note) FROM Inscription) calcule la moyenne sur toutes les inscriptions. Le HAVING compare chaque groupe à cette valeur globale.

3. Étudiant avec la meilleure note :

Si plusieurs étudiants ont la même note maximale, tous seront affichés.

Pourquoi c'est une sous-requête corrélée ? La sous-requête SELECT MAX(i2.note) ... WHERE i2.idCours = i.idCours fait référence à i.idCours de la requête externe. Elle est réévaluée pour chaque ligne de la requête principale. C'est plus lent qu'une sous-requête simple, mais nécessaire ici.

Résultat attendu :

Alternative avec WITH (CTE) pour la lisibilité :

Solution 1, avec IN :

Solution 2, avec EXISTS :

Solution 3, avec JOIN + DISTINCT :

Comparaison des trois approches :

Règle pratique : Préférez EXISTS quand vous voulez juste vérifier l'existence, JOIN quand vous avez besoin de colonnes de la table jointe.