2NF – Second Normal Form :

Relation R is in Second Normal Form (2NF) only iff :

  1. R should be in 1NF and
  2. R should not contain any Partial Dependency
What is a Partial Dependency ?
Let R be a relational Schema and X,Y,A be the attribute sets over R.
X: Any Candidate Key
Y: Proper Subset of Candidate Key
A: Non Key AttributeIf Y → A exists in R, then R is not in 2 NF.
(Y → A) is a Partial dependency only if

  • Y: Proper subset of Candidate Key
  • A: Non Prime Attribute

Removal of Partial Dependency 

If there is any partial dependency, remove partially dependent attributes from original table, place them in a separate table along with a copy of its determinant.

Example 1 :

Consider the relation Student(SID, Sname, Cname) which is in 1 NF (No Multi-Valued-Attributes) :

Student :
SID Sname Cname
S1 A C
S1 A C++
S2 B C++
S2 B DB
S3 A DB
{SID,Cname} : Primary Key
Functional Dependencies:
{SID,Cname} → Sname
SID → Sname
 Student 2NF
Partial Dependencies :
SID → Sname {as SID is a Proper Subset of Candidate Key {SID,Cname}.
Solution : Removal of Partial Dependency by creating separate table
R1 :
SID Sname
S1 A
S2 B
S3 A

SID : Primary Key

R2 :
SID Cname
S1 C
S1 C++
S2 C++
S2 DB
S3 DB

{SID,Cname} : Primary Key

The above two relations R1 and R2 
1. Lossless Join
2. 2NF
3. Dependency Preserving
Example 2:

Consider the relation Supplier(SID, Status, City, PID, Qty) which is in 1 NF (No Multi-Valued-Attributes) :

Supplier :
SID Status City PID Qty
S1 30 Delhi P1 100
S1 30 Delhi P2 125
S1 30 Delhi P3 200
S1 30 Delhi P4 130
S2 10 Karnal P1 115
S2 10 Karnal P2 250
S3 40 Rohtak P1 245
S4 30 Delhi P4 300
S4 30 Delhi P5 315
Key : (SID, PID)
 Supplier FDD 1NF- Normal Forms
Partial Dependencies :
SID → Status
SID → City
Solution :
Removal of Partial Dependency by creating separate table
Supplier_City 2NF Supplier_Qty 2NF
Drawback of 2NF

Anomalies in Relation { Sup_City } :

  • Deletion Anomaly – If we delete a tuple in Sup_City, then we not only loose the information about a supplier, but also loose the status value of a particular city.
  • Insertion Anomaly – We cannot insert a City and its status until a supplier supplies atleast one part.
  • Updation Anomaly – If the status value for a city is changed, then we will face the problem of searching every tuple for that city.

Possibilities of Redundancy in 2NF

However, there is less redundancy in 2NF rather than in 1 NF, but 2NF is not free from redundancy.
The possibilities of redundancy in 2NF are :

Rdundancy in 2NF

These two are the possibilities in 2NF which forms redundancy.

The example of (i) is in the Sup_City relation :
City → Status {Non Key Attribute → Non Key Attribute}

The example of (ii) is in the STUDENT relation :
SID → Cname {Proper Subset of 1 CK → Proper Subset of other CK}
Some Points regarding 2NF :

The table is automatically in 2NF if primary key consists of only one
attribute or all attributes are part of primary key or table consists 
of only two attributes.

Previous Home Next
1NF – First Normal Form 3NF – Third Normal Form

     

Incoming search terms:

  • 2nf questions
  • advantage of the 2NF
  • questionof 2nf

Leave a Reply