What is Normalization and what are three normal forms with Examples

Normalization:-Normalization is a mechanism of decomposing tables to remove redundancy of data and removal of other characteristics like insertion, deletion and update anomalies.

To understand Insertion, deletion and update anomalies let’s take an example of “EmpDetail” table.

Eid	Name	Visa Type	Address
101	Vikas	H1	Faridabad
101	Vikas	B1	Faridabad
102	Ramesh	Null	Amritsar
103	Prakash	L1	Raipur

In the above table you can see that we have employee details along with their Visa details. Employee Ramesh has no Visa detail as Visa Type column has null value in it.

Insertion Anomalies: - If we want to insert an employee details which has no Visa that leads insertion anomalies as Visa Type value would be null.

Deletion Anomalies: - If employee Prakash’s Visa got expired and we want to delete that Visa details for Prakash, whole data will be deleted that leads deletion anomalies.

Update Anomalies:- If we want to update data for Eid =101 , we need to make sure we are updating both rows otherwise data would be inconsistent.

1^st Normal Form: - First normal form says that there should not be any repeating group in a row and First Normal form also advocates about the uniqueness of each row in a table i.e. primary key should be there in each table.

Lets take an example to understand 1^st Normal form.

Eid	Name	Address1	Address2
101	Vikas	Faridabad	Gurgaon
102	Ramesh	Jaipur	Null
103	Prakash	Noida	Null

In above Emloyee table, we have repeating group i.e. Address1 and Address2 which is violating first normal form. To be in first normal form we can split employee table in two tables as mentioned below.

  

Employee			AddressDetail
Eid	Name		Eid	Address
101	Vikas		101	Faridabad
102	Ramesh		101	Gurgaon
103	Prakash		102	Jaipur

We have decomposed employee table in two tables, Employee and AddressDetail, in employee table Eid is primary key and in AddressDetail table Eid and Address both can be the part of primary key . If we want to get the employee detail and address detail we have to join the both tables using joins.

2^nd Normal Form:- For a table to be in 2^nd Normal form it should follow the 1^st Normal form and each Non Key columns should be fully dependent on the primary key or in other words there should not be any partial dependency. To understand this, let’s take an example.

Eid	ProjectID	ProjectName	EmpName
101	P01	Syntal	Vikas
102	P02	Cris	Priya
101	P03	Vintex	Vikas
103	P04	Alex	Rahul
104	P05	Dwan	Shivam

The above table has mapping of Project and employee and combination of projectID and Eid is the primary key of the table. Projectname is dependent on Project ID not the complete primary key similarly, EmpName is directly dependent on Eid not the whole primary key hence this is the violation of 2^nd Normal form. To be in 2^nd Normal form we need to split this table in two tables as shown below.

EmpDetail			ProjectDetail
Eid	EmpName		ProjectID	ProjectName
101	Vikas		P01	Syntal
102	Priya		P02	Cris
101	Vikas		P03	Vintex
103	Rahul		P04	Alex
104	Shivam		P05	Dwan

I have split the table in two tables, one is EmpDetail and other one is ProjectDetail where in Eid is the primary key of Empdetail and ProjectID is the primary key for ProjectDetail table. And non key attributes are fully dependent in each table, now we can say that tables are in 2^nd Normal form.

3rd Normal Form:- To be in 3rd Normal form table should follow first two normal forms apart from that there should not be any transitive relationship in columns. Or in other words each column should directly dependent on primary key. E.g. if there are three columns in a table (A,B,C) and A is the primary key of the table and B is dependent on A and C is dependent on B.

AßBßC

Lets take an example to understand this fact.

EmpDetail
Eid	Basic_Sal	HRA(50% of Basic)
101	4000	2000
102	6000	3000
103	5000	2500
104	9000	4500
105	4600	2300

In the above table Eid is the primary key and Basic Salary is non key column which is dependent on Primary key and HRA is also a non key column which is not directly dependent on Primary key(Eid) rather it is dependent on other non key column Basic_Sal ,as HRA value is 50% of Basic_Sal. In this way this table violating 3rd normal form. To be in 3rd Normal form we will spilt above table in two tables as shown below.

BasicSalaryDetail			HRADetail
Eid	Basic_Sal		Eid	HRA
101	4000		101	4000
102	6000		102	6000
103	5000		103	5000
104	9000		104	9000
105	4600		105	4600

We have split the above table in two tables, BasicSalaryDetail and HRADetail, now these two tables are following all three normal forms. Lets see how.

• Both tables do not contain any repeating group and each row in both table can be uniquely identified by primary key(Eid) , in that way 1st normal form’s condition is satisfied.
• In both tables non key columns (Basic_Sal and HRA) fully dependent on Primary key in that way 2nd normal form’s condition is satisfied.
• We have already removed the transitive relationship, hence both tables are in 3rd normal form. 

 I hope you will like this article. Please feel free to share your thoughts on this article.