Database develop. life cycle - Second Normal Form (2NF)

Second Normal Form (2NF)

1. Definition

A table is in 2NF if:

  1. It is already in 1NF.

  2. Every non-key attribute is fully functionally dependent on the whole primary key (no partial dependency).

In simple words:

  • If the primary key is composite (made of two or more columns), then non-key attributes must depend on the entire key, not just part of it.

2. Example – Table in 1NF

Let’s take the Student-Course Enrollment example (from 1NF):

StudentID Name Phone Course
101 Alice 555-1234 DBMS
101 Alice 555-5678 Networks
102 Bob 555-8765 DBMS

  • Here, the composite primary key could be (StudentID, Course), since a student can take multiple courses.

3. Problem (Partial Dependency)

  • Name and Phone depend only on StudentID, not on the combination (StudentID, Course).

  • This violates 2NF because Name and Phone are partially dependent on the key.

4. Transforming to 2NF

We separate the table into two (eliminating partial dependencies):

Student Table

StudentID Name Phone
101 Alice 555-1234
101 Alice 555-5678
102 Bob 555-8765

Enrollment Table

StudentID Course
101 DBMS
101 Networks
102 DBMS

5. Now in 2NF

  • Name and Phone depend only on StudentID (stored in Student table).

  • Course enrollment depends on (StudentID, Course) (stored in Enrollment table).

  • No more partial dependency.

6. Why 2NF Matters

  • Prevents data redundancy.

  • Example: Without 2NF, if a student changes their phone number, it must be updated in multiple rows.

  • Makes updates and deletions consistent.