SQL Server Interview Question – What is Normalization and its different forms?

Answer: 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 haikh 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,

Please click here to see more SQL Server interview questions

Advertisements

About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview. Do not forget to watch our Learn step by step video series. Learn MVC in 16 hours:- https://www.youtube.com/watch?v=Lp7nSImO5vk Learn AngularJS Step by Step:- https://www.youtube.com/watch?v=0kmdjqgO9IY Learn Design Pattern in 8 hours:- https://www.youtube.com/watch?v=YDobmucohqk Learn C# and .NET in 60 days:- https://www.youtube.com/watch?v=yh2SrzCkNQA Learn MSBI in 32 hours:- https://www.youtube.com/watch?v=mGPJx3ocFgg Learn SharePoint Step by Step in 8 hours:- https://youtu.be/C2fW76SwJNU
This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s