Boyce–Codd normal form (BCNF or 3.5NF)

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