Boyce–Codd normal form (BCNF or 3.5NF)

BCNF : 
In BCNF part of primary key should not be fully functionally dependent on non-primary key.

Conditions :

          1. Relation must be in 3NF.
          2. If X determines Y (X->Y) than X must be a super key.


Example :
        A -> BCD………….. (i)
      BC -> AD……………..(ii)
        D -> B………………..(iii)
 
Here key is A and BC. And this example is now in 3NF form.
 
From example we can see that A is a super key because it determines all the elements.
BC is a key.
 
But   D -> B is violating our condition, because D is not any key. So it should be in different table. 
R2(D,B)
 
N.B :  Here R2 indicates a table.
 
Another table should be : R1 (ABC) (ADC) [ Get ADC because  from (iii) ]
 
Now from R2(D,B), D -> B means D is a key and B is not a key. So D should be in R1 table.
 
For that R1 (ABC) is incorrect, because D is absent.
Finally we can see that R1 (ADC) is correct.
 
Final Result :
     From R1 (ADC) and R2 (D, B)
     We get :   
        A -> DC
        C -> AD (From BC -> AD, B is now not a key.)
 
This is now in BCNF from.
 
Here A and C is now candidate key.

Leave a Reply