Tuesday, April 5, 2011

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:


Applying Normalization on this table.

1NF:After applying 1NF the table look like



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

CountryTable

ProductTable

For avoiding duplication Create a new master table of Country and Product.
3NF:After applying 3NF the table look like

A non key field Total is removed from the table.
Regards,
Please click here to see more SQL Server interview questions

1 comment:

Kamikazis said...

Thanks for the Wonderful Article. Just a small suggestion.

Could you please change the B-Tree Diagram in the .NET and SQL Server Interview Question - How does index makes search faster?

The first leaf node value is from 1 to 10 but in the Diagram it's 11 to 20 and it holds the physical data : 1 to 10 :).