SQL Server Interview Quetion - What is Normalization and its different forms?

Normalization is the process of organizing the table's data in proper manner.
In other words Normalization is the process of breaking up data into a logical non-repetitive format that can be easily reassembled as a whole.

Normalization have 3 different forms namely 1Normal Form,2Normal Form,3Normal Form.

1Normal Form:
A table is said to be in 1NF if it satisfies the following rules.
 • The table must not contain any redundant groups of data
 • data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values.

2Normal Form:
 A table is said to be in 2NF if it satisfies the following rules.
 • The table must be in 1NF
 • All the non-key column must depend on primary key.

3Normal Form:
A table is said to be in 3NF if it satisfies the following rules.
 • The table must be in 2NF
 • A non-key field should not depend on another Non-key field.
 • The data should not be derived further.

For ex:
Below table is in denormalize format:

CustomerName	Region	Product	Quantity	PerProduct	Total
Shivprasad Bist	India,Mumbai	Shirt	2	10	20
Raju Bist	India,Gujrat	Pant	2	30	60
Moosa Shaikh	India,Chennai	Pant	4	15	60
Feroz Shaikh	India,Gujrat	Shirt	2	10	20

Applying Normalization on this table.
1NF:After applying 1NF the table look like

CustomerFirstName	CustomerLastName	Country	State	Product	Quantity	PerProduct	Total
Shivprasad	Bist	India	Mumbai	Shirt	2	10	20
Raju	Bist	India	Gujrat	Pant	2	30	60
Moosa	Shaikh	India	Chennai	Pant	4	15	60
Feroz	Shaikh	India	Gujrat	Shirt	2	10	20

The customer Name is divided into two units like Customer FirstName and Customer LastName.
Region field is also divided into two units like Country and State.

2NF:After applying 2NF the table look like

CustomerFirstName	CustomerLastName	Country	State	Product	Quantity	PerProduct	Total
Shivprasad	Bist	1	1	2	2	10	20
Raju	Bist	1	2	1	2	30	60
Moosa	Shaikh	1	3	1	4	15	60
Feroz	Shaikh	1	2	2	2	10	20

CountryTable

CountryId	CountryName	StateId	StateName
1	India	1	Mumbai
		2	Gujrat
		3	Chennai

ProductTable

ProductId	ProductName
1	Pant
2	Shirt

For avoiding duplication Create a new master table of Country and Product.

3NF:After applying 3NF the table look like 

CustomerFirstName	CustomerLastName	Country	State	Product	Quantity	PerProduct
Shivprasad	Bist	1	1	2	2	10
Raju	Bist	1	2	1	2	30
Moosa	Shaikh	1	3	1	4	15
Feroz	Shaikh	1	2	2	2	10

A non key field Total is removed from the table. 

Regards,