Normalization In Database 

Normalization In Database: The process to convert the complex data structure into simple and stable data structure is called normalization.

The purpose of normalization is to produce a stable set of relations. Basically, a normalized relation has a simple structure, than an un-normalized one. By applying the normalization principles, we can improve the stability of the relations.

Normalization Steps

Normalization is often accomplished in steps, each of which corresponds to a normal form. A normal form is a state of a relation that can be determined by applying simple rules regarding dependencies to that relation.      

A brief description of major steps are given as

First Normal Form (1^st NF)

Any repeating group from the relation is removed. After applying this rule, each cell of the relation has a single value.

Second Normal Form (2^nd NF)

Any partial functional dependency of the relation is removed.

Third Normal Form (3^rd NF)

Any transitive dependency is removed.

Boyce-Cod Normal Form

Every determinant must be a candidate key.

Fourth Normal Form (4^th NF)

Any multi-valued dependency is removed.

Fifth Normal Form (5^TH NF)

Any remaining anomaly is removed.

The table will be solve by normalization is as follows

First Normal Form

For example the data of employees of an organization is shown as the following table

After the 1^st Normal form the table will be show as

Second Normal Form

A relation in the second normal form if and only if it is in the first normal form and all the non key attributes are fully functionally dependent on the key attribute. It is an important step in normalization in database.

In this case

EMPLOYEE (ID, name, Department, salary, Course, Date Completed)

The functional dependencies in this relation are as follows

Emp ID             Name, Dept, Salary

Emp ID, Course             Date Completed

EMPLOYEE Relation

Course Relation

Third Normal Form

A relation in third normal form if it is in second normal form and no transitive dependency exist.

A third normal form also defined as A non-key attribute must not depend on any other non-key attribute of the relation.

The table of PATIENT-HISTORY (PatientID, Visit Date, Physician, Diagnosis, Treatment)

Now the dependency

DIAGNOSIS (Diagnosis, Treatment)

Fourth Normal Form

A relation is in Fourth Normal Form if and only if it is in Boyce Coded Normal Form and has no multi-valued dependencies.

The table of MEDICINES relation.

After the removing the multi-value dependency the table will be shown as

Fifth Normal Form

A relation is in fifth normal form if it   is in fourth normal form and does not have a join dependency.  It is also known as Project-Join Normal form.

A relation that has a join dependency cannot be divided into two or more relations such that the resulting relations can be re-combined to form the original relation. It is final step of normalization in database.

Data Integrity