bcnfvs3nf.pptVIP

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 3NF Schema 3NF Schema BCNF vs 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 Y is a subset of K for some key K of R 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 BCNFify 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’ */ } 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;