# 2NF – Second Normal Form

## 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

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.```

### Incoming search terms:

• 2nf questions