Exercice Langage SQL : Requêtes complexes

Objectif : 

S’entraîner à exprimer les requêtes complexes.

Enoncé de l'Exercice

On suppose qu'une bibliothèque gère une base de données dont le schéma est le suivant (les clés primaires des relations sont soulignées) :

         Emprunt (Personne, Livre, DateEmprunt, DateRetourPrevue, DateRetourEffective)

         Retard (Personne, Livre, DateEmprunt, PenalitéRetard)

Exprimer, lorsque cela est possible, les requêtes suivantes en algèbre relationnelle, en calcul à variable nuplet et en SQL.

  1. Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
  2. Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
  3. Quelles sont les personnes ayant emprunté tous les livres (empruntés au moins une fois) ?
  4. Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs) ?
  5. Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ont empruntés ?


Dans cet exercice, le schéma relationnel est particulièrement simple, a n que l'expression des requêtessoit facile à exprimer. Il s'agit néanmoins de requêtes complexes. Vous pouvez vous entraînerà exprimer ces requêtes en améliorant le schéma, c'est-à-dire en ajoutant deux relations Personneet Livre et précisant les clés étrangères dans les relations Emprunt et Retard faisant référence à unepersonne et à un livre.

1- Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?

En algèbre relationnelle :
IIPersonne(?Livre='Recueil...'(Emprunt))

En calcul relationnel :
{t.Personne | Emprunt(t) ?(u.Livre='Recueil...') }

En SQL:

12SELECT Personne FROM Emprunt WHERE Livre = 'Recueil...'


2- Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
 
En algèbre relationnelle : 
?Personne(Emprunt)??Personne(Retard)

En calcul relationnel :
{t.Personne | Emprunt(t) ? ¬[? u Retard(u) ? (u.Personne =t.Personne) )]}

En SQL:
Deux manières possibles:

12345678910111) SELECT t.Personne FROM Emprunt tWHERE NOT EXISTS (SELECT * FROM Retard u WHERE u.Personne=t.Personne) 2) SELECT Personne FROM EmpruntWHERE Personne NOT IN (SELECT Personne FROM Retard)

3- Quelles sont les personnes ayant emprunté tous les livres (empruntés au moinsune fois) ?

En SQL: simple traduction de la requête en calcul relationnel :

1234567891011SELECT t.Personne  FROM Emprunt t  WHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE v.Personne=t.Personne AND v.Livre=u.Livre ) )

4- Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs)?

En SQL, simple traduction de la requête en calcul relationnel :

123456789SELECT t.Livre FROM Emprunt tWHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE u.Livre=t.Livre AND v.Personne=u.Personne ) )

5- Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ontempruntés ?

En calcul relationnel :
{t.Personne | Emprunt(t) ? [? u [Emprunt(u) ? (u.Personne = t.Personne)] =? (? vRetard(v)?(v.Personne =u.Personne)?(u.Livre =v.Livre) )]}

En SQL:

12345678910SELECT t.Personne FROM Emprunt tWHERE NOT EXISTS (SELECT * FROM Emprunt u WHERE u.Personne=t.Personne AND NOT EXISTS (SELECT * FROM Retard v WHERE v.Personne=u.Personne AND v.Livre=u.Livre ) )

                   

Article publié le 12 Décembre 2011 Mise à jour le Lundi, 12 Décembre 2011 03:51