Second Normal Form

In this lesson, we will discuss how to decompose a table into second normal form and see some examples.

We'll cover the following

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

Get hands-on with 1200+ tech skills courses.