2NF and 3NF

Removing partial and transitive dependencies.

Second Normal Form (2NF)

A table is in 2NF if it's in 1NF AND every non-key column is fully functionally dependent on the entire primary key. This only matters for tables with composite primary keys.

Violation of 2NF


  NOT in 2NF: Partial dependency
  Enrollments table (PK: StudentID + CourseID)
  ┌─────────┬─────────┬──────┬──────────────────┬───────────┐
  │StudentID│CourseID │Grade │ StudentName      │ CourseTitle│
  ├─────────┼─────────┼──────┼──────────────────┼───────────┤
  │ 1       │ 101     │ A    │ Alice            │ Databases │
  │ 2       │ 101     │ B    │ Bob              │ Databases │
  └─────────┴─────────┴──────┴──────────────────┴───────────┘

  Problem:
  • StudentName depends only on StudentID (not CourseID)
  • CourseTitle depends only on CourseID (not StudentID)
  • These are PARTIAL dependencies — they depend on part of the PK

Converting to 2NF


  IN 2NF: Remove partial dependencies

  Enrollments              Students            Courses
  ┌─────────┬─────────┐   ┌────┬─────────┐   ┌────┬────────┐
  │StudentID│CourseID │   │ ID │ Name    │   │ ID │ Title  │
  │ Grade   │         │   ├────┼─────────┤   ├────┼────────┤
  ├─────────┼─────────┤   │ 1  │ Alice   │   │101 │ DB     │
  │ 1       │ 101     │   │ 2  │ Bob     │   │    │        │
  │ 2       │ 101     │   └────┴─────────┘   └────┴────────┘
  └─────────┴─────────┘

  StudentName → Students table (depends on StudentID)
  CourseTitle → Courses table (depends on CourseID)
  Grade stays in Enrollments (depends on both PK columns)

Third Normal Form (3NF)

A table is in 3NF if it's in 2NF AND there are no transitive dependencies — non-key columns should not depend on other non-key columns.


  NOT in 3NF: Transitive dependency
  Students
  ┌────┬─────────┬────────┬──────────────┐
  │ ID │ Name    │ DeptID │ DeptName     │
  ├────┼─────────┼────────┼──────────────┤
  │ 1  │ Alice   │ 10     │ Computer Sci │
  └────┴─────────┴────────┴──────────────┘

  Problem: DeptName depends on DeptID (not directly on ID)
  ID → DeptID → DeptName (transitive!)

  Fix: Move DeptName to a separate Departments table
  Students              Departments
  ┌────┬────────┬──────┐  ┌────┬──────────────┐
  │ ID │ Name   │DeptID│  │ ID │ Name         │
  ├────┼────────┼──────┤  ├────┼──────────────┤
  │ 1  │ Alice  │ 10   │  │ 10 │ Computer Sci │
  └────┴────────┴──────┘  └────┴──────────────┘

← Previous First Normal Form (1NF)

Next → Normalization Examples