Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Normalization in database

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 206
    Comment on it

     

    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.

     

    Employee_Id Employee_Name Employee_Address Employee_PANNo
    101 Tanuja Bhatt Dehradun ABC123
    102 Sukanya Dehradun XYZ345
    103 Tanuja Bhatt Dehradun ABC123

     

    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:-

     

    Employee_Id Employee_Name Employee_Address Employee_PANNo
    101 Tanuja Bhatt Dehradun,Delhi ABC123
    102 Sukanya Dehradun XYZ345

     

    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:-

     

    Employee_Id Employee_Name Employee_Address Employee_PANNo
    101 Tanuja Bhatt Dehradun ABC123
    101 Tanuja Bhatt Delhi ABC123
    102 Sukanya Dehradun XYZ345

     

     

    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

     

    Employee_Id Employee_Name Employee_PANNo
    101 Tanuja Bhatt ABC123
    102 Sukanya XYZ345

     

    New Employee Address Table For 2NF

     

    Employee_Id Employee_Address
    101 Dehradun
    101 Delhi
    103 Dehradun

     

    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 :

    Employee_Id Employee_Name Employee_PANNo Street City State Zip
    101 Tanuja Bhatt ABC123 Sarawatiipuram Dehradun Uttarakhand 123876
    102 Sukanya XYZ345 Nehrugram Dehradun Uttarakhand 123457

     

    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 :

    Employee_id Employee_Name Employee_PANNo Zip
    101 Tanuja Bhatt ABC123 123876
    102 Sukanya XYZ345 123457

     

    Address Table :

    Zip Street City State
    123876 Sarawatiipuram Dehradun Uttarakhand
    123457 Nehrugram Dehradun Uttarakhand

    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:-

     

    Employee_Id Employee_Name Employee_Department Department_Type Department_No_Of_Employees
    101 Tanuja Bhatt iOS D001 200
    101 Tanuja Bhatt ASP.NET D002 250
    102 Sukanya iOS D001 200
    102 Sukanya ASP.NET D002 250

     

    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_Id Employee_Name
    101 Tanuja Bhatt
    102 Sukanya

     

    Employee_Department Table:

    Employee_Department Department_Type Department_No_Of_Employees
    iOS D001 200
    ASP.NET D002 250

     

    Employee_Department_Mapping Table:

    Employee_Id emp_dept
    101 iOS
    101 ASP.NET
    102 iOS
    102 ASP.NET

     

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

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: