Second Normal Form (2NF)

Definition

A relation is in 2NF if:

1. It's in 1NF

2. Every non-prime attribute is fully functionally dependent on the primary key or No partial dependency

Key Concepts

• Prime Attribute: Part of any candidate key

• Partial Dependency: Non-prime attribute depends on only part of a composite key

Example Problem

CopyCourseEnrollment Table (1NF but not 2NF):
┌───────────┬──────────┬─────────────┬────────────┬────────────┐
│ StudentID │ CourseID │ StudentName │ CourseName │ Instructor │
├───────────┼──────────┼─────────────┼────────────┼────────────┤
│    101    │    C1    │    Alice    │    Math    │  Prof. A   │
│    102    │    C2    │     Bob     │  Physics   │  Prof. B   │
│    101    │    C2    │    Alice    │  Physics   │  Prof. B   │
│    103    │    C1    │    Carol    │    Math    │  Prof. A   │
└───────────┴──────────┴─────────────┴────────────┴────────────┘

Primary Key: (StudentID, CourseID)

Identifying Partial Dependencies

• StudentName depends only on StudentID (partial dependency)

• CourseName, Instructor depend only on CourseID (partial dependency)

Converting to 2NF

1. Student Table:

Copy┌───────────┬─────────────┐
│ StudentID │ StudentName │
├───────────┼─────────────┤
│    101    │    Alice    │
│    102    │     Bob     │
│    103    │    Carol    │
└───────────┴─────────────┘

2. Course Table:

Copy┌──────────┬────────────┬────────────┐
│ CourseID │ CourseName │ Instructor │
├──────────┼────────────┼────────────┤
│    C1    │    Math    │  Prof. A   │
│    C2    │  Physics   │  Prof. B   │
└──────────┴────────────┴────────────┘

3. Enrollment Table:

Copy┌───────────┬──────────┐
│ StudentID │ CourseID │
├───────────┼──────────┤
│    101    │    C1    │
│    102    │    C2    │
│    101    │    C2    │
│    103    │    C1    │
└───────────┴──────────┘

Note: Tables with single-attribute primary keys are automatically in 2NF if they're in 1NF.