Home > Database Concepts > Basic Concept of Database Normalization

Basic Concept of Database Normalization

“Normalization” one of the most common word related to data base. In fact this is a interesting topics in Relational database systems. Let me explain this using some new examples….

“NORMALIZATION IS A PROCESS BY WHICH WE LOOK FORWARD OVER TWO FACTS : 1. REDUNDANT DATA ELIMINATION. 2.DATA DEPENDENCIES WILL MAKE SENSE. WHICH HELPS TO REDUCES THE DATABASE SPACE CONSUMPTION.”

Their are some series of guidelines are developed by the communities of database, known as first, second, third and fourth some how fifth normalization forms. But i am going to discuss only three of them :

1. First Normal Form :
Their are only two root rules
1.1 Their should be no duplicate columns.
1.2 For each group of related data their should be separate tables, in which identify each row uniquely by a column or set of column.
Example : This is EmployeeMaster as

Emp Code Emp Name Emp City One Emp City Two Basic Salary Var Salary Total Salary
EMP001 Neeraj Tripathi Delhi Gurgaun 10000 5000 15000
EMP002 Mayank Nath Mumbai Pune 15000 8500 23500

So apply the first normal form, Emp City will come in one column and Emp Name will break in two fiels:

Emp Code Emp First Name Emp Last Name Emp City Basic Salary Var Salary Total Salary
EMP001 Neeraj Tripathi Delhi 10000 5000 15000
EMP001 Neeraj Tripathi Gurgaun 10000 5000 15000
EMP002 Mayank Nath Mumbai 15000 8500 23500
EMP002 Mayank Nath Pune 15000 8500 23500

Now the given record set is in the first normal form.

2. Second Normal Form :
This form state that after fulfilling all the condition of first normal form, their are two more conditions
2.1 Place those data in separate tables which are not directly related to the primary key of that table.
2.2 Create relationship between these tables using new table key (Foreign Key).

Emp Code Emp First Name Emp Last Name Emp City Id Basic Salary Var Salary Total Salary
EMP001 Neeraj Tripathi 1 10000 5000 15000
EMP001 Neeraj Tripathi 2 10000 5000 15000
EMP002 Mayank Nath 3 15000 8500 23500
EMP002 Mayank Nath 4 15000 8500 23500

And City Master become in scene

City Id City Name
1 Delhi
2 Gurgaun
3 Pune
4 Mumbai

That is now takes our database to a second normal form.

3. Third Normal Form :

So now meet all the fundamentals for IInd normal form and then

3.1 Remove all the non key fields which are depend on other non key fields.
In our table “Total Salary” is a kind of field. So :

Emp Code Emp First Name Emp Last Name Emp City Id Basic Salary Var Salary
EMP001 Neeraj Tripathi 1 10000 5000
EMP001 Neeraj Tripathi 2 10000 5000
EMP002 Mayank Nath 3 15000 8500
EMP002 Mayank Nath 4 15000 8500

So the “Total Salary” field is removed which can be easily get by “Basic Salary” + “Var Salary”.
Ok Now our Database is normalized at the stage of Third normal form.

These are most applicable normal forms of database. I am not giving the idea about fourth and fifth normal forms. I apologies for that. But they have been rarely used in database normalization.

Now Happy reading. Cheers.
Neeraj Tripathi

About these ads
Categories: Database Concepts
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: