It is set of rules that has been established to aid in the design of tables that are meant to
be connected through relationships. This set of rules is known as Normalization.
Benefits of Normalizing your database include:
√ Avoiding repetitive entries
√ Reducing required storage space
√ Preventing the need to restructure existing tables to accommodate new data.
√ Increased speed and flexibility of queries, sorts, and summaries.
Note :- During interview people expect to answer maximum of three normal forms and
thats what is expected practically.Actually you can normalize database to fifth normal
form.But believe this book answering three normal forms will put you in decent shape during
interview.
Following are the three normal forms :-
First Normal Form
For a table to be in first normal form, data must be broken up into the smallest units
possible.In addition to breaking data up into the smallest meaningful values, tables in
first normal form should not contain repetitions groups of fields.
10. SQL SERVER194
For in the above example city1 and city2 are repeating.In order this table to be in First
normal form you have to modify the table structure as follows.Also not that the Customer
Name is now broken down to first name and last name (First normal form data should be
broken down to smallest unit).
Second Normal form
The second normal form states that each field in a multiple field primary keytable must
be directly related to the entire primary key. Or in other words,each non-key field should
be a fact about all the fields in the primary key.
In the above table of customer , city is not linked to any primary field.
is now shifted to a different master table.
That takes our database to a second normal form.
Third normal form
A non-key field should not depend on other Non-key field.The field "Total" is dependent
on "Unit price" and "qty".
So now the "Total" field is removed and is multiplication of Unit price * Qty