Do you think IIT Guwahati certified course can help you in your career?
Introduction
In the database management systems, there can be some abnormalities in the data. Sometimes the data can be redundant, which causes anomalies in our database. Abnormalities mean we will face problems in performing insertion, deletion, and data updation into the database. The process of removing this inconsistency in data from the database is called Normalization.
Some guidelines and rules are followed to avoid the inclusion of redundant data. These rules ensure that the database is free of any inconsistency and the data is organized properly. These rules are known as Normal Forms.
And in this article, we will discuss two such normal forms, namely 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form), and the difference between 3NF and BCNF.
What is Normalization?
Before discussing the difference between 3nf and bcnf, we should discuss what normalization is. So normalization is the process by which we remove any redundancy in our data. Redundancy in data causes certain inconsistencies and anomalies, which makes it difficult to perform operations on data.
Normalization is done by making the relationship between the data follow specific rules, and these rules are known as normal forms. 3nf and bcnf are also such kind of normal forms that helps us to perform normalization.
The third Normal Form is a stricter variation over the 2NF (second Normal Form). In 2NF, we remove redundant data by ensuring that all the non-primary attribute in a table is directly dependent on the primary key, i.e., there was no partial dependency. A relation in the database is said to be in 3NF if the following conditions are satisfied by it-
It is in 2NF form.
The non-prime attribute should not be transitively dependent on the candidate key.
Transitive Dependency-
In a relational database, when a non-prime attribute is directly dependent on any other non-prime attribute, then this dependency is termed a transitive dependency.
We can represent it as -
If: A → B and B → C
Then: A → C
In the first relation, A is the determinant, and B is dependent, and in the second relation, B is the determinant, and C is dependent. This makes C indirectly dependent on A. Thus the above relation has a transitive dependency.
Example-
There is a Relation R(A, B, C, D, E) such that the following functional dependencies are present:
{ AB → C, AB → D, D→ E }
Check if it is in 3NF or not.
Answer: No, it is not in 3NF.
Explanation::
Closure of A: {A}
Closure of B: {B}
Closure of C: {C}
Closure of D: {D, E}
Closure of E: {E}
Closure of AB= {A, B, C, D, E}
Since all the elements can be determined by { AB }, the attribute is the Candidate key of the table, and A and B are the prime attributes. The rest of the elements are non-prime attributes.
The relations are in 2NF, as no non-prime attribute is partially dependent on prime attributes.{ A→X and B→X; X is a non-prime attribute type of relations are not present }
But there is a transitive dependency as
AB→ D and D→E, through which E is indirectly dependent on the candidate key.
Therefore the above relations are not 3NF.
Boyce-Code Normal Form (BCNF)
BCNF is a stricter extension of the 3NF Normalization. It removes almost all redundant dependencies from our relations. The difference between 3nf and bcnf is that it is considered to be stronger than the 3NF. It is also referred to as the 3.5 Normal Form. A relation is in BCNF if-
It is already in 3NF
A must be a super key for any given dependency with attributes A and B, A → B
Example-
There is a Relation R(A, B, C) such that the following functional dependencies are present:
{ AB → C, AB → B, C → B}
Check if it is in BCNF or not.
Answer: No, it is in BCNF.
Explanation:
Closure of A: {A}
Closure of B: {B}
Closure of C: {B, C}
Closure of AB: {A, B, C}
Since all the elements can be determined by { AB }, the attribute is the Candidate key of the table, and A and B are the prime attributes. C is the non-prime attribute.
The relations are in 2NF, as no non-prime attribute is partially dependent on prime attributes.{ A→X and B→X; where X is a non-prime attribute type of relations are absent. }
The relation is also in 3NF since C and AB have no transitive dependency.
The relation is not in BCNF because for the functional dependency C → B, C is not a super key, violating the BCNF rules.
Comparison Table between 3NF and BCNF
Let's summarize the difference between 3nf and bcnf -
3NF
BCNF
It stands for Third Normal Form.
It stands for Boyce Code Normal Form,
All the relations which are in 3NF may or may not be in BCNF.
All the relations that are in BNCF must be in 3NF.
It is weaker than BCNF.
It is stronger than 3NF.
The amount of redundant data in 3NF is more than that in BCNF
In comparison to 3NF, the redundancy is very less in BCNF.
There should be no transitive dependency in 3NF. It means that no non-prime attribute should be transitively dependent on the candidate key.
In BCNF, for any given relation C → D, C should be a super key.
3NF can be achieved without losing the dependency that is already there.
The functional dependency may get lost while achieving BCNF form.
3NF Normalization can be achieved without losing any information from the original table. Hence we can do lossless decomposition.
In BCNF, we can lose some information from the original table. Hence, the decomposition might not be lossless.
For a relation to be in third normal form following conditions must be satisfied. Firstly, it should be in the second normal Form, and secondly, there should not be any transitional dependencies in the dependencies.
What is a transitive dependency?
When a non-prime attribute ( an attribute not part of a candidate key) is dependent on another non-prime attribute, this dependency is called a transitive dependency. We can represent the transitive dependency as If X → A and A → Z, then X → Z.
What are the conditions for BCNF?
For a table to be in Boyce Code Normal form following conditions must be satisfied. Firstly, it should be in the third normal Form, and secondly, for every dependency in table A → C, A should be a super key.
What is the difference between 3NF and BCNF?
The difference between 3NF and BCNF is that BCNF is a stricter version of 3NF, and all the relations that follow the rules of BCNF will be 3NF also, but not vice versa. In 3NF, the transitive dependency should not be present, while in BCNF, for any relation, C → D, C should be a super key of the relation.
Conclusion
Congrats, Ninja!! You've learned about normalization and 3NF and BCNF and what is the difference between 3NF and BCNF. You also learned about what kind of redundancies are removed from our data by applying these normalization techniques.
Thanks for reading. I hope you found the blog insightful and that you have understood the difference between 3nf and bcnf, and please upvote if you liked the article.
569+ registered 
