2NF – Second Normal Form :
Relation R is in Second Normal Form (2NF) only iff :
 R should be in 1NF and
 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

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 MultiValuedAttributes) :
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 MultiValuedAttributes) :


Partial Dependencies : SID → Status SID → City 

Solution : Removal of Partial Dependency by creating separate table ⇓ 

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 :
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:
 advantages and disadvantages of 2nf