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
- 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 |
|
 |
| 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) |
|
 |
| 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.
]]>