Algèbre relationnelle

Chapitre 5
Comprendre SQL grâce
à l’Algèbre Relationnelle
1
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Langage de requêtes pour les BDs
relationnelles (1/2)
• Langage de requêtes : ils permettent de
manipuler et de retrouver des données d’une
base de données
• Le modèle relationnel supporte des requêtes
simples et expressives
• Les langages de requêtes pour les BDs
relationnelles sont fondés sur des bases
formelles solides (logique du premier ordre)
2
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Langage de requêtes pour les BDs
relationnelles (2/2)
• Langage de requêtes ≠ langage de
programmation
– Un langage de requêtes n’a pas pour but
d’être utilisé pour faire des traitements ou
calculs complexes
– But : être facile d’utilisation et offrir un
accès efficace aux données
3
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Langages de requêtes formels
• 2 langages de requêtes formels existent
(fondement de SQL)
– L’algèbre relationnelle : langage opérationnel,
une requête s’écrit comme une succession
d’opérations effectuées sur des relations
– Calcul relationnel : langage déclaratif, une
requête s’écrit comme la spécification du
résultat attendu (sous forme logique)
4
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Préliminaire 1
• Une requête s’applique à des instances
de relations et le résultat d’une requête
est aussi une instance de relation
– Autrement dit : Une requête se pose sur des
tables qui ont un contenu (potentiellement
vide) et renvoie une table avec un contenu
(potentiellement vide)
R1 : table
R2 : table
Requête Q
prenant en
entrée R1 et
R2
Résultat de Q
= une table
5
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Préliminaire 2
• Le schéma du résultat d’une requête
sera toujours le même, quelque soit le
contenu des relations
– La table résultat d’une requête mettra toujours
en jeu les mêmes attributs (quelque soit le
contenu !)
6
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Préliminaire 3
• Les schémas des relations prises en
entrées par une requête sont fixés
– Une requête s’exprime grâce à des noms
d’attributs et de tables
• Mais une requête ne doit jamais
dépendre des instances des relations
– Aucune connaissance du contenu des tables
ne doit être nécessaire pour bien comprendre
la requête
7
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Algèbre relationnelle
• Opérateurs de base
– Sélection (
), Projection (), Produit cartésien (X),
– Différence (\ ou -) et Union (U)
• Autres opérateurs
– Intersection, jointure, division, renomage: très utiles
aussi !
• Puisque chaque opérateur appliqué à une
relation renvoie une relation, les opérateurs
peuvent être imbriqués (clôture de l’Algèbre
relationnelle)
8
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Projection : introduction

•
Notation :
A1,…,Ap(R) avec r relation et
A1,…,Ap attributs de R
•
Supprime les attributs qui sont différents des
attributs de la liste de projection (A1,…,Ap)
•
Le schéma du résultat contient exactement les
champs de la liste de projection (A1,…,Ap)
•
NB : L’opérateur de projection élimine les
redondances (ce n’est pas le cas en SQL)
9
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Q1=

Projection : exemple
NumClient, DateRes(Reservation)
Q1
Reservation
NumRes
Classe DateRes
001
1
2
002
NumClient
NumClient
18/11/2007 17902567
17902567
2780289
2780289
19/11/2007
DateRes
18/11/2007
19/11/2007
NB : la projection est un opérateur qui n’agit pas sur
les lignes du résultat mais sur les colonnes (c’est-àdire sur le schéma)
10
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Projection : Définition formelle
Input
– r une relation de schéma R
– X un ensemble d’attributs tels que X R
Output

X(r)={t(X) / t  r}
Où t(X) est la restriction de t sur les attributs de X
Ensemble des n-uplets de r restreints aux
attributs de X
Le schéma de
X(r) est X
11
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Sélection : introduction
• Sélection des n-uplets (lignes) qui
satisfont la condition de sélection
• Le schéma du résultat est identique au
schéma pris en entrée
12
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Sélection : exemple

Q2=
ET
(DateRes<20-11-2007Classe=2)(
Reservation)
Condition de sélection
Q2
Réservation
NumRes
001
Classe DateRes
1
002
2
003
2
NumClient
18/11/2007 17902567
19/11/2007
20/11/2007
NumRes
002
Classe DateRes
2
NumClient
19/11/2007 2780289
2780289
2780289
13
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Sélection : Définition formelle
Input
– r une relation de schéma R
–  une condition de sélection (définie ci-après)
Output
(r)={t / t  r et  (t)}
 L’ensemble des n-uplets de la relation r pour
lesquels la condition  est vraie
Le schéma de
 (r) est R

14
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Définition formelle de la condition 
Définition récursive
• Base : Soient 2 attributs A et B (non
nécesairement différents), et a  Dom(A)
 peut prendre les formes suivantes
A =B, A < B, A=a, A < a (on utilisera aussi >, ≤, ≥ et ≠)
• Si 1 et
2 sont deux conditions alors
(1), ¬1 (négation), 1  2 (et) et 1  2 (ou)
sont des conditions de sélection
15
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Exemples de conditions
• DateRes ≥ 20-11-2007
• Classe = 2
• Nom = ‘Payen’
• (DateRes = 20-11-2007 
DateRes = 21-11-2007)
 Classe = 2
• NumClient = NumSS
16
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Exercice 1
• Quel est le résultat de la requête Q3
suivante en considérant l’instance de la
relation R ci-dessous ?
Q3 =Nom (Prénom = ‘Jean’ (Enseignant) )
Enseignant
NumEns
1
2
3
Nom
Prénom
Voisin
Jean
Benzaken
Claudine
Forest
Jean
17
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Correction (Exercice 1)
• Quel est le résultat de la requête Q3
suivante en considérant l’instance de la
relation R ci-dessous ?
Q3 =Nom (Prénom = ‘Jean’ (Enseignant) )
Enseignant
NumEns
Q3
Nom
Prénom
Nom
1
2
Voisin
Jean
Benzaken
Claudine
Forest
3
Jean
Voisin
Forest
18
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Produit cartésien : Introduction
• Opérateur binaire (prend deux relations
entre entrée : r1 et r2)
• Chacun des n-uplets de r1 est combiné
avec chacun des n-uplets de r2
• Le schéma du résultat a l’union des
attributs des relations
• NB : Si les deux relations ont un attribut
de même nom, on renomme cet attribut
19
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Produit cartésien : Exemple
R1
A
B
C
a1
b1
c1
a2
b2
c2
R2
A
D
a2
d2
a2
d3
a3
d4
Renommage
R1 x R2
R1.A
a1
a1
a1
a2
a2
a2
B
b1
b1
b1
b2
b2
b2
C
c1
c1
c1
c2
c2
c2
R2.A
a2
a2
a3
a2
a2
a3
D
d2
d3
d4
d2
d3
d4
20
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Produit cartésien : Définition formelle
Input
– r, s deux relations de schéma R et S
Output
r x s = {t / t(R)  r et t(S)  s}
La restriction du produit aux attribut de R est r et
celle aux attributs de S est s
r x s a pour schéma R+S
NB : R+S est l’union disjointe de R et S
{R.A / AR } U {S.B / B  S}
21
Département Informatique
Sarah Cohen-Boulakia, Bases de données
SQL par défaut fait un produit cartésien !
Client
Reservation
NumSS
NumRes
1
2
Classe DateRes
Nom
NumClient
17902567 Payen
1
2
18/11/2007 17902567
19/11/2007
SELECT Nom,
Prenom,
Reservation.NumRes,
DateRes
FROM Reservation,
Client
WHERE DateRes <
‘2007-11-19’
Prénom
2780289
Nom
Payen
2780289
Payen
29005579 Quoti
Prenom
NumRes
Olivier
Judith
Mathilde
DateRes
Olivier
1
Payen
Judith
1
18/11/2007
Quoti
Mathilde
1
18/11/2007
18/11/2007
Ces deux lignes mélangent des informations qui n’ont
22
rien
à
voir
!!
Sarah Cohen-Boulakia, Bases de données
Département Informatique
Jointure naturelle : Introduction
• Opération binaire fondamentale (optimisation) !
• Utilise R1 et R2 qui ont des attributs communs
(appelons-les X)
• Le schéma du résultat est
– similaire au schéma du produit cartésien
– modulo que les attributs X n’apparaissent qu’une fois
• Au niveau des lignes : on combine les lignes de
R1 avec les lignes de R2 qui ont même valeur
pour les attributs X
23
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Jointure naturelle : Exemple
R1
A
B
C
a1
b1
c1
a2
b2
c2
R2
A
D
a2
d2
a2
d3
a3
d4
R1
R2
R1
R2
Équivaut à
R1.A = R2.A
A
a2
a2
B
b2
b2
C
c2
c2
D
d2
d3
24
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Jointure naturelle : Définition formelle
• Input
– r et s de schéma R et S
• Output
r
r
s = {t / t(R)  r et t(S)  s}
s=

R union S
 (r x s)
Avec R  S = {B1, …, Bp}
 : t(R.B1)= t(S.B1) … t(R.Bp)= t(S.Bp)
25
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Phi (ou théta) Jointure
• Cas particulier de jointure avec condition
R1

R2
R1
 est une condition de sélection
R2
A
3
D
2
R1
E
B
C
2
3
1
7
1
1
4
9
2
2
R2
R2.A > R1.C
A
D
E
B
C
3
3
2
2
2
3
1
9
1
7
2
7
1
2
1
26
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Union, Intersection, Différence :
Introduction
• Tous ces opérateurs prennent en entrée 2
relations qui doivent avoir le même schéma
• Union (R1  R2) : ensemble de n-uplets qui
sont dans R1 ou dans R2
• Intersection (R1  R2) ensemble de n-uplets
qui sont dans R1 et dans R2
• Différence (R1 \ R2 ou R1 – R2) : ensemble de
n-uplets qui sont dans R1 mais pas dans R2
27
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Union, Intersection, Différence :
Exemples
R1  R2
R1
R2
A
B
a1
b1
A
R1  R2
A
B
A
B
A
B
a1
b1
a1
b1
a2
b2
a2
b2
a3
b3
a3
b3
a2
b3
a3
b4
B
a1
b1
a2
b2
a2
b3
a3
b3
a3
b4
Les n-uplets
à la fois
dans R1 et
dans R2
Les n-uplets de R1
union ceux de R2
 Pas de doublon
Département Informatique
R1 \ R2
Sarah Cohen-Boulakia, Bases de données
Les n-uplets
de R1 mais
qui ne sont
pas dans R2
28
Union, Intersection, Différence :
Définitions formelles
Input
– r, s deux relations de même schéma R
Output
r  s = { t / t  r ou t s }
r  s = { t / t  r et t s }
r \ s = { t / t  r et t  s }
de schéma R
29
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Exercice 2
• Quel est le résultat de la requête Q4
suivante en considérant les instances des
relations ci-dessous ?
Q4 =(A,B (C
R1
≠ c1
(R1)
))  R2
R2
A
B
C
A
B
a1
b1
c1
a2
b2
a2
b2
c2
a2
b3
a3
b3
c3
a3
b4
30
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Correction (Exercice 2) 1/3
• Quel est le résultat de la requête Q4
suivante en considérant l’instance des
relations ci-dessous ?
Q4 =A,B (C
R1
≠ c1
(R1)
)  R2
R2
A
B
C
A
B
a1
b1
c1
a2
b2
a2
b2
c2
a2
b3
a3
b3
c3
a3
b4
31
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Correction (Exercice 2) 2/3
• Quel est le résultat de la requête Q4
suivante en considérant l’instance des
relations ci-dessous ?
Q4 =A,B (C
R1
≠ c1
(R1)
)  R2
R2
A
B
C
A
B
a1
b1
c1
a2
b2
a2
b2
c2
a2
b3
a3
b3
c3
a3
b4
32
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Correction (Exercice 2) 3/3
• Quel est le résultat de la requête Q4
suivante en considérant l’instance des
relations ci-dessous ?
Q4 =(A,B (C
R1
≠ c1
(R1)
))  R2
R2
Q4
A
B
C
A
B
A
B
a1
b1
c1
a2
b2
a2
b2
a2
b2
c2
a2
b3
a3
b3
c3
a3
b4
33
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Propriétés algébriques
• L’union  et l’intersection  sont commutatifs et
associatifs
– r  s = s  r, r  (s1  s2) = (r  s1)  s2
– De même pour 
• Le produit cartésien est associatif et commutatif*
• La jointure est associative et commutative*
• D’autres propriétés peuvent être énoncées :
monotonie de certains opérateurs, fermeture etc.
*si on considère un ordre sur les colonnes
34
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Exemple d’équivalences
• Les relations d’équivalence de l’algèbres sont des
égalités entre des formules
• Elles sont à la base des optimisations de requêtes !
• Exercice
– Sachant que les schémas de R et S sont les mêmes et si
X  R, trouvez un contre exemple aux égalités qui sont
fausses et démontrez celles qui sont justes
• X (r  s) = X (r)  X (s)
• X(r – s) = X (r) - X (s)
• X(r  s) = X (r)  X (s)
35
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Exemple d’équivalences (correction)
• Exercice
– Sachant que R = S (schémas) et X  R, trouvez
un contre exemple aux égalités qui sont
fausses et démontrez celles qui sont justes
•X (r  s) = X (r)  X (s) NON
•X(r – s) = X (r) - X (s) NON
•X(r  s) = X (r)  X (s) OUI !
36
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Non Distributivité de l’intersection
• X (r  s) = X (r)  X (s) NON
Contre exemple
r
s
A
B
A
B
a
b
a
b’
A (r  s) = 
A (r)  A (s) = {a}
37
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Non Distributivité de la différence
• X (r - s) = X (r) - X (s) NON
Contre exemple
r
s
A
B
A
B
a
b
a
b’
A (r - s) = {a}
A (r) - A (s) = 
38
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Distributivité de l’union : Démonstration
X (r  s) = X (r)  X (s)
On se ramène aux définitions
Rappel :
X(r)={t(X) / t  r} ; tX(r)   t’ r et t’(X)=t
t  X (r  s)   t’, t’ (r  s) et t’(X)=t // par définition de la projection
  t’ ((t’ r) ou (t’s)) et t’(X)=t // par définition de l’union
  t’ ((t’ r) et t’(X)=t) ou ((t’s) et t’(X)=t) // distrib. de « et » sur « ou »
 ( t’, t’ r et t’(X)=t) ou ( t’, t’s et t’(X)=t) // distributivité de 
 t
X (r) ou t  X (s) // par définition de la projection

 t  ( X (r) 
X (s)) // par définition de l’union
39
Département Informatique
Sarah Cohen-Boulakia, Bases de données
Conclusion
• L’algèbre relationnelle est définie de façon
très rigoureuse et offre un langage de
requêtes à la fois simple et puissant
• L’algèbre relationnelle est à la base
– De SQL
– Des plans de requêtes utilisés par les SGBD
pour optimiser les requêtes
40
Département Informatique
Sarah Cohen-Boulakia, Bases de données