Second Normal Form(2NF) : Introduction

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

 

Second Normal Form(2NF) : Partial Dependency

• Whenever any non-prime attribute is dependent upon a part of candidate key of a relation, it is known as partial dependency.

For example : Consider a relation R(X,Y,Z,T) with following functional dependencies :

FD1 : XY  T

FD2 : Y Z

The closure will be,

{X}⁺ = {X}

{Y}⁺ = {Y,Z}

{XY}⁺ = {X,Y,Z,T}

{T}⁺ = {T}

Here, Candidate key will be “XY”.

Now, have a look at the FD2,

Y Z

In the above FD2, “Z” is a non-prime attribute which is dependent upon “Y”, which is a partial part of candidate key “XY”. This is called as partial dependency.

NOTE : If the FD2, “Y Z” would have been like “XY Z”, then we could have said that “Z” is fully dependent upon on “XY”, as “XY” is itself a candidate key.

 

Second Normal Form(2NF) : Example

• The Second Normal Form(2NF) is quiet easy and works on two step approach. Consider relation R(X,Y,Z,T) with following FD’s :

FD1 : XY T

FD2 : Y Z

 

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

FD1 : XY T

FD2 : Y Z

Here, Each of the FD has a singleton attribute on its RHS. Hence, the relation is in 1NF.

 

Step-2 : Check the relation for partial dependency.

a) Find the closure and determine the candidate key.

{X}⁺ = {X}

{Y}⁺ = {Y,Z}

{XY}⁺ = {X,Y,Z,T}

{T}⁺ = {T}

Here, Candidate key will be “XY”.

 

b) Check whether the non-prime attributes in the FD’s are fully dependent upon candidate key or not.

XY T (Attribute “T” fully depends upon “XY” as “XY” is a candidate key) 

Y Z (Attribute “Z” is partially dependent upon the candidate key as, “Y” is a part of “XY”)

 

c) To remove partial dependency, decompose the whole relation R(X,Y,Z,T) into R1(X,Y,T) and R2(Y,Z).

Therefore, R1 : XY T (“XY” will be the candidate key)

and R2 : Y Z (“Y” will be the candidate key)

Hence in relation R2, “Z” will be fully dependent upon “Y” as it is the candidate key for relation R2.

NOTE : Decomposition of relation to remove partially dependency is totally depends upon the number and type of FD’s of the relation. The relation which is to be separated from original relation must contain the FD’s which are partially dependency. In the above case FD “Y Z” was the FD which was separated from original relation R.

Previous Next