Difference between 3NF and BCNF in DBMS

Normal form refers to a set of rules used to reduce data redundancy and improve data integrity in relational database design. These rules are applied through a process called normalization. A relation is in the third normal form, if there is no transitive dependency for non-prime attributes as well as it is in the second normal form. The Boyce-Codd Normal Form (BCNF) is based on functional dependencies, BCNF has additional constraints when compared to the basic definition of 3NF.

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 A→B:

A should be a super key
B should be a prime attribute i.e., B should be a part of the candidate key.

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

Example of 3NF

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. 

Advantages of 3NF

• Removes redundancy caused by transitive dependencies.
• Preserves dependencies well (most 3NF decompositions are dependency preserving).
• Easier to implement and maintain in real-world databases.
• Maintains good query performance with fewer joins compared to BCNF.

Disadvantages of 3NF

• Does not eliminate all anomalies.
• Potential Performance Issues
• Some redundancy may still remain.

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. It is a stricter version of Third Normal Form (3NF) that ensures a more simplified and efficient database design. It enforces that every non-trivial functional dependency must have a super key on its left-hand side. For to be in BCNF the given rules must be followed:

Rule 1: The table should be in the 3rd Normal Form.

Rule 2: X should be a super-key for every functional dependency (FD) X−>Y in a given relation. 

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 of BCNF

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. 

So, BCNF is stricter than 3NF. Every BCNF relation is in 3NF, but not every 3NF relation is in BCNF.

Advantages of BCNF

• Stronger normalization, eliminates all redundancy based on functional dependencies.
• Removes all update, insertion, and deletion anomalies.
• Leads to a better database schema.

Disadvantages of BCNF

• Not always dependency-preserving some functional dependencies might be lost in the decomposition.
• May result in more complex queries with additional joins, which can affect performance.
• Slightly harder to implement and maintain in some real world systems.

NOTE: Every BCNF relation is also in 3NF, but every 3NF relation is not in BCNF.

Difference Between 3NF and BCNF