Second Normal Form (2NF)

Concept

A table is in Second Normal Form (2NF) if:

1. It is already in 1NF.
2. Every non-key column is fully dependent on the entire Primary Key. (There are no Partial Dependencies).

Note: If your table has a single-column Primary Key (like an auto-incrementing id), and it is in 1NF, it is automatically in 2NF. 2NF only applies to tables that have a Composite Primary Key (a primary key made of two or more columns).

The Violation

Imagine a university system that tracks which student is enrolled in which course, and how much the course costs.
The Primary Key is a composite of (StudentID, CourseID).

UNNORMALIZED (Violates 2NF):

StudentID (PK)	CourseID (PK)	Grade	Course_Fee
1	CS101	A	$500
1	MATH20	B	$300
2	CS101	C	$500

The Partial Dependency:

• The Grade column relies on the entire Primary Key. (You only get a grade if a specific student takes a specific course). This is perfectly fine.
• The Course_Fee column relies only on the CourseID. It has absolutely nothing to do with the StudentID. This is a Partial Dependency.

Why is this bad?

It causes massive duplication. Every time a new student enrolls in CS101, you have to type the “$500" fee again. If the university raises the fee to$600, you have to update thousands of rows, risking an Update Anomaly.

The Fix

To achieve 2NF, you must extract the partially-dependent columns into a new table.

TABLE 1: Enrollments (The Junction Table)

StudentID (PK)	CourseID (PK)	Grade
1	CS101	A
1	MATH20	B
2	CS101	C

TABLE 2: Courses

CourseID (PK)	Course_Fee
CS101	$500
MATH20	$300

Now, the course fee is defined exactly once in the database.

Interview Questions

Q: A table has a single-column Primary Key: order_id. Can this table ever violate Second Normal Form?
A: No. By mathematical definition, 2NF only targets “Partial Dependencies” (where a column relies on only a part of the Primary Key). If the Primary Key consists of only one column, it cannot be divided into parts. Therefore, any table with a single-column Primary Key that is in 1NF is automatically in 2NF. (However, it might still violate 3NF).

Q: In the unnormalized example above, what happens if the university decides to delete the MATH20 course because no students enrolled in it this semester?
A: This exposes a Deletion Anomaly.
Because the Course_Fee is stored directly inside the student enrollment table, if zero students are enrolled in MATH20, there are zero rows containing MATH20. The database completely loses the fact that MATH20 costs $300. By normalizing the data into 2NF (creating a separate Courses table), the course and its fee can exist independently of whether any students are currently enrolled in it.