Third Normal Form(3NF) : Introduction

• The Third Normal Form which is commonly known as “3NF” works on the concept of transitive dependency and is used to check weather a relation is in 3NF or not.
• Any relation to be in Third Normal Form(3NF) must follow the below mentioned two rules :
  1. The relation/table should be in Second Normal Form.
  2. The relation/table must not have any transitive dependency.
• Let’s try to understand the concept of transitive dependency first and then we will continue Third Normal Form(3NF) with an example.

 

Third Normal Form(3NF) : Transitive Dependency

• Transitive dependency works on the concept of Armstrong’s Axiom of Transitivity.

For example : Consider an relation R(A,B,C,D) with the following Functional dependencies:

FD1 : A  BC

FD2 : B  C

The above FD’s can be rewritten using decomposition rule as :

FD1 : A  B

FD2 : A  C

FD3 : B  C

 

Now, using Armstrong’s Law of Transitivity i.e. if X Y, Y Z then X Z is transitively dependent and should be removed. Similarly, we will try to implement this rule in the FD’s we have. We will be left with :

FD1 : A B

FD2 : B C

Hence in this manner, transitivity can be removed.

NOTE : If a “Non-Prime” attribute is functionally dependent upon another “Non-Prime” attribute, that functional dependency will also be termed as transitive and it needs to be separated into new relation by decomposition of original relation.

 

Third Normal Form(3NF) : Example

• The Third Normal Form(3NF) can easily be implemented using an example.

Consider the relation R(X,Y,Z,T) with the following functional dependency:

FD1 : XY Z

FD2 : Z  T

 

Step-1 : Check whether given relation is in 2NF or not.

Here, “XY” will be the candidate key for the above mentioned FD’s and no partial dependency exists in this relation too. So, this relation is already in 2NF and we are good to go now.

Step-2 : Remove transitive dependencies from the relation and try to decompose it into a separate relations.

XY Z (“Z” is fully dependent on candidate key “XY”)

and Z T (Z & T, both are non-prime attributes and thus forms a transitive dependency)

Now,

Splitting the whole relation into two separated relations R1(X,Y,Z) and R2(Z,T)

R1 : XY Z (Here, “XY” is the candidate key and there is no transitive dependency in this whole relation)

R2 : Z T (Here, “Z” is the candidate key and there is no transitive dependency in this whole relation)

Hence, the relation can be termed to be in 3NF, as both the relation R1 and R2 does not have any transitive dependency as well as both as are in 2NF too.

NOTE : There exists many possibilities in questions of normal forms where multiple cases of transitivity and multiple candidate keys might occur. Hence, we would recommend you to grasp the concept and try to solve as many problems as possible. We will share the question practice sheet for normal forms in the end of the chapter.

Previous Next