Second Normal Form

Second normal form (2NF)

To be in second normal form, a relation must be in first normal form (1NF) and it must not contain any partial dependencies. So a relation is in 2NF as long as it has no partial dependencies, i.e., no non-prime attributes (attributes which are not part of any candidate key) is dependent on any proper subset of a composite primary key of the table.

Example

STUDENT Relation

Stud_Id	Course_Id	Course_Fee
1	C1	1000
2	C2	1500
1	C4	2000
4	C3	1000
4	C1	1000
2	C5	3000

We can determine a few things by looking at the table above. First of all, Course_Fee alone cannot be used to identify each tuple uniquely. Furthermore, the combination of Course_Fee together with Stud_Id or Course_Id also cannot be used to uniquely identify each tuple. Hence, Course_Fee would be a non-prime attribute, as it does not belong to the composite primary key {Stud_Id, Course_Id}.

However, from the table, it is evident that Course_Id $\rightarrow$ Course_Fee , i.e., Course_fee is dependent on Course_Id only, which is a proper subset of the primary key. This results in a partial dependency and so this relation is not in 2NF.

To convert the above relation to 2NF, we need to split the table into two other tables such as:

• Table 1: Stud_Id, Course_Id

• Table 2: Course_Id, Course_Fee