Third Normal form - Transitive dependency

When an indirect relationship causes functional dependency it is called Transitive Dependency.

If  P -> Q and Q -> R is true, then P-> R is a transitive dependency.

To achieve 3NF, eliminate the Transitive Dependency.

A relation schema 'R' is in 3NF if for every non-trivial FD X-->Y, one of the two conditions must hold

1) 'X' is a CK/PK

2) 'Y' is prime attribute 

A relation is in 3NF, if there no transitive dependency for non-prime attributes as well ass it is in 2NF.

A relation that is in 1NF, 2NF and in which no non-prime key attribute is transitively dependent on the primary key, then it is in 3NF.

3NF is consider adequate for normal relational database design because most of the 3NF tables are free of insertion, deletion, and update anomalies.

Moreover, 3NF always ensures functional dependency preserving and loss less.

 Non-prime attribute determine non-prime is a transitively dependent and not allowed in 3NF.

Process for 3NF

• Eliminate all dependent attributes in transitive relationship(s) from each of the tables that have a transitive relationship.
• Create new table(s) with removed dependency.
• Check new table(s) as well as table(s) modified to make sure that each table has a determinant and that no table contains inappropriate dependencies.

Example:-

Consider the relation R(ABCDE) with FD's ={ AB->C, B->D, D->E} is it in 3NF or not. If not then decompose it into 3NF

Solution:

R(ABCDE)

 { AB->C, B->D, D->E}

Key: AB so, prime attributes are : A,B and non-prime C,D,E

Now check each FD if it hold 3NF rules or not. Remember the relation is in 2NF first before 3NF.

1. AB->C ; Key AB determine non-prime allowed in 3NF

2. B->C ; part of the key (B) determining non-prime, it is partial dependency not in 2NF, so decompose the relation as R1(BDE) and R2(ABC) [ since you can divide in any way such that the problem making FD comes in one relation]

R1(BDE)

B->D

D->E

find B+ since ( B-D) is problem making FD and check it is preserved or not

B+={BDE} ; it is preserved and key is B

But the FD: D->E is not allowed in 3NF because of Transitive dependency . So, again decompose the relation R1(BDE) into R11(DE) and R12(BD)

R11(DE)

D->E;  No Transitive dependency  , since in this relation R11 Key is D

R12(DB)

B->D; No Transitive dependency  , since in this relation R12 Key is B.

Therefore, the final relations are 

R11(DE), R12(DB), R2(ABC)