Second Normal Form (2NF)

Second Normal Form (2NF)

Second Normal Form (2NF) is an important stage in database normalization. It builds on the rules of First Normal Form (1NF) and focuses on removing partial dependencies from a relational table.

Normalization helps reduce data redundancy, improve data consistency, and prevent common database anomalies. In this tutorial, we will learn what 2NF is, understand partial dependency with a practical example, and explore why achieving 2NF alone is not always sufficient.

What is Second Normal Form (2NF)?

A relation is said to be in Second Normal Form (2NF) when:

1. The relation is already in First Normal Form (1NF).
2. Every non-key attribute is fully functionally dependent on the entire primary key.

In simple terms, no non-key attribute should depend on only a part of a composite primary key.

If a non-key attribute depends on only one part of a composite key, it is called a partial dependency. Such a dependency must be removed to achieve 2NF.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

What is Partial Dependency?

A partial dependency occurs when a non-key attribute depends on only a portion of a composite primary key rather than the complete key.

For example, suppose a table has a composite primary key consisting of TEACHER_ID and SUBJECT. If TEACHER_AGE depends only on TEACHER_ID, then it is partially dependent on the primary key.

This violates the rules of 2NF.

Example of Second Normal Form

Consider a school database that stores information about teachers and the subjects they teach.

TEACHER Table

TEACHER_ID	SUBJECT	TEACHER_AGE
25	Chemistry	30
25	Biology	30
47	English	35
83	Math	38
83	Computer	38

In this example, the combination of TEACHER_ID and SUBJECT forms the composite primary key.

However, TEACHER_AGE depends only on TEACHER_ID. The teacher’s age does not depend on the subject being taught.

Therefore:

• TEACHER_ID + SUBJECT → identifies a teaching assignment.
• TEACHER_ID → determines TEACHER_AGE.

This means TEACHER_AGE has a partial dependency on the composite key, so the table is not in 2NF.

Converting the Table into 2NF

To remove the partial dependency, we decompose the original table into two separate tables.

TEACHER_DETAIL Table

TEACHER_ID	TEACHER_AGE
25	30
47	35
83	38

Here, TEACHER_ID is the primary key, and TEACHER_AGE depends completely on it.

TEACHER_SUBJECT Table

TEACHER_ID	SUBJECT
25	Chemistry
25	Biology
47	English
83	Math
83	Computer

In this table, the combination of TEACHER_ID and SUBJECT identifies each teaching assignment.

By decomposing the original relation, the partial dependency is eliminated, and the resulting relations satisfy the requirements of 2NF.

Anomalies That Can Still Exist in 2NF

Achieving 2NF does not automatically eliminate every type of database anomaly. A table can satisfy 2NF and still contain transitive dependencies, which can lead to insertion, deletion, and update anomalies.

Consider the following STUDENT table:

STUDENT Table

Stu_Id	Stu_Name	Teach_Id	Teach_Name	Teach_Qual
2523	Anurag	201	Mohan	MCA
3712	Raju	202	Ravi	M.Tech
4906	Raman	203	Mahima	Ph.D
2716	Jyoti	204	Anjali	MCA
1768	Meetali	205	Sonia	M.Tech

Here, Stu_Id is the primary key. Since the primary key contains only one attribute, partial dependency is not possible. Therefore, assuming the table satisfies 1NF, it satisfies the 2NF requirement.

However, there is still unnecessary dependency between teacher information:

Teach_Id → Teach_Name, Teach_Qual

This means teacher details depend on Teach_Id, not directly on the student. Such dependencies can cause several anomalies.

Insertion Anomaly

Suppose a new teacher, Mayank, joins the college with Teach_Id = 206 and qualification MCA, but no student has been assigned to him yet.

Because teacher information is stored together with student information, we may be forced to insert a record with a missing Stu_Id.

Stu_Id	Stu_Name	Teach_Id	Teach_Name	Teach_Qual
NULL	NULL	206	Mayank	MCA

If Stu_Id cannot be NULL because it is the primary key, the teacher cannot be inserted until a student is assigned.

This is known as an insertion anomaly.

Deletion Anomaly

Suppose student Stu_Id = 1768, Meetali, leaves the college.

If her record is deleted, the information about teacher Sonia (Teach_Id = 205) may also be lost if she has no other students assigned to her.

This means deleting student information unintentionally removes independent teacher information.

This situation is called a deletion anomaly.

Update Anomaly

Suppose teacher Anjali (Teach_Id = 204) changes her qualification from MCA to Ph.D.

If Anjali teaches multiple students, her qualification may appear in multiple rows. Every occurrence must be updated.

If even one row is missed, the database can contain conflicting information about the same teacher.

This is known as an update anomaly.

Why Isn’t 2NF Enough?

2NF is an important improvement over 1NF because it removes partial dependencies. However, it does not eliminate transitive dependencies.

As a result, insertion, deletion, and update anomalies may still occur.

To further improve the database design, we move to Third Normal Form (3NF).

3NF focuses on removing transitive dependencies so that non-key attributes depend directly on the key rather than on another non-key attribute.

2NF vs 1NF

Normal Form	Main Requirement
1NF	Attributes must contain atomic values and repeating groups must be removed.
2NF	Must be in 1NF and must not contain partial dependencies.
3NF	Must be in 2NF and must not contain transitive dependencies.

YT:- DecodeIT

Key Points of 2NF

• A table must first satisfy 1NF.
• 2NF primarily deals with partial dependency.
• Partial dependency occurs when a non-key attribute depends on only part of a composite key.
• If the primary key consists of a single attribute, partial dependency cannot occur.
• Decomposition can be used to remove partial dependencies.
• 2NF does not remove transitive dependencies.
• Further normalization to 3NF may be required to reduce additional anomalies.

Conclusion

Second Normal Form (2NF) is an essential step in relational database normalization. Its primary goal is to eliminate partial dependencies and ensure that every non-key attribute depends on the complete primary key.

By separating attributes that depend on only part of a composite key, we can reduce redundancy and improve data organization. However, 2NF does not solve every database design problem. Transitive dependencies and related anomalies may still remain.

For this reason, database designers commonly continue the normalization process by converting the schema into Third Normal Form (3NF).

Keywords

Second Normal Form (2NF), 2NF in DBMS, Second Normal Form in DBMS, database normalization, 2NF normalization, partial dependency, functional dependency, composite key, 1NF and 2NF, 2NF example, database anomalies, insertion anomaly, deletion anomaly, update anomaly, 3NF, DBMS normalization