Database Normalization Steps From 1NF to 3NF

The goal of a relational database design is to generate a set of relation scheme that allow us to store informastion easily. One approach is to design scheme that are in an appropriate normal form (silberschatz, H., 1991)
We have to normalize the database in order to make it easier to maintain, develop, or to resolve the error.
It will be several steps to do, but usually it just only need till  the third step.

Overview

1  1st Normal Form / 1NF

2. 2nd Normal Form / 2NF

3. 3rd Normal Form / 3NF

4. Boyce-Code Normal Form (BCNF)

5. 4th Normal Form / 4NF

6. 5th Normal Form / 5NF

First Normal Form (1NF)

A table meets 1st Normal form if it doesn’t have multivalued attribute, composite attribute or its combination in the same data domain.

Each attribute in that table should have an atomic value (can be divided).

The example below doesn’t meet the 1NF

It can be normalized into 1NF as described below

Steps to transform unnormalized to 1NF:

1. Choose one attribute or a group of attribute to be the key in  the table
2. Identify redundant groups in the unnormalized table
3. Delete the redundant groups

Second Normal Form (2NF)

1. 1.     A table meets 2NF when the 1NF requirement is met , and all attributes except the primary key have functional dependency entirely  to the primary key.
   2.     A table doesn’t meet 2NF, if there is an attribute that its functional dependency just partial. Partially dependent on primary key.
   3.     If there is an attribute that doesn’t have a dependency to the primary key, then the attribute should be moved or deleted.

For example :

This table meets the 1NF but doesn’t meet 2NF because lesson_name doesn’t fully dependent to lesson_id as the primary key.

It should be decomposed into 2 tables,

Steps to transform 1NF to 2NF

1. Identify primary key to the 1NF relationship (based on the example above, the primary key is lesson_id) 
2. Identify functional dependencies in the relationship (the FD is lesson_id -> lesson_name) 
3. If there is partial dependencies to the primary key, delete and place it into new relationship with the copy of its determinan (lesson_name is deleted from the table student and move to the new table) 

3rd Normal Form (3NF)

When it has met the 2NF, and there is no non primary key attribute that dependent to the other non primary key, the table is met 3NF.

The table below accomplish the 2NF but not 3NF

Steps to transform 2NF to 3NF:

1. Identify primary key in the 2NF relationship
2. Identify functional dependencies in the relationship
3. If there is a transitive dependency to the primary key, delete and place it into new relationship with the copy of its determinan.

It should be decomposed into 2 tables

1. student (student_number, nae, address, postal_code)

2. postal_code (code, province, city)

With the 3NF, our table structure is good enough, remember that too many table join will slow the query.