Posts Tagged ‘RDBMS’

Normalization Part-I

Normalization is the systematic and scientific process for deciding which attributes should be grouped together in relation. It reduce the amount of space a database consumes , validating and improving the logical design. It is a series of test on a relation.

Goal:

  1. Eliminating redundant data.
  2. Storing related data in same relation.
  3. Promotes integrity.

Normal Form:

A normal form is a state of a relation that result from applying simple rules and guidelines regarding dependencies means relationship between attributes to that relation.

In practical applications , we ‘ll often use 1NF,2NF and 3NF along with the occasional 4NF. 5NF is very rarely uses.

1. First Normal Form(1NF):

1NF have following rules

  1. Elimination of repeating group.
  2. Create separate table for each group.
  3. Table should be atomic.
  4. Predecessor table and new table have relationship(often 1:Many or Many :1)
  5. In predecessor table each row identifies with unique column


Illustration with anExample

There are one relation to store user information and UserId is a primary key. In this relation each user can have one or more than one telephone number .

UserId Name Tel1 Tel2
1 Sandeep 9461162949 NULL
2 Raviendra 9416612949 9849166129
3 Pradeep 9929412061 NULL

Here Telephone Number is repeating group. Here is two phoneno if suppose here is five column and an user have only one telephone number than remaining five column will be waste and consume extra space in database. So this problem solution is First Normal Form(1NF).

UserId Name
1 Sandeep
2 Raviendra
3 Pradeep

UserId TelephoneNo
1 9461162949
2 9416612949
2 9849166129
3 9929412061

Now that is First Normal Form (1NF).

2. Second Normal Form (2NF):

2NF have following rules

  1. Relation should be in 1NF.
  2. Every non-key attribute is fully and functionally depends on primary key.
  3. Remove subsets of data and place them in separate table.
  4. Create relationship between new table and predecessor table using foreign key.
  5. All the attributes in the relation are component of primary key.

Inllustration with an Example
Here is a relation that contain user information and UserId is primary key.

UserId Name City State Zip
1 Sandeep Jaipur Rajasthan 302006
2 Rupendra Jhunjhunu Rajasthan 333801

Here we have a set of related columns {Zip,State,City}. Columns State and City depend on column Zip.So we create a separate table for set {Zip,State,City} where Zip is a primary key.

UserId Name Zip
1 Sandeep 302006
2 Rupendra 333801

Zip State City
302006 Rajasthan Jaipur
333801 Rajasthan Jhunjhunu

Now that is in Second Normal Form(2NF).

3.Third Normal Form(3NF):

3NF has following rules

  1. Relation should be in 2NF.
  2. Remove columns that are not depend upon primary key.

Illustration with an Example

There is relation that have employee information. Here EmployeeId is a primary key.

EmployeeId Name Company Location
1 Jaiveer XYZ.Pvt.Ltd Jaipur
2 Jitendra ABC.Pvt.Ltd Mumbai
3 Jogendra XYZ.Pvt.Ltd Jaipur

Here company location is not depend on EmployeeId. Location depends on Company so we remove this column. Below both tables have relationship of foreign key and ComapnyId is a primary key .

CompanyId Company Location
1 XYZ.Pvt.Ltd Jaipur
2 ABC.Pvt.Ltd Mumbai

EmployeeId Name CompanyId
1 Jaiveer 1
2 Jitendra 2
3 Jogendra 1

Now it is 3NF