Normalization in database

Normalization

The process of minimizing data redundancy with a process of steps is called Normal Forms

 The total normalization process include 9 NF

First Normal Form

Second Normal Form

Third Normal Form

Boycee-Code Normal Form

Fourth Normal Form

Fifth Normal Form

Restrict Union Normal Form

Project Join Normal Form

Sixth Normal Form

First Normal Form

A table is said to be in first Normal Form, only when it has a primary key and it does not contain multiple valued columns

Example

The following table is created for storing the details of the customer of the company

Cust id	Name	Surname	Phone no
1001	Venkat	B	111111
1002	Sai	S	222222
1002	Sai	S	333333
1003	Rakesh	K	444444
1003	Rakesh	K	555555

The above table customer id  is the primary key, but the table is not in first normal form because it has a multi value column phone no

A customer may have multiple contact number hence, phone no column is a multi valued column to convert this table to first normal form divide it into two tables as follows

Cust id	Name	Surname
1001	Venkat	B
1002	Sai	S
1003	Rakesh	K

Cust id	Phone no
1001	111111
1002	222222
1002	333333
1003	444444
1003	555555

In, this table customer id, will be foreign key the above two table , the first table customer id is the primary key and in the second table customer id is the foreign key referring the customer id of the first table.

Second Normal Form

A table is said to be in second normal form, if and only if the table is in first normal form and second normal form and no partial dependencies exists in the table

When, a primary key is on combination  of multiple columns but if the non primary key column depends on part of the primary key and not on entire primary key, then the dependencies is called as partial dependencies

Example

To store the orders placed by the customers to a company the following table is created

order id	item	custname	order date
1	ram	c1	10/1/2007
1	hd	c1	10/1/2007
2	ram	c2	12/10/2007
2	motherboard	c2	12/10/2007

In, the above table primary key is on the combination of order id , item but now primary key columns customer name, order date depends only on order id and not on item, which is called as partial dependencies

Hence, the table is not in second normal form to convert the table to second normal form divide it into a tables as follows

order id	custname	orderdate
1	c1	10/10/2007
2	c2	12/102007

order id	item
1	I1
1	I2
2	I1
2	I5

The above two tables in the first table order id is the primary key, and in the second table order id is the foreign key

Third Normal Form

A table is said to be in the third normal form if and only the table is in second normal form and table does not contains transient dependencies when a non primary key column depends

Example

For storing the details of php students along with details of the professor guiding them a table is created as follows

studentid	s name	professor	prof phone
s1001	ravi	sunil	99999
s1002	srinivas	naresh	88888
s1003	kiran	chandu	343434343
s1004	fsas	dfsa	34343

               

The above table student id is primary key, but the non primary key column professor phone  depends on another non primary key column  professor, than  it is called as transient dependencies

Hence, the table is not in third normal form

To convert this table to third normal form divide it into two tables as follows

prof id	professor	prof phone
prof1	sunil	9999
prof2	naresh	9999
prof3	dsfs	3434343

student id	student name	prof id
s1001	ravi	prof1
s1002	srinivas	prof2
s1003	kiran	prof3
s1004	sfsf	prof2