2 NF

SECOND NORMAL FORM:

 
2NF is more stringent normal form than 1 NF.

Def1: A relation is in 2NF if and only if, it is in INF and every non-key attribute is fully functionally dependent on the whole key.

Def2: A relation schema R is in 2NF if every non prime attribute A in R is fully functionally dependent on the primary key of R.

Def3: A relation R is in 2NF provided it is in INF and every non prime attribute to be fully
functionally dependent on candidate key of R.

Def4: A relation schema R is in 2NF if every non prime attribute A in R is not partially dependent on any key of R.

 or

A relation schema R is in 2NF if every non prime attribute A in R is fully functionally, dependent on every key of R. 

 

For a table to be in the Second Normal Form, it must satisfy two conditions:
1. The table should be in the First Normal Form.
2. There should be no Partial Dependency. 

A Partial Dependency, where an attribute in a table depends on only a part of the primary key and not on the whole key

· 2 NF is really a test of primary key.

· 2 NF is concerned with the concept of full functional dependency.
· If a relation is in 1 NF and has a single attribute key it must automatically be in 2Nf. 

 

Let us consider a relation schema R (A,B,C,D,E,F) with a set of functional dependencies.

 

F=         {A} ® {B}

{A} ® {C}

 {A} ® {F}

 {A} ® {B}

             {A,D} ® {F}

                {D} ® {E}

 

       Identify the candidate key

 

      IS it in 2NF ?

 

If not decompose it in to 2 NF form. 

Solution: The given functional Dependencies are: {A} ® {B}, {A} ® {C}, {D} ®{E}, {AD} ®{F}

 

{A,D} ⁺ = {A,B,C,D,E,F}

 There is no such single key that can derive/determine the other attribute.

 

 Here we take two keys together i.e. {AD} as the minimum super key. Hence the candidate key is {AD}.

 

The relation is not is 2 NF since partial dependency exits.

 {A, D} ® {B} partially dependency

 

{A, D} ® {C}

 

{A,D} ® {F} Full functional dependency

 

{A,D} ® {E} partial dependency

 

So we decompose our original relations

 

R₁ (A,D,F) Since {A,D} ® {F}

 

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

 

R₃ (D,E) Since {D} ® {E}

 

Example:

 

Let us consider the relation student (Roll No, Name, Address , courseno, cousename, grade)

Roll No	Name	Address	Courseno	Coursename	Grade

 

 

{RollNo}® {Name}

 

{Roll No} ® {Address}

 

{Course no} ® {Course name}

 

{Roll No, Course no} ® {Grade}