Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
Normalization of a data model consists of several steps. These steps are called normalization rules. Each rule is referred to as a normal form.
Both 3NF and BCNF are normalization techniques that aim to reduce data redundancy and maintain data integrity, BCNF is a more rigorous and advanced form of normalization that focuses primarily on eliminating partial dependencies, whereas 3NF also addresses transitive dependencies. BCNF ensures that every non-trivial functional dependency has a superkey as its determinant.
Database Normal Forms include:
- 1NF (First Normal Form)
- 2NF (Second Normal Form)
- 3NF (Third Normal Form)
- BCNF (Boyce-Codd Normal Form)
- 4NF (Fourth Normal Form)
- 5NF (Fifth Normal Form)
- 6NF (Sixth Normal Form)
The first three forms are the most important ones. There are more than 3 normal forms but those forms are rarely used and can be ignored without resulting in a non flexible data model. Each normal form constrains the data more than the previous normal form. This means that you must first achieve the first normal form (1NF) in order to be able to achieve the second normal form (2NF). You must achieve the second normal form before you can achieve the third normal form (3NF).
Let us talk more about 3NF and BCNF.
What is 3NF?
Third Normal Form (3NF) is a level of database normalization used in relational database design to reduce data redundancy and improve data integrity. It builds upon the concepts of First Normal Form (1NF) and Second Normal Form (2NF) and introduces additional rules to ensure that data is stored in a structured and efficient manner.
To achieve 3NF, a relation (table) in a database must satisfy the following conditions:
- It must already be in 2NF, which means that it is free from partial dependencies. In other words, every non-prime attribute (attributes that are not part of the candidate key) should be fully functionally dependent on the entire candidate key.
- It must not have any transitive dependencies. This means that if attribute A determines attribute B and attribute B determines attribute C, then there should be no indirect dependency from A to C. In other words, A should not determine C through B.
- All non-prime attributes (attributes not part of the candidate key) should be functionally dependent on the candidate key(s) and only on the candidate key(s). There should be no partial or transitive dependencies involving non-prime attributes.
What is BCNF?
Boyce-Codd Normal Form (BCNF) is a higher level of normalization in the field of database design. It is an advanced and more stringent form of normalization compared to Third Normal Form (3NF). BCNF aims to eliminate certain types of anomalies and data redundancies by imposing stricter rules on the structure of relational databases.
To achieve BCNF, a relation (table) in a database must satisfy the following conditions:
- It must be in First Normal Form (1NF), which means that it should have atomic (indivisible) values in each cell of the table.
- It must be in Second Normal Form (2NF), ensuring that there are no partial dependencies. In other words, all non-prime attributes (attributes that are not part of the candidate key) should be fully functionally dependent on the entire candidate key.
- It must satisfy an additional condition: for every non-trivial functional dependency X -> Y, where X is a superkey (a set of attributes that uniquely identifies a tuple), X should be a candidate key.
3NF vs BCNF: Key Differences
| Basis | 3NF | BCNF |
|---|---|---|
| Definition | A relation is in 3NF if it is in 2NF and does not have any transitive dependencies. | A relation is in BCNF if, for every non-trivial functional dependency X -> Y, X is a superkey. |
| Dependency Types | Deals with partial and transitive dependencies. | Primarily focuses on eliminating partial dependencies. |
| Superkeys | A relation can have partial dependencies even if it has superkeys that contain the candidate key. | A relation is in BCNF if and only if every non-trivial functional dependency X -> Y, X is a superkey. BCNF deals with superkey-based determinants. |
| Decomposition | May require the decomposition of a relation into multiple relations to eliminate transitive dependencies. | May require the decomposition of a relation into multiple relations to eliminate partial dependencies. |
| Lossless-Join Decomposition | Decomposition into 3NF may or may not guarantee lossless-join. | Decomposition into BCNF always guarantees lossless-join. |
| Candidate Keys | Does not directly consider candidate keys. | Directly involves candidate keys in determining the normal form. |
| Focus | Focuses on eliminating redundancy caused by transitive dependencies. | Focuses on eliminating redundancy caused by partial dependencies. |
| Dependency Preservation | May not always preserve all functional dependencies. | Preserves all functional dependencies. |
| Redundancy | Eliminates some forms of redundancy but may not eliminate all. | Eliminates most forms of redundancy related to functional dependencies. |
| Dependency Closure | Doesn’t necessarily involve computing the closure of attributes. | Often involves computing the closure of attributes to identify superkeys. |
| Example | If a relation has a transitive dependency like A -> B and B -> C, it might not be in 3NF. | If a relation has partial dependencies like A -> B and A -> C, it might not be in BCNF. |
| Additional Normal Forms | An earlier stage in the normalization process. | A more stringent form of normalization, achieving BCNF usually implies achieving 3NF as well. |