Open In App

Difference between 3NF and BCNF in DBMS

Improve
Improve
Like Article
Like
Save
Share
Report

1. Third Normal Form (3NF) : A relation is said to be in Third Normal Form (3NF), if it is in 2NF and when no non key attribute is transitively dependent on the primary key i.e., there is no transitive dependency. Also it should satisfy one of the below given conditions. For the function dependency C->D:

  • C should be a super key and,
  • D should be a prime attribute i.e, D should be a part of the candidate key.

3NF is used to reduce data duplication and to attain data integrity. 

Example: For the relation R(L, M, N, O, P) with functional dependencies as {L->M, MN->P, PO->L}:

The candidate keys will be : {LNO, MNO, NOP}
      as the closure of LNO = {L, M, N, O, P} 
             closure of MNO = {L, M, N, O, P}
             closure of NOP = {L, M, N, O, P}

This relation is in 3NF as it is already in 2NF and has no transitive dependency. Also there is no non prime attribute that is deriving a non prime attribute. 

2. Boyce-Codd Normal Form (BCNF) : BCNF stands for Boyce-Codd normal form and was made by R.F Boyce and E.F Codd in 1974.A functional dependency is said to be in BCNF if these properties hold:

  • It should already be in 3NF.
  • For a functional dependency say P->Q, P should be a super key.

BCNF is an extension of 3NF and it is has more strict rules than 3NF. Also, it is considered to be more stronger than 3NF. 

Example: for the relation R(A, B, C, D) with functional dependencies as {A->B, A->C, C->D, C->A}:

The candidate keys will be : {A, C}
      as the closure of A = {A, B, C, D} 
             closure of C = {A, B, C, D} 

This relation is in BCNF as it is already in 3Nf (there is no prime attribute deriving no prime attribute) and on the left hand side of the functional dependency there is a candidate key. 

Difference between 3NF and BCNF :

S.NO. 3NF BCNF
1. 3NF stands for Third Normal Form. BCNF stands for Boyce Codd Normal Form.
2. In 3NF there should be no transitive dependency that is no non prime attribute should be transitively dependent on the candidate key. In BCNF for any relation A->B, A should be a super key of relation.
3. It is less stronger than BCNF. It is comparatively more stronger than 3NF.
4. In 3NF the functional dependencies are already in 1NF and 2NF. In BCNF the functional dependencies are already in 1NF, 2NF and 3NF.
5. The redundancy is high in 3NF. The redundancy is comparatively low in BCNF.
6. In 3NF there is preservation of all functional dependencies. In BCNF there may or may not be preservation of all functional dependencies.
7. It is comparatively easier to achieve. It is difficult to achieve.
8. Lossless decomposition can be achieved by 3NF. Lossless decomposition is hard to achieve in BCNF.
9.

The table is in 3NF if it is in 2NF and for each functional dependency X->Y at least following condition hold: 

(i) X is a super key, 

(ii) Y is prime attribute of table.

The table is in BCNF if it is in 3rd normal form and for each relation X->Y X should be super key.
10. 3NF can be obtained without sacrificing all dependencies. Dependencies may not be preserved in BCNF.
11. 3NF can be achieved without losing any information from the old table. For obtaining BCNF we may lose some information from old table.

Last Updated : 17 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads