2NF

Definition

A relation is in Second Normal Form (2NF) if it is already in 1NF, and every non-key attribute is fully functionally dependent on the entire candidate key — i.e., no partial dependency (21.7) exists.

2NF is only a meaningful concern for relations with a composite candidate key. A relation with a single-attribute key is automatically in 2NF once it's in 1NF (there's no proper subset of a one-attribute key to be partially dependent on).

Diagnosing the Violation

StudentCourse(StudentID, StudentName, CourseID, CourseName, InstructorID, InstructorName, InstructorOffice, EnrollmentDate) is in 1NF (21.12) but not in 2NF, because its candidate key is the composite {StudentID, CourseID} (21.10), and several non-key attributes are only partially dependent on it (21.7):

• StudentName depends on StudentID alone.
• CourseName, InstructorID, InstructorName, InstructorOffice all depend on CourseID alone.

Only EnrollmentDate is fully dependent on the whole pair (21.6).

The Decomposition

Split the table into three relations, each keyed by exactly the attribute(s) its content truly depends on:

Student(StudentID, StudentName)
Course(CourseID, CourseName, InstructorID, InstructorName, InstructorOffice)
Enrollment(StudentID, CourseID, EnrollmentDate)

with Enrollment.StudentID a foreign key into Student, and Enrollment.CourseID a foreign key into Course.

Result:

• StudentName ("Asha") is now stored once, in one Student row, regardless of how many courses she takes.
• CourseName/InstructorID/InstructorName/InstructorOffice ("Database Systems", "Dr. Rao", "Room 204") are now stored once, in one Course row, regardless of how many students enroll.
• Enrollment keeps only the genuinely fully-key-dependent fact, EnrollmentDate.

This directly fixes the insertion anomaly (Dr. Fernandez can now be inserted into Course with no enrollment needed — though note: this is only fully solved once Instructor is split out too, see 21.14) and the redundancy of StudentName/CourseName from 21.1.

What 2NF Does NOT Fix Yet

Look closely at the new Course relation: InstructorName and InstructorOffice are fully dependent on CourseID (2NF is satisfied — CourseID is a single-attribute key here, so there's no partial dependency possible). But they are only reached transitively, through InstructorID (CourseID -> InstructorID -> InstructorOffice, see 21.8). The update anomaly for changing an instructor's office (across all their courses) and the insertion anomaly for a courseless instructor still exist inside this Course table. 2NF is a real improvement, but it is not the end of the story — that's exactly what 3NF (21.14) addresses next.

Edge Cases

• 2NF must be checked against every candidate key of the relation, not just the primary key, if multiple candidate keys exist.
• A relation already keyed by a single surrogate attribute (e.g., an EnrollmentID primary key with {StudentID, CourseID} merely a unique constraint) is automatically in 2NF with respect to that surrogate key — but if {StudentID, CourseID} is also a candidate key, partial dependency must still be checked against it too.

Key Takeaways / Interview Angle

• Q: State 2NF in one sentence. 1NF, plus no non-key attribute is partially dependent on any candidate key.
• Q: Why is 2NF only relevant with composite keys? Because partial dependency, by definition, requires a proper subset of the key to exist — impossible with a single-attribute key.
• Q: What decomposition achieves 2NF here? Splitting into Student(StudentID, StudentName), Course(CourseID, CourseName, InstructorID, InstructorName, InstructorOffice), and Enrollment(StudentID, CourseID, EnrollmentDate).