Finding All Implied FD's Motiv atio n: Supp ose w e ha v - PDF document

Finding All Implied FD's Motiv atio n: Supp ose w e ha v e a relation AB C D with some FD's . If w e decide to decomp ose F in to and , what are the FD's for AB C D AB C AD , ? AB C AD � Example: = ! , ! , ! A . F AB C C D D It lo oks lik e just ! holds in , but AB C AB C in fact ! follo ws from and applies to C A F relation . AB C � Problem is exp onen tial in w orst case. 1

Algorithm + F or eac h set of attributes compute . X X � Eliminate some \ob vious" dep endencies that follo w from others: 1. : righ t side is a subset of left side. T rivial FD's ✦ + Consequence: no p oin t in computing ; or closure of full set of attributes. 2. Eliminate ! if ! holds. X Y Z X Z ✦ + Consequence: If is all attributes, then X there is no p oin t in computing closure of sup ersets of . X 3. Eliminate FD's whose righ t sides are not single attributes. 2

Example ! ! ! Example: = , , A . What F AB C C D D FD's follo w? + + � = A ; = (nothing). A B B + � = (add ! A ). C AC D C + � = (nothing new). D AD + � ( AB ) = (add ! ; skip all AB C D AB D sup ersets of ). AB + � ( B ) = (nothing new; skip all C AB C D sup ersets of ). B C + � ( B ) = (add ! ; skip all D AB C D B D C sup ersets of ). B D + + + � ( AC ) = ; ( AD ) = ; ( C ) = AC D AD D (nothing new). AC D + � ( AC ) = (nothing new). D AC D � All other sets con tain AB , B C , or B D , so skip. � Th us, the only in teresting FD's that follo w from are: ! A , ! , ! . F C AB D B D C 3

Normalization Goal = BCNF = Bo yce-Co dd Normal F orm = all ! FD's follo w from the fact \k ey ev erything." � F ormally , is in BCNF if ev ery non trivial FD R ! for R , sa y X A , has X a sup erk ey . Wh y? 1. Guaran tees no redundancy due to FD's. 2. Guaran tees no = one up date anomalies o ccurrence of a fact is up dated, not all. 3. Guaran tees no = v alid fact deletion anomalies is lost when tuple is deleted. 4

Example of Problems Drinkers(name, addr, beersLiked, manf, favoriteBeer) name addr b eersLik ed manf fa v oriteBeer Janew a y V o y ager Bud A.B. Wic k edAle Janew a y ??? Wic k edAle P ete's ??? Sp o c k En terprise Bud ??? Bud FD's: 1. ! name addr 2. ! name favoriteBeer ! 3. beersLiked manf � ???'s are redundan t, since w e can �gure them out from the FD's. � Up date anomalies: If Janew a y gets transferred to the epid , will w e c hange in eac h of Intr addr her tuples? � Deletion anomalies: If nob o dy lik es Bud, w e lose trac k of Bud's man ufacturer. 5

Eac h of the giv en FD's is a BCNF violation: � Key = f name, beersLiked g ✦ Eac h of the giv en FD's has a left side a prop er subset of the k ey . Another Example manfAddr) . Beers(name, manf, � ! ! FD's = manf , manfAddr . name manf � Only k ey is name . ✦ ! violates BCNF with a manf manfAddr left side unrelated to an y k ey . 6

Decomp osition to Reac h BCNF Setting: relation , giv en FD's . Supp ose R F ! relation has BCNF violati on A . R X � Notice: w e need only lo ok among FD's of , F b ecause an y non trivial FD that follo ws from them m ust con tain one of their left sides in its left side. ✦ Th us, an y FD that follo ws and has a non- sup erk ey as a left side means there is an FD in with the same prop ert y . F 7

+ 1. righ t side to include . Exp and X ✦ Cannot b e all attributes | wh y? + + 2. Decomp ose in to and ( R � ) [ . R X X X R X + X 3. Find the FD's for the decomp osed relations. ✦ Pro ject the FD's from = calculate F all consequen ts of that in v olv e only F + attributes from or only from ( R � X + [ ) . X X 8

Example = R Drinkers(name, addr, beersLiked, manf, favoriteBeer) = F 1. ! name addr 2. ! name favoriteBeer 3. ! beersLiked manf ! Pic k BCNF violati on addr . name � Expand righ t side: ! favoriteBeer . name addr � Decomp osed relations: Drinkers1(name, addr, favoriteBeer) Drinkers2(name, beersLiked, manf) � Pro jected FD's (skipping a lot of w ork that leads no w ehere in teresting): ✦ F or Drinkers1 : ! and name addr ! favoriteBeer . name ✦ F or Drinkers2 : ! manf . beersLiked 9

� BCNF violati ons? ✦ F or Drinkers1 , is k ey and all left name sides are sup erk eys. ✦ F or Drinkers2 , is {name, beersLiked} ! the k ey , and violates beersLiked manf BCNF. Decomp ose Drinkers2 � Expand: nothing. � Decomp ose: Drinkers3(beersLi ked, manf) Drinkers4(name, beersLiked) � Resulting relations are all in BCNF: Drinkers1(name, addr, favoriteBeer) Drinkers3(beersLi ked, manf) Drinkers4(name, beersLiked) 10

Wh y Decomp osition \W orks"? What do es it mean to \w ork"? Wh y can't w e just tear sets of attributes apart as w e lik e? � Answ er: the decomp osed relations need to represen t the same information as the original. Pro jection and Join � The op erations that relate original and decomp osed relations. � Supp ose is decomp osed in to and . W e R S T on to b y: pr oje ct R S 1. Eliminate columns of not in . R S 2. Eliminate duplicate ro ws. 11

Example = R name addr b eersLik ed manf fa v oriteBeer Janew a y V o y ager Bud A.B. Wic k edAle Janew a y V o y ager Wic k edAle P ete's Wic k edAle Sp o c k En terprise Bud A.B. Bud 12

� Pro ject on to Drinkers1(name, addr, favoriteBeer) : name addr fa v oriteBeer Janew a y V o y ager Wic k edAle Sp o c k En terprise Bud � Pro ject on to manf) : Drinkers3(beersLik ed, b eersLik ed manf Bud A.B. Wic k edAle P ete's � Pro ject on to beersLiked) : Drinkers4(name, name b eersLik ed Janew a y Bud Janew a y Wic k edAle Sp o c k Bud 13

Reconstruction of Original Can w e �gure out the original relation from the decomp osed relations? � Sometimes, if w e (natural) the relations. join � : R . / S ✦ Sc hema = union of attributes of and . R S ✦ T uples = all formed from a tuple from r and from that agree in all common R s S attributes. Example = . / Drinkers3 Drinkers4 name b eersLik ed manf Janew a y Bud A.B. Janew a y Wic k edAle P ete's Sp o c k Bud A.B. � Join of ab o v e with = original R . Drinkers1 14

Theorem Supp ose w e decomp ose a relation with sc hema in to and and pro ject the relation X Y Z X Y X Z for on to and . Then X Y Z X Y X Z X Y . / X Z is to reconstruct if and only if guar ante e d X Y Z either ! or ! holds. X Y X Z � Notice that whenev er w e decomp ose b ecause of a BCNF violation, one of these FD's m ust hold. Pro of (if ) 1. An ything y ou pro ject comes bac k in the join. ✦ Do esn't dep end on FD's. Z X Y t Z X X Y t t 1 2 15

2. An ything that comes bac k in the join w as in the original . X Y Z Z X Y t s Z X X Y t s 1 2 r � Notice that and agree on . t s X 1 2 � If ! , then = t . X Y r � ! If , then = s . X Z r � Either w a y , is in original . r X Y Z 16

Pro of (only-if ) If neither ! nor ! holds, then w e can X Y X Z �nd an example relation where the pro ject- X Y Z join returns to o m uc h. Z X Y 1 1 z x y 2 2 z x y Z X 1 z x 2 z x X Y 1 x y 2 x y Z X Y 1 1 z x y 1 2 z x y 2 1 z x y 2 2 z x y 17