3NF vs BCNF
Normalization is a process that is carried out to minimize the redundancies that are present in data in relational databases. This process will mainly divide large tables in to smaller tables with fewer redundancies. These smaller tables will be related to each other through well defined relationships. In a well normalized database, any alteration or modification in data will require modifying only a single table. Third Normal Form (3NF) was introduced in 1971 by Edgar F. Codd, who is also the inventor of the relational model and the concept of normalization. Boyce-Codd Normal Form (BCNF) was introduced in 1974 by Codd and Raymond F. Boyce.
What is 3NF?
3NF is the Third normal form used in relational database normalization. According to the Codd’s definition, a table is said to be in 3NF, if and only if, that table is in the second normal form (2NF), and every attribute in the table that do not belong to a candidate key should directly depend on every candidate key of that table. In 1982 Carlo Zaniolo produced a differently expressed definition for 3NF. Tables that comply with the 3NF generally do not contain anomalies that occur when inserting, deleting or updating records in the table.
What is BCNF?
BCNF (also known as 3.5NF) is another normal form used in relational database normalization. It was introduced to capture some the anomalies that are not addressed by the 3NF. A table is said to be in BCNF, if and only if, for each of the dependencies of the form A → B that are non-trivial, A is a super-key. Decomposing a table that is not in the BCNF normal form does not guarantee the production of tables in the BCNF form (while preserving the dependencies which were present in the original table).
What is the difference between 3NF and BCNF?
Both 3NF and BCNF are normal forms that are used in relational databases to minimize redundancies in tables. In a table that is in the BCNF normal form, for every non-trivial functional dependency of the form A → B, A is a super-key whereas, a table that complies with 3NF should be in the 2NF, and every non-prime attribute should directly depend on every candidate key of that table. BCNF is considered as a stronger normal form than the 3NF and it was developed to capture some of the anomalies that could not be captured by 3NF. Obtaining a table that complies with the BCNF form will require decomposing a table that is in the 3NF. This decomposition will result in additional join operations (or Cartesian products) when executing queries. This will increase the computational time. On the other hand, the tables that comply with BCNF would have fewer redundancies than tables that only comply with 3NF. Furthermore, most of the time, it is possible to obtain a table that comply with 3NF without hindering dependency preservation and lossless joining. But this is not always possible with BCNF.