Boyce-Codd Normal Form (BCNF)

Application of the general definitions of 2NF and 3NF may identify additional redundancy caused by dependencies that violate one or more candidate keys. However, despite these additional constraints, dependencies can still exist that will cause redundancy to be present in 3NF relations. This weakness in 3NF, resulted in the presentation of a stronger normal form called Boyce–Codd Normal Form (Codd, 1974).

Although, 3NF is adequate normal form for relational database, still, this (3NF) normal form may not remove 100% redundancy because of X→Y functional dependency, if X is not a candidate key of given relation. This can solve by Boyce-Codd Normal Form (BCNF).

Boyce-Codd Normal Form (BCNF):
Boyce–Codd Normal Form (BCNF) is based on functional dependencies that take into account all candidate keys in a relation; however, BCNF also has additional constraints compared with the general definition of 3NF.

A relation is in BCNF iff, X is superkey for every functional dependency (FD) X→Y in given relation.

In other words,

A relation is in BCNF, if and only if, every determinant is a Form (BCNF) candidate key.

Note – To test whether a relation is in BCNF, we identify all the determinants and make sure that they are candidate keys.

Example-1:
Find the highest normal form of a relation R(A, B, C, D, E) with FD set as:

{ BC->D, AC->BE, B->E } 

Explanation:

  • Step-1: As we can see, (AC)+ ={A, C, B, E, D} but none of its subset can determine all attribute of relation, So AC will be candidate key. A or C can’t be derived from any other attribute of the relation, so there will be only 1 candidate key {AC}.
  • Step-2: Prime attributes are those attribute which are part of candidate key {A, C} in this example and others will be non-prime {B, D, E} in this example.
  • Step-3: The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is in 2nd normal form because BC->D is in 2nd normal form (BC is not a proper subset of candidate key AC) and AC->BE is in 2nd normal form (AC is candidate key) and B->E is in 2nd normal form (B is not a proper subset of candidate key AC).

The relation is not in 3rd normal form because in BC->D (neither BC is a super key nor D is a prime attribute) and in B->E (neither B is a super key nor E is a prime attribute) but to satisfy 3rd normal for, either LHS of an FD should be super key or RHS should be prime attribute. So the highest normal form of relation will be 2nd Normal form.

Example-2:
For example consider relation R(A, B, C)

A -> BC, 
B -> A

A and B both are super keys so above relation is in BCNF.

Note –
BCNF decomposition may always not possible with dependency preserving, however, it always satisfies lossless join condition. For example, relation R (V, W, X, Y, Z), with functional dependencies:

V, W -> X
Y, Z -> X
  W -> Y 

It would not satisfy dependency preserving BCNF decomposition.

Refer for:
4th and 5th Normal form and finding the highest normal form of a given relation.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.