But y ou can't c hec k the FD ! in AB C these - PDF document

3NF One FD structure causes problems: � If y ou decomp ose, y ou can't c hec k the FD's in the decomp osed relations. � If y ou don't decomp ose, y ou violate BCNF. Abstractly: ! and ! . AB C C B � In b o ok: ! and title city theatre theatre ! city . � Another example: ! zip , ! street city zip city . Keys: f A; g and f A; g , but ! has a left B C C B side not a sup erk ey . � Suggests decomp osition in to and . B C AC ✦ But y ou can't c hec k the FD ! in AB C these relations. 1

Example = street , = city , = zip . A B C street zip 545 T ec h Sq. 02138 545 T ec h Sq. 02139 cit y zip Cam bridge 02138 Cam bridge 02139 Join: cit y street zip Cam bridge 545 T ec h Sq. 02138 Cam bridge 545 T ec h Sq. 02139 2

\Elegan t" W ork around De�ne the problem a w a y . � A relation is in 3NF i� for ev ery non trivial R FD ! A , either: X 1. is a sup erk ey , or X 2. is = mem b er of at least one k ey . A prime � Th us, the canonical problem go es a w a y: y ou don't ha v e to decomp ose b ecause all attributes are prime and therefore no 3NF violati ons can o ccur. 3

T aking Adv an tage of 3NF F or an y relation and set of FD's Theorem: R , w e can �nd a decomp osition of in to 3NF F R relations, suc h that if the decomp osed relations satisfy their pro jected dep endencies from , then F their join will satisfy itself. F � In fact, with some more e�ort, w e can guaran tee that the decomp osition is also \lossless"; i.e., the join of the pro jections of on to the decomp osed relations is alw a ys R R itself, just as for the BCNF decomp osition. � But what w e giv e up is absolute absence of redundancy due to FD's. � The \ob vious" approac h of doing a BCNF decomp osition, but stopping when a relation sc hema is in 3NF, do esn't alw a ys w ork | it migh t still allo w some FD's to get lost. 4

Roadmap 1. Study sets of FD's: needed for the minimal decomp ositions. ✦ Requires study of when t w o sets of FD's are e quivalent , in the sense that they are satis�ed b y exactly the same relation instances. 2. Giv e the algorithm for constructing a decomp osition in to 3NF sc hemas that preserv es all FD's. ✦ Called the algorithm. synthesis 3. Sho w ho w to mo dify this construction to guaran tee losslessness. 5

3NF Syn thesis Algorithm Roughly , w e create for eac h FD in a relation F con taining only its attributes. � But: w e need �rst to mak e in the F minimal sense that: a) No FD can b e eliminated from . F b) No attribute can b e eliminated from a left side of an FD of . F � Note that minimal sets of FD's are not necessarily unique. Equiv alen t Sets of FD's FD sets are if they eac h deriv e the other, e quivalent i.e., if they allo w the same set of relation instances. � F or eac h of (a) and (b) in the de�nition of \minimalit y ," w e mean \without making a set of FD's inequiv al en t to ." F 6

T esting Equiv alence 1 ! A 1 1 ! 1 X Y B 2 ! A 2 2 ! 2 X Y B � � � � � � ! ! X n An Y m B m � F or eac h i , ! m ust follo w from the set Y i B i on the left. ✦ + i.e, ( Y i ) m ust con tain i , when closure B is computed using the FD's left . on the � Also, eac h ! m ust follo w from the set X i Ai on the righ t. � Imp ortan t sp ecial case: no need to c hec k an FD that app ears in b oth sets. 7

Example Supp ose has ! , ! , and ! . F A B B C AC D � is not minimal. F � 1 with ! , ! , and ! is F A B B C A D minimal. ✦ Note that from w e can infer ! , F A D and from 1 w e can infer ! . F AC D � 2 consisting of ! , ! and ! F A B B C C D is equiv al en t to . not F ✦ Note y ou cannot infer ! from . C D F 8

A Dep endency-Preserving Decomp osition 1. Minimize the giv en set of dep endencies. 2. Create a relation with sc hema for eac h X Y FD ! . X Y 3. Eliminate a relation sc hema that is a subset of another. 4. Add in a relation sc hema with all attributes that are not part of FD. any 9

Example � Start with = and consisting of R AB C D F ! , ! , and ! . A B B C AC D � 1 with ! , ! , and ! is a F A B B C A D minimal equiv alen t. � With 1 as our minimal set of FD's, w e get F database sc hema , , and , whic h is AB B C AD su�cien t to c hec k 1 and therefore . F F 10

Dep endency Preserv ation with Losslessness Same as for just dep endency preserv ation, but add in a relation sc hema consisting of a k ey for . R Example In ab o v e example, is a k ey for , so w e should A R add as a relation sc hema. Ho w ev er, is a subset A A of , and so nothing is needed; the original AB database sc hema f AB g is lossless. ; B C ; AD Not Co v ered � Wh y basing the decomp osition on a minimal equiv ale n t set of FD's guaran tees 3NF. � Wh y the k ey + FD's syn thesis approac h guaran tees losslessness. 11

Multiv alued Dep endencies Consider the relation Drinkers(name, addr, beersLiked) , with the FD ! addr . phone, name That is, drink ers can ha v e sev eral phones and lik e sev eral b eers. T ypical relation: name addr phone b eersLik ed jo e p 1 b 1 a jo e a p 1 b 2 jo e p 1 b 3 a jo e p 2 b 1 a jo e p 2 b 2 a jo e p 2 b 3 a � Key = f name, beersLiked g . phone, � BCNF violati on: ! addr . Decomp ose name in to addr) , D1(name, D2(name, phone, beersLiked) . ✦ Both are in BCNF. 12

� But lo ok at 2: D name phone b eersLik ed jo e p 1 b 1 jo e p 1 b 2 jo e p 1 b 3 jo e p 2 b 1 jo e p 2 b 2 jo e p 2 b 3 � The phones and b eers are eac h rep eated. ✦ If Jo e had phones and lik ed b eers, n m there w ould b e tuples for Jo e, when nm max( n; m ) should b e enough. 13

Multiv alued Dep endencies The ! ! holds in multivalue d dep endency X Y a relation if whenev er w e ha v e t w o tuples of R R that agree in all the attributes of , then w e can X sw ap their comp onen ts and get t w o new tuples Y that are also in . R others X Y 14

Example In Drinkers , w e ha v e MVD ! ! phone . F or name example: name addr phone b eersLik ed jo e p 1 b 1 a jo e p 2 b 2 a with comp onen ts sw app ed yields: phone name addr phone b eersLik ed jo e p 1 b 2 a jo e p 2 b 1 a whic h are also tuples of the relation. � Note: w e m ust c hec k this condition for al l pairs of tuples that agree on name , not just one pair. 15

MVD Rules 1. Ev ery FD is an MVD. ✦ Because if ! , then sw apping 's X Y Y do esn't create new tuples. ✦ Example, in Drinkers : ! ! addr . name 2. Complementation : if ! ! , then ! ! , X Y X Z where is all attributes not in or . Z X Y ✦ Example: since ! ! name phone holds in Drinkers , so do es ! ! beersLiked . name addr 16

Splitting Do esn't Hold Sometimes y ou need to ha v e sev eral attributes on the left of an MVD. F or example: Drinkers(name, areaCode, phone, beersLiked, beerManf) name areaCo de phone BeersLik ed b eerManf Jo e 650 555-1111 Bud A.B. Jo e 650 555-1111 Wic k edAle P ete's Jo e 415 555-9999 Bud A.B. Jo e 415 555-9999 Wic k edAle P ete's � ! ! holds, but neither name areaCode phone ! ! nor ! ! do. name areaCode name phone 17

4NF Eliminate redundancy due to m ultiplicati v e e�ect of MVD's. � Roughly: treat MVD's as FD's for decomp osition, but not for �nding k eys. � F ormally: is in F ourth Normal F orm if R whenev er MVD ! ! is ( Y X Y nontrivial is not a subset of , and [ is not all X X Y attributes), then is a sup erk ey . X ✦ Remem b er, ! implies ! ! , so X Y X Y 4NF is more stringen t than BCNF. � Decomp ose , using 4NF violati on ! ! , R X Y in to and [ ( R � ). X Y X Y X Y R 18

Example Drinkers(name, addr, areaCode, phone, beersLiked, beerManf) � FD: ! name addr � Non trivial MVD's: ! ! name areaCode phone and ! ! beerManf . name beersLiked � Only k ey: f name, areaCode, phone, beerManf g beersLiked, � All three dep endencies violate 4NF. � Successiv e decomp osition yields 4NF relations: D1(name, addr) D2(name, areaCode, phone) D3(name, beersLiked, beerManf) 19