What is normalization & what are the types of normalization?
Normalization is a set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is termed as Normalization.
The advantages of normalizing your database will include following points:
- Avoiding of the repetitive entries
- Reducing the required storage space
- Preventing the need to restructure existing tables to accommodate the new data.
- To increased the flexibility of queries, speed and sorts, and summaries.
The three normal forms are illustrated below:-
1)First Normal Form :
For a table to be in the first normal form, the data must be broken up into the smallest possible units .In addition to breaking the data into the smallest meaningful values, the tables in first normal form should not contain any repetitions groups of fields.
Figure :- Repeating groups example
For this in the above example city1 & city2 are repeating.In order to show this table to be in First normal form you have to change the table structure as follows.Also note that the Customer Name is now broken down to first name and the last name (First normal form data must be broken down to smallest unit).
2)Second Normal form:
The second normal form states that every field in a multiple field, primary keytable should be directly related to the whole primary key. Or in the other words, each non-key field must be a fact of about all the fields in the primary key. In the table above of customer, the city is not linked to any primary field.
Figure:- Normalized customer table.
Figure :- City is now shifted to a different master table.
3)Third normal form:
The non-key field should not depend on other Non-key field. The field "Total" is dependent on the "Unit price" and the "qty".
Figure :- Fill third normal form