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. |
Difference between 3NF and BCNF in DBMS
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. In this article, we will see the differences between 3NF and BCNF.