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.