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

 Posted by ArticlesMaint on 4/2/2011 | Category: Sql Server Interview questions | Views: 7987


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,




Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response