WHAT IS NORMALIZATION?

Abhishek Srivasatava

Total Post:70

Points:350
Posted by  Abhishek Srivasatava
 335  View(s)
Ratings:
Rate this:
  1. Abhishek Srivasatava

    Post:70

    Points:350
    What is normalization?

    Normalization :

    Normalization is a process of effectively organizing the column and table of a database to reduce data redundancy and improve data integrity.

     

    Normal form of a table

    Prsn_empl_id

    Prsn_dpnd_id

    Plan opted

    669911

    123456

    Health , Dental

    669901

    987415

    Vision

     

    1NF

    First Normal Form (1NF)

    All attributes in a relation must have atomic domains.

    In this scheme there should be no repeated element in single block.

    For example:

     

    Prsn_empl_id

    Prsn_dpnd_id

    Plan opted

    669911

    123456

    Health

    669911

    123456

    Dental

    669901

    987415

    Vision

     

    2NF

    Second Normal Form (2NF)

    for implementing this scheme it must satisfied 1st scheme.

    Before moving further one should know about the prime attribute and non-prime attribute.

    Prime attribute: the entire element which is a part of the prime key.

    Non-prime attribute: the entire element which is not part of the prime key.

    For this scheme, non prime attribute should be functionally dependent on prime key.


    1NF table 

    Prsn_empl_id

    Prsn_dpnd_id

    Plan opted

    669911

    123456

    Health

    669911

    123456

    Dental

    669901

    987415

    Vision

     

    Modified to 2NF

    Prsn_empl_id

    Prsn_dpnd_id

    669911

    123456

    669901

    987415

     

     

    Prsn_empl_id

    Plan opted

    669911

    Health

    669911

    Dental

    669901

    Vision

     

    3NF

    It must satisfy 2NF criteria and there should no relationship between primary key and non primary element data.

    For example we have table like this

    Name

    Addr_id

    Address type

    Address

    A

    10

    Permanent

    Abc

    A

    5

    Temp

    Pqr

    B

    10

    Permanent

    Xyz

    B

    5

    temp

    Efg

     

    We can divide above table in two

    Name

    Addr_id

    Address

    A

    10

    Abc

    A

    5

    Pqr

    B

    10

    Xyz

    B

    5

    Efg

     

    Addr_id

    Address type

    5

    Temp

    10

    Permanent

     

    Boyce-Codd Normal Form

    It is developed by the Raymond F Boyce.

    X Y is a trivial functional dependency (Y X)

    X is a super key for schema R

    Normal table

    Employee_id

    Name

    DOB

    Car

    Car_id

    price

    1001

    A

    12/01/1993

    U

    997

    11M

    1002

    B

    15/01/1993

    X

    997

    12M

    1003

    C

    17/01/1993

    Y

    997

    13M

    1004

    D

    20/01/1993

    Z

    997

    14M

     

    Converting to Boyce-Codd Normal Form

    Employee_id

    Name

    DOB

    1001

    A

    12/01/1993

    1002

    B

    15/01/1993

    1003

    C

    17/01/1993

    1004

    D

    20/01/1993

     

    Car

    Car_id

    price

    U

    997

    11M

    X

    996

    12M

    Y

    995

    13M

    Z

    994

    14M

     

    Employee_id

    Car_iD

    1001

    997

    1002

    996

    1003

    995

    1004

    994

     

      Modified On Sep-27-2016 05:08:37 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!