BCNF V/S 3NF

Here one can know about difference between BCNF AND 3NF.
There is also example to make it more simple and easy to understand.

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. 3NF Schema
Account Client Office
A Joe 1
B Mary 1
A John 1
C Joe 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 Client, Office -> Client, Office, Account
Account -> Office

3. 3NF Schema
Account Client Office
A Joe 1
B Mary 1
A John 1
C Joe 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 Client, Office -> Client, Office, Account
Account -> Office

4. 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
3NF has some redundancy
BCNF does not
Unfortunately, BCNF is not dependency
preserving, but 3NF is
Client, Office -> Client, Office, Account
Account -> Office
Account Client Office
A Joe 1
B Mary 1
A John 1
C Joe 2
Account Office
A 1
B 1
C 2
Account Client
A Joe
B Mary
A John
C Joe
Account -> Office
No non-trivial FDs
Lossless
decomposition

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. BCNFify
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(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
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

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