Page 1

1 Les dpendances entre les donnes et les moyens de les vrifier Y Kermarrec ( partir des transparents de J Ullman et de son livre)

Page 2

2 Les dpendances fonctionnelles uX -> A est une assertion sur une relation R : lorsque deux n-uplets ont les mme valeurs dattributs sur X alors ils doivent avoir les mme valeurs sur les attributs A uX -> A : X dtermine A

Page 3

3 Exemple uDrinkers(name, addr, beersLiked, manf, favBeer). uDF videntes : 1.name -> addr 2.name -> favBeer 3.beersLiked -> manf

Page 4

4 Exemple nameaddr beersLiked manffavBeer JanewayVoyager Bud A.B.WickedAle JanewayVoyager WickedAle PetesWickedAle SpockEnterprise Bud A.B.Bud Car name -> addr car name -> favBeer Car beersLiked -> manf

Page 5

5 Cls des Relations uK est une cl de la relation R si: 1.Lensemble K dtermine tous les attributs de R 2.Pour aucun sous ensemble de K, (1) est vrai wSi K vrifie (1), et pas (2), on dira que K est une super-cl

Page 6

6 Comment trouver les DFs ? uAnalyser le problme et produire un cahier des charges prcis uMettre en vidence les liens entre les donnes uReformuler le tout au client

Page 7

7 Comment trouver les cls ? 1.Au hasard ou en relisant le cahier des charges, en essayant de trouver une super cl puis une cl 1.Ex : deux cours ne peuvent pas avoir lieu dans la mme salle en mme temps 2.Salle heure -> cours 2.Dtermination des cls partir des DF

Page 8

8 Fermeture des DF uFermeture de Y note Y + uPoint de dpart : Y + = Y uitration: trouver une DF dont la partie gauche est un sous-ensemble de Y +. Si cette DF est X -> A, rajouter A Y +.

Page 9

9 Y+Y+ new Y + XA

Page 10

10 A quoi a sert ? uVrifier que K est une cl uUtile pour la normalisation cad la dcomposition de relations uExemple: ABCD avec comme DF AB ->C, C ->D, et D ->A. wDcomposer R en ABC, AD. Quelles sont les DFs sur ABC wAB ->C, mais aussi C ->A !

Page 11

11 Anomalies uLa conception dun schma relationnel doit viter les anomalies wAnomalie de mise jour : une occurrence dune donne est mise jour mais pas toutes wAnomalie de destruction : une information est perdue lorsque le n-uplet est enlev

Page 12

12 Exemple de mauvaise conception Drinkers(name, addr, beersLiked, manf, favBeer) nameaddrbeersLikedmanffavBeer JanewayVoyagerBudA.B.WickedAle Janeway???WickedAlePetes??? SpockEnterpriseBud???Bud Les donnes sont redondantes car les ??? Peuvent tre dduits partir des DFs name -> addr favBeer et beersLiked -> manf.

Page 13

13 Dautres anomalies nameaddrbeersLikedmanffavBeer JanewayVoyagerBudA.B.WickedAle JanewayVoyagerWickedAlePetesWickedAle SpockEnterpriseBudA.B.Bud anomalie de mise jour anomalie de destruction

Page 14

14 Dcomposition uLes 2 extrmes : wUne seule relation avec tous les attributs (aussi appele relation universelle) wAutant de relations que dattributs (un attribut par relation) uAvantages et inconvnients ?

Page 15

15 Lidal uNe pas perdre de donnes uNe pas perdre de DFs uNe pas gnrer de nouvelles informations lors de jointures (afin de reconstituer la relation universelle)

Page 16

16 Boyce-Codd Normal Form uUne relation R est en BCNF si pour toute DF X ->A (non-triviale) X est une super-cl. wNon trivial A nest pas un sous ensemble de X

Page 17

17 Exemple uDrinkers(name, addr, beersLiked, manf, favBeer) uDFs: name->addr favBeer, beersLiked->manf uSeule cl {name, beersLiked}. uPour chaque DF, la partie gauche nest pas une super cl uDrinkers nest pas en BCNF

Page 18

18 Exemple uBeers(name, manf, manfAddr) uDF: name->manf, manf->manfAddr uCl : {name}. uname->manf respecte la rgle BCNF, mais pas manf->manfAddr

Page 19

19 Dcomposition en BCNF uSoit R avec F lensemble des DFs uTrouver une DF qui ne respecte pas la rgle BCNF, soit X ->B. ucalculer X + wNe doit pas gnrer tous les attributs car sinon X est une super cl.

Page 20

20 Dcomposer R avec X -> B -Remplacer R par : -R 1 = X +. -R 2 = (R X + ) U X. -Projeter les DFs de R sur les 2 nouvelles relations R1 et R2 -Calculer la fermeture de F = toutes les DFs -Ne garder les DFs que celles dont tous les attributs sont dans R 1 ou dans R 2.

Page 21

21 Principe de dcomposition R-X +R-X + XX +-XX +-X R2R2 R1R1 R

Page 22

22 Exemple uDrinkers(name, addr, beersLiked, manf, favBeer) uF = name->addr, name -> favBeer, beersLiked->manf uDrinkers nest pas BCNF car name->addr. uCalcul de la fermeture {name} + = {name, addr, favBeer}. uDcomposer en 2 relations: 1.Drinkers1(name, addr, favBeer) 2.Drinkers2(name, beersLiked, manf)

Page 23

23 Exemple uDrinkers1 et Drinkers2 sont elles BCNF? uProjeter les DFs est compliqu mais simple dans cet exemple. uPour Drinkers1(name, addr, favBeer), les DFs sont name->addr et name->favBeer wDonc name est la seule cl et la relation est BNCF.

Page 24

24 Exemple uPour Drinkers2(name, beersLiked, manf), la seule DF beersLiked->manf et la seule cl est {name, beersLiked}. wViolation de la rgle BCNF. ubeersLiked + = {beersLiked, manf}, on dcompose Drikers2 en 1.Drinkers3(beersLiked, manf) 2.Drinkers4(name, beersLiked)

Page 25

25 Exemple uLe rsultat de la dcomposition de Drinkers: 1.Drinkers1(name, addr, favBeer) 2.Drinkers3(beersLiked, manf) 3.Drinkers4(name, beersLiked) wA noter Drinkers1 nous parle des buveurs, Drinkers3 nous parle de bires, et Drinkers4 nous parle des relations entre bire et buveurs.

Page 26

26 Contraintes et Triggers uUne contrainte est une relation entre les donnes que le SGBD doit vrifier wExemple: contraintes de cls. uTriggers : ce sont des actions qui sont dclenches sur vnement

Page 27

27 Les contraintes uLes cls uLes cls trangres. uLes contraintes de domaine wContraindre le domaine dun attribut donn uDes contraintes sur des tuples uAssertion : une expression boolenne SQL

Page 28

28 Les cls trangres uSoit la relation Sells(bar, beer, price). uOn peut sattendre ce que la valeur de lattribut beer apparaisse dans la relation Beers.Name uUne contrainte qui impose quune bire dans Sells soit une bire dans Beers est appele contrainte de cl trangre

Page 29

29 Cl trangre uUtiliser le mot cl REFERENCES soit : 1.Lors de la dclaration de lattribut (sil est seul) 2.En tant qulment du schma FOREIGN KEY ( ) REFERENCES ( ) uLes attributs ainsi rfrencs doivent tre dclars PRIMARY KEY ou UNIQUE.

Page 30

30 Exemple CREATE TABLE Beers ( nameCHAR(20) PRIMARY KEY, manfCHAR(20) ); CREATE TABLE Sells ( barCHAR(20), beerCHAR(20) REFERENCES Beers(name), priceREAL );

Page 31

31 Exemple (autre formulation) CREATE TABLE Beers ( nameCHAR(20) PRIMARY KEY, manfCHAR(20) ); CREATE TABLE Sells ( barCHAR(20), beerCHAR(20), priceREAL, FOREIGN KEY(beer) REFERENCES Beers(name));

Page 32

32 Vrifier une cl trangre uSil y a une cl trangre sur des attributs de R vers une cl primaire de la relation S, 2 cas de violations sont possibles: 1.Une insertion ou mise jour de R introduit une valeur absente dans S. 2.Une destruction ou mise jour de S qui provoque des rfrences fantmes ( dangling reference )

Page 33

33 Action -- 1 uSuppose R = Sells, S = Beers uUne insertion ou mise jour de Sells qui introduit une bire non existante doit tre rejete uUne destruction ou mise jour de Beers qui enlve une valeur de bire prsente dans un n-uplet de Sells peut tre trait de 3 manires

Page 34

34 Action -- 2 uLes 3 manires de ragir la disparition de bires sont : 1.Default : rejeter la modification 2.Cascade : raliser les mmes modifications dans Sells wDeleted beer : destruction des tuples de Sells wUpdated beer: modifier les valeurs dans Sells. 3.Set NULL : mettre bire NULL

Page 35

35 Exemple: Cascade uOn retire Bud de la liste des bires wOn enlve tous les n-uplest de Sells qui ont Bud comme valeur dattribut pour bire uOn modifie Bud en Budweiser. wOn modifie tous les tuples de Sells avec beer = Bud par beer = Budweiser.

Page 36

36 Exemple: Set NULL uOn enlve BUD de Beers wModifie tous les n-uplets avec beer = Bud et remplacer cet attribut par beer = NULL. uOn modifie le tuple en changeant de nom Bud vers Budweiser. wMme modification que prcdemment

Page 37

37 Choix dune politique uLorsquon dclare une cl trangre, il faut aussi prciser la politique SET NULL ou CASCADE pour les destructions ET les mises jour uLa prcision est donne lors de la dclaration de la cl trangre: ON [UPDATE, DELETE][SET NULL CASCADE] uLaction par dfaut est de rejeter

Page 38

38 Exemple CREATE TABLE Sells ( barCHAR(20), beerCHAR(20), priceREAL, FOREIGN KEY(beer) REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE );

Page 39

39 Vrification sur attributs uDonner une contrainte sur le domaine de valeur dun attribut uCHECK( ) : utiliser lors de la dfinition de lattribut uLa condition peut rfrencer lattribut en direct mais aussi nimporte quelle autre requte (relation et attribut).

Page 40

40 Exemple CREATE TABLE Sells ( barCHAR(20), beerCHAR(20)CHECK ( beer IN (SELECT name FROM Beers)), priceREAL CHECK ( price