Normalization Forms: 1NF, 2NF, 3NF, and BCNF

Normalization is a process used in database design to eliminate redundancy and avoid data anomalies (insertion, deletion, update). It breaks large, repetitive tables into smaller, more meaningful relations by using normal forms (NF).

Let’s understand each normal form step-by-step:

First Normal Form (1NF): Eliminate Repeating Groups

Rule:

A relation is in 1NF if:

• All attributes have atomic (indivisible) values.
• There are no repeating groups or arrays.

In the above table, Courses has a multi-valued attribute, so it is not in 1NF. To make the table in 1NF we have to remove the multivalued attributes from the table as given below:

Now the table is in 1NF as there is no multi-valued attribute present in the table.

Second Normal Form (2NF): Eliminate Partial Dependency

Rule:

A relation is in 2NF if:

• It is already in 1NF.
• All non-key attributes are fully functionally dependent on the entire primary key, not just a part of it.

Example:

This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is [Purchase Location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form.

To bring this table to second normal form, we break the table into two tables, and now we have the following:

What we have done is to remove the partial functional dependency that we initially had. Now, in the table [TABLE_STORE], the column [Purchase Location] is fully dependent on the primary key of that table, which is [Store ID].


Third Normal Form (3NF): Eliminate Transitive Dependency

Rule:

A relation is in 3NF if:

• It is already in 2NF.
• There is no transitive dependency: no non-key attribute depends on another non-key attribute.

Example:

Table: Department

Suppose for each department there can be only one manager, thus non-key columns Department_name and Department_Manager_Id depends on Department_Id (Primary key). But Department_Manager_Name depends upon Department_Manager_Id which further depends upon Department_Id, thus there is transitive dependency in this table. To remove transitive dependency we need to redesign the tables as given below:

Table: Department

Table: DepartmentManager

Now the table is in third normal form.

Boyce-Codd Normal Form (BCNF): Stronger Version of 3NF

Rule:

A relation is in BCNF if:

• It is in 3NF.
• For every functional dependency X → Y, X must be a super key.

Summary Table: