Thursday, May 2, 2013

Normalization in Database Management System


Introduction

This bolg that follow describe the all normal forms and teach you how to apply these normal forms. To describing all the normal forms first look up at, What is Normalization

Normalization

Database designed based on ER model may have some amount of inconsistency, redundancy and ambiguity. To resolve that problems we use the "Normalization", so it  is the process that helps you avoid redundancies and inconsistencies in your data. We need Normalization because It provide a well designed Relation Database Management System (RDBMS).

We normalize data into Relation Database Management System because of some reason.
  • Minimize redundancy
  • Make Database structure flexible
  • Data should be consistent through out  database.
  • Better understanding of database.
Image of Normalization step by step

Now I describe some of them:

Un-Normalized Relation

A table is an un-normalized form, when a table contain non-atomic value at each-row, So in the given below table "Contact_No"  cell contain non-atomic city. Because user name "Neha" have two contact number into single cell.
                                                                                                           User_Detail
User_ID
Respect
User_Name
Contact_No
City
1e234
Mr.
Nitin
9787564565
Delhi
2r345
Miss.
Neha
8767875423
8745764579
New Delhi
3t564
Mr.
Vinod
9956741887
Noida
1NF (First Normal Form)

The first normal form say that remove the repeating groups or say if the table is in the 1NF then no non atomic value and each row provide a unique combination of value.
                                                                                                           User_Detail
User_ID
Respect
User_Name
Contact_No
City
1e234
Mr.
Nitin
9787564565
Delhi
2r345
Miss.
Neha
8767875423
New Delhi
2r345
Mr.
Neha
8745764579
New Delhi
3t564
Miss.
Vinod
9956741887
Noida
as, you can see the above table each row contain the unique combination of value, so the above table is in the 1NF.
2NF (Second Normal Form)
The second normal form say the table must be in 1NF and remove the partial dependency of that table, single column should be primary key and create relation ship among new tables.

Table1
                                                                                User_Detail
User_ID
Respect
User_Name
City
1e234
Mr.
Nitin
Delhi
2r345
Miss.
Neha
New Delhi
3t564
Mr.
Vinod
New Delhi

Table2
                                                                                  Con_Detail
Con_ID
User_Name
Contact_No
1
1e234
9787564565
2
2r345
8767875423
3
2r345
8745764579
4
3t564
9956741887
We divided 1NF tables into two tables, and both table 1 and table 2 is in the 2NF, because both table has a one primary key column, and User_name column of the table2 has create the relation between table 1 and table2 on the behalf of table 1 User_ID column, who is a primary key column. So at last we say that the 1NF table normalized into 2NF.
3NF (Third Normal Form)
The third normal form says, meats all requirement of the second normal forms and remove the transitive dependency means remove that columns which is not dependent upon the primary key.

1-
                                                                                User_Detail
User_ID
Respect_id
User_Name
City
1e234
1
Nitin
Delhi
2
Miss.
Neha
New Delhi
3t564
1
Vinod
New Delhi
2-
                                                                                  Con_Detail
Con_ID
User_Name
Contact_No
1
1e234
9787564565
2
2r345
8767875423
3
2r345
8745764579
4
3t564
9956741887
3-
                                                                                  Id_prefrence
ID
Respect
1
Mr.
2
Miss

BCNF or 3.5 (Boyce-Codd
Normal Form)
The second normal form say the table must be in 2NF and make sure every determinate is a candidate key ( A candidate key is a column, or set of column, in a table that can uniquely identify any database record without referring to any other data.).