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.).