1- Quel est le nombre total d'étudiants ?

:
SELECT COUNT(*) FROM ETUDIANT ;

2- Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la
plus basse ?
SELECT MIN(NOTE), MAX(Note) FROM EVALUER ;
3- Quelles sont les moyennes de chaque étudiant dans chacune des matières ?
SELECT ETUDIANT.N°Etudiant, NOM, Prénom,LibelléMat,CoeffMat,
AVG(Note) AS MoyEtuMat FROM EVALUER,MATIERE, ETUDIANT
WHERE [Link]= [Link]
AND EVALUER.N°Etudiant= ETUDIANT.N°Etudiant
GROUP BY ETUDIANT.N°Etudiant ,Nom,Prénom,LibelléMat,CoeffMat ;

4-Quelles sont les moyennes par matière ?

SELECT LibelléMat, AVG(MoyEtuMat)
FROM MOYETUMAT;
GROUP BY LibelléMAT;
5-Quelle est la moyenne générale de chaque étudiant ?
SELECT N°Etudiant,Nom, Prénom ;
SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT
GROUP BY N°Etudiant , Nom, Prénom;
6-Quelle est la moyenne générale de la promotion ?
Avec la vue MGETU de la question 5 :
SELECT AVG(MgEtu)
FROM MGETU;
7-Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la
moyenne générale de la promotion ?
Avec la vue MGETU de la question 5 :
SELECT N°Etudiant , Nom , Prénom,MgEtu
FROM MGETU
WHERE MgEtu >=(SELECT AVG(MgEtu) FROM MGETU) ;

2- 1. Expression de contraintes par des dépendances fonctionnelles :

3- (a) "On peut déduire le nom et le prénom d'un utilisateur à partir de son
identificateur."
4- Cette contrainte s'exprime par la dépendance fonctionnelle :
5-
UtilisateurID → Nom, Prénom
En effet, à un identificateur d'utilisateur est associé un et un seul nom et un et un
seul prénom.

(b) "Un utilisateur (identifié par son identificateur) possède un seul login et
un seul password par serveur de mails."

6- Cette contrainte s'exprime par la dépendance fonctionnelle :

7-
UtilisateurID, ServeurMail → Login, Passwd
En effet pour un couple (identificateur d'utilisateur, serveur de mail) est associé un et
un seul login et un et un seul mot de passe.

(c) "Une adresse email est associée à un et un

seul identificateur d'utilisateur."
8-
Cette contrainte s'exprime par la dépendance fonctionnelle :
9-
AdresseEmail → UtilisateurID
En effet, à une adresse mail est associée un et un seul identificateur d'utilisateur.

(d) "Une adresse email est associée à un et un seul serveur de mails."

10- Cette contrainte s'exprime par la dépendance fonctionnelle :

11-
AdresseEmail → ServeurMail
En effet, à une adresse mail est associée un et un seul serveur de mails.
12-
2. Identication des clés minimales de la relation R

La famille de dépendances fonctionnelles associées à R est :

F = { UtilisateurID −→ Nom, Prénom; UtilisateurID, ServeurMail −→ Login,

Passwd;
AdresseEmail −→ UtilisateurID; AdresseEmail −→ ServeurMail }

L'attribut AdresseEmail ne peut être déduit d'aucun autre attribut, il doit donc
appartenir à tous les clés minimales possibles de la relation. A partir de
l'attribut AdresseEmail on peut déduire identificateur de l'Utilisateur est donc, par
transitivité, le nom et le prénom de l'utilisateur : AdresseEmail → UtilisateurID →
Nom, Prénom. A partir de ce même attribut, on peut en déduire aussi le nom du
serveur de mail et donc avec l'identificateur d'utilisateur, le login et le mot de passe
de l'utilisateur :
13-
AdresseEmail → UtilisateurID, ServeurMail → Login, Passwd
14-
D'où : [AdresseEmail]+= { AdresseEmail, UtilisateurID, Nom, Prénom,
ServeurMail, Login, Passwd } = R

La relation R a donc une seule clé minimale possible : AdresseEmail.

3. Déduction de la forme normale du schéma de la relation R

Les deux dernières dépendances fonctionnelles sont de la forme clé primaire →
 autre attribut, et vérient donc les propriétés de la forme normale BCNF. En
revanche, les deux premières dépendances fonctionnelles sont transitives
puisqu'elles ne sont composées que d'attributs n'appartenant pas à une clé. Par
conséquent, le schéma de la relation R est en deuxième forme normale.

15-

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.

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 : {[Link] | Emprunt(t) ∧ ([Link] =' 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 :

{[Link] | Emprunt(t) ∧ ¬[∃ u Retard(u) ∧ ([Link] = [Link]) )]}

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 [Link] FROM Emprunt t

WHERE NOT EXISTS (SELECT * FROM Retard u
WHERE [Link]=[Link]
)

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 :

{[Link] | Emprunt(t) ∧ [∀ u (Emprunt(u)) =⇒ (∃ v Emprunt(v) ∧ ([Link] = [Link])

∧ ([Link] = [Link]) )]}

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 [Link] = [Link] et [Link] = [Link]).

On peut également l'écrire de la manière suivante :

{[Link] | Emprunt(t) ∧ [∀ u ¬(Emprunt(u)) ∨ (∃ v Emprunt(v) ∧ ([Link] =

[Link]) ∧ ([Link] = [Link]) )]}
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 [Link] = [Link] et [Link] = [Link]).

D'où dit de manière négative :

{[Link] | Emprunt(t)∧ ¬[∃ u Emprunt(u) ¬(∃ v Emprunt(v)∧([Link] = [Link]) ∧ ([Link] =

[Link]) )]}

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

SELECT [Link]
FROM Emprunt t
WHERE NOT EXISTS ( SELECT *
FROM Emprunt u WHERE NOT EXISTS ( SELECT *
FROM Emprunt v
WHERE [Link]=[Link]
AND [Link]=[Link]
)
)

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 :

{[Link] | Emprunt(t) ∧ [∀ u (Emprunt(u)) =⇒ (∃ v Emprunt(v) ∧ ([Link] = [Link]) ∧ ([Link] =

[Link]) )]}

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 [Link] = [Link] et [Link] = [Link] ).

On peut également l'écrire de la manière suivante :

{[Link] | Emprunt(t) ∧ [∀ u ¬(Emprunt(u)) ∨ (∃ v Emprunt(v) ∧ ([Link] = [Link]) ∧ ([Link] =

[Link]) )]}

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 [Link] = [Link] et [Link] = [Link] ).

D'où dit de manière négative :

{[Link] | Emprunt(t)∧ ¬[∃ u Emprunt(u) ¬(∃ v Emprunt(v) ∧ ([Link] = [Link]) ∧ ([Link] =

[Link]) )]}

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

SELECT [Link] FROM Emprunt t
WHERE NOT EXISTS ( SELECT * FROM Emprunt u
WHERE NOT EXISTS ( SELECT * FROM Emprunt v
WHERE [Link]=[Link] AND [Link]=[Link]
)
)

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 :

{[Link] | Emprunt(t) ∧ [∀ u [Emprunt(u) ∧ ([Link] = [Link])] =⇒ (∃ v Retard(v) ∧

([Link] = [Link]) ∧ ([Link] = [Link]) )]}

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
[Link] = [Link]) alors on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-
dire [Link] = [Link] et [Link] = [Link]).

On peut également écrire :

{[Link] | Emprunt(t) ∧ [∀ u ¬[Emprunt(u) ∧ ([Link] = [Link])] ∨ (∃ v Retard(v)

∧ ([Link] = [Link]) ∧ ([Link] = [Link]) )]}

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 [Link] = [Link])

soit on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-dire [Link] = u.P
ersonne et [Link] = [Link]).

D'où dit de manière négative :

{[Link] | Emprunt(t)∧ ¬[∃ u Emprunt(u)∧([Link] = [Link]) ¬(∃ v Retard(v)∧ ([Link] =

[Link]) ∧ ([Link] = [Link]) )]}

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

SELECT [Link]
FROM Emprunt t
WHERE NOT EXISTS (SELECT * FROM Emprunt u WHERE [Link]=[Link]
AND NOT EXISTS (SELECT * FROM Retard v WHERE [Link]=[Link]
AND [Link]=[Link] )
)