Basics of Normalisation

Normalization could be defined as the process of efficiently organizing data or in other terms elminating redunant dataand maintain the data relative to other tables by means of relationships.

There are various steps to Normalization which we generally call as Normal Forms.There are various NFs but in real database world,database is said to be normalised if we normalise to Third Normal Form (3NF).Let us see each normal forms in detail:

1 NF

(a) Elimination of duplicate columns from the same entity.

(b) Creation of separate entity for each group of related data and identify each row with a unique column or set of columns(composite) which is called as primary key.

Example:-EmployeeID PayElement1 PayElement2 PayElement3

Here EmployeeID,PayElement1,PayElement2,PayElement3 are the column names,but the basic flaw in this model is all employees will not have all of these Payelements,some might have 2 and other might have just 1.

And also what if some more elemnts are added in near future for which the table structure needs to be modified,so now we define these tables to accomodate the above data.

PayElement

PayElementID -- > PayElementID is primary KeyPayElementName

Employee

EmployeeID

PayElementID -- > Here EmployeeID,PayElementID is the composite primary key.

Now with the above structure we can accomodate any number of pay elements for employees

2 NF

(a) Remove columns which is partially dependent on a entity and place them in separate entity. (b) Create relationships with the new entity created by foreign keys.

3 NF

(a) Remove columns that are not fully dependent upon the primary key.

OrderID

CustomerID

Price

Quantity

Total

In this total is not dependent on the primary key OrderID and CustomerID and in fact its a derived field from Price and Quantity and hence can be removed.