of 50/50
1 Les dépendances entre les données et les moyens de les vérifier Y Kermarrec (à partir des transparents de J Ullman et de son livre)

1 Les dépendances entre les données et les moyens de les vérifier Y Kermarrec (à partir des transparents de J Ullman et de son livre)

  • View
    106

  • Download
    1

Embed Size (px)

Text of 1 Les dépendances entre les données et les moyens de les vérifier Y Kermarrec (à partir des...

  • 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