Kaushik's Notes

2NF - Second Normal Form

7 min read

Overview

  • Definition: A relation is in 2NF if it’s in 1NF and all non-key attributes are fully functionally dependent on the primary key
  • Purpose: Eliminates partial dependencies
  • Requirement: Must first be in 1NF
  • Focus: Removes dependencies on part of a composite primary key

Key Concepts

Functional Dependency

  • Definition: Attribute B is functionally dependent on attribute A if each value of A determines exactly one value of B
  • Notation: A → B (A determines B)
  • Example: Student_ID → Student_Name

Full Functional Dependency

  • Definition: Non-key attribute depends on the entire primary key, not just part of it
  • Applies To: Composite primary keys (multiple columns)
  • Example: (Student_ID, Course_ID) → Grade

Partial Dependency

  • Definition: Non-key attribute depends on only part of a composite primary key
  • Problem: Causes redundancy and anomalies
  • Example: (Student_ID, Course_ID) → Student_Name (depends only on Student_ID)

Rules for 2NF

Prerequisites

  • Must be in 1NF: All attributes contain atomic values
  • Identify Primary Key: Single or composite primary key

Requirements

  • Full Dependency: Every non-key attribute must be fully functionally dependent on the entire primary key
  • No Partial Dependencies: Non-key attributes cannot depend on just part of the primary key
  • Single-Column Primary Key: Automatically satisfies 2NF if primary key is single column

Violations of 2NF

Example 1: Student Course Enrollment

Table in 1NF but NOT 2NF:

Student_Course Table:
Student_ID | Course_ID | Student_Name | Course_Name | Instructor | Grade
1          | CS101     | John Smith   | Programming | Dr. Brown  | A
1          | CS201     | John Smith   | Database    | Dr. White  | B
2          | CS101     | Mary Jones   | Programming | Dr. Brown  | B
2          | CS301     | Mary Jones   | Networks    | Dr. Green  | A

Primary Key: (Student_ID, Course_ID)

Partial Dependencies:

  • Student_ID → Student_Name (Student_Name depends only on Student_ID)
  • Course_ID → Course_Name (Course_Name depends only on Course_ID)
  • Course_ID → Instructor (Instructor depends only on Course_ID)

Full Dependency:

  • (Student_ID, Course_ID) → Grade (Grade depends on both Student_ID and Course_ID)

Converting to 2NF

Example 1: Student Course System

Before (1NF but not 2NF):

Student_Course Table:
Student_ID | Course_ID | Student_Name | Course_Name | Instructor | Grade
1          | CS101     | John Smith   | Programming | Dr. Brown  | A
1          | CS201     | John Smith   | Database    | Dr. White  | B
2          | CS101     | Mary Jones   | Programming | Dr. Brown  | B

After (2NF):

Students Table:
Student_ID | Student_Name
1          | John Smith
2          | Mary Jones

Courses Table:
Course_ID | Course_Name | Instructor
CS101     | Programming | Dr. Brown
CS201     | Database    | Dr. White

Enrollments Table:
Student_ID | Course_ID | Grade
1          | CS101     | A
1          | CS201     | B
2          | CS101     | B

Example 2: Order Management System

Before (1NF but not 2NF):

Order_Details Table:
Order_ID | Product_ID | Customer_Name | Product_Name | Unit_Price | Quantity
1001     | P1         | John Smith    | Laptop       | 1000       | 2
1001     | P2         | John Smith    | Mouse        | 25         | 3
1002     | P1         | Mary Jones    | Laptop       | 1000       | 1

Primary Key: (Order_ID, Product_ID)

Partial Dependencies:

  • Order_ID → Customer_Name
  • Product_ID → Product_Name
  • Product_ID → Unit_Price

After (2NF):

Orders Table:
Order_ID | Customer_Name
1001     | John Smith
1002     | Mary Jones

Products Table:
Product_ID | Product_Name | Unit_Price
P1         | Laptop       | 1000
P2         | Mouse        | 25

Order_Items Table:
Order_ID | Product_ID | Quantity
1001     | P1         | 2
1001     | P2         | 3
1002     | P1         | 1

Example 3: Employee Project Assignment

Before (1NF but not 2NF):

Employee_Project Table:
Emp_ID | Project_ID | Emp_Name | Emp_Dept | Project_Name | Hours_Worked
101    | P1         | John     | IT       | Website      | 40
101    | P2         | John     | IT       | Database     | 30
102    | P1         | Mary     | HR       | Website      | 35
103    | P2         | Bob      | IT       | Database     | 45

Primary Key: (Emp_ID, Project_ID)

Partial Dependencies:

  • Emp_ID → Emp_Name
  • Emp_ID → Emp_Dept
  • Project_ID → Project_Name

After (2NF):

Employees Table:
Emp_ID | Emp_Name | Emp_Dept
101    | John     | IT
102    | Mary     | HR
103    | Bob      | IT

Projects Table:
Project_ID | Project_Name
P1         | Website
P2         | Database

Employee_Projects Table:
Emp_ID | Project_ID | Hours_Worked
101    | P1         | 40
101    | P2         | 30
102    | P1         | 35
103    | P2         | 45

Real-World Examples

Library Management System

Before (Not 2NF):

Book_Loans Table:
Member_ID | Book_ID | Member_Name | Member_Address | Book_Title | Author | Loan_Date
M001      | B001    | John Smith  | 123 Main St    | Database   | Author1| 2024-01-15
M001      | B002    | John Smith  | 123 Main St    | Networks   | Author2| 2024-01-16
M002      | B001    | Mary Jones  | 456 Oak Ave    | Database   | Author1| 2024-01-17

After (2NF):

Members Table:
Member_ID | Member_Name | Member_Address
M001      | John Smith  | 123 Main St
M002      | Mary Jones  | 456 Oak Ave

Books Table:
Book_ID | Book_Title | Author
B001    | Database   | Author1
B002    | Networks   | Author2

Loans Table:
Member_ID | Book_ID | Loan_Date
M001      | B001    | 2024-01-15
M001      | B002    | 2024-01-16
M002      | B001    | 2024-01-17

Inventory Management

Before (Not 2NF):

Purchase_Details Table:
Purchase_ID | Item_ID | Supplier_Name | Supplier_Contact | Item_Name | Item_Category | Quantity | Unit_Price
P001        | I001    | ABC Corp      | 123-456-7890     | Laptop    | Electronics   | 10       | 1000
P001        | I002    | ABC Corp      | 123-456-7890     | Mouse     | Electronics   | 20       | 25
P002        | I001    | XYZ Ltd       | 987-654-3210     | Laptop    | Electronics   | 5        | 950

After (2NF):

Suppliers Table:
Supplier_ID | Supplier_Name | Supplier_Contact
S001        | ABC Corp      | 123-456-7890
S002        | XYZ Ltd       | 987-654-3210

Items Table:
Item_ID | Item_Name | Item_Category
I001    | Laptop    | Electronics
I002    | Mouse     | Electronics

Purchases Table:
Purchase_ID | Supplier_ID | Purchase_Date
P001        | S001        | 2024-01-15
P002        | S002        | 2024-01-16

Purchase_Details Table:
Purchase_ID | Item_ID | Quantity | Unit_Price
P001        | I001    | 10       | 1000
P001        | I002    | 20       | 25
P002        | I001    | 5        | 950

Benefits of 2NF

Eliminates Redundancy

  • Reduced Storage: Eliminates duplicate data storage
  • Consistency: Single source of truth for each piece of information
  • Example: Student name stored once instead of in every enrollment record

Prevents Anomalies

Insert Anomaly Prevention

  • Before 2NF: Cannot add course without student enrollment
  • After 2NF: Can add courses independently in Courses table

Update Anomaly Prevention

  • Before 2NF: Must update student name in multiple places
  • After 2NF: Update student name once in Students table

Delete Anomaly Prevention

  • Before 2NF: Deleting last enrollment loses course information
  • After 2NF: Course information preserved in Courses table

Improved Data Integrity

  • Referential Integrity: Foreign key constraints ensure data consistency
  • Easier Validation: Validate data in single location
  • Reduced Errors: Less chance of inconsistent data

Implementation Steps

Step 1: Ensure 1NF

  • Verify table is in First Normal Form
  • All attributes contain atomic values
  • Primary key identified

Step 2: Identify Composite Primary Key

  • Check if primary key consists of multiple columns
  • If single column, table automatically satisfies 2NF

Step 3: Find Partial Dependencies

  • List all non-key attributes
  • Determine which part of primary key each depends on
  • Identify attributes that don’t depend on entire primary key

Step 4: Create New Tables

  • Create separate table for each partial dependency
  • Move partially dependent attributes to new tables
  • Use appropriate part of original primary key as foreign key

Step 5: Verify Results

  • Ensure no partial dependencies remain
  • Check that all non-key attributes fully depend on primary key
  • Validate data integrity with foreign key relationships

Common Scenarios

Single Column Primary Key

✅ AUTOMATICALLY 2NF:
Employee Table:
Emp_ID | Name | Department | Salary
(Primary Key: Emp_ID - single column)

Composite Key with Full Dependencies

✅ ALREADY 2NF:
Grades Table:
Student_ID | Course_ID | Grade | Semester
(All non-key attributes depend on both Student_ID AND Course_ID)

Composite Key with Partial Dependencies

❌ NEEDS NORMALIZATION:
Course_Enrollment Table:
Student_ID | Course_ID | Student_Name | Course_Name | Grade
(Student_Name depends only on Student_ID)
(Course_Name depends only on Course_ID)

Summary

  • Purpose: Eliminates partial dependencies from composite primary keys
  • Requirement: Must be in 1NF first
  • Process: Move partially dependent attributes to separate tables
  • Benefit: Reduces redundancy, prevents anomalies, improves data integrity
  • Next Step: Check for transitive dependencies (3NF)

Graph View

Backlinks