Normalization in SQL

1 NF (First Normalization Form):

·         Elimination of duplicate column from the same table

·         Create separate tables for each group or related data and identify each row with unique column (primary key)

·         Ex:

Employee – Without Normalization

employeeId	employeeName	City
101	Ved pathak	Mumbai
102	Rahul	Pune

1 NF

Employee

employeeId	employeeName	CityId
101	Ved pathak	1
102	Rahul	2

City

CityId	City
1	Mumbai
2	Pune

2 NF (Second Normalization Form):

·         Remove subset of data that apply to multiple rows of a table and place them in a separate tables

·         Create relationship between these new tables and their procedures through the use of foreign key

Employee – Without Normalization

employeeId	employeeName	City	State
101	Ved pathak	Mumbai	Maharashtra
102	Rahul	Pune	Maharashtra

2 NF

Employee

employeeId	employeeName	CityId
101	Ved pathak	1
102	Rahul	2

City

CityId	StateId	City
1	1	Mumbai
2	1	Pune

State

StateId	State
1	Maharashtra
1	Maharashtra

3 NF (Third Normalization Form):

·         The 3 NF form should follow the above 1NF and 2 NF conditions

·         Remove column that are not fully dependent upon primary key

Employee – Without Normalization

employeeId	employeeName	City	State	Zip
101	Ved pathak	Mumbai	Maharashtra	111111
102	Rahul	Pune	Maharashtra	222222

3 NF

Employee

employeeId	employeeName	ZipCode
101	Ved pathak	1
102	Rahul	2

Zip

ZipCode	City	State	Zip
101	Mumbai	Maharashtra	111111
102	Pune	Maharashtra	222222