Download - Modèle relationnel et algèbre relationnelle
62
Modèle relationnel et algèbre
relationnelle
INT Management
63
Plan du document
� Modèle relationnel slide 64� Opérateurs de l’algèbre slide 78� Exemples de requêtes slide 94� Optimisation slide 99
64
Les concepts descriptifs
� Notion de domaine � Produit cartésien � Relation� Attribut� Clé� Schéma de relation et de BD� Clé étrangère� Métabase
Modèle relationnel
65
Notion de domaine
� Définition� Ensemble de valeurs
� Exemples� Entier, réel, chaîne de caractères, booléen� Salaire = 1000…100000 (€)� Couleur = {‘rosé’, ‘blanc’, ‘rouge’}
Modèle relationnel
66
Produit cartésien
� Définition� Le produit cartésien de D1, ...., Dn est l'ensemble des n-
uplets (tuples) <V1, ...., Vn> tel que Vi ∈ Di
� Notation� D1 X ....X Dn
� Exemple :� D1 = {‘BD’, ‘IO’} (Code UV)� D2 = {‘Carpentier’, ‘Lalevée’, ‘Millot } (Prof)
CarpentierIO
LalevéeIO
MillotIO
MillotBD
LalevéeBD
CarpentierBD
D2D1D1 X D2
Modèle relationnel
67
Relation
� Définition� Sous-ensemble du produit cartésien d'une liste de
domaines� Caractérisée par un nom
� Exemple� D1 = Code UV� D2 = Prof
Modèle relationnel
LalevéeIO
CarpentierBD
D2D1UV
68
Relation (2)
� Plus simplement, une relation est un tableau àdeux dimensions
� Une ligne est un n-uplet (tuple)� On associe un nom à chaque colonne afin de la
repérer indépendamment de l'ordre = attribut� Prend ses valeurs dans un domaine� Exemple : code
Modèle relationnel
LalevéeIO
CarpentierBD
coordcodeUV
69
Exemples de relations
21MaiselMeunier3
20CROUSMillot2
20MaiselBélaïd1
AgeAdresseNomNumElève
10BD2
20BD1
18IO3
17IO2
NoteCodeUVNumElèveInscrit
Modèle relationnel
70
Clé
� Définition� Une clé est un groupe d'attributs minimum qui détermine un n-
uplet unique dans une relation (à tout instant)
� Exemple� Clé de Elève ?� Clé de UV ?� Clé de Inscrit ?
� Contrainte d'intégrité� Toute relation doit posséder une clé renseignée (sans valeur
inconnue)
Modèle relationnel
71
Schéma de relation� Définition
� Le schéma d'une relation décrit :� Son nom� La liste des attributs qu'elle comporte et des domaines associés� La liste des attributs composant la clé (la clé est soulignée)
� Exemple� Elève(num : entier, nom : chaîne, adresse : chaîne, age :
entier de 18 à 35)
� Intention vs. Extension� Schéma de relation : intention de la relation� Table : extension� Schéma d'une BD relationnelle : ensemble des schémas des
relations
Modèle relationnel
72
Clé étrangère
� Définition� Une clé étrangère est un groupe d'attributs qui
apparaît comme clé dans une autre relationR1(A1, A2, .... , Ap, Ap+1, ...., An)
R2(B1, B2, ......, Bn)
� Rôle� Les clés étrangères définissent des contraintes
d'intégrités référentielles entre relations
Modèle relationnel
73
Clé étrangère (2)
� Mises à jour et clés étrangères� Insertion: la valeur des attributs doit exister dans la
relation référencée.� Insertion de (4, ‘BD’, 15) dans Inscrit ?
� Suppression dans la relation référencée; les n-uplets référençant doivent disparaître.� Suppression de l’élève 2 dans Elève ?
� Les clés étrangères sont la traduction des associations du modèle E/A
Modèle relationnel
74
Clé étrangère
� ExemplesÉlève(num, nom, adresse, age)
UV(code, nbh, coord)
Inscrit(numElève, codeUV, note)
Livre(côte, titre,numElève, datePrêt)
Chambre(no, prix, numElève)
Modèle relationnel
75
Métabase
� Définition� base de données contenant l'ensemble des schémas et des
règles de correspondances associées à une base de données
� Principe� Une base décrivant les autres bases, c'est-à-dire:
� les relations� les attributs� les domaines� les clés .....
� Notion de dictionnaire de données� Base particulière, système, gérée par l'administrateur de BD
Modèle relationnel
76
Résumé des notionsModèle relationnel
Clé
Tab
le
Schéma
Intention
Tuple / n-uplet
Relation2Clé
étrangère
Relation1 AttributnAttribut1
Attributa
Attribut2
vn
v1
v3
w1
v1
w2
w1
w2
w2
Attributb
xi
wn
BD
Attribut2Relation1
Attribut1Relation1
AttributNomRelSchémaBD
Métabase
…
77
Langages associés au modèle relationnel
� Langages de Définition de Données (LDD) :� Définition /mise à jour des schémas des relations
� Langages de manipulation de données (LMD) :� Interrogation : recherche de données� Mises à jour : insertion, suppression, modification
� 2 classes de langages :� Algébriques � SQL� Prédicatifs � QBE
78
Les opérateurs de manipulation
� Tout résultat d'une opération est une relation; peut donc être réutilisée en entrée d'un nouvel opérateur.
� Les opérateurs peuvent être classifiés en :� opérateurs ensemblistes / opérateurs relationnels� opérateurs de base / opérateurs dérivés� opérateurs unaires / opérateurs binaires
� Unaires : sélection (restriction), projection, � Binaires : union, intersection, différence, produit cartésien, jointure,
division
Algèbre relationnelle
79
Restriction� But
� Permet de "sélectionner" des tuples
� La restriction réduit la taille de la relation verticalement
� Contraintes� Unaire� Spécifier une condition
� Notation� Notation textuelle:
Tσcond(R)� Notation graphique:
� Inscrits en BD : σcodeUV=‘BD’(Inscrit)
� Majors (note > 15) de BD : σcodeUV=‘BD’ et note >15(Inscrit)
Algèbre relationnelle
Cond.
10BD2
20BD1
NoteCodeUVNumElèveResu
20BD1
NoteCodeUVNumElèveResuR
T
80
Projection
� But� Permet de "sélectionner" des
attributs� La projection réduit la taille de la
relation horizontalement
� Contraintes� Unaire� Spécifier une liste d'attributs
� Notation� Notation textuelle: TΠattributs(R)� Notation graphique:
R
T
� Adresses des élèves : ΠAdresse(Elève)
� Code et nb heures des UV : Πcode,nbh(UV)
Algèbre relationnelle
attributs.
Maisel
CROUS
AdresseResu
15BD
45IO
nbhCodeResu
Pas de
doublon
81
Union
� But� Permet de fusionner 2 relations
� Contraintes� Binaire� Même schéma
� Notation� Notation textuelle: T R ∪ S � Notation graphique:
� Nom des profs, des élèves
� Nom des personnes à l’INT : Prof∪Eleve2
Algèbre relationnelle
∪
R S
T
Carpentier
Millot
Lalevée
NomProf
Meunier
Millot
Bélaïd
NomElève2
Meunier
Bélaïd
Carpentier
Millot
Lalevée
NomResu
Pas de
doublon
82
Intersection
� But� Permet d’obtenir l’ensemble des
tuples appartenant à deux relations
� Contraintes� Binaire� Même schéma
� Notation� Notation textuelle: T R ∩ S � Notation graphique:
� Nom des profs, des élèves
� Noms communs élèves-profs : Prof∩Elève2
Algèbre relationnelle
∩
R S
T
Carpentier
Millot
Lalevée
NomProf
Meunier
Millot
Bélaïd
NomElève2
Millot
NomResu
83
Différence� But
� Obtenir l’ensemble des tuplesd’une relation qui ne figurent pas dans une autre
� Contraintes� Binaire� Même schéma
� Non commutatif
� Notation� Notation textuelle: T R - S � Notation graphique:
� Nom des profs, des élèves
� Noms des élèves qui ne portent pas le nom d’un prof : Eleve2-Prof
Algèbre relationnelle
-
R S
T
Carpentier
Millot
Lalevée
NomProf
Meunier
Millot
Bélaïd
NomElève2
Meunier
Bélaïd
NomRésu
84
Produit cartésien
� But� Ensemble de tous les tuples obtenus par concaténation de chaque
tuple de R avec chaque tuple de S� Contraintes
� Binaire� Schéma du résultat:
� R(a1, a2, ...., an), S(b1, b2, ..., bp)� T R X S, T(a1, a2, ...., an, b1, b2, ..., bp)
� Card (R X S) = Card (R) * Card (S)� Notation
� Notation textuelle: T R X S � Notation graphique:
Algèbre relationnelle
X
R S
T
85
Produit cartésien (2)Algèbre relationnelle
21MaiselMeunier3
20CROUSMillot2
20MaiselBélaïd1
AgeAdresseNomNumElève
Carpentier15BD
Lalevée45IO
CoordNbhCodeUV
Carpentier15BD21MaiselMeunier3
Carpentier15BD20CROUSMillot2
Carpentier15BD20MaiselBélaïd1
21
20
20
Age
IO
IO
IO
Code
45
45
45
Nbh
Lalevée
Lalevée
Lalevée
Coord
MaiselMeunier3
CROUSMillot2
MaiselBélaïd1
AdresseNomNumElève X UV
86
Jointure
� But� Permet d’établir le lien sémantique entre les relations
� Contraintes� Binaire� Schéma du résultat:
� R(a1, a2, ...., an), S(b1, b2, ..., bp)� T R �� S T(a1, a2, ...., an, b1, b2, ..., bp)
� Notation� Notation textuelle: T R �� S
condition
� Notation graphique:
Algèbre relationnelle
R S
T
condition
87
200
150
Prix
3
2
numElève
1021MaiselMeunier3
2120CROUSMillot2
Age NoAdresseNomNumElève��Chambre
150
200
Prix
2
3
numElève
21
10
NoChambre
Elève.Num=Chambre.numElève
1er exemple de jointure
� 1 tuple de Chambre � 1 tuple de résultat� 1 tuple de Elève � 0 ou 1 tuple de résultat
� On a perdu Bélaïd !
21
20
20
Age
MaiselMeunier3
CROUSMillot2
MaiselBélaïd1
AdresseNomNumElève
88
3
2
2
1
NumElève
18
10
17
20
Note
IO21MaiselMeunier3
BD20CROUSMillot2
20
20
Age
IO
BD
CodeUV
CROUSMillot2
MaiselBélaïd1
AdresseNomNumInscrit ��Elève
10BD2
20BD1
18IO3
17IO2
NoteCodeUVNumElèveInscrit
21MaiselMeunier3
20CROUSMillot2
20MaiselBélaïd1
AgeAdresseNomNumElève
Inscrit.NumElève=Elève.Num
2ème exemple de jointure
� 1 tuple de Inscrit � 1 tuple de résultat
� 1 tuple de Elève � 0 à n tuples de résultat
� On a dupliqué Millot !
89
Division� But
� Répondre aux requêtes de type « tous les »� Un tuple t est dans T si et seulement si pour tout tuple s de S, le tuple <t,s> est
dans R
� Contraintes � Binaire� Schéma du résultat:
� R(a1, a2, ...., an, b1, b2, ..., bp), S(b1, b2, ..., bp)� T R ÷ S, T(a1, a2, ...., an)
� Notation� Notation textuelle: T = R � S� Notation graphique:
� Dérivation� Projection + Produit cartésien + Différence.� R � S T1 - T2 avec:
� T1 ∏ schéma(R) - schéma(S) (R)� T2 ∏ schéma(R) - schéma(S) ((∏ schéma(R) - schéma(S) (R) X S) - R)
Algèbre relationnelle
�
R S
T
90
Division (2)
� Exemple� Quels sont les élèves inscrits à toutes les UVs ?
Algèbre relationnelle
10BD2
20BD1
18IO3
17IO2
NoteCodeUVNumElèveInscrit
91
Division (3)� Exemple
� Construire R : ensemble de toutes les informations dont on a besoin = attributs NumElève et CodeUV de Inscrit (R)
� Construire S : ensemble correspondant à "tous les" (UV) = codeUV (S)
� Résultat R � S� Vérification :
� Résultat X S ⊆ R
� RΠNumElève,CodeUV(Inscrit)
� SΠCode(UV)
� Résultat
BD2
BD1
IO3
IO2
CodeUVNumElèveR
BD
IO
CodeUVS
2
NumElèveResu
Algèbre relationnelle
92
Bilan : sémantique et notations des
opérateurs
Algèbre relationnelle
T R � S
T R �� Scondition
T R X S
T R - S
T R ∩ S
T R ∪ S
TΠattributs(R)
Tσcond(R)
Notation textuelle
Répondre aux requêtes de type « tous les »Division
conditionEtablir le lien sémantique entre les relationsJointure
Concaténer chaque tuple de R avec chaque tuple de S
Produit cartésien
Tuples d’une relation qui ne figurent pas dans une autre
Différence
Obtenir l’ensemble des tuples communs à deux relations
Intersection
Fusionner les extensions de 2 relationsUnion
« Sélectionner » des attributsProjection
« Sélectionner » des tuplesRestriction
Notation graphique
SémantiqueOpérateur
Cond.
attributs.
∪
∩
−
X
�
condition
93
Bilan : contraintes des opérateursAlgèbre relationnelle
Schéma(R)=Schéma(S)+Schéma(T)
Schéma(T)=Schéma(S)∪Schéma(R)
Schéma(T)=Schéma(S)∪Schéma(R)
Schéma(R)=Schéma(S)=Schéma(T)
Schéma(R)=Schéma(S)=Schéma(T)
Schéma(R)=Schéma(S)=Schéma(T)
Schéma(T) ⊆ Schéma(R)
Schéma(T) = Schéma(R)
Schémas
BinaireDivision T R � S
Condition de jointure sur attributs de R et S
BinaireJointure T R �� Scondition
BinaireProduit cartésien T R X S
BinaireDifférence T R - S
BinaireIntersection T R ∩ S
BinaireUnion T R ∪ S
Liste d’attributs de RUnaireProjection TΠattributs(R)
Condition sur attributs de RUnaireRestriction Tσcond(R)
« Paramètres »Unaire/Binaire
Opérateur
94
Exemples de requêtes en algèbre
relationnelle
� Base de données exemple : les vinsVins(num, cru, annee, degre)Recoltes(nvin, nprod, quantite)Producteurs(num, nom, prenom, region)Buveurs(num, nom, prenom, ville)Commandes(ncde, date, nb, nvin, qte)Livraisons(ncde, no_ordre, qteLivree)
Exemples
V
R
P
B
C
L
95
Modèle E/A de la BD des vins
0,n
1,1
(1,1)0,n
1,1
0,n
0,n
0,n
Vins
numcrudegréannee
Producteurs
numnomprenomregion
recoltes
Livraisons
No_ordreqteLivréedate
concerne
Buveurs
numnomprenomville
Commandes
ncdeqtédate
Passer
Donner_lieu
quantite
Exemples
96
Composition des opérateurs
� Ecriture textuelle :Temp σ annee=1995 (Vins)Resultat Π num(Temp)Resultat Π num(σ annee=1995 (Vins))
� Arbre algébrique : annee=1995
num
Vins.
Résultat
Exemples
97
Noms des producteurs de Muscadet
Mus σ cru=‘Muscadet’ (V)
RecMus Mus �� RMus.num=R.nvin
ProdMus RecMus �� PRec.nprod=P.num
Resultat Π nom(ProdMus)
Exemples
cru=‘Muscadet’.
nom
Vins
Résultat
Recoltes
Producteurs
num=nvin
nprod=num
98
Numéros des vins ne faisant l’objet d’aucune commandeNumVins Π num(V)
VinsCdes Π nvin(C)
Resultat NumVins – VinsCdes
Exemples
num
Résultat
Vins
nvin
Commandes
-
99
Optimisation
� Noms et prénoms des buveurs habitant Paris ayant commandé du Mâcon 1995 avant le 01 janvier 2000� Plusieurs réponses possibles� Optimiser = choisir la meilleure façon
Optimisation
100
Réponse 1
ville=‘Paris’
Buveurs
num=nb
nvin=num
nom, prenom
Résultat
date < 01/01/2000
Commandes
cru=‘Mâcon’ et
annee=1995
Vins
Optimisation
101
Réponse 2
ville=‘Paris’
Buveurs
num=nb
nvin=num
nom, prenom
Résultat
date < 01/01/2000
Commandes Vins
cru=‘Mâcon’ et
annee=1995
Optimisation
102
Réponse 3
ville=‘Paris’ et
date < 01/01/2000 et
cru=‘Mâcon’ et
annee =1995
Buveurs
num=nb
nvin=num
nom, prenom
Résultat
Commandes Vins
Optimisation
103
Réponse 4
date < 01/01/2000
Commandes
nvin,nb
num=nb
nvin, nom, prenom
cru=‘Mâcon’ et
annee=1995
Vins
num
nvin=num
nom, prenom
Résultat
ville=‘Paris’
Buveurs
nb, nom, prenom
Optimisation
104
Optimisation par le SGBD
� Optimiser :� Le nombre d’E/S� Le temps UC� …
� Facteurs déterminants :� Ordre d'exécution des opérations algébriques� Algorithme implantant les opérations algébriques� Placement des données sur le disque� Taille des relations intermédiaires
Optimisation
105
Restructuration
� Heuristique la plus employée� Remonter les opérateurs unaires (restriction, projection) =>
diminution de la taille des relations� Descendre les jointures
� Propriétés utilisées� Associativité des jointures� Commutativité restriction / projection� Commutativité restriction / jointure� Commutativité projection / jointure
Optimisation
106
Problème
� Libérer l’utilisateur des problèmes d’optimisation� Exprimer une question sans préciser l’enchaînement des
opérations algébriques� � langage non procédural� � langage déclaratif
� Responsabilités du SGBD :� Traduction sous la forme d’un arbre algébrique� Optimisation de l’arbre� Exécution de l’arbre optimisé
Optimisation