A realtion(table) is said to be in 2NF if it satifies the following condition:
- Table is in 1NF (First normal form)
- In the second normal form, all non-key attributes are fully functionally dependent on the primary key.
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 subjects they teach. They create a table that looks like this: Since a teacher can teach more than one subjects, the table can have multiple rows for a same teacher.
| teacher_id | subject code | teacher_age |
| 111 | CSE 013 | 38 |
| 111 | EEC 021 | 38 |
| 222 | MCA 502 | 38 |
| 333 | MEE 024 | 40 |
| 333 | CHE 011 | 40 |
Candidate Keys: {teacher_id, subject code}
Non prime attribute: teacher_age
The table is in 1 NF because each attribute has atomic values. However, it is not in 2NF because non prime attribute teacher_age is dependent on teacher_id alone which is a proper subset of candidate key. This violates the rule for 2NF as the rule says “no non-prime attribute is dependent on the proper subset of any candidate key of the table”.
To convert the given table into 2NF, we decompose it into two tables:
teacher_details table:
| teacher_id | teacher_age |
| 111 | 38 |
| 222 | 38 |
| 333 | 40 |
teacher_subject table:
| teacher_id | subject |
| 111 | CSE 013 |
| 111 | EEC 021 |
| 222 | MCA 502 |
| 333 | MEE 024 |
| 333 | CHE 011 |
Now the tables are in Second normal form (2NF).
To know about
