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:

1
2

SELECT 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:

1
2
3
4
5
6
7
8
9
10
11

1)
 
SELECT t.Personne FROM Emprunt t
WHERE NOT EXISTS 
                (SELECT * FROM Retard u WHERE u.Personne=t.Personne)
 
2)
 
SELECT Personne FROM Emprunt
WHERE 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 :

1
2
3
4
5
6
7
8
9
10
11

SELECT 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 :

1
2
3
4
5
6
7
8
9

SELECT t.Livre FROM Emprunt t
WHERE 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:

1
2
3
4
5
6
7
8
9
10

SELECT t.Personne FROM Emprunt t
WHERE 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 
                                                           )
                                 )