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, DateRetourEective)
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 ?
----------------------------------------------------------------------------------------------------
Correction
----------------------------------------------------------------------------------------------------
Dans cet exercice, le schéma relationnel est particulièrement simple, an que l'expression des requêtes soit 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 Personne et Livre et précisant les clés étrangères dans les relations Emprunt et Retard faisant référence à une personne et à un livre.
En algèbre relationnelle : ΠPersonne(σ Livre='Recueil...' (Emprunt))
L'algèbre relationnelle est un langage composé d'opérations ensemblistes. Il permet d'indiquer comment le résultat de la requête est calculé en termes d'opérations ensemblistes sur des ensembles de nuplets (les relations). Dans cette requête par exemple, le résultat est calculé en parcourant tous les nuplets de la relation Emprunt, en y sélectionnant les nuplets dont l'attribut Livre a pour valeur 'Recueil...' et en prenant uniquement les valeurs de l'attribut
Personne (i.e. en projetant sur l'attribut Personne).
En calcul relationnel : {t.Personne | Emprunt(t) ∧ (u.Livre =' Recueil...') }
Le calcul relationnel décrit, sous forme logique, le résultat de la requête (sans préciser comment on le calcule). Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que l'attribut Livre corresponde à 'Recueil Examens BD'.
En SQL:
SELECT Personne
FROM Emprunt WHERE Livre = 'Recueil...'
Il aurait également été possible de remplacer la clause WHERE par WHERE Livre LIKE 'Recueil%' indiquant que l'on recherche les emprunteurs des ouvrages dont le titre commence par 'Recueil'.
En algèbre relationnelle : ΠPersonne(Emprunt) − ΠPersonne(Retard)
La résultat de la requête est calculé en prenant toutes les valeurs de l'attribut Personne dans la relation Emprunt et en éliminant les valeurs de ce même attribut apparaissant également dans la relation Retard. Il s'agit d'une différence entre deux ensembles.
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt (donc des personnes empruntant) tels qu'il n'existe pas de nuplets u dans la relation Retard avec la même valeur pour l'attribut Personne (donc telles qu'il n'existe pas de retards associés à ces personnes).
En SQL, deux manières possibles, par simple traduction en SQL de la requête en calcul relationnel (le calcul relationnel étant à l'origine de la syntaxe de SQL) :
SELECT t.Personne FROM Emprunt t
WHERE NOT EXISTS (SELECT * FROM Retard u
WHERE u.Personne=t.Personne
SELECT Personne FROM Emprunt
WHERE Personne NOT IN
(SELECT Personne FROM Retard)
Les variables nuplet (ex. t et u) ne sont nécessaire que lorsqu'il y a ambiguïté au niveau des noms d'attributs (cf. requête de gauche).
En algèbre relationnelle : ΠPersonne,Livre(Emprunt) ÷ ΠLivre(Emprunt)
Le résultat de cette requête est calculé en utilisant l'opérateur de division. Pour une bonne compréhension de la division.
La sous-requête ΠLivre(Emprunt) correspond à la liste des livres empruntés. Le résultat de la sous-requête Π Personne,Livre(Emprunt) contient tous les couples (Personne, Livre emprunté au moins une fois par cette personne). Le résultat de la division sera donc la liste des personnes associées, dans le résultat de Π Personne,Livre(Emprunt), à chacun des livres apparaissant dans le résultat de la requête ΠLivre(Emprunt).
En calcul relationnel :
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un livre emprunté (donc d'un nuplet u dans Emprunt) alors on trouve un nuplet v dans Emprunt associant cette personne à ce livre (c'est-à-dire v.Personne = t.Personne et u.Livre = v.Livre).
On peut également l'écrire de la manière suivante :
Ce qui signie que le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet u soit c'est n'est pas un nuplet de Emprunt soit (implicitement c'est un nuplet de Emprunt et) on trouve un nuplet v dans Emprunt associant cette personne à ce livre (c'est-à-dire v.Personne = t.Personne et u.Livre = v.Livre).
D'où dit de manière négative :
En SQL, simple traduction de la requête en calcul relationnel :
En algèbre relationnelle : ΠPersonne,Livre(Emprunt) ÷ ΠPersonne(Emprunt)
Le résultat de cette requête est calculé en utilisant également l'opérateur de division.
La sous-requête ΠPersonne(Emprunt) correspond à la liste des emprunteurs. Le résultat de la sous-requête Π Personne,Livre(Emprunt) contient tous les couples (Personne ayant emprunté au moins une fois, Livre emprunté au moins une fois par cette personne). Le résultat de la division sera donc la liste des livres associés, dans le résultat de ΠPersonne,Livre(Emprunt), à chacun des emprunteurs apparaissant dans le résultat de la requête Π Personne(Emprunt).
En calcul relationnel :
Le résultat de la requête contient les valeurs de l'attribut Livre des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un emprunteur (donc d'un nuplet u dans Emprunt) alors on trouve un nuplet v dans Emprunt associant ce livre à cet emprunteur (c'est-à-dire u.Livre = t.Livre et v.Personne = u.Personne ).
On peut également l'écrire de la manière suivante :
Ce qui signifie que le résultat de la requête contient les valeurs de l'attribut Livre des nuplets t de la relation Emprunt tels que quel que soit un nuplet soit il ne s'agit pas d'un nuplet u dans Emprunt soit (il s'agit d'un d'un nuplet u dans Emprunt et) il existe un nuplet v dans Emprunt associant ce livre à cet emprunteur (c'est-à-dire u.Livre = t.Livre et v.Personne = u.Personne ).
D'où dit de manière négative :
En SQL, simple traduction de la requête en calcul relationnel :
En algèbre relationnelle : Il n'est pas possible d'exprimer cette requête par une division.
La requête est donc décomposée en deux sous-requêtes. La requête, R1, ci-dessous, retourne la liste des personnes ayant emprunté au moins un livre sans le rendre en retard.
R1= ΠPersonne [ΠPersonne,Livre,DateEmprunt(Emprunt) − ΠPersonne,Livre,DateEmprunt(Retard)]
La requête ci-dessous enlève de la liste des personnes qui empruntent des livres (sous-requête de gauche) la liste des personnes ayant rendu au moins un livre sans retard (requête R1). Cela correspond à comment calculer le résultat de la requête que l'on recherche.
ΠPersonne(Emprunt) − R1
En calcul relationnel :
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un livre emprunté par cette personne (donc d'un nuplet u dans Emprunt tel que u.Personne = t.Personne) alors on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-dire v.Personne = u.Personne et u.Livre = v.Livre).
On peut également écrire :
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet soit il ne s'agit pas d'un livre emprunté par cette personne (donc d'un nuplet u dans Emprunt tel que u.Personne = t.Personne)
soit on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-dire v.Personne = u.P ersonne et u.Livre = v.Livre).
D'où dit de manière négative :
En SQL, là encore , simple traduction de la requête en calcul relationnel:
Dans cet exercice, le schéma relationnel est particulièrement simple, an que l'expression des requêtes soit 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 Personne et Livre et précisant les clés étrangères dans les relations Emprunt et Retard faisant référence à une personne et à un livre.
1. Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
En algèbre relationnelle : ΠPersonne(σ Livre='Recueil...' (Emprunt))
L'algèbre relationnelle est un langage composé d'opérations ensemblistes. Il permet d'indiquer comment le résultat de la requête est calculé en termes d'opérations ensemblistes sur des ensembles de nuplets (les relations). Dans cette requête par exemple, le résultat est calculé en parcourant tous les nuplets de la relation Emprunt, en y sélectionnant les nuplets dont l'attribut Livre a pour valeur 'Recueil...' et en prenant uniquement les valeurs de l'attribut
Personne (i.e. en projetant sur l'attribut Personne).
En calcul relationnel : {t.Personne | Emprunt(t) ∧ (u.Livre =' Recueil...') }
Le calcul relationnel décrit, sous forme logique, le résultat de la requête (sans préciser comment on le calcule). Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que l'attribut Livre corresponde à 'Recueil Examens BD'.
En SQL:
SELECT Personne
FROM Emprunt WHERE Livre = 'Recueil...'
Il aurait également été possible de remplacer la clause WHERE par WHERE Livre LIKE 'Recueil%' indiquant que l'on recherche les emprunteurs des ouvrages dont le titre commence par 'Recueil'.
2. Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
En algèbre relationnelle : ΠPersonne(Emprunt) − ΠPersonne(Retard)
La résultat de la requête est calculé en prenant toutes les valeurs de l'attribut Personne dans la relation Emprunt et en éliminant les valeurs de ce même attribut apparaissant également dans la relation Retard. Il s'agit d'une différence entre deux ensembles.
En calcul relationnel :
{t.Personne | Emprunt(t) ∧ ¬[∃ u Retard(u) ∧ (u.Personne = t.Personne) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt (donc des personnes empruntant) tels qu'il n'existe pas de nuplets u dans la relation Retard avec la même valeur pour l'attribut Personne (donc telles qu'il n'existe pas de retards associés à ces personnes).
En SQL, deux manières possibles, par simple traduction en SQL de la requête en calcul relationnel (le calcul relationnel étant à l'origine de la syntaxe de SQL) :
SELECT t.Personne FROM Emprunt t
WHERE NOT EXISTS (SELECT * FROM Retard u
WHERE u.Personne=t.Personne
)
SELECT Personne FROM Emprunt
WHERE Personne NOT IN
(SELECT Personne FROM Retard)
Les variables nuplet (ex. t et u) ne sont nécessaire que lorsqu'il y a ambiguïté au niveau des noms d'attributs (cf. requête de gauche).
3. Quelles sont les personnes ayant emprunté tous les livres (empruntés au moins une fois) ?
En algèbre relationnelle : ΠPersonne,Livre(Emprunt) ÷ ΠLivre(Emprunt)
Le résultat de cette requête est calculé en utilisant l'opérateur de division. Pour une bonne compréhension de la division.
La sous-requête ΠLivre(Emprunt) correspond à la liste des livres empruntés. Le résultat de la sous-requête Π Personne,Livre(Emprunt) contient tous les couples (Personne, Livre emprunté au moins une fois par cette personne). Le résultat de la division sera donc la liste des personnes associées, dans le résultat de Π Personne,Livre(Emprunt), à chacun des livres apparaissant dans le résultat de la requête ΠLivre(Emprunt).
En calcul relationnel :
{t.Personne | Emprunt(t) ∧ [∀ u (Emprunt(u)) =⇒ (∃ v Emprunt(v) ∧ (v.Personne = t.Personne) ∧ (u.Livre = v.Livre) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un livre emprunté (donc d'un nuplet u dans Emprunt) alors on trouve un nuplet v dans Emprunt associant cette personne à ce livre (c'est-à-dire v.Personne = t.Personne et u.Livre = v.Livre).
On peut également l'écrire de la manière suivante :
{t.Personne | Emprunt(t) ∧ [∀ u ¬(Emprunt(u)) ∨ (∃ v Emprunt(v) ∧ (v.Personne =
t.Personne) ∧ (u.Livre = v.Livre) )]}
Ce qui signie que le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet u soit c'est n'est pas un nuplet de Emprunt soit (implicitement c'est un nuplet de Emprunt et) on trouve un nuplet v dans Emprunt associant cette personne à ce livre (c'est-à-dire v.Personne = t.Personne et u.Livre = v.Livre).
D'où dit de manière négative :
{t.Personne | Emprunt(t)∧ ¬[∃ u Emprunt(u) ¬(∃ v Emprunt(v)∧(v.Personne = t.Personne) ∧ (u.Livre = v.Livre) )]}
En SQL, simple traduction de la requête en calcul relationnel :
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
)
)
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 algèbre relationnelle : ΠPersonne,Livre(Emprunt) ÷ ΠPersonne(Emprunt)
Le résultat de cette requête est calculé en utilisant également l'opérateur de division.
La sous-requête ΠPersonne(Emprunt) correspond à la liste des emprunteurs. Le résultat de la sous-requête Π Personne,Livre(Emprunt) contient tous les couples (Personne ayant emprunté au moins une fois, Livre emprunté au moins une fois par cette personne). Le résultat de la division sera donc la liste des livres associés, dans le résultat de ΠPersonne,Livre(Emprunt), à chacun des emprunteurs apparaissant dans le résultat de la requête Π Personne(Emprunt).
En calcul relationnel :
{t.Livre | Emprunt(t) ∧ [∀ u (Emprunt(u)) =⇒ (∃ v Emprunt(v) ∧ (u.Livre = t.Livre) ∧ (v.Personne = u.Personne) )]}
Le résultat de la requête contient les valeurs de l'attribut Livre des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un emprunteur (donc d'un nuplet u dans Emprunt) alors on trouve un nuplet v dans Emprunt associant ce livre à cet emprunteur (c'est-à-dire u.Livre = t.Livre et v.Personne = u.Personne ).
On peut également l'écrire de la manière suivante :
{t.Livre | Emprunt(t) ∧ [∀ u ¬(Emprunt(u)) ∨ (∃ v Emprunt(v) ∧ (u.Livre = t.Livre) ∧ (v.Personne = u.Personne) )]}
Ce qui signifie que le résultat de la requête contient les valeurs de l'attribut Livre des nuplets t de la relation Emprunt tels que quel que soit un nuplet soit il ne s'agit pas d'un nuplet u dans Emprunt soit (il s'agit d'un d'un nuplet u dans Emprunt et) il existe un nuplet v dans Emprunt associant ce livre à cet emprunteur (c'est-à-dire u.Livre = t.Livre et v.Personne = u.Personne ).
D'où dit de manière négative :
{t.Livre | Emprunt(t)∧ ¬[∃ u Emprunt(u) ¬(∃ v Emprunt(v) ∧ (u.Livre = t.Livre) ∧ (v.Personne = u.Personne) )]}
En SQL, simple traduction de la requête en calcul relationnel :
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
)
)
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 ont empruntés ?
En algèbre relationnelle : Il n'est pas possible d'exprimer cette requête par une division.
La requête est donc décomposée en deux sous-requêtes. La requête, R1, ci-dessous, retourne la liste des personnes ayant emprunté au moins un livre sans le rendre en retard.
R1= ΠPersonne [ΠPersonne,Livre,DateEmprunt(Emprunt) − ΠPersonne,Livre,DateEmprunt(Retard)]
La requête ci-dessous enlève de la liste des personnes qui empruntent des livres (sous-requête de gauche) la liste des personnes ayant rendu au moins un livre sans retard (requête R1). Cela correspond à comment calculer le résultat de la requête que l'on recherche.
ΠPersonne(Emprunt) − R1
En calcul relationnel :
{t.Personne | Emprunt(t) ∧ [∀ u [Emprunt(u) ∧ (u.Personne = t.Personne)] =⇒ (∃ v Retard(v) ∧ (v.Personne = u.Personne) ∧ (u.Livre = v.Livre) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un livre emprunté par cette personne (donc d'un nuplet u dans Emprunt tel que u.Personne = t.Personne) alors on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-dire v.Personne = u.Personne et u.Livre = v.Livre).
On peut également écrire :
{t.Personne | Emprunt(t) ∧ [∀ u ¬[Emprunt(u) ∧ (u.Personne = t.Personne)] ∨ (∃ v Retard(v) ∧ (v.Personne = u.Personne) ∧ (u.Livre = v.Livre) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet soit il ne s'agit pas d'un livre emprunté par cette personne (donc d'un nuplet u dans Emprunt tel que u.Personne = t.Personne)
soit on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-dire v.Personne = u.P ersonne et u.Livre = v.Livre).
D'où dit de manière négative :
{t.Personne | Emprunt(t)∧ ¬[∃ u Emprunt(u)∧(u.Personne = t.personne) ¬(∃ v Retard(v)∧ (v.Personne = u.Personne) ∧ (u.Livre = v.Livre) )]}
En SQL, là encore , simple traduction de la requête en calcul relationnel:
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 )
)
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 )
)
Aucun commentaire:
Enregistrer un commentaire