Normalization is a design technique that is widely used as a guide in designing relational databases. Normalization is essentially a two step process that puts data into tabular form by removing repeating groups and then removes duplicated data from the relational tables.
Normalization theory is based on the concepts of normal forms. A relational table is said to be a particular normal forms that have been defined.
The goal of normalization is to create a set of relational tables that are free of redundant data and that can be consistently and correctly modified. This means that all tables in a relational database should be in the third normal form (3NF).
Two Types of Redundancy
There are two types of redundancy in normalization:
1. Row Level Redundancy
In this table 1st and 3rd row is same called row level redundancy.
SOLUTION : Row level redundancy is removed by PRIMARY KEY
2. Column Level Redundancy
Problem Occured in Normalization
- Insertion Anomaly (special problem occurred in special case)
- Deletion Anomaly
- Updation Anomaly
Types of Normal Form
First Normal Form
A relation which contains a repeating group called an unauthorized relation. Removal of repeating groups is most important while trying to remove problems from relations. Relations without repeating groups are said to be in first normal form.
Definition of First Normal Form
In first normal form, table should not contain any multi-valued attribute.
CONVERTING IT TO 1st NORMAL FORM
Second Normal Form
Table must be in 1st normal form.
Table should not contain any partial dependency in relation.
All the non-prime attribute should be fully functional dependent on candidate key.
CANDIDATE KEY : Customer_ID, Store_ID
PRIME ATTRIBUTE : Customer_ID, Store_ID
NON-PRIME : Location
CONVERTING IT TO 2nd NORMAL FORM
Third Normal Form
Table must be in 2nd normal form
There should be NO TRANSITIVE DEPENDENCY in table.
CANDIDATE KEY : Roll No
FUNCTIONAL DEPENDENCY : Roll No -> State, State -> City
PRIME ATTRIBUTE : Roll No
NON-PRIME ATTRIBUTE : State, City