Hi Reader's,

In this blog we are going to discuss about the Normalization process with in the database.Database Normalization can be defined as a technique which is used to organize the data within the database. Database is a collection of multiple tables in which data is stored in the form of rows and columns. Normalization is basically used to reduce the redundancy from the tables and anomalies like insertion,deletion and updation anomaly.

There are two main purpose of using Normalization in database:-
    1.    Remove redundancy from tables.
    2.    Check data dependencies also to store only logical data.

Why Normalization is required

If database is not normalized so redundant data will be stored in the database and that will create data ambiguity error as we can get multiple data when we need only specific data. Insertion,Deletion and Updation anomalies occurs very frequently.  

For Example :-

Suppose we have a table in database with name Employee and fields/columns within this table are Employee_Id, Employee_Name and Employee_Address. Table given below contains the redundant data of an employee.

Redundant data of an employee is inserted within the table so when we want to update the address it will create an inconsistent data (inconsistent means same data within the table one record change due to updation but not other one ) within the table as query will update that data which occurs first in the table. Simultaneously deletion anomaly can also occur as if we want to delete the data of a particular employee and another row of that particular employee data is present within the table so definitely this will create deletion anomaly.

Types Of Normalization Form

•  First Normal Form (1NF)
•  Second Normal Form (2NF)
•  Third Normal Form (3NF)
•  BCNF

First Normal Form (1NF)

The concept of First Normal Form says no two rows will contain the similar data/information that is each cell of table will contain unique value so that with the help of single column we can easily fetch the information from table.
 

For Example:-

So in First Normal Form we will have only one value at one cell so to modify the above table into the First Normal Form we need only single value in each cell. After First Normal Form table will be look like as in given way:-

Second Normal Form (2NF)

To perform Second Normal Form on a table two conditions are required:-

• Table should be in First Normal Form.
• Not any non-prime attribute of the table is dependent on the  subset of any candidate key of table.

Prime attributes are those attributes which are part of candidate key and non-prime are visa-versa of prime attributes.

So if we will take the above example than we have to split the above table into two parts so that no non-prime attribute is dependent on candidate key. Single table will be split in two tables in following way to perform Second Normal Form.

New Employee Table For 2NF

New Employee Address Table For 2NF

Now all the non-prime attributes are separated so that not any non-prime attribute is dependent on candidate key so that it will not suffer the update anomaly.

Third Normal Form (3NF)

To perform Third Normal Form in a table there must be two conditions fulfilled after that table will be in Third Normal Form and those conditions are:-

• Table should be in Second Normal Form (2NF).
• Remove transitive functional dependency.

Removal of Transitive functional dependency includes reduction of redundant data and also get the data integrity. Third Normal Form includes that all the non-prime attributes should be dependent on primary key of the table.

For Example:-

Employee Table :

In this table Employee_Id is Primary key, but street, city and state depends on Zip feild of the table. The transitive dependency can be identify as dependency between zip and other fields. To apply Third Normal Form (3NF), we need to create an another table with feilds street, city and state, with Zip as primary key in the new table.

New Employee Table :

Address Table :

In this way we can perform Third Normal Form of Normalization in a table.

Boyce and Codd Normal Form (BCNF)

Boyce and Codd Normal Form can be defined as the one step ahead from Third Normal Form. This normal form handles those kind of anomalies which can not be handled by Third Normal Form. To perform Boyce and Codd Normal Form in a table that table should be in Third Normal Form only than BCNF can be performed. For every functional dependency A->B, A should be the super key of the table than only BCNF can be performed.

For Example:-

To make this table in Boyce and Codd Normal Form we will split this single table into three tables:-

Employee_Name Table:

Employee_Name Table:

Employee_Department Table:

Employee_Department_Mapping Table:

In this way we can implement the concept of normalization in database.