1 / 8

BCNF vs 3NF

BCNF vs 3NF. BCNF : For every functional dependency X-&gt;Y in a set F of functional dependencies over relation R , either: Y is a subset of X or, X is a superkey of R 3NF : For every functional dependency X-&gt;Y in a set F of functional dependencies over relation R , either:

BCNF vs 3NF

E N D

Presentation Transcript

1. BCNF vs 3NF • BCNF: For every functional dependency X->Y in a set F of functional dependencies over relation R, either: • Y is a subset of X or, • X is a superkey of R • 3NF: For every functional dependency X->Y in a set F of functional dependencies over relation R, either: • Y is a subset of X or, • X is a superkey of R, or • Y is a subset of K for some key K of R • N.b., no subset of a key is a key

2. For every functional dependency X->Y in a set F of functional dependencies over relation R, either: • Y is a subset of X or, • X is a superkey of R, or • Y is a subset of K for some key K of R 3NF Schema Client, Office -> Client, Office, Account Account -> Office

3. For every functional dependency X->Y in a set F of functional dependencies over relation R, either: • Y is a subset of X or, • X is a superkey of R, or • Y is a subset of K for some key K of R 3NF Schema Client, Office -> Client, Office, Account Account -> Office

4. Lossless decomposition Account -> Office No non-trivial FDs BCNF vs 3NF 3NF has some redundancy BCNF does not Unfortunately, BCNF is not dependency preserving, but 3NF is For every functional dependency X->Y in a set F of functional dependencies over relation R, either: • Y is a subset of X or, • X is a superkey of R • Y is a subset of K for some key K of R Client, Office -> Client, Office, Account Account -> Office

5. Closure • Want to find all attributes A such that X -> A is true, given a set of functional dependencies F define closure of X as X* Closure(X): c = X Repeat old = c if there is an FD Z->V such that Zc and Vc then c = c U V until old = c return c

6. Closure(X): c = X Repeat old = c if there is an FD Z->V such that Zc and Vc then c = c U V until old = c return c BCNFify For every functional dependency X->Y in a set F of functional dependencies over relation R, either: • Y is a subset of X or, • X is a superkey of R BCNFify(schema R, functional dependency set F): D = {{R,F}} while there is a schema S with dependencies F' in D that is not in BCNF, do: given X->Y as a BCNF-violating FD in F such that XY is in S replace S in D with S1={XY,F1} and S2={(S-Y) U X, F2} where F1 and F2 are the FDs in F over S1 or S2 (may need to split some FDs using decomposition) End return D

7. B-tree Insertion INSERTION OF KEY ’K’ find the correct leaf node ’L’; if ( ’L’ overflows ){ split ’L’, by pushing the middle key upstairs to parent node ’P’; if (’P’ overflows){ repeat the split recursively; } else{ add the key ’K’ in node ’L’; /* maintaining the key order in ’L’ */ } Slide from Mitch Cherniak and George Kollios

8. B-tree deletion - pseudocode DELETION OF KEY ’K’ locate key ’K’, in node ’N’ if( ’N’ is a non-leaf node) { delete ’K’ from ’N’; find the immediately largest key ’K1’; /* which is guaranteed to be on a leaf node ’L’ */ copy ’K1’ in the old position of ’K’; invoke this DELETION routine on ’K1’ from the leaf node ’L’; else { /* ’N’ is a leaf node */ if( ’N’ underflows ){ let ’N1’ be the sibling of ’N’; if( ’N1’ is "rich"){ /* ie., N1 can lend us a key */ borrow a key from ’N1’ THROUGH the parent node; }else{ /* N1 is 1 key away from underflowing */ MERGE: pull the key from the parent ’P’, and merge it with the keys of ’N’ and ’N1’ into a new node; if( ’P’ underflows){ repeat recursively } } } Slide from Mitch Cherniak and George Kollios

More Related