3 NF

 

THIRD NORMAL FORM (3NF)

 

A given relation is called in Third Normal Form (3NF) if and only if-

1. Relation already exists in 2NF.
2. No transitive dependency exists for non-prime attributes.

 

Transitive Dependency   A → B is called a transitive dependency if and only if- A is not a super key. B is a non-prime attribute. If any one condition fails, then it is not a transitive dependency.

 

A relation is called in Third Normal Form (3NF) if and only if-

Any one condition holds for each non-trivial functional dependency A → B

1. A is a super key
2. B is a prime attribute

Consider a relation- R ( A , B , C , D , E ) with functional dependencies-

A → BC

CD → E

B → D

E → A

3NF is more stricter normal form than the 2NF

 

 3NF is concerned with transitive functional dependency.

 Transitive functional dependency may exist, only if there are more than one non key field.

  We may say that if a relation is in 2NF and has zero or one non key field it must automatically be in 3NF

 

Definition: A relation is in 3NF if and only if it is in 2NF and there are no transitive functional dependency.

 

 Transitive functional dependency arises when one non key attribute is functionally dependent on another non key attribute .

 

 In other words, the value of one non key attribute is determined by the value of another non key attribute.

 

 This means there is redundancy in the database.

 

 Let us consider a relation employee (proj.no, manager, address)

 

Proj.no	Manager	Address
P1	Black, B	32, High Street

P2	Smith, J	11, New street
P3	Black, B	32	High street
P4	Black,B	32	High street
P5	Thomas,J	69	Black street

 

       Here transitive dependency exists, as proj. no determines manager and manager determines address. If a manager is associated with more than one project then his address is repeated leading to data redundancy.  If we need to change address of a manager we need to change at many places leading to updation anomalies.

 

  So, we split the table in to two relations.

   The determinant attributes become the primary key in the new relation

     The attribute manager becomes the link between two tables/new relations

Project

 

Proj.no	Manager
P1	Black, B
P2	Smith, J
P3	Black, B
P4	Black,B
P5	Thomas,J

 

 Manager

 

Manager	Address
Black, B	32, High Street
Smith, J	11, New street
Thomas,J	69, Back street

 

            Here in project relation , proj.no serves as the primary key and manager can be determined by project no.

 

            In manager relation, address can be determined by knowing the manager name, so, manager becomes the primary key.

 

            So, in the above relation the non prime attributes depend upon the primary key

Alternative

 

Definion: A relation is said to be in 3NF when it is in 2NF and every non key attributes is functionally dependent only on the primary key.

 

Guidelines for converting a table to 3NF:

 

            Find the non. key attributes and remove them that are functionally dependent on attributes that are not the primary key.   Place them in a different relation.

 

Third Normal Form (3NF) (Summary)

 

A relation R is said to be in third normal form (3NF) if the relation R is in 2NF and non-prime attributes are:

 

            Mutually independent

 

            Functionally dependent on the primary key.

            In other words, no attributes of the relation should be transitively dependent on the primary key.

 

            Thus in 3NF, no non prime attribute is functionally dependent on another non-prime attribute.

 

This means that a relation in 3NF consists of the primary key and a set of independent non prime attributes

Definition: A Relation is in if and only if it is in 2NF and for any non trivial functional

 

dependency.

 

X® Y

 

  Either X is the super key or

 

  Y to be a prime attribute

 

Example: Let us consider a relation R (A,B,C,D) With functional dependencies {A} ® {B} , {A} ® {C}

 

And {C} ® {D} .Determine the candidate key and normalize upto 3NF 

 

Solution: Functional Dependencies are:

 

Here   {A} ® {B}

 

{A} ® {C}

 

{C}® {D}

 

The closure of C =C⁺ = {C, D}

 

The closure of A =A+ = {A, B, C, D}

 

So, A is the candidate key. Since the key determine the non key attributes it is in 2NF.

 

 

Since there exist transitive dependencies {A} ® {C} and {C} ® {D}.

 

So, we decompose, the relation into two relations

 

R₁ (A, B, C)                    Since {A®B, A®C}

 

 

R (A,B,C,D)

 

 

R₂ (C, D)                         Since { C ®D }