Normalization in DBMS: 1NF, 2NF and 3NF in Database

Normalization is a process of organizing the data in database to avoid anomalies of inserting, updating and deleting. After this method, a large table becomes small tables and defines relationships between them to identify the organizing data clearly.

Normalization was developed by IBM researcher E.F.Codd in the 1970 s.

Example:

Emp_ID	Emp Name	Emp_Add	DOB	Age	Emp_Dep
101	Anne	London	1991.02.21	25	D001
101	Anne	London	1991.02.21	25	D002
123	Jack	Paris	1993.06.30	23	D890
166	Ola	Rio	1992.05.06	24	D900
166	Ola	Rio	1992.05.06	24	D004

We have to face these problems because the above table is not normalized.

Update anomaly – There are two rows for employee Anne because she works in two departments in the company. If we want to change her address, we have to update two rows. Although we change only one address; there is an in-apposite data in the database. Anne has two different addresses and they make an incorrect database.

Insert anomaly – A new employee joins to company who is still training and not belongs to any department. Although we want to add his name into database, we cannot insert a row into the database as the Emp_Depart field without data.

Delete anomaly – If we want to close D890, we have to delete that row. If we delete that row, the details of Jack are removing from database because he belongs to only one department.

 To solve these problems, we use normalization.  

First Normal Form

• There are no duplicate fields.
• Each cell contains only one value.
• Each row is unique.
• There is a primary key.

Example:

In first table, Emp_ID and Emp_Dep act as primary key, no duplicate rows and no multiple items.

Emp_ID	Emp_ Name	Emp_ Add	DOB	Age	Emp_Dep
101	Anne	London	1991.02.21	25	D001
101	Anne	London	1991.02.21	25	D002
123	Jack	Paris	1993.06.30	23	D890
166	Ola	Rio	1992.05.06	24	D900
166	Ola	Rio	1992.05.06	24	D004

   

Second Normal Form

• The table must be in 1NF.
• All non-key fields must depend on all components of the primary key.

Example:

                                 

                                      Employee Details Table

Emp_ID	Emp Name	DOB	Age	Emp_Add
101	Anne	1991.02.21	25	London
123	Jack	1993.06.30	23	Paris
166	Ola	1992.05.06	24	Rio

Emp_ID	Emp_Dep
101	D001
101	D002
123	D890
166	D900
166	D004

  

   Department Table

      

In 2NF, we split the table (1NF) into two tables which are Department table and Employee details table.

Third Normal Form

• The table must be in 2NF.
• Non – key field doesn’t depend upon another.
• All non- key fields depend only on the primary key.

Example:

Emp_ID	Emp Name	DOB	Emp_Add
101	Anne	1991.02.21	London
123	Jack	1993.06.30	Paris
166	Ola	1992.05.06	Rio

  Employee
  Details                     Table

Emp_ID	Emp_Dep
101	D001
101	D002
123	D890
166	D900
166	D004

      Department Table

                 Age Table

DOB	Age
1991.02.21	25
1993.06.30	23
1992.05.06	24

     

In 3NF, we split Employee details table into two tables which are Employee details and Age table.