1. Rôle des clés étrangères. Elles garantissent l'intégrité référentielle : les valeurs d'une clé étrangère doivent correspondre à une valeur d'une clé candidate d'un tuple de la relation référencée, ou être complètement nulles. Elles matérialisent les associations du modèle E-A et interdisent les liens orphelins — un TRAVERSEE pointant vers un capitaine inexistant est refusé par le moteur.

2. La projection et la cardinalité. Oui, la projection peut réduire la cardinalité : elle supprime les doublons. Si deux tuples ne différaient que par des attributs non projetés, ils deviennent identiques après projection et se confondent en un seul — c'est la sémantique ensembliste de l'opérateur. Pour garantir que la cardinalité reste invariée, il suffit de projeter un ensemble d'attributs qui contient une clé candidate (a fortiori la clé primaire) : chaque tuple reste alors distinguable, donc aucune fusion n'est possible.

3. Compatibilité envers l'union. Les opérations qui la requièrent sont les opérateurs ensemblistes binaires : union, intersection et différence. Elle signifie deux choses : les deux relations ont le même degré, et leurs attributs sont compatibles deux à deux (le i-ème attribut de l'une a le même domaine que le i-ème attribut de l'autre). Les noms d'attributs, eux, peuvent différer. Le produit cartésien, en revanche, n'exige aucune compatibilité : il accepte des schémas quelconques.

4. a) Clés étrangères.

1TRAVERSEE.CAPITAINE -> CAPITAINE.NUMERO2TRAVERSEE.NAVIRE    -> NAVIRE.NUMERO

b) Le nom des capitaines.

1π_NOM ( CAPITAINE )

c) Salaire inférieur à 2000.

1π_NOM ( σ_SALAIRE < 2000 ( CAPITAINE ) )

d) Navires fabriqués avant le 1/1/99 et de capacité supérieure à 100.

1π_NOM ( σ_DATE_FABRICATION < '1/1/99' ∧ CAPACITE > 100 ( NAVIRE ) )

e) Capitaines effectuant une traversée en direction de NICE. « En direction de » désigne la destination, donc VILLE_ARRIVE. On joint après avoir réduit TRAVERSEE à ce qui sert :

1π_NOM ( CAPITAINE ⋈_CAPITAINE.NUMERO = TRAVERSEE.CAPITAINE π_CAPITAINE ( σ_VILLE_ARRIVE = 'NICE' ( TRAVERSEE ) ) )

f) Salaire supérieur à 2500 et effectuant une traversée.

1π_NOM ( σ_SALAIRE > 2500 ( CAPITAINE ) ⋈_CAPITAINE.NUMERO = TRAVERSEE.CAPITAINE π_CAPITAINE ( TRAVERSEE ) )

L'ordre compte ici pour l'efficacité, pas pour le résultat : filtrer le salaire avant la jointure réduit le volume intermédiaire. Les deux écritures sont acceptées, la première est meilleure.

g) Navires de capacité inférieure à 50 ou effectuant une traversée à destination de NICE. C'est le « ou » qui impose l'union, et c'est aussi la question qui teste la compatibilité envers l'union :

1π_NUMERO ( σ_CAPACITE < 50 ( NAVIRE ) ) ∪ π_NAVIRE ( σ_VILLE_ARRIVE = 'NICE' ( TRAVERSEE ) )

Les deux opérandes ont le même degré (1) et des domaines compatibles (un numéro de navire des deux côtés). Une jointure ou une sélection n'aurait pas suffi : aucun navire ne satisfait nécessairement les deux conditions à la fois, il faut la réunion.

h) Capitaines effectuant toutes les traversées au départ de NICE. Question en « tous les » : division.

1R = π_CAPITAINE, NUMERO ( σ_VILLE_DEPART = 'NICE' ( TRAVERSEE ) )2S = π_NUMERO ( σ_VILLE_DEPART = 'NICE' ( TRAVERSEE ) )3π_CAPITAINE ( R ÷ S )

i) Les mêmes, de nom différent de « De Maria ». On compose la division avec une sélection sur CAPITAINE :

1π_NOM ( σ_NOM ≠ 'De Maria' ( CAPITAINE ) ⋈ ( R ÷ S ) )

l) Navires fabriqués après le navire « Casanova ». Il faut d'abord extraire la date de fabrication de « Casanova » dans une relation à un seul attribut renommé, puis comparer :

1C = ρ_DATE_CASANOVA ← DATE_FABRICATION ( π_DATE_FABRICATION ( σ_NOM = 'Casanova' ( NAVIRE ) ) )2π_NOM ( σ_NAVIRE.DATE_FABRICATION > C.DATE_CASANOVA ( NAVIRE × C ) )

Le renommage n'est pas décoratif : sans lui, les deux attributs s'appelleraient DATE_FABRICATION et la condition de comparaison serait ambiguë.

1. Algèbre relationnelle contre SQL. Quatre différences structurelles.

• Deux syntaxes : l'algèbre est symbolique et fonctionnelle, SQL est textuel et en clauses.
• SQL est informatique, l'algèbre est formelle : SQL nomme explicitement les relations et les attributs, l'algèbre raisonne sur des symboles.
• Sacs contre ensembles : sans DISTINCT, une projection SQL conserve les doublons, alors que la projection algébrique les élimine par définition. L'algèbre est ensembliste, SQL est multi-ensembliste.
• Composition : toute opération d'algèbre rend une relation, donc s'imbrique librement (propriété de fermeture). SQL n'offre cette composition que par les sous-requêtes et les CTE.

2. a) Clés étrangères.

1CHAMBRE.nomHôtel                    -> HOTEL.nom2EQUIPE_SERVICE.nomHôtel             -> HOTEL.nom3INTERVENTION.identifiantChambre     -> CHAMBRE.identifiantChambre4INTERVENTION.numéroEquipeService    -> EQUIPE_SERVICE.numéro5RESERVATION.identifiantChambre      -> CHAMBRE.identifiantChambre

Notez que INTERVENTION porte deux clés étrangères et aucune vers HOTEL : l'hôtel est atteignable par transitivité, via la chambre ou via l'équipe.

b) Chambres réservées par « Dupont », avec le nom de l'hôtel.

1π_CHAMBRE.numéro, HOTEL.nom ( σ_nomClient = 'Dupont' ( RESERVATION )2                              ⋈_RESERVATION.identifiantChambre = CHAMBRE.identifiantChambre CHAMBRE3                              ⋈_CHAMBRE.nomHôtel = HOTEL.nom HOTEL )

On filtre d'abord (le client), on joint ensuite : le prédicat le plus sélectif va le plus près possible des feuilles.

c) Équipes intervenues dans toutes les chambres. Division, à nouveau :

1R = π_numéroEquipeService, identifiantChambre ( INTERVENTION )2S = π_identifiantChambre ( CHAMBRE )3π_numéroEquipeService ( R ÷ S )

Une équipe qui n'est jamais intervenue n'apparaît pas dans INTERVENTION : elle ne peut donc pas figurer dans le résultat, ce qui est bien la réponse attendue.

d) Nombre de chambres par hôtel de catégorie « luxe ».

1select h.nom, count(c.identifiantChambre) as nbChambres2from HOTEL h3join CHAMBRE c on c.nomHôtel = h.nom4where h.catégorie = 'luxe'5group by h.nom;

group by h.nom suffit (le nom identifie l'hôtel). Un hôtel de catégorie « luxe » sans chambre n'apparaîtrait pas avec un join : si l'énoncé attend 0, il faut un left join.

e) Chambres jamais réservées.

1select c.identifiantChambre2from CHAMBRE c3where not exists (select 1 from RESERVATION r4                  where r.identifiantChambre = c.identifiantChambre);

Variante équivalente : left join RESERVATION ... where r.numéro is null.

f) La sous-requête est corrélée. Elle référence CHAMBRE.nomHôtel, attribut de la requête externe : elle ne peut pas être évaluée une fois pour toutes, elle doit être réévaluée pour chaque ligne de CHAMBRE, avec une moyenne calculée sur les chambres du même hôtel. C'est ce qui la distingue d'une sous-requête indépendante, qui ne dépend pas de la ligne courante et que le moteur peut calculer une seule fois.

Résultat : la requête renvoie les identifiants des chambres dont la surface est supérieure à la surface moyenne des chambres de leur propre hôtel. Une chambre de 30 m² dans un hôtel dont la moyenne est de 20 m² sort ; la même chambre dans un hôtel dont la moyenne est de 40 m² ne sort pas. C'est un classement relatif, jamais absolu.

Partie 1 — schéma (six tables).

1create table Categorie (2  numCat integer primary key,3  libelle text unique not null,4  description text5);6 7create table Application (8  idApp integer primary key,9  nom text not null,10  prix real not null,11  dateSortie text,12  developpeur text,13  catPrincipale integer not null references Categorie(numCat),14  catSecondaire integer references Categorie(numCat)   -- nullable : catégorie secondaire facultative

Trois points de conception que l'énoncé fait travailler :

• catSecondaire doit accepter NULL : une application peut ne pas avoir de catégorie secondaire, et c'est la question 1 de la partie 2 qui l'exploite.
• La contrainte « seules les applications achetées peuvent être installées » est encodée par la clé étrangère composite de Installation vers Achat(email, idApp). Pour que ce soit possible, il faut déclarer une contrainte d'unicité sur (email, idApp) dans Achat — la clé primaire numFacture ne suffit pas à servir de cible.
• « attachés à ce compte et à aucun autre » interdit de mettre numSerie dans Utilisateur (colonne multivaluée) : d'où la table Terminal avec sa clé étrangère.

Partie 2 — requêtes.

1-- 1. Applications sans catégorie secondaire2select nom from Application where catSecondaire is null;3 4-- 2. Utilisateurs ayant acheté au moins une application5select distinct u.email, u.nom, u.prenom6from Utilisateur u7join Achat a on a.email = u.email;8 9-- 3. Nombre d'applications achetées par catégorie principale, pour un utilisateur donné10select ap.catPrincipale, count(*) as nb11from Achat a12join Application ap on ap.idApp = a.idApp13where a.email = 'jean-vivien.millo@inria.fr'14group by ap.catPrincipale;

Deux remarques de fond.

• La question 4 se résout par une anti-jointure : « n'a pas acheté » couvre aussi les utilisateurs qui n'ont rien acheté du tout. Un join puis where ap.nom <> 'Angry birds' donnerait une réponse fausse, puisqu'il exclurait silencieusement les clients sans achat.
• Les questions 5 et 6 sont les deux formes de la question en « tous les » et de la comparaison à un agrégat global. La question 5 est une division (double not exists) ; la question 6 est une sous-requête scalaire dans le WHERE, qui peut être non corrélée parce que le maximum de la catégorie « Sport » est une constante.

Partie 3 — mises à jour.

1-- 1. Ajouter la taille en kilo-octets2alter table Application add column tailleKo integer;3 4-- 2. Baisser de 10 centimes les applications à plus de 2 dollars5update Application set prix = prix - 0.10 where prix > 2;6 7-- 3. Créer une table à partir d'une requête (CREATE TABLE ... AS SELECT)8create table AchatUtilisateur as9select u.nom, u.prenom, a.numFacture, a.dateAchat10from Utilisateur u11join Achat a on a.email = u.email;

La requête 3 illustre la différence entre LDD et LMD : create table ... as select est une instruction de définition de données qui se nourrit d'une instruction de manipulation. C'est le pont entre les deux langages d'un SGBD.

Partie 1 — schéma CarTrack. L'énoncé laisse volontairement des zones d'ambiguïté ; l'attendu est un schéma cohérent accompagné de ses justifications.

1create table Client (2  numClient integer primary key,3  nom text not null, prenom text not null,4  adresse text, telephone text5);6 7create table Voiture (8  plaque text primary key,9  marque text, modele text, couleur text,10  dateImmatriculation text,11  numClient integer not null references Client(numClient)12);13 14create table Systeme (

Les décisions à justifier :

• Le 1-1 est modélisé par une table Systeme séparée, comme le suggère l'énoncé, avec une contrainte unique sur plaque : c'est cette unicité qui encode le « un seul système par voiture ». Sans elle, la relation serait 1-N.
• Les relevés sont dans une table à part parce qu'un système produit une quantité non bornée de mesures : les stocker en colonnes de Systeme serait un attribut multivalué.
• Le lien partenaire-système est N-N (un partenaire installe plusieurs systèmes, un système est installé par un partenaire), et il porte des attributs (la date et le résultat du test d'installation). C'est le cas typique qui impose la table d'association.
• Le test d'installation appartient à l'association, pas au système seul : deux partenaires peuvent avoir installé le même modèle de système et obtenu des résultats différents.

Partie 2 — requêtes SQL.

1-- 1. Unités disponibles du ou des produits « Ventilateur »2select p.code, p.nom, sum(s.unites) as unitesDisponibles3from Produit p4join Stock s on s.codeProduit = p.code5where p.nom = 'Ventilateur'6group by p.code, p.nom;

Le group by est nécessaire : Stock est historisé par date, et le même produit peut apparaître sur plusieurs lignes. Sans regroupement, on obtiendrait une ligne par relevé de stock.

1-- 2. Nombre de commandes de livres effectués par Marie Pascal2select count(*) as nbCommandes3from Commande c4join Client cl on cl.code = c.codeClient5join Produit p on p.code = c.codeProduit6join Categorie cat on cat.code = p.codeCategorie7where cl.nom = 'Pascal' and cl.prenom = 'Marie' and cat.nom = 'Livre';

Trois jointures sont inévitables : le nom du client est dans Client, la catégorie du produit dans Categorie, et le lien passe par Produit. La comparaison porte sur nom et prenom : rien ne garantit l'unicité du nom de famille.

1-- 3. Clients ayant acheté en novembre 2013, avec quantité totale, tri décroissant2select c.codeClient, sum(c.quantite) as quantiteTotale3from Commande c4where c.date >= '2013-11-01' and c.date < '2013-12-01'5group by c.codeClient6order by quantiteTotale desc;

Le filtre de date s'écrit en bornes (>= et <) plutôt qu'avec between ou month() : c'est à la fois SARGable (un index sur date reste utilisable) et correct sur les bornes — between '2013-11-01' and '2013-11-30' raterait les commandes du 30 novembre à 14h si la colonne contenait une heure.

1-- 4. Produits jamais achetés2select p.nom3from Produit p4where not exists (select 1 from Commande c where c.codeProduit = p.code);5 6-- 5. Produits plus chers que la moyenne de leur catégorie7select p.nom, p.prix, p.codeCategorie8from Produit p9where p.prix > (select avg(p2.prix)10                from Produit p211                where p2.codeCategorie = p.codeCategorie);

La question 5 est le cas d'école de la sous-requête corrélée : la moyenne dépend de la catégorie de la ligne courante, donc elle est recalculée pour chaque produit. Une version non corrélée serait fausse, puisqu'elle comparerait chaque produit à la moyenne globale du catalogue. Une variante par jointure sur une table dérivée donne le même résultat :

1select p.nom, p.prix2from Produit p3join (select codeCategorie, avg(prix) as moyenne from Produit group by codeCategorie) m4  on m.codeCategorie = p.codeCategorie5where p.prix > m.moyenne;

1. Modèle E-A. Quatre entités et deux associations, plus une association réflexive.

1Animal(numAnimal, nom, espece)          -- association « parqué dans » vers Emplacement, 1..1 / 0..*2Emplacement(numEmplacement, type)       -- association « affecté à » vers Gardien, 1..* / 0..*3Gardien(numGardien, nom, salaire)       -- association réflexive « remplace », 0..* / 0..1

• Animal — parqué dans — Emplacement : cardinalités 1..1 du côté animal (un animal est dans exactement un emplacement) et 0..* du côté emplacement (un emplacement peut être vide). C'est un 1-N.
• Emplacement — affecté à — Gardien : 1..* des deux côtés. C'est un N-N, donc une table d'association.
• Gardien — remplace — Gardien : association réflexive, 0..1 du côté « remplaçant » et 0..* du côté « remplacé ». Un gardien a au plus un remplaçant ; il peut être le remplaçant de plusieurs.

2. Tables.

1Animal(numAnimal, nom, espece, numEmplacement#)2Emplacement(numEmplacement, type)3Gardien(numGardien, nom, salaire, numRemplacant#)4Affectation(numEmplacement#, numGardien#)

Application des trois règles de passage : l'association 1-N Animal–Emplacement place la clé étrangère du côté « 1 », c'est-à-dire dans Animal ; l'association N-N Emplacement–Gardien devient la table Affectation à clé primaire composite ; l'association réflexive se traduit par une clé étrangère de Gardien vers Gardien, numRemplacant étant nullable puisqu'un gardien peut n'avoir aucun remplaçant.

La contrainte « il ne peut pas être son propre remplaçant » n'est pas exprimable par une simple clé étrangère : elle demande un check (numRemplacant <> numGardien) — et le fait de pouvoir nommer précisément cette limite est souvent ce qui distingue une bonne copie.

1Enseignant(Numéro, Nom, Age)2Cours(Code, Intitulé, Nb_heures, num_enseignant#)3Projet(Code, Intitulé, Description)4EnseignantProjet(num_enseignant#, code_projet#)

Association « Donne » (1-N). La cardinalité de (Enseignant, Donne) vaut 1 : la clé étrangère va du côté « 1 », donc dans Cours. Mettre num_cours dans Enseignant serait une faute : un enseignant donne plusieurs cours, donc la colonne serait multivaluée — exactement ce que la 1NF interdit. C'est l'erreur que le corrigé du TD signale en premier.

Association « Recherche pour » (N-N). Aucune des deux cardinalités ne vaut 1 : impossible de placer la clé étrangère d'un côté ou de l'autre sans créer de doublons ou violer la 1NF. Il faut la table intermédiaire EnseignantProjet, dont la clé primaire est composite — les deux clés étrangères — ce qui interdit d'associer deux fois le même couple enseignant-projet.

Le critère de décision, énoncé une fois pour toutes : si la cardinalité d'une entité dans l'association vaut 1, la clé étrangère va chez elle ; sinon, l'association devient une table.

Méthode : écrire d'abord le tableau des temps (chaque action correspond à un temps), puis chercher les couples lecture/écriture sur la même donnée, puis chercher un cycle.

a) T1 lit x (t1), T2 lit x (t2), T1 écrit x (t3), T2 écrit x (t4).

• T2 lit x avant que T1 ne l'écrive : flèche $T_2 \to T_1$.
• T1 écrit x avant que T2 ne l'écrive : flèche $T_1 \to T_2$.

Le graphe est cyclique : la planification n'est pas sérialisable.

b) T1 lit x (t1), T2 lit y (t2), T3 écrit x (t3), T1 écrit y (t4).

• T1 a lu x avant l'écriture de T3 : flèche $T_1 \to T_3$.
• T2 a lu y avant l'écriture de T1 : flèche $T_2 \to T_1$.

Le graphe est acyclique : la planification est sérialisable. L'ordre topologique $T_2, T_1, T_3$ fournit la planification sérielle équivalente : les opérations de T2 d'abord, puis celles de T1, puis celles de T3.

c) T1 lit x (t1), T2 écrit x (t2), T1 écrit x (t3), T2 avorte (t4), T1 valide (t5).

• T1 lit x avant que T2 ne l'écrive : flèche $T_1 \to T_2$.
• T2 écrit x avant que T1 ne l'écrive : flèche $T_2 \to T_1$.

Le graphe brut est cyclique. Mais T2 avorte : ses effets sont annulés (c'est l'atomicité), et ses opérations ne participent donc plus à l'exécution finale. Une fois T2 retirée du graphe, il ne reste que T1 : la planification est sérialisable, et la planification sérielle équivalente se réduit à T1 seule. Le point à retenir : une transaction avortée ne peut pas figurer dans l'ordre sériel équivalent.

d) T1 écrit x (t1), T2 lit x (t2), T1 écrit x (t3), T2 valide (t4), T1 avorte (t5).

• T1 a écrit x avant que T2 ne le lise : flèche $T_1 \to T_2$.
• T2 a lu x avant que T1 ne le réécrive : flèche $T_2 \to T_1$.

Le graphe est cyclique : la planification n'est pas sérialisable. Le fait que T1 avorte ensuite ne rachète rien : T2 a validé sur la base d'une valeur que T1 a ensuite annulée — c'est exactement le problème de la dépendance non validée. C'est la différence essentielle avec le cas c.

e) T1 lit x (t1), T2 écrit x (t2), T1 écrit x (t3), T3 lit x (t4).

• T1 lit x avant l'écriture de T2 : $T_1 \to T_2$.
• T2 écrit x avant l'écriture de T1 : $T_2 \to T_1$.
• T1 écrit x avant la lecture de T3 : $T_1 \to T_3$ ; T2 aussi : $T_2 \to T_3$.

Le cycle $T_1 \leftrightarrow T_2$ subsiste : la planification n'est pas sérialisable. L'ajout de T3, qui ne fait que lire après coup, ne change rien au diagnostic.

On parcourt la séquence dans l'ordre et on enregistre, pour chaque donnée, le dernier écrivain et les lecteurs depuis cette écriture. Une flèche $T_i \to T_j$ apparaît dès que $T_j$ écrit une donnée que $T_i$ a lue, ou que $T_j$ lit une donnée que $T_i$ a écrite.

Ensemble des flèches : $T_2 \to T_3$, $T_1 \to T_2$, $T_2 \to T_1$, $T_3 \to T_1$.

Le graphe contient le cycle $T_1 \to T_2 \to T_3 \to T_1$ : la planification n'est pas sérialisable au niveau des conflits.

Construction du graphe. Une flèche $T_i \to T_j$ existe quand $T_i$ attend un élément actuellement verrouillé par $T_j$. On croise la colonne « attend » avec la colonne « verrouille » :

1T1 ──→ T5          T5 ──→ T22T1 ──→ T2          T6 ──→ T73T2 ──→ T4          T7 ──→ T54T2 ──→ T35T3 ──→ T66T3 ──→ T57T4 ──→ T5

Détection. Par le théorème de Holt, un verrou indéfini existe si et seulement si le graphe des attentes contient un cycle. Les cycles orientés simples sont au nombre de trois :

1T2 → T3 → T5 → T22T2 → T4 → T5 → T23T2 → T3 → T6 → T7 → T5 → T2

Conclusion : le graphe contient des cycles, donc il y a interblocage. Les transactions concernées sont T2, T3, T4, T5, T6 et T7 ; T1 attend un verrou détenu par T5 mais personne n'attend un verrou détenu par T1, elle n'est donc pas dans un cycle — elle subit l'attente sans participer à l'impasse.

Le problème : la mise à jour perdue. Sans verrouillage, les deux transactions liraient la même valeur initiale de qte et écriraient chacune une valeur calculée à partir de cette valeur initiale. La seconde écriture écraserait la première : sur un stock de 1000, on attendrait 4500 et on obtiendrait 1500 ou 4000 selon l'ordre. C'est exactement « une opération de modification apparemment achevée avec succès est écrasée par un autre utilisateur ».

La résolution par le verrouillage. Le tableau montre le mécanisme en action :

• t1 : T1 obtient le verrou d'écriture sur qte. T2 demande le même verrou et doit attendre : un verrou exclusif n'est pas compatible avec un autre verrou exclusif.
• t2 et t3 : T1 est seule à pouvoir lire et écrire. Elle lit la valeur, calcule qte + 3000.
• t4 : T1 valide et déverrouille. Ce n'est qu'à ce moment que les effets de l'écriture deviennent visibles aux autres transactions.
• t4 à t6 : T2 lit alors la valeur déjà modifiée par T1, et calcule qte + 500 à partir d'elle.

Le résultat final est la somme des deux mises à jour, ce qui est le comportement attendu. Le protocole de verrouillage en deux phases est ce qui garantit que T2 ne peut pas acquérir son verrou après en avoir libéré un, ni libérer un verrou avant d'avoir acquis tous ceux dont elle a besoin : les opérations de verrouillage précèdent toutes la première opération de déverrouillage.

Pourquoi le verrouillage seul ne suffit pas. Le cours donne une planification de verrouillage incorrecte pour le prouver : T9 déverrouille soldex avant d'avoir fini sa phase de croissance, ce qui laisse T10 s'insérer au milieu et produit un résultat qui ne correspond à aucune exécution sérielle (soldex = 220 et soldey = 340 alors que les deux ordres sériels donnent respectivement 220/330 et 210/340). C'est la discipline en deux phases qui garantit la sérialisabilité, pas la présence de verrous.

Un point de conception à comprendre d'abord. « Plusieurs sociétés de location peuvent porter le même nom » : nomS ne peut donc pas être la clé primaire de Societe, d'où numS. De même, Dvd possède son propre identifiant numD : un même film (même titre, même année) peut exister chez deux sociétés, et ce sont deux lignes différentes, donc deux DVD différents. C'est ce que le corrigé du TD souligne : bien comprendre le rôle de l'identifiant unique. Conséquence directe sur la question 4 : un même film loué chez deux sociétés n'est pas le même DVD.

Requête 1.

1select d.numD, d.titreD2from Dvd d3join Societe s on s.numS = d.societeD4where s.nomS = 'DVDStore';

La jointure est indispensable : le nom de la société n'est pas dans Dvd, seulement sa clé étrangère.

Requête 2.

1select d.numD, d.titreD, a.nomA, a.prenomA2from Dvd d3join Societe s on s.numS = d.societeD4join Casting c on c.dvdC = d.numD5join Acteur a on a.numA = c.acteurC6where s.nomS = 'DVDStore'7order by d.numD, a.nomA;

Trois jointures en chaîne, et le rôle (roleC) est disponible dans Casting si l'énoncé le demande. Attention à la cardinalité : un DVD avec cinq acteurs produit cinq lignes. Si l'on veut une ligne par DVD avec la liste des acteurs, il faut agréger — group_concat en SQLite et MySQL, string_agg en PostgreSQL.

Requête 3.

1select d.titreD, a.nomA, a.prenomA, c.roleC2from Dvd d3join Casting c on c.dvdC = d.numD4join Acteur a on a.numA = c.acteurC5order by d.titreD, a.nomA;

C'est la même jointure que la question 2 sans le filtre sur la société : Casting est la table d'association qui porte la relation N-N entre Dvd et Acteur, et c'est elle qui porte aussi le rôle — attribut de l'association, pas de l'acteur.

Requête 4.

1select distinct e.clientE2from Emprunt e3join Casting c on c.dvdC = e.dvdE4join Acteur a on a.numA = c.acteurC5where a.prenomA = 'Xavier';

Le distinct est nécessaire : un client qui a loué trois films avec Xavier apparaîtrait trois fois. Comme toujours quand la question porte sur l'existence d'une correspondance, la semi-jointure est plus fidèle :

1select e.clientE2from Emprunt e3where exists (4  select 1 from Casting c5  join Acteur a on a.numA = c.acteurC6  where c.dvdC = e.dvdE and a.prenomA = 'Xavier'7);

1. Utilité du V2P. Il garantit la sérialisabilité des conflits : il évite l'incohérence des données lorsque deux transactions effectuent des opérations de lecture et d'écriture sur une même valeur. Le protocole impose que toutes les opérations de verrouillage d'une transaction précèdent sa première opération de déverrouillage. La transaction se décompose alors en une phase de croissance (elle acquiert tous ses verrous, n'en libère aucun) et une phase de résorption (elle libère ses verrous, n'en acquiert plus).

Le mécanisme pratique : la première transaction arrivée obtient le verrou exclusif, ce qui restreint la lecture et l'écriture de la valeur à elle seule ; les autres transactions doivent attendre la fin de la transaction détentrice. Une fois celle-ci validée, elle cède sa place et la valeur finale reste cohérente. C'est le « premier arrivé, premier servi » — mais c'est la discipline en deux phases, et non le verrouillage en soi, qui empêche une transaction de s'insérer au milieu d'une autre.

2. Ordre de traitement des clauses. L'ordre est :

1FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  sous-requête  ->  SELECT  ->  ORDER BY  ->  LIMIT

La logique est celle d'un pipeline : chaque étape ne peut utiliser que ce que les étapes précédentes ont produit.

• FROM lit les tables : c'est la source.
• WHERE applique une ou des conditions sur les lignes issues de ces tables.
• GROUP BY regroupe les lignes selon un attribut — les lignes regroupées sont celles qui ont passé le WHERE.
• HAVING n'affiche les groupes que selon une condition de calcul sur un attribut.
• La sous-requête récupère un jeu de données, qui peut ensuite alimenter le SELECT.
• SELECT sélectionne les attributs.
• ORDER BY fixe l'ordre d'affichage.
• LIMIT borne le nombre de lignes affichées.

Deux conséquences pratiques découlent de cet ordre : un alias défini dans le SELECT n'est pas utilisable dans le WHERE (évalué avant) mais l'est dans le ORDER BY (évalué après) ; et un agrégat comme AVG(...) ne peut pas apparaître dans un WHERE, puisqu'il n'existe pas encore à ce stade.