Difference Between BCNF and 4NF (4th Normalization)

BCNF vs 4NF (4th Normalization)
 

Database normalization is a technique, which is dealing with relational database management systems. Data errors can be avoided in a well normalized database. Normalization is used to reduce the data redundancy of the database. That means implementing database tables and their relationships, eliminating redundancy and inconsistent dependency. There are some predefined rules set for normalization. Those rules are called normal forms.

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF or 3.5NF)
5. Fourth Normal Form (4NF)

First Normal Form is referred to as the atomicity of a table. Table atomicity can be reached from two steps.

1. Removing duplicate columns from the same table.
2. Creating separate tables for related duplicated columns. ( there must be primary keys to identify each row of this tables)

In the Second normal form, the attempt is to reduce the redundant data in a table by extracting them and placing them in a separate table. This can be achieved by doing the following steps.

1. Select the data set, which applies to multiple rows, and place them in separate tables.
2. Create the relationships between these new tables and parent tables using foreign keys.

To take database to the Third normal form, already the database must be achieved at first and second normal forms. When the database is in 1NF and 2NF, there are no any duplicate columns and no any subsets of data that apply to multiple rows. Third normal form can be achieved by removing the columns of the tables, which are not fully, depend upon the primary key.

Boyce-Codd Normal Form (BCNF or 3.5NF)

BCNF stands for “Boyce-Codd Normal Form”. This normal form also known as the 3.5 Normal form of database normalization. To achieve BCNF, the database must be already achieved to third normal form. Then following steps should be done to achieve the BCNF.

1. Identify all candidate keys in the relations
2. Identify all functional dependencies in the relations.
3. If there are functional dependencies in the relation, where their determinants are not candidate keys for the relation, remove the functional dependencies by placing them in a new relation along with a copy of their determinant.

Fourth Normal Form

Database must be in third normal form, before normalizing it to the fourth normal form. If the database is already in third normal form, next step should be to remove the multi-valued dependencies. (If one or more rows imply the presence of one or more other rows in the same table, it is called multi-valued dependency.)