slides - Université de la Nouvelle

Fondements des Bases de Données
Frédéric Flouvat
Université de la Nouvelle-Calédonie
[email protected]
Présentation de l'UE
Volume horaire :
• 14h de cours (7 séances), 16h de TD (8 séances) et 10h de TP (5
séances)
• répartition cours/TD en fonction de l'avancement
• TP pour appliquer une fois les notions abordées en cours et TD
Evaluation en contrôles continus:
• 2 TP notés -> 2 notes de CC
• un CC à la fin de chaque chapitre -> 2 notes de CC
Objectif : interrogations avancées et normalisation des bases de
données
• approfondir les connaissances pratiques (contraintes, programmation
procédurale, vues, …) et théoriques (algèbre relationnel, dépendances et
normalisation)
2
Plan du cours
Chapitre 1: Manipuler les données
• Rappels: modèle relationnel, algèbre relationnel et SQL
• Programmation procédurale
• Contrôler les transactions
Chapitre 2: Définir et structurer les bases de données
• Contraindre les données
• Définir des vues des données
• Normaliser leur structure
3
Chapitre 1: Manipuler les données
Rappels
Frédéric Flouvat
(en partie dérivé du cours du Pr. Jeffrey Ullman, Stanford University)
Université de la Nouvelle-Calédonie
[email protected]
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Généralités
Une Base de Données : ensemble structuré et organisé permettant le
stockage de grandes quantités d’informations afin d'en faciliter l'exploitation.
Un Système de Gestion de Bases de Données : comprend un ensemble
de données liées (la bd) et un ensemble de programmes permettant la
définition, la saisie, le stockage, le traitement, la modification, la diffusion et
la protection de ces données.
•
•
•
•
•
•
•
Liens entre les données
Cohérence des données
Souplesse d’accès aux données
Sécurité
Partage des données
Indépendance des données
Performances
5
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Architecture ANSI/SPARC
Niveau
externe
Groupe d’utilisateurs 1
Groupe d’utilisateurs 2
Vue 1
Vue 2
Groupe d’utilisateurs n
…
Vue n
Schéma conceptuel
Niveau
conceptuel
Niveau
physique
Schéma logique
Représentation physique
Base de
Données
6
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
La démarche de conception
Univers
d’application
Conceptuel
Elaboration du
schéma Entité-Association
Logique
Passage au
schéma relationnel
Physique
Implémentation
sur le SGBD
7
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Modèle relationnel
Dans le modèle relationnel, les données sont structurées en
relation, d'ordinaire représentées sous forme tabulaire.
• Ex :
Personnes
nss
nom
prenom
age
12
Ijo
John
45
45
Mayer
Solange
35
Une relation est définie sur un schéma de relation.
Chaque ligne est appelée un tuple.
Les noms des colonnes sont appelés attributs.
• Ex : La relation Personnes est définie sur le schéma PERSONNES qui
compte 4 attributs : schema(PERSONNES)={nss, nom, prenom, age}
t = <12, Ijo, John, 45> est un tuple de cette relation (tPersonnes)
8
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Modèle relationnel
Une base de données est un ensemble de relations ri sur Ri.
Un schéma de base de données est un ensemble {R1, …, Rn} de
schémas de relations.
• Ex : Soit d={Personnes, Departements, Activites} une base de données.
Personnes
nss
nom
prenom
age
12
Ijo
John
45
45
Mayer
Solange
35
Activites
Departements dep
adresse
Sciences
Nouville
Lettres
Magenta
#nss #dep
fonction
12
Sciences Prof
45
Sciences Vacataire
45
Lettres
MdC
12
Eco
Vacataire
9
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Définitions
Soit A un attribut. On note DOM(A), le domaine de A i.e.
l’ensemble des valeurs que peux prendre l’attribut A.
• Ex : DOM(dep) = {Sciences, Lettres, Eco, Droit, SHS …}
• Ex : DOM(age) = ensemble des entiers naturels
Le domaine actif de A dans r noté ADOM(A,r), est l’ensemble des
valeurs constantes prises par A dans r.
• Ex : ADOM(dep,Activites) = {Sciences, Lettres, Eco}
La projection d’un tuple t sur l’attribut A (resp. {A,B}) est notée t[A]
(resp t[A,B]).
• Ex : Soit t = <12, Ijo, John, 45>
• t[nom] = <Ijo> et t[nom, prenom] = <Ijo, John>
10
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Différents types de langages
LDD : Langage de Définition de Données
• Schéma de la bd et des vues
LMD : Langage de Manipulation de Données
• Requêtes, mises à jour
LCD : Langage de Contrôle des Données
• Gestion des accès utilisateurs
LCT : Langage de Contrôle des Transactions
• Gestion des transactions
11
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Langages de requêtes relationnels
Les langages de requêtes sont généralement déclaratifs i.e. on
spécifie ce que la sortie doit contenir et non comment l’obtenir.
Les SGBD relationnels fonctionnent en amont avec des langages
procéduraux qui spécifient comment obtenir les résultats aux
requêtes.
• Déclaratif : {nss  DOM(Activites) | (nss,dep,fonction)Activites,
(dep,adresse)Departements,
adresse=‘Carnot'}
• Procédural :
for each tuple t1=<n,d,f> in relation Activites do
for each tuple t2=<d’,a> in relation Departements do
if d=d’ and a=‘Carnot' then output n
end
end
12
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Langages de requêtes relationnels
Le modèle relationnel supporte des langages de requêtes simples et
puissants qui permettent beaucoup d’optimisation.
Langages théoriques
• Algèbre relationnelle : Langage procédural très utile pour représenter
les plans d'exécution des requêtes.
• Calcul relationnel : Langage déclaratif orienté utilisateur.
• Datalog : Langage déclaratif à base de règles. Augmente le calcul
relationnel avec des capacités d'inférence.
Langages commerciaux
• SQL : Les opérateurs s’inspirent des différents langages théoriques.
13
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Syntaxe générale d'une requête SQL
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
<liste des attributs à projeter ou *>
<liste des tables>
<critères de restriction>]
<liste des attributs d’agrégation>]
<critères de restriction sur les agrégats>]
<liste des attributs de tri>]
SELECT
<liste des attributs à projeter ou *>
FROM
<liste des tables>
[CONNECT BY PRIOR <critères de récursivité>]
[START WITH <condition de départ>]
14
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Principes des langages de requêtes relationnels
Elle est constituée d'un ensemble d'opérateurs algébriques :
• π, σ, , , 
• Entrée : une ou deux relations (opérateurs unaires ou binaires)
• Sortie : une relation

Requête relationnelle : Composition d'un nombre fini
d'opérateurs algébriques. Le résultat d'une requête est une
relation.
L'ordre d'évaluation des opérateurs est spécifié dans la requête
(requête procédurale).
L’algèbre relationnelle manipule des ensembles.
15
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Intérêts
A la base de l’optimisation de requêtes :
• Les SGBDR traduisent les requêtes formulées avec SQL en une
requête interne construite avec les opérateurs algébriques,
requête qui sera par la suite optimisée pour en générer un plan
d’exécution équivalent.
Tous les LMD sont construits à partir de ces opérateurs de base.
• + généralement mise à jour de la bd et emploi d'expressions
arithmétiques et de fonctions d'agrégation telles que cardinalité,
somme, minimum, maximum et moyenne.
16
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Les opérateurs
Opérateurs de base
•
•
•
•
•
Projection (π)
Sélection (σ)
Produit cartésien ()
Différence ()
Union ()
Opérateurs supplémentaires (non essentiels, mais très utiles)
•
•
•
•
Intersection ()
Jointure (⋈)
Division ()
Renommage (ρA→B)
17
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Projection
Soient r une relation sur R et Y  schema(R).
La projection de r sur Y notée πY(r), est définie par :
πY(r) = { t[Y]  DOM(Y) | tr }
Soit S le schéma de relation associé à πY(r). On a schema(S)=Y.
• Ex : πnom(Personnes) =
πnom,prenom(Personnes) =
πdep(Activites) =
dep
nom
nom
prenom
Ijo
Ijo
John
Mayer
Mayer
Solange
Sciences
Lettres
Eco
18
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Sélection
Soient r une relation sur R et F une formule de sélection.
La sélection des tuples de r par rapport à F notée σF(r), est définie
par :
σF(r) = { t  r | t ⊨ F }
Soit S le schéma de relation associé à σF(r). On a schema(S) =
schema(R).
• Ex : σfonction=‘Prof’(Activites) =
nss
dep
fonction
12
Sciences Prof
19
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Sélection
Une formule de sélection simple sur R est une expression de la
forme : A = a ou A = B, où A, B  schema(R) et a  Dom(A).
Une formule de sélection est une expression composée de
formules de sélection simples connectées à l'aide des connecteurs
logiques ,,  et des parenthèses.
Soit r une relation sur R, tr et F une formule de sélection.
t satisfait F, noté t ⊨ F, est défini récursivement par :
1)
2)
3)
4)
5)
6)
t ⊨ A = a si t[A] = a
t ⊨ A = B si t[A] = t[B]
t ⊨ F1F2 si t ⊨ F1 et t ⊨ F2
t ⊨ F1F2 si t ⊨ F1 ou t ⊨ F2
t ⊨ F si t ⊭ F
t ⊨ (F) si t ⊨ F
20
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Opérateurs ensemblistes
r1
Soient r1 et r2 deux relations sur R1 et R2.
Union : r1  r2 = { t | t  r1 ou t  r2 }.
Différence : r1 – r2 = { t | t  r1 et t  r2 }.
r2
Intersection : r1  r2 = { t | t  r1 et t  r2 }.
r1  r2 =
• Ex :
r1  r2 =
r1 - r2 =
A
B
C
A
B
C
A
B
C
1
2
3
1
2
3
1
1
1
1
1
1
1
2
2
1
2
2
2
2
2
A
B
C
1
2
3
1
1
1
1
2
2
A
B
C
2
2
2
1
1
1
21
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Opérateurs ensemblistes
Propriétés :
• σF(r) = r  σF(r)
• σF1F2(r) = σF1(r)  σF2(r)
• σF1F2(r) = σF1(r)  σF2(r)
Toutes ces opérations prennent comme entrées deux relations qui
doivent être compatibles vis-à-vis de l’union.
Soit S le schéma de relation associé à r1  r2 , r1 - r2 ou r1  r2. On a
par convention schema(S) = schema(R1).
22
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Produit cartésien
Soient r1 et r2 deux relations sur R1 et R2 avec
schema(R1)schema(R2) = ø.
Le produit cartésien r1  r2 est définie par :
r1  r2 = { t | t1  r1 et t2  r2 tel que
t[schema(R1)]= t1 et t[schema(R2)]= t2 }
Soit S le schéma de relation associé à r1  r2. On a schema(S) =
schema(R1)schema(R2).
23
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Produit cartésien
r1
A
B
C
1
2
1
1
r2
D
E
3
2
4
1
1
1
3
2
2
A B C D E
• Ex :
r1  r2 =
1 2 3 2 4
1 1 1 2 4
1 2 2 2 4
1 2 3 1 3
1 1 1 1 3
1 2 2 1 3
24
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Jointure
Soient r1 et r2 deux relations sur R1 et R2 et F une formule de
sélection.
La jointure de r1 et r2 par rapport à F, notée r1 ⋈F r2, est définie par :
r1 ⋈F r2 = σF(r1  r2)
Soit S le schéma de relation associé à r1⋈F r2. On a schema(S) =
schema(R1)schema(R2).
•
•
•
•
Équi-jointure quand F est une égalité.
Thêta-jointure quand F n’est pas une égalité (>,<,>=,<=,<>).
Auto-jointure quand r1 = r2.
Jointure naturelle quand équi-jointure sur tous les attributs communs à
R1 et R2 + projection (pas utile d’écrire F).
25
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Jointure
r1

Ex : r1  r2 =
A B C D E
1 2 3 2 4
1 1 1 2 4
1 2 2 2 4
1 2 3 1 3
A
B
C
1
2
1
1
r2
D
E
3
2
4
1
1
1
3
2
2
r1 ⋈A=D r2 =
A B C D E
1 2 3 1 3
1 1 1 1 3
1 2 2 1 3
r1 ⋈A<D r2 =
A B C D E
1 2 3 2 4
1 1 1 2 4
1 2 2 2 4
1 1 1 1 3
1 2 2 1 3
26
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Jointure naturelle
r2
r1
A
B
C
2
2
2
1
1
1

Ex :
A
D
E
1
2
1
r3
A
B
C
3
1
1
1
2
1
2
2
3
1
3
1
2
r1 ⋈ r2 =
r 1 ⋈ r3 =
r1  r3
r4
D
E
F
1
1
1
1
3
2
2
2
r 1 ⋈ r4 =
r1  r4
A B C D E
A
B
C
A B C D E F
2 2 2 3 1
1
1
1
2 2 2
1
1
1
1 1 1 2 3
2 2 2
2
2
2
1 1 1 1 2
1 1 1
1
1
1
1 1 1
2
2
2
27
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Renommage
Soient r une relation sur R, A  schema(R) et B  schema(R).
Le renommage de A en B dans r noté ρA→B(r), est défini par :
ρA→B(r) = { t | ur, t[schema(R)  {B}] = u[schema(R)  {A}]
et t[B] = u[A]}
Soit S le schéma de relation associé à ρA→B(r). On a schema(S) =
(schema(R){A}){B}.
• Ex : r1 ⋈ ρB→B’,C→C’(r3) =
A B C B C’
’
1 1 1 1 1
1 1 1 2 3
28
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Division
La division permet d'exprimer le quantificateur universel ().
Exemple de requête : "Donner la liste des étudiants qui sont inscrits
à tous les cours".
Inscriptions
etud
cours
1
BD
1
RO
1
BI
2
RO
Enseignements
cours
BD
RO
BI
29
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Division
Soient r une relation sur R, avec schema(R) = XY, et s une relation
sur S, avec schema(S) = Y.
La division de r par s notée r  s, est défini par :
r  s = { t[X] | tr et sπY(σF(r))}
avec X={A1,..., Aq} et F =(A1=t[A1])…(Aq=t[Aq])
Soit schema(T) le schéma de relation associé à r  s.
On a schema(T) = X.
30
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Division
La division n'est pas une opération essentielle car elle peut être
exprimée à l'aide des opérateurs de projection, différence et produit
cartésien.
Pour calculer r  s, il faut calculer toutes les valeurs de X dans r qui
ne sont pas disqualifiées par des valeurs de Y dans s.
Une valeur de X est disqualifiée si en lui rattachant une valeur de Y
de s, le tuple obtenu sur XY n'appartient pas à r.
• Valeurs disqualifiées de X : πx((πx(r)  s)  r)
• r  s = πx(r)  valeurs disqualifiées de X
 r  s = πx(r)  πx((πx(r)  s)  r)
31
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Division
Mod1
F3
cours
c2

Ex : UNC  Mod1 =
etud
Mod2
UNC
cours
etud
cours
2
c1
1
c1
4
c3
1
c2
c4
1
c3
1
c4
2
c1
2
c2
3
c2
4
c2
4
c4
UNC  F3 =
UNC  Mod2 =
etud
1
cours
etud
2
c2
1
3
4
32
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Propriétés
σF(σF’(r)) = σF’(σF(r))
πX(πY(r)) = πXY(r)
σF(πX(r)) = πX(σF(r))
σF(r ⋈ s) = σF(r) ⋈ s si F porte sur schema(R)
πX(r ⋈ s) = πX(r) ⋈ πX(s) si schema(R)  schema(S) = X
(r1  r2) ⋈ r3 = (r1 ⋈ r3)  (r2 ⋈ r3)
Ex : Si schema(R1)=XY et schema(R2) =XZ, exprimer r1 ⋈ r2 avec , ρ, σ, π
r1 ⋈ r2 = πXYZ(σX=X’(r1 
ρX→X’(r2)))
Ex : Si schema(R1)=schema(R2), exprimer r1  r2 avec 
r1  r2 = r1  (r1  r2)
33
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Représentation en arbre
Une expression algébrique peut se représenter sous forme d’arbre :
• La racine de l’arbre correspond à la requête
• Les feuilles de l’arbre correspondent aux relations
• Les nœuds de l’arbre correspondent aux opérateurs
Q
algébriques
πX
• Ex : Représenter
Q = πX(σC1(r1 ⋈ r2) ⋈ (σc2(r3)r4))
⋈

σC1
σc2
⋈
r1
r2
r4
r3
34
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Arbres et optimisation de requêtes
Femme
c1
(nom)
Cathy
Julia
Linda
c2
(adr)
Wé
Nouméa
Nouméa
Homme
c3
(nom)
Bob
Sam
c4
(adr)
Nouméa
Koné
Quels sont les couples possibles avec une femme de Nouméa et un
homme de Koné ?
πc1, c3
4 col, 2 lig
σc2= "Nouméa" and c4= "Koné"
x
femme
πc1
σc2= "Nouméa"
2 col, 2 lig
homme
42 cases manipulées
1 col, 1 lig
π c3
2 col, 2 lig
4 col, 6 lig
2 col, 3 lig
x
1 col, 2 lig
2 col, 3 lig
femme
2 col, 1 lig
σc4= "Koné"
2 col, 2 lig
homme
19 cases manipulées
35
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Extension de l’algèbre relationnelle
Quatre nouveaux opérateurs:
δ (delta) : élimine de la relation résultat les tuples en double.
 Extension de la théorie aux multi-ensembles
τ (tau) : trie les tuples.
γ (gamma) : groupe et agrège.
Outerjoin : évite les "tuples incomplets" = les tuples qui ne sont
liés/joins à aucun autre tuple.
36
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Elimination des doublons
R1 :=
δ(R2).
R1 est une copie de chaque tuple qui apparaissent dans R2 une ou
plusieurs fois (une copie sans doublons donc).
Exemple:
R= (
δ(R) =
A
B)
1
3
1
2
4
2
A
B
1
3
2
4
37
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Trier
R1 := τL (R2).
• L est une liste d’attributs de R2.
R1 est la liste des tuples de R2 triés par rapport au premier
attribut de L, puis par rapport au deuxième attribut, …
• si plusieurs solutions sont possibles, en prend une
arbitrairement
τ est le seul opérateur n’ayant pas pour résultat un ensemble
ou un multi-ensemble.
• le résultat est une séquence de tuples
Exemple:
R= (A
1
3
5
B)
2
4
2
τ
B
(R) = [(5,2), (1,2), (3,4)]
38
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Opérateurs d’agrégation
Les opérateurs d’agrégation ne sont pas des opérateurs de
l’algèbre relationnelle.
Ils sont appliqués sur une colonne d’une table et ont pour résultat
une seule valeur.
Exemple d’opérateurs: SUM, AVG, COUNT, MIN, et MAX.
Exemple:
R= (A B)
1
3
3
4
3
2
SUM(A) = 7
COUNT(A) = 3
MAX(B) = 4
AVG(B) = 3
39
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Opérateur de regroupement
R1 :=
1.
2.
γ
L
(R2). L est une liste composées d’éléments de type:
attributs (regroupement ).
AGG(A), où AGG est un des opérateurs d’agrégation et A est
un attribut.
–
Une flèche et un nouveau nom d’attribut renomme la composante.
Fonctionnement de
•
L
(R)
Groupe R par rapport à tous les attributs de regroupement de L
–
•
•
γ
forme un groupe pour chaque liste de valeur distincte pour les
attributs de R
Pour chaque groupe, calcule AGG(A)
Chaque groupe est associé à un tuple dans la relation résultat
–
–
avec pour attributs, les attributs de regroupement
et les résultats des opérations d’agrégation
40
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Opérateur de regroupement
Exemple:
R= ( A
1
4
1
γ
A,B,AVG(C)->X
B
2
5
2
C
3
6
5
)
(R) = ??
Premièrement, regrouper R
par rapport A et B :
A
B
C
1
2
3
1
2
5
4
5
6
Puis, calculer la moyenne
de C pour chaque groupe:
A
1
4
B
2
5
X
4
6
41
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Jointure externe (Outerjoin)
Supposons que l’on fasse R
⋈
C
S.
Un tuple de R qui n’est associé à aucun tuple de S par jointure est
dit incomplet.
• De la même manière pour un tuple de S.
La jointure externe conserve les tuples incomplets dans la solution
en leur associant la valeur NULL.
42
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Jointure externe (Outerjoin)
Exemple:
R=
(
A
1
4
B)
2
5
S= ( B
2
6
C )
3
7
(1,2) est associé par jointure à (2,3), mais les autres tuples
sont incomplets
R OUTERJOIN S =
A
1
4
NULL
B
2
5
6
C
3
NULL
7
43
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
MAINTENANT --- RETOUR AU SQL
Chaque opération a un équivalent en SQL
44
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Jointures externe
R OUTER JOIN S est l’instruction principale pour faire une jointure
externe. Elle est complétée par:
Uniquement
1. NATURAL devant OUTER (Optionnel).
une des deux
2. ON <condition> après JOIN (Optionnel).
3. LEFT, RIGHT, ou FULL devant OUTER (Optionnel).



LEFT = conserve les tuples incomplets de R seulement.
RIGHT = conserve les tuples incomplets de S seulement.
FULL = conserve les tuples incomplets des deux; valeur par défaut.
Exemple:
A partir de Sells(bar, beer, price) et Frequents(drinker ,bar), afficher
tous les clients et les bières qu’ils peuvent consommer
SELECT drinker, beer
FROM Sells, Frequents
RIGHT OUTER JOIN ON Sells.bar = Frequents.bar ;
45
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Agrégations
SUM, AVG, COUNT, MIN, et MAX peuvent être appliqués à une
colonne dans une clause SELECT afin d’appliquer l’agrégation sur
cette colonne.
Egalement, COUNT(*) pour compter le nombre de tuples.
Exemple:
A partir de Sells(bar, beer, price), trouver le prix moyen d’une Bud:
SELECT AVG(price)
FROM Sells
WHERE beer = ’Bud’;
46
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Eliminer les doublons dans une Agrégation
Utiliser DISTINCT à l’intérieur de l’agrégation.
Exemple:
Trouver le nombre de prix différents associés à la Bud:
SELECT COUNT(DISTINCT price)
FROM Sells
WHERE beer = ’Bud’;
47
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Les NULL ignorés dans les Agrégations
Les valeurs NULL ne contribuent jamais à une somme, une
moyenne, ou un comptage, et ne peuvent jamais être le minimum ou
le maximum d’une colonne.
Mais s’il n’y a pas de valeurs non-NULL dans une colonne, alors le
résultat de l’agrégation est NULL.
• Exception: COUNT d’un ensemble vide retourne 0.
Exemple:
SELECT count(*)
FROM Sells
WHERE beer = ’Bud’;
SELECT count(price)
FROM Sells
WHERE beer = ’Bud’;
Le nombre de bars qui
vendent de la Bud
Le nombre de bars qui
vendent de la Bud à un
prix connu
48
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Groupement
Après l’expression SELECT-FROM-WHERE, ajouter GROUP BY et
une liste d’attributs.
La relation qui résulte du SELECT-FROM-WHERE est groupées en
accord avec les valeurs de tout ces attributs, et un opérateur
d’agrégation est appliqué sur chaque groupe.
Exemple:
A partir de Sells(bar, beer, price), trouver ler prix moyen de chaque
bière:
SELECT beer, AVG(price)
FROM Sells
GROUP BY beer;
beer
Bud
…
AVG(price)
2.33
…
49
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Groupement
Exemple:
A partir de Sells(bar, beer, price) et Frequents(drinker,bar), trouver
pour chaque client le prix moyen de la Bud au bar qu’ils fréquentent:
SELECT drinker, AVG(price)
FROM Frequent,Sells
WHERE beer = ‘Bud’
AND Frequents.bar = Sells.bar
GROUP BY drinker;
Construit tous les tuples drinkerbar-price associés à la Bud
Puis, les regroupent par clients
50
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Restriction sur le SELECT avec une Agrégation
Si une agrégation est effectuée, alors chacun des éléments du
SELECT doit être soit:
1. Une opération d’agrégation, ou
2. Un attribut utilisé au niveau d’un GROUP BY
Exemple de requête interdite:
•
Il semble correcte de rechercher le bar qui vend les bières
Bud les moins chères par la requête:
SELECT bar, MIN(price)
FROM Sells
WHERE beer = ’Bud’;
•
Mais cette requête est interdite en SQL.
51
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Clauses HAVING
HAVING <condition> peut suivre une clause GROUP B.
Si tel est le cas, la condition est appliquée à chaque groupe, et les
groupes ne satisfaisant pas la condition sont ignorés.
Exemple:
A partir de Sells(bar, beer, price) et Beers(name, manf), trouver le
prix moyen des bières qui sont soient servies dans au moins trois
bars ou sont fabriquées par Pete.
???
52
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Clauses HAVING
Exemple:
Les groupes de bières avec au
moins 3 bars non-NULL et aussi les
groupes de bières avec pour
fabricant Pete
SELECT beer, AVG(price)
FROM Sells
GROUP BY beer
HAVING COUNT(bar) >= 3 OR
beer IN (SELECT name
FROM Beers
WHERE manf = ’Pete’’s’);
Les bières
fabriquées
par Pete
53
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Restrictions sur les conditions du HAVING
Tout doit être dans une sous-requête.
A part les sous-requêtes, les conditions peuvent être sur des
attributs uniquement si:
1. ce sont les attributs utilisés pour le regroupement, ou
2. s’ils sont agrégés
(même condition que pour la clause SELECT lorsqu’elle est
utilisée pour une agrégation).
54
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Modifications des relations d’une base de données
Une commande de modification ne retourne pas de résultat
(comme le fait une requête), mais change le contenu des relations.
Trois types de modifications:
1. Insert : insertion d’un ou plusieurs tuples.
2. Delete : suppression d’un ou plusieurs tuples.
3. Update : mise à jours des valeurs d’un ou plusieurs tuples
existants.
55
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Insertion
Pour insérer un unique tuple:
INSERT INTO <relation>
VALUES ( <list of values> );
Attention: les valeurs doivent être dans le même ordre que les attributs
de la relation et les types doivent correspondre.
Exemple:
Ajouter à Likes(drinker, beer) le fait que Sally aime les bières Bud.
INSERT INTO Likes
VALUES(’Sally’, ’Bud’);
56
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Spécifier les attributs de l’INSERT
Il est possible d’ajouter au nom de la relation une liste d’attributs
Deux raisons de faire ainsi:
1. l’ordre des attributs de la relation a été oublié.
2. il n’y a pas de valeurs pour tous les attributs, et nous voulons
que le SGBD remplace ces valeurs manquantes par NULL ou
par une valeur par défaut.
Exemple:
Une autre approche pour ajouter à Likes(drinker, beer) le fait que
Sally aime les Bud :
INSERT INTO Likes(beer, drinker)
VALUES(’Bud’, ’Sally’);
57
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Ajouter des valeurs par défaut
Dans la commande CREATE TABLE permettant de créer une table,
il est possible de faire suivre la définition d’un attribut par DEFAULT
et une valeur.
Lorsqu’un tuple à insérer n’a pas de valeur pour cet attribut, la
valeur par défaut est utilisée.
Exemple:
CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
DEFAULT ’123 Sesame St.’,
phone CHAR(16)
);
58
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Ajouter des valeurs par défaut
Exemple :
INSERT INTO Drinkers(name)
VALUES(’Sally’);
• relation résultat:
name
Sally
address
123 Sesame St
phone
NULL
59
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Insertion de plusieurs tuples
Il est possible d’insérer la totalité du résultat d’une requête dans
une relation, en faisant:
INSERT INTO <relation>
( <subquery> );
Exemple :
En utilisant la relation Frequents(drinker, bar), insérer dans une
nouvelle relation PotBuddies(name) contenant tous les amis
potentiels de Sally, i.e. les clients qui fréquente au moins un des
bars que fréquente Sally.
60
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Insertion de plusieurs tuples
Exemple :
les autres clients
INSERT INTO PotBuddies
(SELECT d2.drinker
FROM Frequents d1, Frequents d2
WHERE d1.drinker = ’Sally’ AND
d2.drinker <> ’Sally’ AND
d1.bar = d2.bar
);
les pairs de tuples de
clients où le premier
est Sally et le second
est quelqu’un d’autre,
et les bars sont les
mêmes
61
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Suppression de tuples
Pour supprimer les tuples d’une relation en fonction de conditions:
DELETE FROM <relation>
WHERE <condition>;
Exemple:
Supprimer de Likes(drinker, beer) le fait que Sally aime la Bud:
DELETE FROM Likes
WHERE drinker = ’Sally’ AND
beer = ’Bud’;
Supprimer tous les tuples d’une relation:
DELETE FROM Likes;
• Remarque: pas de clause WHERE.
62
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Problème de suppression
Exemple:
Supprimer de Beers(name,manf) toutes les bières pour lesquelles
il y a une autre bière fournie par le même fabricant.
DELETE FROM Beers b
WHERE EXISTS (
SELECT name FROM Beers
WHERE manf = b.manf AND
name <> b.name);
Bières faite par le même
fabricant et ayant un nom
différent du nom de la bière
représenté par le tuple b.
name
name
manf
Bud
Anheuser-Busch
Bud lite
Anheuser-Busch
Man
Peterson
Man
?
name
manf
Peterson
manf
Bud lite
Anheuser-Busch
Man
Peterson
63
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Problème de suppression
Exercice: exécution de la suppression
• Supposons que Anheuser-Busch fait uniquement de la Bud et
de la Bud Lite.
• Supposons que le SGBD arrive au tuple b de la première Bud.
• La sous-requête est non vide, à cause du tuple avec Bud Lite,
donc le tuple b de la Bud est supprimé.
• Maintenant, quand le tuple b devient le tuple avec Bud Lite,
est-il aussi supprimé ?
➡ Réponse: le tuple avec Bud Lite est aussi supprimé
Ceci est dû au fait que la suppression s’effectue en deux
étapes:
1. marquer les tuples pour lesquels la condition du
WHERE est satisfaite
2. supprimer les tuples marqués
64
Le modèle relationnel
Algèbre relationnelle et SQL
Modifications des données en SQL
Mise à jour
Pour changer la valeur de certains attributs dans certains tuples
d’une relation:
UPDATE <relation>
SET <list of attribute assignments>
WHERE <condition on tuples>;
Exemple:
Modifier le numéro de téléphone du client Fred à la valeur 555-1212:
UPDATE Drinkers
SET phone = ’555-1212’
WHERE name = ’Fred’;
Fixer à $4 le prix maximum d’une bière:
UPDATE Sells
SET price = 4.00
WHERE price > 4.00;
65
Chapitre 1: Manipuler les données
Programmation procédurale
Frédéric Flouvat
(dérivé du cours du Pr. Jeffrey Ullman, Stanford University
et du cours du Pr. Christian Retoré, Université de Bordeaux)
Université de la Nouvelle-Calédonie
[email protected]
66
Premières procédures PSM
Manipuler les requêtes SQL
PL/pgSQL
Le SQL dans de vrais programmes
Pour l’instant, le SQL est utilisé comme interface générique pour
faire des requêtes
• un environnement où l’utilisateur est face à un terminal et fait des
requêtes à une base de données.
La réalité est différente:
interagissent avec le SQL.
des
programmes
conventionnels
Les options possibles:
• le code dans un langage spécifique est stocké dans la base de
données (PSM, PL/SQL).
• les instructions SQL sont englobées dans un langage hôte (p.ex.
le C) et transformées/exécutées en ligne par un préprocesseur
(Embedded SQL).
• des outils de connections sont utilisés pour permettre à un
langage conventionnel d’accéder à la base de données (p.ex.
CLI, JDBC, PHP)
67
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Les Procédures Stockées
PSM, ou “persistent stored modules,” permet de stocker des
procédures comme des éléments du schéma de la base de
données.
• SQL/PSM un standard international (ISO)
PSM = un mélange d’instructions conventionnelles (if, while, etc.) et
de SQL.
Permet de faire des choses que ne permet pas le SQL tout seul.
Attention: en pratique, des implémentations légèrement différentes
en fonction des éditeurs
• p.ex. PL/SQL dans Oracle, PL/pgSQL dans PostgreSQL
68
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Structure de base d’une procedure PL/pgSQL
CREATE [OR REPLACE] FUNCTION <name> (<arguments>) RETURNS <type> AS $$
DECLARE
<optional declarations>
BEGIN
<PL/SQL statements>
instructions
END;
obligatoires
$$ LANGUAGE plpgsql;
Les paramètres/arguments:
• syntaxe nom mode type (différent de PSM), séparés par des virgules,
avec mode égal à :
– IN = variable en entrée, non modifiée par la procédure.
– OUT = variable retournée en sortie de l’algorithme.
– INOUT = les deux.
Type de retour (RETURNS): mettre VOID pour une procédure
Déclaration des variables locales (DECLARE): optionnelle
Corps de la procédure (BEGIN … END;): séparer les instructions par des
points virgules
69
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Exemple de procédure sockée PL/pgSQL
Ecrivons une procédure avec deux paramètres b et p, et qui ajoute
un tuple ayant bar = ’Joe’’s Bar’, beer = b, et price = p, à la relation
Sells(bar, beer, price).
• utilisée par Joe pour ajouter des bières à sa carte plus facilement.
CREATE FUNCTION JoeMenu( b IN VARCHAR(10), p IN
INTEGER )
les paramètres sont tous les
RETURNS VOID AS $$
deux en lecture seule, i.e.
BEGIN
non modifiés
INSERT INTO Sells
VALUES (’Joe’’s Bar’, b, p);
END;
$$ LANGUAGE plpgsql;
le corps --une seule insertion
70
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Invoquer des Procédures PL/pgSQL
SELECT * FROM <name>(<arguments>) ;
ou SELECT <name>(<arguments>) ;
Exemple:
SELECT JoeMenu(’Moosedrool’, 5.00);
Les fonctions peuvent être utilisées dans des expressions SQL, à
condition que le type de la valeur retournée soit approprié.
• Attention au type de retour RECORD qui n'a pas de structure prédéfinie
et qui peut donc nécessiter un transtypage (cast) pour pouvoir être
utilisé dans une autre expression SQL
71
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
PL/pgSQL Déclarations et Affectations
Déclarer une variable/un paramètre: <name> <type>
• Les types SQL.
• De nouveaux types:
– p.ex. type composite
CREATE TYPE element_inventaire AS (
nom
text,
id_fournisseur
integer,
prix
numeric
);
Affecter une valeur: <variable> := <expression>;
• Exemple:
b := 'Bud';
72
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Les variables de type attribut et variables de type tuple
PL/pgSQL permet à une variable d’avoir le même type qu'un attribut d'une
relation ou la même structure qu’un tuple de ses tuples.
• Attention: ne fonctionne pas avec les types composites (CREATE TYPE)
x R.a%TYPE donne a x le même type que l'attribut a de R.
x R%ROWTYPE donne à x le type des tuples de R.
• x.a donne la valeur de l’attribut a du tuple x.
Exemple: Reprendre JoeMenu(b,p) en utilisant Sells.beer et Sells.price.
CREATE FUNCTION JoeMenu( b IN "Sells".beer%TYPE, p IN "Sells".price%TYPE)
RETURNS VOID AS $$
BEGIN
INSERT INTO Sells
VALUES (’Joe’’s Bar’, b, p);
END;
$$ LANGUAGE plpgsql;
73
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Instructions conditionnelles IF et CASE
Les IF
• IF <condition> THEN
<statement(s)>
END IF;
• IF . . . THEN . . . ELSE . . . END IF;
• IF … THEN … ELSIF … … THEN … ELSE … END IF;
•
Les CASE
• CASE <search-expression>
WHEN <expression> [, <expression> [ ... ]] THEN <statement(s)>
WHEN … THEN …
ELSE ...
END CASE;
• CASE
WHEN <boolean-expression> THEN <statement(s)>
...
END CASE;
74
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Exemple: IF
Classer les bars en function de leur nombre de clients en utilisant la relation
Frequents(drinker,bar).
• <100 clients: ‘unpopular’.
• 100-199 clients: ‘average’.
• >= 200 clients: ‘popular’.
La fonction Rate(b) classe le bar b.
CREATE FUNCTION Rate( b IN CHAR(20) ) RETURNS CHAR(10) AS $$
DECLARE
nombre de clients du bar b
cust INTEGER;
BEGIN
SELECT COUNT(*) INTO cust FROM "Frequents" WHERE bar = b;
IF cust < 100 THEN RETURN ’unpopular’ ;
ELSIF cust < 200 THEN RETURN ’average’;
IF imbriqués
ELSE RETURN ’popular’;
END IF;
retourne la valeur ici et non
RETURN;
END;
lors des autres RETURN
$$ LANGUAGE plpgsql;
75
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Les boucles
Forme basique:
[<loop label>] LOOP <statements>
END LOOP [<loop name>];
Interrompre une boucle:
EXIT [<loop label>] [ WHEN <condition> ]
Exemple:
loop1 LOOP
...
EXIT loop1;
...
END LOOP loop1;
si cette instruction est exécutée
fin de la boucle
76
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Les boucles
[<while label>] WHILE <condition> LOOP
<statements>
END LOOP [<while label>] ;
[<for label>] FOR <variable> IN [ REVERSE ] <expression> ..
<expression> [ BY <expression> ]
LOOP
<statements>
END LOOP [<for label>];
Exemple:
FOR i IN 1 .. 10 BY 2 LOOP
…
END LOOP;
77
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Exécuter des requêtes dans une procédure
Les requêtes générales SELECT-FROM-WHERE ne peuvent pas
être exécutées directement.
Seules les requêtes ne retournant aucun résultat peuvent être
directement invoquées (p.ex. INSERT).
Il y a trois principales méthodes pour manipuler le résultat d’une
requête SELECT:
• SELECT . . . INTO…
• Parcourir le résultat de la requête dans une boucle FOR.
• Les curseurs.
78
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
SELECT . . . INTO
Un moyen de récupérer la valeur d’une requête qui retourne un
unique tuple est d’utiliser INTO <variable> après la clause SELECT.
Exemple:
SELECT price INTO p FROM Sells
WHERE bar = ’Joe’’s Bar’ AND
beer = ’Bud’;
79
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
FOR. . . IN
Un moyen de récupérer la valeur d’une requête qui retourne un ensemble de
tuples est d’utiliser une boucle FOR … IN…
Exemple:
CREATE OR REPLACE FUNCTION JoeGouge() RETURNS VOID AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT beer, price FROM "Sells" WHERE bar='Joe"s Bar' LOOP
IF r.price < 3.00 THEN
UPDATE "Sells" SET price = r.price+ 1.00
WHERE bar = ’Joe’’s Bar’ AND beer = r.beer;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
80
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Les curseurs
Un curseur est principalement une variable de type tuple qui stocke tous
les tuples résultats d’une requête.
Déclaration d’un curseur en PL/pgSQL :
DECLARE <name> CURSOR [(<arguments>)] FOR <query>;
Exemple:
DECLARE c CURSOR (b varchar) FOR SELECT * FROM Sells WHERE beer=b;
Utilisation d’un curseur :
• initialisation du curseur:
OPEN <name>[(<arguments>)] ;
– la requête est évaluée, et le curseur pointe sur le premier tuple du
résultat.
• libération du curseur:
CLOSE <name>;
81
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Parcourir les tuples d’un Curseur
Pour accéder au tuple suivant du curseur c, utiliser la commande:
FETCH c INTO x1, x2,…,xn ;
• les x sont des variables référençant chaque composant du tuple
stocké dans c.
• c est déplacé automatiquement au prochain tuple.
Sortir d’une boucle d’un curseur
• l’utilisation classique d’un curseur est de créer une boucle avec
FETCH, et de faire un traitement pour chaque tuple parcouru.
• mais comment sortir de la boucle quand tous les tuples ont été
traités?
 EXIT WHEN NOT FOUND
82
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Exemple: Curseur en PL/pgSQL (déclaration)
La procédure JoeGouge() utilise un curseur pour parcourir les ventes
de Joe’s-Bar dans Sells(bar, beer, price), et augmente de $1 le prix
des bières initialement vendue en dessous de $3 chez Joe’s Bar.
Utilisé pour stocker les
pairs beer-price lors du
parcours du curseur
CREATE FUNCTION JoeGouge() RETURNS VOID AS $$
DECLARE
theBeer "Sells".beer%TYPE;
thePrice "Sells".price%TYPE;
c CURSOR FOR
SELECT beer, price FROM "Sells"
WHERE bar = ’Joe’’s Bar’;
Retourne le
menu de Joe
83
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Exemple: Curseur en PL/pgSQL (corps de la procédure)
BEGIN
OPEN c;
LOOP
condition
d’arrêt de la
FETCH c INTO theBeer, thePrice;
boucle
EXIT WHEN NOT FOUND;
IF thePrice < 3.00 THEN
UPDATE "Sells" SET price = thePrice + 1.00
WHERE bar = ’Joe’’s Bar’ AND beer = theBeer;
END IF;
END LOOP;
Si Joe vend une bière moins $3, augmenter
CLOSE c;
son prix de $1 à Joe’s Bar.
END;
$$ LANGUAGE plpgsql;
84
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Déclaration)
Deux options principalement pour déclarer le type de retour de la fonction:
• Déclarer … RETURNS SETOF <sometype> …
• Déclarer … TABLE( <col1> <type1>, <col2> <type2> … ) …
Déclaration: RETURNS SETOF
• possibilité de retourner un ensemble de valeurs simples
– p.ex. RETURNS SETOF integer
• possibilité de retourner un ensemble de tuples en créant un nouveau type
composite (ou en faisant référence à un type composite existant)
– p.ex.
CREATE TYPE nomprenom AS ( nom VARCHAR(20), prenom
VARCHAR(30) ) ;
CREATE FUNCTION clients() RETURNS SETOF nomprenom AS $$
…
85
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Déclaration)
Déclaration: TABLE( <col1> <type1>, <col2> <type2> … )
• syntaxe proche de la norme SQL/PSM
• facilité d'interprétation
• possibilité de retourner un ensemble de valeurs simples ou un ensemble de
tuples
• mais interdiction d'utiliser des paramètres en sortie (mode OUT)
– p.ex.
CREATE FUNCTION clients() RETURNS TABLE( nom VARCHAR(20),
prenom VARCHAR(30) ) AS $$
…
86
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Invocation)
Deux options pour retourner des résultats dans le corps de la fonction:
• RETURN QUERY <SQLquery> ;
• RETURN NEXT [<expression>] ;
Retourner : RETURN QUERY
• compatible RETURNS SETOF et RETURNS TABLE
• retourne le résultat d'une requête
• possibilité de l'utiliser plusieurs fois
– dans ce cas, chaque ensemble est ajouté à la suite de l'autre
• résultat final uniquement retourné à la fin de la procédure ou au moment de
l'appel RETURN;
87
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Invocation)
Exemple: RETURNS TABLE et RETURN QUERY
CREATE OR REPLACE FUNCTION BarSallyJo ()
RETURNS TABLE( bar TEXT, name VARCHAR(30) ) AS $$
BEGIN
…
RETURN QUERY SELECT bar, drinker FROM "Frequents" WHERE drinker = 'Sally';
…
RETURN QUERY SELECT bar, drinker FROM "Frequents" WHERE drinker = 'Jo';
…
RETURN;
END;
$$ LANGUAGE plpgsql;
88
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Invocation)
Retourner : RETURN NEXT
• enregistre une valeur/tuple à la fois dans l'ensemble des solutions
• généralement invoqué dans une boucle
• son utilisation dépend du type de retour de la fonction:
Si RETURNS SETOF <sometype> alors RETURN NEXT <expression>;
– <expression> est classiquement une variable du type associé au SETOF
Si RETURNS TABLE( <col1> <type1>, <col2> <type2> … ) alors
RETURN NEXT;
– <col1>, <col2> … sont utilisées et initialisées comme des variables dans le
corps de la procédure
– chaque RETURN NEXT; enregistre dans l'ensemble des résultats un tuple
initialisé à partir des valeurs en cours pour <col1>, <col2> …
• résultat final uniquement retourné à la fin de la procédure ou au moment
de l'appel RETURN;
89
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Invocation)
Exemple: RETURNS TABLE et RETURN NEXT;
CREATE OR REPLACE FUNCTION BarSallyJo ()
RETURNS TABLE( bar TEXT, name VARCHAR(30) ) AS $$
BEGIN
FOR bar, name IN
SELECT bar, drinker FROM "Frequents" WHERE drinker = 'Sally'
LOOP
RETURN NEXT;
END LOOP;
FOR bar, name IN
SELECT bar, drinker FROM "Frequents" WHERE drinker = 'Jo’
LOOP
RETURN NEXT ;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
90
Persistent Stored Modules
Syntaxe de base PL/pgSQL
Manipuler des requêtes SQL et retourner des tables
Retourner un ensemble de valeurs (Invocation)
Exemple: RETURNS SETOF et RETURN NEXT;
CREATE TYPE infobar AS ( bar TEXT, name VARCHAR(30) );
CREATE OR REPLACE FUNCTION BarSallyJo () RETURNS SETOF infobar AS $$
DECLARE
r infobar;
BEGIN
FOR r IN
SELECT bar, drinker FROM "Frequents" WHERE drinker = 'Sally'
LOOP
RETURN NEXT r ;
END LOOP;
…
RETURN;
END;
$$ LANGUAGE plpgsql;
91
Chapitre 1: Manipuler les données
Contrôler les transactions
Université de la Nouvelle-Calédonie
[email protected]
Quelques références bibliographiques
Documentation PostgreSQL 9.4, Chapitre 13. "Contrôle d'accès simultané"
Livre "Database Management Systems", Raghu Ramakrishnan et Johannes Gehrke.
"Database Systems: The Complete Book", Pr. Jeffrey Ullman, Stanford University
"PostgreSQL Concurrency with MVCC", Heroku dev center
"Transaction Processing in PostgreSQL" de Tom Lane, Great Bridge
"Bases de données: Aspects système", Philippe Rigaux, Université Paris-Dauphine
"Bases de données avancées", Jacques Le Maitre, Université du Sud Toulon-Var
"Conception des bases de données", Stéphane Crozat, UTC
93
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Rappel
Une bases de données est un ensemble de données
• structurées
• persistantes
• cohérentes
Cohérence garantie par des contraintes d'intégrités (contraintes de
domaine, intégrité référentielles, dépendances fonctionnelles …)
•
cf "Contraindre les données", chapitre 2
Mais cohérence peut être remise en cause par:
• Défaillances du système
• Accès concurrents
94
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Pourquoi des accès concurrents ?
Les systèmes de gestion de bases de données sont généralement
utilisés par plusieurs utilisateurs et/ou processus à la fois.
• Interrogations et modifications.
Toutes ces opérations arrivent dans un certain ordre au SGBD qui
les met en file d'attente pour traitement ("Scheduler")
schéma issu du cours de Philippe
Rigaux, Université Paris-Dauphine
Exemple:
• Deux personnes titulaires d’un compte commun retirent au même
moment $100 dans deux distributeurs de billets différents.
 Le SGBD doit alors s’assurer que les deux retraits sont appliqués
correctement au compte sous-jacent.
95
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Exemple: Transfert entre 2 comptes
Transfert(A,B,M)
Begin
varA := Read(A)
varA := varA + M
Write(A,varA)
varB := Read(B)
varB := varB - M
Write(B,varB)
Commit
A= 1500 et B=2000
Le guichet 1 exécute T1 :
• Transfert(A,B,100)
Le guichet 2 exécute T2 :
• Transfert(A,B,-200)
Si exécution à la suite (i.e. en série), pas de problème
T1 -- A=1600 B=1900
T2 -- A=1300 B=2200
OU
T2 -- A=1400 B=2100
T1 -- A=1400 B=2100
96
Transactions ACID & SQL
Introduction aux accès concurrents
Contrôle de la concurrence dans PostgreSQL
Exemple: Transfert entre 2 comptes
Transfert(A,B,M)
Begin
varA := Read(A)
varA := varA + M
Write(A,varA)
varB := Read(B)
varB := varB - M
Write(B,varB)
Commit
Si exécution "entrelacée", problèmes possibles
T1: R(A)
T2: R(A)
T2: W(A)
T1: W(A)
T1: R(B)
T2: R(B)
T1: W(B)
T2: W(B)
---------
A=1500
A=1500
A=1300
A=1600
B=2000
B=2000
B=1900
B=2200
 A= 1600 et B= 2200 !!!
97
Transactions ACID & SQL
Introduction aux accès concurrents
Contrôle de la concurrence dans PostgreSQL
Les problèmes liés aux accès concurrents
Perte de mise à jour
T1
T2
BD
A=10
Read(A)
Lectures incohérentes
Read(A)
T1
A=A+10
Write(A)
T2
A = 20
A=A+50
BD
A=120
B=80
Read(A)
A=A-50
Write(A)
A = 60
Write(A)
A = 70
Read(A)
Read(B)
display A+B
(affiche 150)
Read(B)
B=B+50
Write(B)
B = 130
98
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Les problèmes liés aux accès concurrents
T1
Lectures non reproductibles
T2
BD
A=10
Read(A)
(lit 10)
A=20
Write(A)
A = 20
Read(A)
(lit 20)
Objets fantômes
• la transaction ré-exécute une
requête renvoyant un ensemble
de lignes satisfaisant une
condition de recherche et trouve
que l'ensemble des lignes
satisfaisant la condition a changé
du fait d'une autre transaction
récemment validée.
T1
T2
BD
Clients ={}
INSERT INTO
"Clients"(name)
VALUES ('Bob');
Clients =
{<'Bob', …>}
SELECT
COUNT(name)
FROM "Clients";
(résultat 0)
SELECT
COUNT(name)
FROM "Clients";
(résultat 1)
99
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Les transactions en Base de Données
Transaction = ensemble d'instructions dont le résultat sera visible
au reste du système d'un seul bloc si la transaction est validée
(commit), ou pas du tout visible si elle est annulée (abort)
Exemples: réservation d’une place d’avion, passer une commande
sur un catalogue en ligne, …
Hypothèses:
• Les transactions interagissent entre elles uniquement via des
lectures et des écritures dans la base de données.
– pas de messages échangés
• Une base de données est une collection fixe d'objets
indépendants.
100
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Transactions et SQL
Définir une transaction
Begin;
<instructions SQL>
COMMIT;
L’instruction SQL COMMIT valide une transaction.
• Ses modifications sont maintenant permanentes dans la base de données.
• Attention mode Autocommit: valide automatiquement toutes les instructions
– dans PostgreSQL, les requêtes "isolées" sont par défaut validées
automatiquement
L’instruction SQL ROLLBACK implique aussi la fin d’une transaction,
mais en l’annulant.
• Aucun effet sur la base de données.
• Une erreur comme une division par zéro ou une violation de contraintes
peut également provoquer un rollback, même si le programmeur ne l‘a pas
déclenché explicitement.
101
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Propriétés ACID
Cohérence globale des données garantie par un certain nombre de
propriétés sur les transactions
 Les transactions ACID sont:
• Atomiques : La transaction est appliquée intégralement ou pas du
tout.
• Cohérentes : Les contraintes de la base de données sont préservées
et les données sont cohérentes pour l'application.
• Isolées : Une transaction ne doit jamais voir les résultats
intermédiaires des autres transactions (comme si elle était là seule à
être exécutée).
• Durables : Dès qu’une transaction est validée, le système doit assurer
que ses effets ne seront jamais perdus, même en cas de panne.
102
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Garantir Atomicité & Durabilité des transactions
La
des transactions est garantie par l’instruction SQL
COMMIT
• car une fois la transaction validée par un commit, le SGBD
garantie que ses modifications seront permanentes dans la base
de données (même après une panne)
L'
des transactions est garantie par l’instruction SQL
ROLLBACK
• car une fois la transaction annulée par un rollback, toutes ses
opérations le sont aussi (tout ou rien)
 Rendu possible grâce à l'utilisation de journaux (fichiers)
103
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Garantir la Cohérence des transactions
Garantie par le système via les contraintes d'intégrités associées
au schéma
• Clés primaires, clés étrangères, triggers, …
Garantie par le développeur en utilisant correctement les
propriétés des transactions de façon à prendre en compte les
spécificités de l'application
• Bonne utilisation des commit/rollback, définition du bon niveau
d'isolation, …
• Exemple des réservations dans une compagnie aérienne:
– données cohérentes si le nombre de places occupées est le même
que le nombre de places réservées
 difficile via des contraintes
 si niveau d'isolation trop faible ou mauvaise utilisation des
commit/rollback, possibilité d'entrelacement des transactions (et
donc d'incohérences)
• comme pour le virement entre deux comptes bancaires
104
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Garantir l'Isolation des transactions
Possibilité en SQL de définir le niveau d'isolation d'une transaction
• p.ex. dans PostgreSQL,
SET TRANSACTION ISOLATION LEVEL <level>;
où <level> =
1.
SERIALIZABLE
2.
REPEATABLE READ
3.
READ COMMITTED
4.
READ UNCOMMITTED
Garantir l'Isolation en utilisant le mode SERIALIZABLE
• Isolation totale des transactions, i.e. les transactions s'exécutent
indépendamment les unes des autres comme si elles étaient
exécutées en série
Problème: très "coûteux"
 Possibilité d'améliorer l'efficacité en abaissant le niveau d'isolation,
mais attention à bien étudier les besoins de l'application …
105
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Les différents niveaux d'isolation SQL
READ UNCOMMITED = lecture de données non validées
• La transaction peut voir toutes les données de la base de données,
même si elles ont été écrites par une transaction non validées.
++ pas de perte de mise à jour
−−possibilité de lectures incohérentes, de lectures non reproductibles, et
objets fantômes
READ COMMITED = lecture de données validées
• La transaction ne peut voir que les données validées, mais pas
nécessairement les mêmes données à chaque fois.
++ pas de perte de mise à jour, pas de lectures incohérentes
−−possibilité de lectures non reproductibles, et objets fantômes
106
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Les différents niveaux d'isolation SQL
REPEATABLE READ = lecture répétée
• La transaction ne peut voir que les données validées, mais si les données
sont lues à plusieurs reprises dans la même transaction, alors toutes les
informations vues la première fois sont également vues la seconde fois.
– données vues = données première lecture + données actuellement validées
 Attention: la seconde lecture et celles suivantes peuvent voir plus de tuples que
ce qu’il y a réellement dans la base de données.
++pas de perte de mise à jour, pas de lectures incohérentes, pas de lectures
non reproductibles
−−possibilité d'avoir des objets fantômes
SERIALIZABLE = lecture en série
• La transaction ne peut voir que les données qui sont validées lorsqu'elle
débute
++ pas de perte de mise à jour, pas de lectures incohérentes, pas de lectures
non reproductibles, et pas d'objets fantômes
107
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Ces différents niveaux dans PostgreSQL
PostgreSQL READ UNCOMMITED = PostgreSQL READ COMMITED
PostgreSQL READ COMMITED (niveau par défaut) identique à la
norme SQL
PostgreSQL REPEATABLE READ plus stricte que dans la norme SQL
• La transaction ne peut voir que les données validées au début de la
transaction
– identique à SERIALIZABLE
• La transaction peut être mise en attente et annulée en cas d'échec de
sérialisation
PostgreSQL SERIALIZABLE identique à la norme SQL
• Le SGBD analyse d'abord si la requête est sérialisable et déclenche une
erreur s'il détecte un problème
108
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Lectures et modifications concurrentes dans PostgreSQL
Cas des lectures : la transaction lit des données en cours
d'utilisation dans d'autres transactions
• Les lecteurs ne bloquent pas ceux qui écrivent et inversement
• Seules les données visibles par la transaction changent en fonction de
son niveau d'isolation
Cas des modifications : la transaction essaye de modifier des
données modifiées dans d'autres transactions
T1
• Read commited :
Begin
La transaction va attendre
si une autre transaction est
en train de modifier un tuple
qu'elle doit aussi modifier
T2 (READ COMMITED)
Begin
modifier tuple t ---------------modifier tuple t
attente
COMMIT
------modifier tuple t
COMMIT
109
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Lectures et modifications concurrentes dans PostgreSQL
T1
Begin
Cas des modifications (suite)
• Serializable :
La transaction n'est pas exécutée
et une erreur est déclenchée
modifier tuple t ---------- T2 (SERIALIZABLE)
analyse sérialisation
-> tentative modification tuple t
-> ERROR: could not serialize
access due to concurrent update
COMMIT
T1
Begin
T2 (REPEATABLE READ)
Begin
• Repeatable read :
La transaction attend le résultat
de l'autre transaction pour
déclencher une erreur ou pour
effectuer la modification
modifier ---------tuple t
------modifier tuple t
attente
COMMIT ou ROLLBACK ------ Si COMMIT T1 :
ERROR: could not serialize
access due to concurrent update
Si ROLLBACK T1 : modifier tuple t
COMMIT
110
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Remarque : Isolation sérialisable et vrai sérialisation
Définition "mathématique" d’exécution sérialisable: toute paire de
transactions concurrentes validée avec succès apparaîtra comme
ayant été exécutée en série, l'une après l'autre -- bien que celle
survenant en premier n'est pas prévisible
 L’ordre d’exécution réel des transactions ne change pas le
résultat
Isolation sérialisable de PostgreSQL ne respecte pas totalement
cette définition
• Evite les comportements non désirables précédents
• Mais le résultat peut changer en fonction de l’ordre d’exécution
111
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Remarque : Isolation sérialisable et vrai sérialisation
Exemple:
classe
valeur
1
10
1
20
2
100
2
200
Transaction A:
Begin;
SELECT SUM(valeur) into sommeA FROM ma_table WHERE classe = 1;
INSERT INTO ma_table value(2,sommeA);
COMMIT;
Transaction B:
Begin;
SELECT SUM(valeur) into sommeB FROM ma_table WHERE classe = 2;
INSERT INTO ma_table value(1,sommeB);
COMMIT;
• si A exécuté avant : sommeB = 330
• si B exécuté avant : sommeB = 300
 Pas réellement sérialisable
Solution pour garantir une vrai sérialisation: système de verrous
explicites des ressources
• mais complexe à mettre en place et très coûteux
• PostgreSQL le permet (cf chapitre 13.3 Verrouillage explicite)
112
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Implémentation de ces mécanismes par le SGBD
Différentes stratégies mises en place en interne par les SGBD pour
garantir la cohérence et les propriétés ACID des transactions
• Verrouillage: une transaction verrouille certaines données qu'elle
lit ou écrit pour interdire aux autres d'y accéder
– verrouiller un tuple, une table, …
 inconvénient: dégrade les performances
• Versionnement: conserver plusieurs versions des données
– p.ex. image avant mise à jour et image après
 avantage: ne bloque pas les lectures/écritures
 inconvénient: occupe plus d'espace
 PostgreSQL utilise un contrôle de concurrence multi-versions
(MVCC ou Multi-version Concurrency Control)
113
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Multi-Version Concurrency Control MVCC
Les transactions sont numérotées par ordre chronologique
Plusieurs versions de chaque tuple sont conservées
• chaque version est associée au numéro de la transaction l'ayant
créée ou modifiée (insert, update ou delete)
• PostgreSQL stocke aussi l'état de la transaction (validée, en
cours ou annulée)
En fonction du niveau d'isolation de la transaction et des opérations
réalisées, PostgreSQL accède aux bonnes informations et effectue
les traitements adaptés
114
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Multi-Version Concurrency Control MVCC
Exemple simplifié :
• Soit la transaction 10 en mode SERIALIZABLE qui lit les
données a, b, c, d, e, f et g
• Soit les transactions 7 et 8
dans l'état "validé"
• La valeur lue pour c par la
transaction 10 est celle
associée à la transaction 8
• La valeur lue pour e par la
transaction 10 est celle
associée la transaction 7
schéma issu du cours de Jacques Le
Maitre, Université Sud Toulon-Var
115
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Où sont stockées ces versions ?
 Dans le journal des transactions
• Journal = fichier système qui stocke (p.ex. sur disque dur) tout ce
qui passe dans le système, i.e. un historique persistant
• Il est indispensable pour la validation, l'annulation, la gestion des
accès concurrents mais aussi pour la reprise après panne
3 fichiers dans PostgreSQL :
• fichier pg_log pour les activités de la base de données
– p.ex. messages d'erreurs, requêtes, messages de démarrage/arrêt
• fichier pg_xlog pour stocker une image des données manipulées
dans les transactions récentes
• fichier pg_clog pour l'état des transactions (validée, annulée, en
cours)
116
Introduction aux accès concurrents
Transactions ACID & SQL
Contrôle de la concurrence dans PostgreSQL
Les limites du MVCC
Nécessité de maintenir des tuples potentiellement obsolètes
• En réalité, UPDATE créé un nouveau tuple et DELETE ne le supprime
pas vraiment (il est simplement marqué comme étant supprimé)
• Certains tuples vont être conservés alors qu'ils ne peuvent plus être
utilisés dans des transactions futures
Les identifiants des transactions ne peuvent dépasser une certaine
valeur maximale
• Identifiants sont des entiers 32 bits
 "Pas plus" de 4 milliards de transactions
 Solution: faire des nettoyages réguliers de la base de données
(instruction VACUUM)
• Nettoyage automatique configurable (AUTOVACUUM)
117
Chapitre 2: Définir et structurer les bases de données
Contraindre les données
Frédéric Flouvat
(dérivé du cours du Pr. Jeffrey Ullman, Stanford University)
Université de la Nouvelle-Calédonie
[email protected]
118
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Contraintes et Triggers
Une contrainte est une règle que doivent respecter les données et
que le SGBD doit imposer.
• Exemple: contraintes de clés.
Les Triggers sont uniquement exécutés lorsqu’une condition
prédéfinie apparaît, p.ex. insertion d’un tuple.
• Plus facile à implémenter que des contraintes complexes.
119
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les types de contraintes
Les clés (primaires).
Les clés étrangères, ou contraintes d’intégrité référentielle.
Les contraintes de valeurs.
• contraintes de valeurs sur un attribut particulier.
Les contraintes sur les tuples.
• relations entre composants.
Les assertions: n’importe quelle expression SQL booléenne.
• contraintes booléennes sur les objets de la base de données.
120
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les clés primaires
Clé primaire composé d’un seul attribut:
• Mettre PRIMARY KEY ou UNIQUE après le type dans la
déclaration de l’attribut.
CREATE TABLE Beers (
name CHAR(20) UNIQUE,
manf CHAR(20)
);
Différence PRIMARY KEY vs. UNIQUE
• il peut y avoir une seule PRIMARY KEY pour une relation,
mais plusieurs attributs UNIQUE.
• Aucun attribut d’une PRIMARY KEY ne peut avoir la NULL
pour un tuple, alors que les attributs déclarés UNIQUE
peuvent prendre la valeur NULL et ceci plusieurs fois.
121
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les clés primaires
Clé primaire composée de plusieurs attributs:
• Mettre PRIMARY KEY(<liste d’attributs>) après le dernier
attribut.
CREATE TABLE Sells (
bar
CHAR(20),
beer
VARCHAR(20),
price REAL,
PRIMARY KEY (bar, beer)
);
122
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les clés étrangères
Les valeurs prises par les attributs de la clé étrangère d’une
relation doivent aussi apparaître ensemble au niveau des
attributs d’une autre relation.
• une clé étrangère est clé primaire dans une autre relation.
• attributs utilisés pour les jointures.
Exemple:
Dans Sells(bar, beer, price), les valeurs pour l’attribut beer
apparaissent aussi toutes au niveau de l’attribut name de la
relation Beers(name, manf).
name
bar
beer
price
5
manf
Bud
Anheuser-Busch
Bud lite
Anheuser-Busch
Pete
Bud
Pete
Bud lite 2.22
Man
Peterson
Kend
Man
17
Al
1
123
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Définir des clés étrangères
Utiliser le mot clé REFERENCES, au choix :
1. après un attribut (pour une clé composé d’un attribut).
2. comme un élément de l‘expression:
FOREIGN KEY (<list of attributes>)
REFERENCES <relation> (<attributes>)
Les attributs référencés doivent être déclarés PRIMARY KEY ou
UNIQUE.
124
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Définir des clés étrangères
Exemple avec un attribut:
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20)
);
CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20) REFERENCES Beers(name),
price REAL
);
125
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Définir des clés étrangères
Exemple en tant qu’élément du schéma de la relation:
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20)
);
CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20),
price REAL,
FOREIGN KEY(beer) REFERENCES
);
Beers(name)
126
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Violation des contraintes de clé étrangère
Si une contraint de clé étrangère est définie de la relation R vers la
relation S, deux violations de cette contrainte sont possibles:
1. Une insertion ou une mise à jour de R introduit des valeurs qui
n’existent pas dans S.
name
bar
1.
beer
price
manf
Bud
Anheuser-Busch
Pete
Bud
5
Bud lite
Anheuser-Busch
Pete
Bud lite
2.22
Man
Peterson
Kend
Nber
1
17
Al
Une suppression ou mise à jour de S entraîne que des tuples
de R deviennent “incomplets”.
name
bar
beer
price
manf
Bud
Anheuser-Busch
Pete
Bud
5
Bud lite
Anheuser-Busch
Pete
Bud lite
2.22
Man
Peterson
Kend
Man
1
17
Al
127
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Actions à prendre pour imposer les contraintes de clé étrangère
Exemple:
• Supposons que R = Sells, S = Beers.
• Une insertion ou une mise à jour de Sells doit être rejetée
lorsqu’elle implique la vente d’une bière n’existant pas.
• Une suppression ou une mise à jour de Beers qui enlève une
valeur de bière utilisée dans certains tuples de Sells peut être
traitée de trois façons.
1.
2.
3.
Default : rejet de la modification.
Cascade : faire les mêmes modifications dans Sells.
• bière supprimée : supprime des tuples de Sells.
• bière mise à jour: change des valeurs dans Sells.
Set NULL : remplace la bière par NULL.
128
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Actions à prendre pour imposer les contraintes de clé étrangère
Exemple: CASCADE
• Suppression du tuple de la bière Bud de la relation Beers:
– Alors supprimer tous les tuples de Sells qui ont beer = ’Bud’.
• Mise à jour du tuple de la bière Bud en changeant ’Bud’ par
’Budweiser’:
– Alors changer tous les tuples de Sells qui ont beer = ’Bud’ par
beer = ’Budweiser’.
Exemple: SET NULL
• Suppression du tuple de la bière Bud de la relation Beers:
– Alors changer tous les tuples de Sells qui ont beer = ’Bud’ par
beer = NULL.
• Mise à jour du tuple de la bière Bud en changeant ’Bud’ par
’Budweiser’:
– même changement que pour la suppression.
129
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Choisir une politique de validation de contrainte
Quand une clé étrangère est déclarée, la politique de validation de la
contrainte peut être SET NULL ou CASCADE indépendamment des
suppressions et des mises à jours.
Faire suivre la déclaration de clé étrangère par:
ON [UPDATE, DELETE][SET NULL, CASCADE]
• Les deux clauses UPDATE ET DELETE peuvent être utilisées.
• Si aucune n’est définie, celle par défaut est utilisée (rejet).
130
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Choisir une politique de validation de contrainte
Exemple: définir une politique
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),
price
REAL,
FOREIGN KEY(beer)
REFERENCES Beers(name)
ON DELETE SET NULL
ON UPDATE CASCADE
);
131
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Contraintes sur les attributs
Contraintes sur la valeur d’un attribut.
Ajouter CHECK(<condition>) après la déclaration de l’attribut.
La condition peut être définie sur l’attribut contraints, mais tout autre
relations ou attributs doivent être dans une sous-requête.
Exemple:
CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20)
CHECK ( beer IN
(SELECT name FROM Beers)),
price REAL CHECK ( price <= 5.00 )
);
132
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Timing des vérifications
Vérifications des valeurs effectuées uniquement quand une valeur
pour l’attribut contraint est insérée ou mise à jour.
Exemple:
• CHECK (price <= 5.00)
- vérifie chaque nouveau prix et refuse la modification (pour le tuple
visé) si le prix est supérieur à 5$.
• CHECK (beer IN (SELECT name FROM Beers))
– vérifie que chaque nouvelle bière soit une bière référencée dans
Beers
– pas vérifié si une bière est supprimée de Beers, contrairement
aux clés étrangères.
133
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Contraintes sur les tuples
CHECK (<condition>) peut être ajoutée comme un élément de la
définition du schéma de la relation.
Dans ce cas, la condition peut traiter de tous les attributs de la relation.
• mais l’utilisation d’un autre attribut ou d’une autre relation nécessite
de faire une sous-requête
Vérifiée à l’insertion ou lors de la mise à jour uniquement.
Exemple: Seul les bars de Joe peuvent vendre de la bière à plus de 5$.
CREATE TABLE Sells (
bar
CHAR(20),
beer
CHAR(20),
price REAL,
CHECK (bar = ’Joe’’s Bar’ OR price <= 5.00)
);
134
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les Triggers: Motivation
Les contraintes sur les attributs et les tuples sont vérifiées à des
instants connus, mais ne sont pas “puissantes”.
Les triggers laissent l’utilisateur décider à quel moment une
condition doit être vérifiée.
135
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Des règles Evénement-Condition-Action
Un autre nom des “trigger” est règles ECA, ou règles événementcondition-action.
Evénement : typiquement un type de modification de la base de
données, p.ex. “insertion dans Sells.”
Condition : Toute expression booléenne SQL.
Action : Toute instruction SQL.
Exemple: A la place d’utiliser une clé étrangère et de rejeter les
insertions dans Sells(bar, beer, price) avec des bières inconnues,
un trigger peut ajouter cette bière à Beers, en mettant la valeur
NULL pour le fabriquant.
136
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Définition d’un Trigger
Exemple précédent:
l’événement
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
l’action
la condition
137
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Options: CREATE TRIGGER
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
CREATE TRIGGER <name>
OU
CREATE OR REPLACE TRIGGER <name>
• utile pour remplacer ou modifier un trigger.
138
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Options: L’événement
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
AFTER ou BEFORE
• Egalement, INSTEAD OF, si la relation est une vue.
INSERT, DELETE ou UPDATE
• UPDATE peut être UPDATE OF <attribute list> ON … pour cibler un ou
plusieurs attributs.
Recommandations:
• BEFORE pour vérifier ou modifier les données insérées ou maj
• AFTER pour propager des modifications sur d'autres tables
139
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Options: FOR EACH ROW
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
Les triggers sont “niveau ligne” ou “niveau instruction.”
FOR EACH ROW indique un trigger niveau ligne; par défaut niveau
instruction.
Le triggers niveau ligne: executé une fois pour chaque tuple modifié.
Les triggers niveau instruction: exécuté une fois pour chaque
instruction SQL, peu importe le nombre de tuples modifiés.
140
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Options: REFERENCING
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
Un événement INSERT implique un nouveau tuple (niveau ligne) ou
une nouvelle table (niveau instruction) .
• la “table” est l’ensemble des tuples insérés.
DELETE implique un ancien tuple ou une table.
UPDATE impliques les deux.
En résumé
[NEW OLD][ROW TABLE] AS <name>
141
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Options: La Condition
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
Toute condition à résultat booléen.
Evaluée sur la base de données avant ou après l’événement, en
fonction de BEFORE ou AFTER.
• mais toujours avant que les changements prennent effet.
Accède au nouvel/ancien tuple/table grâce à la variable définie dans
la clause REFERENCING.
142
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Options: L’Action
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
Il peut y avoir plus d’une instruction SQL dans l’action.
• Entouré par BEGIN . . . END s’il y en a plus d’une.
Mais les requêtes d’interrogations n’ont pas de sens dans une
action, une action est donc limitée à des requêtes de modifications.
Attention: l'action peut déclencher à nouveau le trigger
• Risque d'exécution infinie
143
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Un autre Exemple de trigger
En utilisant Sells(bar, beer, price) et une relation unaire
RipoffBars(bar), maintenir la liste des bars qui augmentent le prix
d’une bière de plus de 1$.
événement: uniquement
les changements de prix
CREATE TRIGGER PriceTrig
AFTER UPDATE OF price ON Sells
mises à jour -> accés aux
REFERENCING
anciens et nouveaux tuples
OLD ROW AS ooo
NEW ROW AS nnn
considérer chaque changement de prix
FOR EACH ROW
WHEN(nnn.price > ooo.price + 1.00)
condition: une
INSERT INTO RipoffBars
augmentation du prix > 1$
VALUES(nnn.bar);
si le prix augmente assez,
ajouter le bar dans RipoffBars
144
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Remarque sur la visibilité des modifications
Quelles sont les données que voit un trigger lorsqu'il s'exécute ?
• Dans certains cas pas évident car la requête Q qui a déclenché
le trigger peut être encore active et faire des modifications
 Trigger niveau instruction
• Si BEFORE évènement: aucune des modifications de Q visibles
• Si AFTER évènement: toutes les modifications de Q visibles
 Trigger niveau ligne (FOR EACH ROW)
• Si BEFORE événement: les modifications des lignes déjà
traitées par Q sont visibles
• pb: ordre de traitement des tuples pas prévisible
• Si AFTER événement: toutes les modifications de Q visibles
145
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les Trigger dans PostgreSQL
Les différences
• Obligatoirement associé à une fonction qui retourne un objet trigger
– Nécessité de créer la fonction d'abord, puis de créer le trigger
– Déclaration d'une fonction sans arguments
Possibilité de récupérer des arguments via le tableau TG_ARGV[] (la variable
TG_NARGS donne le nombre d'arguments passés en paramètre)
– Nécessité de retourner un objet trigger
Si niveau instruction, RETURN NULL
Si niveau ligne,
si AFTER événement, RETURN NULL
si BEFORE événement,
RETURN NULL annule l'opération sur la ligne courante
RETURN NEW pour valider INSERT/UPDATE
RETURN OLD pour valider DELETE
– Plusieurs variables prédéfinies permettant de récupérer des informations
sur le trigger déclenché (nom, événement déclencheur, table visée, etc)
TG_NAME, TG_WHEN, TG_LEVEL, TG_OP, TG_RELNAME,…
146
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les Trigger dans PostgreSQL
Les différences (suite)
• Référencer le tuple modifié (celui sur lequel se fait l'action)
– NEW dans la fonction pour INSERT/UPDATE
– OLD dans la fonction pour DELETE/UPDATE
• Ne supporte pas les instructions REFERENCING …  utilisation
directe des variables NEW et OLD
– p.ex. CREATE TRIGGER PriceTrig
AFTER UPDATE OF price ON "Sells"
REFERENCING OLD ROW AS ooo NEW ROW AS nnn
…
• Possibilité de préciser plusieurs évènements déclencheurs avec OR
– CREATE TRIGGER BeerTrig
AFTER INSERT OR BEFORE … ON "Sells"
147
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les exemples précédents dans PostgreSQL
CREATE OR REPLACE FUNCTION trig_beer() RETURNS TRIGGER AS
$$
BEGIN
IF ( NEW.beer NOT IN (SELECT name FROM Beers) ) THEN
INSERT INTO "Beers"(name) VALUES (NEW.beer);
END IF;
RETURN NULL;
END;
$$
LANGUAGE 'plpgsql’;
CREATE TRIGGER BeerTrigger
AFTER INSERT ON "Sells"
FOR EACH ROW
EXECUTE PROCEDURE trig_beer() ;
148
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les exemples précédents dans PostgreSQL
CREATE OR REPLACE FUNCTION trig_price() RETURNS TRIGGER AS
'
BEGIN
INSERT INTO "RipoffBars" VALUES (NEW.bar);
RETURN NULL;
END;
'
LANGUAGE 'plpgsql'
CREATE TRIGGER PriceTrig
AFTER UPDATE ON "Sells"
FOR EACH ROW
WHEN ( NEW.price > OLD.price + 1.00 )
EXECUTE PROCEDURE trig_price();
149
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les contraintes du point de vue théorique
Différents concepts théoriques pour représenter les principaux types
de contraintes (appelées aussi "classes de contraintes")
• Dépendances fonctionnelles -> fondement théorique des clés et
de la normalisation
• Dépendances d'inclusions -> fondement théorique des clés
étrangères
• Dépendances multi-valuées, …
 Mécanismes formels pour exprimer des propriétés attendues pour
les données
 Dépendances utilisées pour
• protéger les données contre certaines anomalies (p.ex. avec des
triggers)
• améliorer la conception/maintenance d'un schéma
• pour améliorer les performances
150
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Erreur de conception et Anomalies
Bon schéma relationnel:
• pas de redondance
– redondance = plusieurs fois la même information
• le fait que A.B. soit le fabricant de la Bud
• pas d’anomalies.
– Anomalie de mise à jour : une occurrence d’une information est
modifiée et pas les autres
• si Janeway part pour l’Intrepid, pensera-t-on à changer tous les nuplets?
– Anomalie de suppression : une information pertinente est perdue
en détruisant un n-uplet. :
• si personne n’aime Bud, on perd le fait que son fabricant soit A.B.
name
Janeway
Janeway
Spock
addr
Voyager
Voyager
Enterprise
beersLiked
Bud
WickedAle
Bud
manf
A.B.
Pete’s
A.B.
favBeer
WickedAle
WickedAle
Bud
151
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Dépendances Fonctionnelles
X -> A propriété d’une relation R si 2 n-uplets (tuples) sont égaux
sur les attributs X alors ils sont égaux sur l’attribut A.
• Quand c’est le cas, on dit que R satisfait la DF “X -> A ”
Conventions:
• …, X, Y, Z ensembles d’attributs;
• On écrit ABC, plutôt que {A,B,C }.
A, B, C,… attributs.
Exemple:
Drinkers(name, addr, beersLiked, manf, favBeer)
FD naturelles pour ce schéma:
1. name -> addr
2. name -> favBeer
3. beersLiked -> manf
152
Les triggers
Introduction aux contraintes en SQL
Retour à la théorie: les dépendances
Exemple
name
Janeway
Janeway
Spock
addr
Voyager
Voyager
Enterprise
Parce que name -> addr
beersLiked
Bud
WickedAle
Bud
manf
A.B.
Pete’s
A.B.
favBeer
WickedAle
WickedAle
Bud
Parce que name -> favBeer
Parce que beersLiked -> manf
153
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
DF à plusieurs attributs
Plus d’un attribut à droite: pratique mais pas indispensable
• Pratique comme racourci pour plusieurs DF
• Exemple:
name -> addr
name -> favBeer
deviennent
name -> addr favBeer
Plus d’un attribut à gauche: essentiel.
• Exemple: bar beer -> price
154
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Clés d’une Relation
K est une clé de R ssi pour tout attribut A de R on a la DF K -> A
K est une clé minimale de R ssi
•
K est une clé,
•
et aucun sous ensemble strict de K n’est une clé de R
Exemple: Drinkers(name, addr, beersLiked, manf,favBeer)
•
{name, beersLiked} est une clé: ces 2 attributs déterminent tous les
autres.
name -> addr favBeer et beersLiked -> manf
•
{name, beersLiked} est une clé minimale: ni {name}, ni {beersLiked}
ne sont des clés
name ne détermine pas manf; beersLiked ne détermine pas addr.
•
–
Il n’y a pas d’autre clé minimale, mais il y a beaucoup d’autres clés
tout ensemble d’attributs contenant {name, beersLiked}.
155
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les dépendances d'inclusions (DI)
Autre type de dépendances appelées dépendances d’inclusion (DI)
ou ≪ contraintes d'intégrité référentielles ≫.
Entre deux relations.
Exemple:
• Tout titre projeté actuellement (présent dans la relation
Programme) est le titre d’un film (c’est-à-dire apparaissant dans
la relation Films).
Programme[Titre] ⊆ Films[Titre].
Les DI peuvent faire intervenir des séquences d’attributs de chaque
côté.
156
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Différences DF versus DI
Les DI se différencient des DF sur plusieurs points
1. Peuvent être définies entre attributs de relations différentes
2. Possèdent un caractère plus global (représentent les liens
logiques entre des relations).
3. Les DI sont définies non pas entre deux ensembles
quelconques d’attributs, mais entre deux séquences d’attributs
de même taille.
–
L’ordre des attributs est donc très important pour les DI ! ! !
157
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Syntaxe et sémantique des DI
Soit R un schéma de base de données. Une dépendance d’inclusion
sur R est une expression de la forme
R [X ] ⊆ S [Y ],
• où R,S ∈ R, X et Y sont des séquences d’attributs distincts
respectivement de R et de S, et |X| = |Y|.
Une DI est satisfaite dans une base de données si toutes les valeurs
prises par la partie gauche apparaissent dans la partie droite.
Autrement dit,
• Soit d = {r1, r2, . . . , rn} une base de données sur un schéma R =
{R1,...,Rn}. Une dépendance d’inclusion Ri[X] ⊆ Rj[Y] sur R est
satisfaite dans d, noté d |= Ri[X] ⊆ Rj[Y], si ∀ti ∈ ri,∃tj ∈ rj tel que
ti[X] = tj[Y]
• de manière équivalente, πX (ri ) ⊆ πY (rj ).
158
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Exemple
Supposons des schémas de relation pour décrire les modules :
MODULE = {NUMMODULE ; INTITULE ; DESC }
et un schéma de relation pour décrire les séances de cours :
SEANCE = {DATE ; NUMMODULE ; NUMSALLE }
Pour forcer que les numéros de modules dans les séances soient
bien des modules qui existent, on devra alors définir la contrainte :
SEANCE [NUMMODULE ] ⊆ MODULE [NUMMODULE ]
159
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
DI et clé étrangère
Une contrainte d’intégrité référentielle est une DI dont la partie droite
est une clé
• Un attribut (ou ens. d’attributs) d’une relation apparaît comme clé
d’une autre relation.
La partie gauche d’une contrainte d’intégrité référentielle est appelée
clé étrangère
160
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Exemple
les DI ne définissent pas toujours des clés étrangères ! ! !
Il suffit d’imaginer qu’on souhaite imposer que tous les cours
possèdent au moins une séance dans l’année.
On définira alors une DI :
COURS[NUMCOURS] ⊆ SEANCE[NUMCOURS]
Tous les cours apparaîtront au moins une fois dans la relation des
séances
NUMCOURS n’est pas une clé de SEANCE (on imagine
difficilement que tous les cours n’aient qu’une seule séance !)
Donc ce n’est pas une clé étrangère
161
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les Dépendances : comment les trouver et pourquoi ?
Comment les trouver ?
•
L'analyse du problèmes donne des dépendances de bon sens
–
•
“jamais deux cours à la même heure dans la même salle”
heure salle -> cours.
Problème:
–
des dépendances peuvent être impliquées de façon implicite par
d'autres dépendances
nom -> adresse et adresse -> ville, donc nom -> ville
–

ces contraintes implicites peuvent échapper à la connaissance du
concepteur
Nécessité de méthodes permettant de déduire l'ensemble des
dépendances induites par un ensemble de dépendances de
départ
•
Inférence des DF, DI, …
162
Introduction aux contraintes en SQL
Les triggers
Retour à la théorie: les dépendances
Les Dépendances : comment les trouver et pourquoi ?
Pourquoi cette formalisation théorique des contraintes ?
•
Exhiber des propriétés théoriques
•
Définir des algorithmes permettant de découvrir automatiquement
les dépendances
–
•
même celles implicites
Normaliser les schémas pour éviter les anomalies dans les
données
–
normaliser = décomposer les schémas en fonction des dépendances
163
Chapitre 2: Définir et structurer les bases de données
Définir des vues des données
(dérivé du cours du Pr. Jeffrey Ullman, Stanford University)
Université de la Nouvelle-Calédonie
[email protected]
Les Vues
Une vue est une relation définie en fonction de tables
stockées (appelée tables de base) et d’autres vues.
Il existe deux types de vues:
1.
Virtuelles = pas stockées dans la base de données; juste
une requête enregistrée construisant à chaque appel la
relation.
2.
Matérialisées = relation réellement construite et
enregistrée.
165
Déclarer une Vue
Déclaré par l’instruction SQL:
CREATE [MATERIALIZED] VIEW <name> AS <query>;
• Par défaut, la vue est virtuelle.
Exemple:
• CanDrink(drinker, beer) est une vue « contenant » les pairs
buveur-bière tel que le buveur fréquente au moins un bar
proposant la bière:
CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;
166
Accéder à une Vue
Interroger une vue comme si c’était une table de base.
Dans certains cas, possibilité de modifier le contenu d’une vue
si celle-ci ne dépend que d’une seule table de base.
• modifier = INSERT, DELETE, UPDATE
• par contre ALTER toujours possible
Exemple:
SELECT beer FROM CanDrink
WHERE drinker = ’Sally’;
167
Modification de Vues grâce à des Triggers
En général, il est impossible de modifier directement une vue virtuelle,
tout simplement parce qu’elle n’existe pas concrètement.
• Dans PostgreSQL, modification automatique des données sous-jacentes
(table complète ou certaines colonnes) possible ssi
– une seule table dans le FROM,
– pas de DISTINCT, GROUP BY, LIMIT, UNION, INTERSECT, EXCEPT,
etc
– aucun opérateur d'agrégation et aucune fonction retournant des
ensembles dans le SELECT
Mais l’option d’événement INSTEAD OF des triggers permet
d’interpréter des modifications sur des vues d’une manière qui fait
sens.
CREATE TRIGGER <nom>
INSTEAD OF [INSERT, DELETE, UPDATE] ON <nomVue>
[FOR EACH ROW]
WHEN <condition>
<fonction trigger PL/pgSQL>
168
Modification de Vues grâce à des Triggers
Exemple:
• La vue Synergy est composée du triplet (drinker, beer, bar) et
représente les buveurs, les bières qu’ils aiment, ainsi que les bars
qui les servent.
Prend une copie de
chaque attribut
CREATE VIEW Synergy AS
SELECT Likes.drinker, Likes.beer, Sells.bar
FROM Likes, Sells, Frequents
WHERE Likes.drinker = Frequents.drinker
AND Likes.beer = Sells.beer
AND Sells.bar = Frequents.bar;
Jointure naturelle entre
Likes, Sells, et Frequents
169
Modification de Vues grâce à des Triggers
Exemple (suite):
• Nous ne pouvons pas insérer dans Synergy --- c’est une vue
virtuelle.
• Mais nous pouvons utiliser à la place un trigger avec INSTEAD OF
pour transformer l’insertion d’un tuple (drinker, beer, bar) en trois
insertions sur Likes, Sells, et Frequents.
– Sells.price aura la valeur NULL.
CREATE TRIGGER SynergyViewTrig
INSTEAD OF INSERT ON Synergy
FOR EACH ROW
EXECUTE PROCEDURE insertSynergyView();
CREATE FUNCTION insertSynergyView() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO LIKES VALUES(new.drinker, new.beer);
INSERT INTO SELLS(bar, beer) VALUES(new.bar, new.beer);
INSERT INTO FREQUENTS VALUES(new.drinker, new.bar);
END;
$$ LANGUAGE plpgsql;
170
Les vues Matérialisées
CREATE MATERIALIZED VIEW <name> [ (<colomn_name [,…]) ]
AS <query>;
Problème: chaque fois qu’une table de base change, la vue
matérialisée peut changer.
• Il faut reconstruire la vue à chaque changement.
Solution: Reconstruction périodique de la vue matérialisée, qui sans
cela serait "périmée".
 L’implémentation dépend du SGBD.
• Dans PostgreSQL,
– uniquement disponible depuis la version 9.3
– utiliser la commande REFRESH MATERIALIZED VIEW <name>
pour rafraîchir les données de la vue
171
Les vues Matérialisées
Exemples:
• Mailing List d’une classe
– La mailing list d’une classe peut être obtenue par une vue
matérialisée sur la table de base contenant tous les étudiants.
– si la vue est mise à jour 4 fois par jours, les étudiants enregistrés
entre deux mises à jour ne recevront pas les mails.
• Un entrepôt de données
– Les magasins Wal-Mart stockent les ventes de tous les magasins
dans une base de données.
– Pendant la nuit, les ventes du jour sont utilisées pour mettre à jour
un entrepôt de données (data warehouse) = des vues matérialisées
sur les ventes.
– L’entrepôt est utilisé par les preneurs de décision pour prédire les
tendances et envoyer des produits là où ils se vendent le mieux.
172
Chapitre 2: Définir et structurer les bases de données
Inférence des DF et normalisation
(dérivé du cours du Pr. Jeffrey Ullman, Stanford University
et du cours du Pr. Christian Retoré, Université de Bordeaux)
Université de la Nouvelle-Calédonie
[email protected]
PLAN
 Inférence des DF
 Normalisation de bases de données
174
Rappels sur les Dépendances Fonctionnelles
La DF X -> Y est satisfaite dans la relation R ssi 2 tuples égaux sur
les attributs X sont aussi égaux sur les attributs Y
• p.ex. name -> addr favBeer
• Une clé primaire représente un cas particulier de DF
Intérêt des DF: améliorer le schéma des bases de données en
évitant des anomalies
• Anomalie de mise à jour et anomalie de suppression
Des DF peuvent être déduites en analysant le problème (p.ex.
heure salle -> cours)
 Problème: des DF implicites pouvent échappées à cette analyse
• Problème de l'inférence des DF, i.e. à partir d'un ensemble de DF
connues, comment trouver toutes les DF satisfaites par une relation
175
Déduire des DF
On se donne F un ensemble de DF: X1 -> A1, X2 -> A2,…, Xn -> An ,
et on souhaite savoir si une DF Y -> B est la conséquence
sémantique de F
• c’est-à-dire Y -> B est satisfaite dans tout modèle satifaisant F.
Exemple:
• Si A -> B et B -> C sont vraies, sans doute que A -> C aussi,
même si on ne le dit pas.
Important pour la conception de bons schémas relationnels.
176
Test d'inférence
Pour tester si Y -> B, commencer par supposer que deux tuples sont
égaux sur tous les attributs de Y
Y
0000000. . . 0
00000?? . . . ?
Utiliser l'ensemble de DF de départ pour en déduire que les tuples
sont égaux sur d’autres attributs
• Si B est l’un des attributs pour lesquels il y a égalité alors Y -> B
est vrai
• Sinon les 2 tuples, avec les égalités induites par les
dépendances, forment un contre-exemple demontrant que Y -> B
n'est pas une conséquence des DF de départ
177
Test d'inférence
Exemple :
• DF données { C->B , AB->D }
• AC-> D ?
A
B
C
D
E
A
B
C
D
E
A
B
C
D
E
a’ b’ c’ d’ e’
a’ b’ c’ d’ e’
a’ b’ c’ d’ e’
a’
a’ b’ c’
a’ b’ c’ d’
c’
C->B
Donc AC->D est satisfait
AB->D
• AB-> C ?
178
Test de fermeture
Une façon plus simple pour inférer des DF est de calculer la
fermeture de Y, noté Y+
• Etant donné un ensemble F de DF et X est un ensemble d’attributs, on
note X+ l’ensemble des attributs A tels que X->A est conséquence de F
 Fermeture d'un ensemble d'attributs
Initialisation: Y + = Y.
Induction: Rechercher une partie gauche de DF dans F qui soit
inclue dans le Y + courant. Si la DF est X->A, ajouter A à Y +.
Répéter ces opérations jusqu'à ce que l'on n'ajoute plus de
nouveaux attributs dans Y+.
• Autrement dit, Y+n+1=Y+n U {A| Z->A et Z inclus dans Y+n}
• Arrêt: stabilité Y+n+1=Y+n
X
Y+
A
new Y+
179
Test de fermeture
Exemple :
• Soit F = { AB->C, C->A, BC->D, ACD->B, D->EG, BE->C,
CG->BD, CE->AG }, quelle est la fermeture de BD ?
• Initialisation: BD+={B,D}
• Itération 1:
– rechercher les DF dont la partie gauche est dans BD+: D->EG
– ajouter leur partie droite dans la fermeture: BD+={B,D,E,G}
• Itération 2:
– rechercher les DF dont la partie gauche est dans le nouveau BD+:
D->EG, BE->C
– ajouter leur partie droite dans la fermeture: BD+={B,D,E,G,C}
• Itération 3:
– rechercher les DF dont la partie gauche est dans le nouveau BD+:
C->A, BC->D, D->EG, BE->C, CE->AG
– ajouter leur partie droite dans la fermeture: BD+={B,D,E,G,C,A}
• …
180
Idée simple pour trouver toutes les DF
Commencer à partir d'un ensemble de DF connues et trouver toutes
les DF non triviales qui découle de cet ensemble de DF
•

non triviale = partie droite non inclue dans la partie gauche
Fermeture d'un ensemble de DF
Un algorithme simple, mais exponentiel
1.
2.
3.
Pour chaque ensemble d’attribut X de la relation, on calcule X +.
Ajouter X ->A pour tout A dans X + - X.
Supprimer XY ->A si on découvre X ->A.
car XY ->A découle de X ->A quelle que soit la manière dont on projette
Astuces:
•
•
Inutile de calculer la fermeture de l'ensemble contenant tous les
attributs et celle de l'ensemble vide
Si on trouve X + = tous les attributs, alors il en va de même de tout X’
contenant X
181
Idée simple pour trouver toutes les DF
Exemple:
• Soit la relation ayant pour schéma {A,B,C} avec les DF A ->B et
B ->C, cad F={A->B, B->C}
•
•
•
•
•
•
A +=ABC ; donne A ->B, A ->C
B +=BC ; donne B ->C
C +=C ; ne donne rien
AB + ; inutile de calculer car A += tous les attributs
AC + ; inutile de calculer car A += tous les attributs
BC +=BC ; ne donne rien
• DF obtenues:
F+={A ->B, A ->C, B ->C}
182
Objectif de l'inférence des DF
Motivation: "normalisation", le processus où le schéma d'une relation
est divisé en plusieurs schémas.
Exemple: Soit une relation R ayant pour schéma {A,B,C,D} avec
l'ensemble de DF {AB->C, C->D, D->A}
• Décomposer en 2 schémas {A,B,C} et {A,D}.
• Quelles DF doivent être satisfaites dans {A,B,C} ? non seulement
AB->C mais aussi C->A !
ABCD
a1 b 1 c d 1
a2 b 2 c d 2
d1=d2
parce que C -> D
a1 b 1 c
a2 b 2 c
a1=a2
parce que D -> A
donne
ABC
Ainsi, des tuples issus de la projection avec les mêmes
valeurs pour C ont la même valeur pour A, cad C -> A.
183
PLAN
 Inférence des DF
 Normalisation de bases de données
184
Design de schéma relationnel
L'objectif du design de schéma relationnel est d'éviter les anomalies
et les redondances
• Anomalie de mise à jour : une occurrence d’une information est
modifiée et pas les autres
• Anomalie de suppression : une information pertinente est perdue
en détruisant un n-uplet.
Exemple de mauvais design:
Drinkers(name, addr, beersLiked, manf, favBeer)
name
Janeway
Janeway
Spock
addr
Voyager
???
Enterprise
beersLiked
Bud
WickedAle
???
manf
A.B.
Pete’s
A.B.
favBeer
WickedAle
???
Bud
Les données sont redondantes, car chaque ??? peut être retrouvé en utilisant
les DF name -> addr favBeer et beersLiked -> manf.
185
Design de schéma relationnel
Ce mauvais schéma fait aussi ressortir des anomalies
name
Janeway
Janeway
Spock
addr
Voyager
Voyager
Enterprise
beersLiked
Bud
WickedAle
Bud
manf
A.B.
Pete’s
A.B.
favBeer
WickedAle
WickedAle
Bud
• Anomalie de mise à jour: si Janeway part pour l’Intrepid,
pensera-t-on à changer tous les tuples?
• Anomalie de suppression: si personne n’aime Bud, on perd le
fait que son fabricant soit Anheuser-Busch.
 Besoin de propriétés, de règles, de méthodes permettant de
concevoir de bons schémas
186
Forme Normale de Boyce-Codd (BCNF)
Une relation R est dite en BCNF ssi pour toute Dépendance
Fonctionnelle non triviale X ->A sur les attributs de R, X est une
super clé.
• non triviale = X ne contient pas A
• super clé = clé (minimale) ou sur-ensemble d'une clé (minimale)
Exemple:
Drinkers(name, addr, beersLiked, manf, favBeer)
DF: name->addr favBeer, beersLiked->manf
• Une seule clé minimale {name, beersLiked}.
• Pour chaque DF: la partie gauche n’est pas une super clé
• Drinkers n’est pas en BCNF (prendre l’une des deux DF au
choix)
187
Forme Normale de Boyce-Codd (BCNF)
Autre exemple:
Beers(name, manf, manfAddr)
DF: name->manf, manf->manfAddr
• Une seule clé minimale {name} .
• name->manf ne contredit pas BCNF, mais par contre la relation
n’est pas BCNF à cause de manf->manfAddr .
188
Décomposer un schéma en BCNF
Soit une relation R avec un ensemble F de DF
Chercher les DF X ->B telles que X ne soit pas une clé
• Si R pas BCNF, il y en a au moins une.
Calculer X +.
• Rq: X + ne contient pas tous les attributs, sinon X serait une clé.
Décomposer R en suivant X->B
• Remplacer R par par deux relations dont les attributs sont:
R1=X+
– R1 = X +
– R2 = R – (X + – X )
• Projeter les DF de la fermeture de F
sur ces deux nouveaux schémas
R-X+
X +- X
X
R2
R
189
Décomposer un schéma en BCNF
Exemple :
Drinkers(name, addr, beersLiked, manf, favBeer)
F = { name->addr, name -> favBeer, beersLiked->manf }
• Trouver une DF qui fait que Drinkers n’est pas en BCNF:
name->addr
• Calcule de la fermeture des attributs de la partie gauche:
{name}+ = {name, addr, favBeer}
• On obtient deux relations:
1. Drinkers1(name, addr, favBeer) = {name}+
2. Drinkers2(name, beersLiked, manf)
= {name, addr, beersLiked, manf, favBeer} - {addr, favBeer}
190
Décomposer un schéma en BCNF
Exemple (suite) :
• Ce n'est pas fini; nous devons vérifier que Drinker1 et Drinker2
sont en BCNF
• Projeter les DF est assez facile pour ce cas (car F=F+)
– F+ = { name->addr, name -> favBeer, beersLiked->manf }
– Pour Drinkers1(name, addr, favBeer), les DF pertinentes sont
name->addr et name->favBeer.
– {name} est donc la seule clé et Drinkers1 est en BCNF.
– Pour Drinkers2(name, beersLiked, manf), la seule DF est
beersLiked->manf, et la seule clé est {name, beersLiked}.
 Pas BCNF, on recommence la décomposition pour Drinkers2.
191
Décomposer un schéma en BCNF
Exemple (suite) :
• La DF qui fait que Drinkers2 n’est pas en BCNF est
beersLiked->manf
• Calcule de la fermeture {beersLiked}+ = {beersLiked, manf}
• On décompose donc Drinkers2 en
1. Drinkers3(beersLiked, manf)=X+
2. Drinkers4(name, beersLiked)={name,beersLiked,manf} - {manf}
• Projeter les DF de l'ensemble F+ dans ces nouveaux schémas
– Pour Drinkers3(beersLiked, manf), la seule DF pertinente est
beersLiked->manf
– {beersLiked} est donc la seule clé et Drinkers3 est en BCNF
– Pour Drinkers4(name, beersLiked), aucune DF (implicite ou pas)
n'est pertinente
– {name, beersLiked} est clé et Drinkers4 est en BCNF
192
Décomposer un schéma en BCNF
Exemple (fin) :
 Décomposition de Drinkers :
1. Drinkers1(name, addr, favBeer)
2. Drinkers3(beersLiked, manf)
3. Drinkers4(name, beersLiked)
• Rq: Drinkers1 décrit les personnes, Drinkers3 décrit les bières, et
Drinkers4 décrit la relation entre les personnes et les bières.
Drinkers1 name
addr
favBeer
Janeway
Voyager
WickedAle
Spock
Enterprise
Bud
Drinkers3 beersLiked manf
Bud
A.B.
WickedAle
Pete's
Drinkers4 name
beersLiked
JaneWay
Bud
JaneWay
WickedAle
Spock
Bud
193
Problème de la décomposition en BCNF
Certaines configurations de DF posent problème lorsque l'on essaye
de décomposer un schéma en BCNF
Exemple:
• AB ->C et C ->B avec A = street, B = city, et C = zip
• Il y a deux clés, {A,B } et {A,C }
• C ->B contredit BCNF, il faudrait décomposer en {A,C} et {B,C}
• Problème: si nous utilisons ces schémas, nous ne retrouvons
plus la DF AB ->C à partir des DF projetées
194
Exemple de DF non préservée
street
zip
545 Tech Sq. 02138
545 Tech Sq. 02139
city
Cambridge
Cambridge
zip
02138
02139
faire une jointure sur le code postal
street
city
545 Tech Sq. Cambridge
545 Tech Sq. Cambridge
zip
02138
02139
Bien qu'aucune DF ne soit violée dans chacune des relations
décomposées, la DF street city -> zip est violée dans la base de
données dans son ensemble
195
La 3NF évite le problème de non préservation des DF
3e Forme Normale (3NF) assouplit la condition de BCNF pour
garantir une décomposition préservant les DF
Un attribut est dit premier s’il fait partie d’une clé minimale.
Une relation n’est pas en 3NF ssi on peut trouver une DF X ->A telle
que
• X n’est pas une clé et
• A n'est pas premier (ne fait pas partie d’une clé minimale)
Exemple :
• Dans l'exemple précédent avec la relation ayant pour schéma {A,B,C} et
les DF {AB ->C, C ->B}
• Les clés minimales sont AB et AC
• Chaque attribut A, B, ou C est premier
• Bien que C ->B contredise BCNF, ce schéma est en 3NF
196
Couverture minimale des DF
Besoin de calculer une couverture minimale des DF pour
décomposer en 3NF
• Toutes DF a un seul attribut à droite
• Aucune DF ne peut être retirée
– si on en retire une, la fermeture de la couverture minimale n'est plus
égale à celle de l'ensemble de DF de départ
• Aucun attribut ne peut être enlevé
– sans changer le résultat de la fermeture
 Plus petit ensemble de DF équivalent
 leurs fermetures sont égales
Méthode:
1. Décomposer chaque DF pour avoir un seul attribut à droite
2. Supprimer les attributs en surnombre à gauche
3. Supprimer les DF redondantes
197
Exemple de couverture minimale des DF
Soit F={A->B, ABCD->E, EF->G, EF->H, ACDF->EG}
Décomposition des DF pour avoir un seul attribut à droite
• ACDF->EG devient ACDF->E et ACDF->G
 F={A->B, ABCD->E, EF->G, EF->H, ACDF->E,ACDF->G}
Suppression des attributs en surnombre à gauche (tester chaque
attribut de chaque DF)
•
ABCD->E peut être remplacé par ACD->E car ABCD+=ACD+ (grâce à
A->B)
 F={A->B, ACD->E, EF->G, EF->H, ACDF->E,ACDF->G}
Suppression les DF redondantes
•
ACDF->G peut être supprimé car cette dépendance est impliquée par
ACD->E et EF->G
• idem pour ACDF->E
 F={A->B, ACD->E, EF->G, EF->H}
198
Construction d'une décomposition 3NF
Soit une relation R et un ensemble F de DF
Calculer une couverture minimale de F
Pour chaque DF X->A dans cette couverture minimale, créer une
relation ayant pour schéma {X,A}
Si la clé (minimale) n'est pas contenue dans aucune DF, alors ajouter
une relation avec pour schéma la clé
Exemple :
• Soit la relation R avec pour schéma {A,B,C}, pour ensemble de
DF F={A->B, C->B}, et pour clé minimale {A,C}
• La couverture minimale est {A->B, C->B}
• Création de deux relations à partir des DF: R1={A,B} et R2={C,B}
• Création d'une relation à partir de la clé de R: R3={A,C}
199
Propriétés importantes pour les décompositions
Préservation des dépendances fonctionnelles
•
On peut vérifier dans les relations projetées que les
dépendances originales sont préservées.
•
Contre-exemple: décomposition BCNF de street-city-zip où
street city -> zip n'est plus forcément vérifié
Décomposition Sans Perte d’Information (SPI)
•
On peut projeter la relation de départ sur chacune des
composantes et reconstruire la relation de départ.
•
•
une décomposition BCNF vérifie SPI
Exemple: décomposition de Drinkers en Drinkers1, Drinkers3
et Drinkers4
200
Comment tester une décomposition préservant les DF ?
Soit un ensemble F de DF connues et une décomposition de la
relation R en R1, R2, … Rk
Calculer la fermeture F+ de l'ensemble des DF
Projeter cet ensemble de DF sur les relations issues de la
décomposition
• Pour chaque relation Ri, conserver uniquement les DF
composées des attributs de Ri
• On obtient donc k ensembles de DF: FR1, FR2,… FRk
• Rappel: méthode utilisée pour la décomposition en BCNF
Faire l'union des DF projetées sur R1, R2, … Rk et vérifier que le
résultat est bien égale à F+
• Autrement dit, (FR1 U FR2 U… U FRk)+=F+
201
Comment tester une décomposition SPI ?
Soit une décomposition de la relation R en R1, R2, … Rk
Si on projette les données de R dans R1, R2, … Rk, peut-on
retrouver R en faisant une jointure ?
• Tout tuple de R peut être retrouvé à partir de ses fragments projetés
• Donc la seule question est: quand nous faisons la jointure, a-t-on des
tuples supplémentaires qui n'existaient pas initialement ?
πName,Branch(r)
Name
Project
Branch
Brown
Mars
L.A.
Green
Venus
Miami
Hoskins
Saturn
Miami
Brown
L.A
Green
Miami
Hoskins
Miami
πProject,Branch(r)
Project Branch
πName,Branch(r) πProject,Branch(r)
Name
Jointure
Name
Décomposition
r
Branch
Project
Branch
Brown
Mars
L.A.
Green
Venus
Miami
Hoskins Saturn
Miami
Mars
L.A.
Green
Saturn
Miami
Venus
Miami
Hoskins Venus
Miami
Saturn
Miami
202
Le test de Chase
Supposons qu'un tuple t = abc… soit construit par la jointure des
relations Ri issues de la décomposition
Créer un tableau représentant le résultat de la jointure des relations
décomposées
Pour chaque relation décomposée Ri, créer un tuple dans ce tableau
tel qu'il a les valeurs
• a,b,c… pour les attributs de Ri
• ai,bi,ci… pour les autres attributs
• Exemple:
– soit R={A,B,C,D} décomposé en trois relations R1={A,B}, R2={B,C}
et R3={C,D}
A
B
C
D
a
b
c1
d1
a2
b
c
d2
a3
b3
c
d
203
Le test de Chase (suite)
Appliquer l'ensemble des DF connues afin de déduire des égalités
dans les données
• Si deux tuples ont les mêmes valeurs pour une partie gauche de
DF, faites que leurs valeurs pour les attributs de la partie droite
correspondent aussi
• Remplacer par les valeurs du tuples t=abc…
• Exemple (suite)
A
– F={C->D, B->A} donc
car B->A
B
C
D
a
b
c1
d1
a a2
b
c
d d2
a3
b3
c
d
car C->D
Si une ligne correspondant au tuple t=abc… apparaît, décomposition
SPI
Sinon, perte d'informations
• Exemple (fin):
 décomposition SPI
204
Exemple de décomposition avec perte d'informations
Soit R={A,B,C,D} décomposé en trois relations R1={A,B}, R2={B,C}
et R3={C,D
F={C->D}
A
Construction du tableau
Application des DF
• C->D
B
C
D
a
b
c1
d1
a2
b
c
d2
a3
b3
c
d
A
B
C
D
a
b
c1
d1
a2
b
c
d
a3
b3
c
d
 Décomposition avec perte d'informations
car on ne retrouve pas le tuple abc
205
Propriétés des décompositions 3NF et BCNF
Décomposition
Sans Perte
d’Information
Préservation des
dépendances
fonctionnelles
BCNF
3NF
oui
oui
Pas forcément
(ex: street-city-zip)
oui
206