The structuring of Database is normalization, Normalization is the process of restructuring tables to eliminate design problems. This process removes redundant data, makes it possible to access data more easily. Normalization of a complex table is to taking it through a process of splitting into a set of smaller tables to remove the data anomalies. This process removes repeating groups within rows and then duplicate data within columns.

The main motive of normalization is to store each row of data only once, to avoid data redundancy and repetitions.

         Motives of Normalization

• Normalization is done to avoid the data redundancy and increase data integrity.
• It is a better process of developing data structures.
• It ensures data dependencies make sense that means data is logically stored.
• It eliminates the undesirable characteristics like Insertion, Updating and Deletion irregularity.

Types of Normalization:

·    1NF- First Normal Form

·   2NF-Second Normal Form   

·  3NF- Third Normal Form

·   4NF- Fourth Normal Form

·   5NF-Fifth Normal Form

·  BCNF -Boyce – Cod Normal Form                                  

1NF (First normal form):-

•Eliminate repeating groups in individual tables.

•Create a separate table for each set of related data.

•Identify each set of related data with a primary key.

Before Normalization:-

After normalization by 1NF method:-

Second Normal Form

•Create separate tables for sets of values that apply to multiple records.

•Relate these tables with a foreign key.

Normalization of database using 2NF form:-

1) New Student Table following 2NF will be:

2) New Subject Table introduced for 2NF will be:

Third Normal Form

•Eliminate fields that do not depend on the key.

According to Third Normal form every non-prime attribute of table must be dependent on primary key, or let say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So to remove transitive functional dependency s from the table the table must be in Second Normal form. For example, consider a table with following fields.

Employee_Detail Table:

In the above table, Emp_id is Primary key, but street, city and state depends upon Postal id. The dependency between zip and other fields of the table is called transitive dependency. So to apply 3NF, we need to move the street, city and state to new table, with Postal id as primary key of that table let’s see how:-

New Employee_Detail Table:

New Address _Detail table:

BCNF:-

Boyce and Cod Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

• R must be in 3rd Normal Form
• And, for each functional dependency (X -> Y), X should be a super Key. 

Consider the following relationship: R(P,Q,R,S)

And the following Dependencies:

P- >QRS

QR->PS

S->Q

As, we can see the realtion is already in 3NF,with keys as P & QR

Hence we can see P-QRS as a functional Dependancy with P as a super

key,

QR->PS is also a key,but in S->Q is not a key

So,we can break out the relation R in two forms R1 nad R2:-           

Hence breaking the table into two forms one with one with P, S, R and other with S & Q.

Fourth Normal Form (4NF)

When attributes in a relation have multi-valued dependency, further Normalization to 4NF and 5NF are required. A multi-valued dependency is a typical kind of dependency in which each and every attribute within a relation depends upon the other, yet none of them is a unique primary key.

 Fifth Normal form (5NF)

A relation decomposed into two relations must have loss-less join Property, which ensures that no spurious or extra tuples are generated, when relations are reunited through a natural join.A relation R is in 5NF if and only if every join dependency in R is implied by the candidate keys of R.