DBMS – Second Normal Form (2NF)

A relation is said to be in 2NF if it satisfy both the following conditions:

• Relation must be in 1NF (First normal form)
• No non-prime attribute is dependent on the proper subset of any candidate key of table.

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

Example: Let’s say a School store the teachers data in TEACHER table, which looks like this:

TEACHER_ID	SUBJECT	TEACHER_AGE
I101	MATHS	45
I101	PHYSICS	45
I102	MATHS	45
I103	PHYSICS	42
I103	ENGLISH	42

Here we have multiple teachers with same TEACHER_AGE also there are different teachers who teach the same subject.

Here using TEACHER_AGE alone, we cannot determine the value of SUBJECT or TEACHER_ID.

TEACHER_AGE together with SUBJECT cannot determine the value of TEACHER_ID as there are multiple different teachers with same TEACHER_AGE and SUBJECT.

TEACHER_AGE together with TEACHER_ID cannot determine the value of SUBJECT as same teacher teaches multiple subjects.

Hence the table has only one candidate key which is {TEACHER_ID, SUBJECT}, also the attribute TEACHER_AGE is a non-prime attribute as it doesn’t belong to the candidate key.

But, TEACHER_ID -> TEACHER_AGE, i.e., TEACHER_AGE is dependent on TEACHER_ID, which is a proper subset of the candidate key. Non-prime attribute TEACHER_AGE is dependent on a proper subset of the candidate key, which is 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 tables such as :
Table 1: TEACHER_ID, TEACHER_AGE
Table 2: TEACHER_ID, SUBJECT

Table 1:
TEACHER_ID      TEACHER_AGE
I101            45
I102            45
I103            42

Table 2:
TEACHER_ID      SUBJECT
I101            MATHS
I101            PHYSICS
I102            MATHS
I103            PHYSICS
I103            ENGLISH

Now these relations are in 2NF. Second normal form helped us to reduce the redundancy in the original table. If multiple teaches are teaching multiple subjects, we need not to create multiple rows with the same data.