What is Normalization In Database?

 


                             What is Normalization In Database?


The structuring of Database is normalization, Normalization is the process of restructuring tables to eliminate design problems. This process removes redundant data, makes it possible to access data more easily. Normalization of a complex table is to taking it through a process of splitting into a set of smaller tables to remove the data anomalies. This process removes repeating groups within rows and then duplicate data within columns.

The main motive of normalization is to store each row of data only once, to avoid data redundancy and repetitions.

         Motives of Normalization

  • Normalization is done to avoid the data redundancy and increase data integrity.
  • It is a better process of developing data structures.
  • It ensures data dependencies make sense that means data is logically stored.
  • It eliminates the undesirable characteristics like Insertion, Updating and Deletion irregularity.

Types of Normalization:

·    1NF- First Normal Form

·   2NF-Second Normal Form   

·  3NF- Third Normal Form

·   4NF- Fourth Normal Form

·   5NF-Fifth Normal Form

·  BCNF -Boyce – Cod Normal Form                                  

1NF (First normal form):-

•Eliminate repeating groups in individual tables.

•Create a separate table for each set of related data.

•Identify each set of related data with a primary key.

Before Normalization:-

STUDENT

               AGE

SUBJECT

Sumit

                  17

Math’s, Sociology

Shivangi

                  20

Sociology

          Rahul

                  19

Economics

After normalization by 1NF method:-

            STUDENT

             AGE

             SUBJECT

      Sumit

17

Math’s

      Sumit

17

Sociology

      Shivangi

20

Sociology

      Rahul

19

Economics

   

Second Normal Form

Create separate tables for sets of values that apply to multiple records.

Relate these tables with a foreign key.

Normalization of database using 2NF form:-

1) New Student Table following 2NF will be:

       STUDENT

          AGE

      Sumit

17

      Shivangi

20

      Rahul

19

2) New Subject Table introduced for 2NF will be:

       

     STUDENT

       SUBJECT

       Sumit

          Math’s

       Sumit

         Sociology

       Shivangi

         Sociology

       Rahul

         Economics


Third Normal Form

•Eliminate fields that do not depend on the key.

According to Third Normal form every non-prime attribute of table must be dependent on primary key, or let say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So to remove transitive functional dependency s from the table the table must be in Second Normal form. For example, consider a table with following fields.

Employee_Detail Table:

Emp_id

Emp_name

DOB

Street

City

State

Postal id

Salary

In the above table, Emp_id is Primary key, but street, city and state depends upon Postal id. The dependency between zip and other fields of the table is called transitive dependency. So to apply 3NF, we need to move the street, city and state to new table, with Postal id as primary key of that table let’s see how:-

New Employee_Detail Table:

Emp_id

Emp_name

DOB

Salary

Zip

New Address _Detail table:

Postal id

Street

City

State

 

BCNF:-

Boyce and Cod Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

  • R must be in 3rd Normal Form
  • And, for each functional dependency (X -> Y), X should be a super Key. 

Consider the following relationship: R(P,Q,R,S)

And the following Dependencies:

P- >QRS

QR->PS

S->Q

As, we can see the realtion is already in 3NF,with keys as P & QR

Hence we can see P-QRS as a functional Dependancy with P as a super

key,

QR->PS is also a key,but in S->Q is not a key

So,we can break out the relation R in two forms R1 nad R2:-           

 What is Normalization In Database?

Hence breaking the table into two forms one with one with P, S, R and other with S & Q.

Fourth Normal Form (4NF)

When attributes in a relation have multi-valued dependency, further Normalization to 4NF and 5NF are required. A multi-valued dependency is a typical kind of dependency in which each and every attribute within a relation depends upon the other, yet none of them is a unique primary key.


What is Normalization In Database?


 Fifth Normal form (5NF)

A relation decomposed into two relations must have loss-less join Property, which ensures that no spurious or extra tuples are generated, when relations are reunited through a natural join.A relation R is in 5NF if and only if every join dependency in R is implied by the candidate keys of R.

 

Last updated:3/20/2018 5:08:32 AM

0 Comments

Leave Comment