Second Normal Form (2NF)

 .

Second Normal Form (2NF)

A table (Relation) is said to be in 2NF if and only if:

1. It is in 1NF.
2. Every non-prime attribute is fully dependent on the primary keys of the relation.

OR

No non-prime attribute is partially dependent the proper subset if any candidate key of the table.

An attribute that is not part of any candidate key is known as non prime attribute.

Example

Suppose a school wants to store the data of teachers and the subject they tech. They create a table that looks like following.

Candidate key: {Employee_id, Subjects}

                            Non-prime attribute: teacher_age

The table is in 1NF because each attribute has atomic value. However, it is not in 2NF because non-prime attribute teacher age is partially dependent on proper subject of candidate key, ie Non-prime attribute this violet the rule for 2NF as the rule says, "No non-prime attribute is partially dependent the proper subject of any candidate key of the table".

We can convert the above table in 2NF by decomposing this relation as below.

Subject_detail