Database Normalization Cheat Sheet

Photo by İsmail Enes Ayhan on Unsplash

Database normalization removes redundancy and ensures that data is logically stored. It removes insertion, deletion, and updation anomalies. Here I’ll briefly explain the 5 normalization rules.

1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. BCNF: Boyce and Codd Normal Form
5. Fourth Normal Form

First Normal Form

A table is supposed to be in first normal form if,

• All the attributes are single-valued (atomic).
• All the columns have unique names.
• The order in which data is stored does not matter.

Example: In Shirt_Info table, Size attribute is not atomic. Hence it can be decomposed into Design_Info and Size_Info as shown in the image.

First Normal Form Example

Second Normal Form

A table is supposed to be in second normal form if,

• It is in the 1st normal form.
• It does not have any partial dependency

Example: In Customer_Info table, Store_Name depends on Store_ID and not on Cust_ID. This is a partial dependency. Hence, Customer_Info is not in second normal form (though it satisfies 1NF). It can be decomposed into Customer_Data and Store_Data as shown below.

Second Normal Form Example

Third Normal Form

A table is supposed to be in third normal form if,

• It satisfies 2nd normal form.
• It does not have any transitive dependency.

Example: Movie_Info is in second normal form but it has a transitive dependency. Therefore, it is not in third normal form. It can be decomposed into Movie_Rating and Genre_Info as shown below.

Third Normal Form Example

Boyce and Codd Normal Form (BCNF)

A table is supposed to be in BCNF if,

• It is in 3rd Normal Form.
• For every dependency X->Y, X cannot be a non-prime attribute if Y is prime attribute (i.e. X should be a super key)

Example: Student_Info is not in BCNF because in the dependency Faculty -> Subject, Subject is prime attribute and Faculty is non-prime attribute. It can be decomposed into Student_Faculty_Info and Faculty_Info as shown below.

Also read about attribute closure, testing for BCNF and BCNF Decomposition Algorithm.

BCNF Example

Fourth Normal Form

A table is supposed to be in fourth normal form if,

• It is in BCNF.
• It has no multi-valued dependency.

Multi-valued Dependency: For a dependency A → B, if for a single value of A, multiple values of B exists, then the relation will be a multi-valued dependency.

Fourth Normal Form Example

Hope this helps. Let me know your feedback/suggestions in the comments section below.