Beginner's Guide to Database Normalization: 1NF, 2NF, and 3NF

Introduction

This lecture provides an introduction to the first, second, and third normal forms (1NF, 2NF, 3NF) in database normalization, aimed at beginners.

First Normal Form (1NF)

1NF requires that:

• Uniqueness of Rows: Each row in a table must be unique. No duplicate rows are allowed.
• Atomic Values: Each cell should contain a single atomic value. No cell should contain a list or composite data.
• Non-divisibility: Values should not be further divisible into multiple components.

Example for 1NF

• Problem: Duplicate rows and cells with multiple values.
  • Example Table: Takeaway orders with columns for Customer Name and Order.
  • Duplicate Example: Bob Jones ordering "burger, fries, coke" twice cannot be distinguished.
• Solution: Add an order ID to make rows unique and split multiple values into separate rows.
  • Introduce separate table:
    • Order ID | Item
    • 1 | Burger
    • 1 | Fries
    • 1 | Coke
• Atomicity: Split composite values (e.g., names) into separate columns:
  • First Name | Last Name
  • Bob | Jones

Second Normal Form (2NF)

2NF requires:

• No Partial Dependencies: All non-prime attributes must be fully functionally dependent on the whole candidate key.

Example for 2NF

• Problem: Partial dependency in a table of student and course information.
  • Example Table: Student ID, Course ID, Course Fee.
  • Issue: Course Fee depends only on Course ID, not Student ID.
• Solution: Separate table for course fees:
  • Course ID | Course Fee
  • 1 | 500
  • 2 | 1000
  • 3 | 750
• Revised student-course table without course fees.

Third Normal Form (3NF)

3NF requires:

• No Transitive Dependencies: All fields must be directly dependent only on the primary key.

Example for 3NF

• Problem: Transitive dependency in a tournaments table.
  • Example Table: Tournament Name, Year, Winner, Winner's Date of Birth.
  • Issue: Winner's Date of Birth can be determined by Winner's Name.
• Solution: Separate table for winners:
  • Winner's Name | Date of Birth
  • Separate main table with Tournament Name and Year determining the Winner.

Conclusion

1NF, 2NF, and 3NF are essential steps in database normalization to eliminate redundancies and ensure data integrity. Following these forms helps maintain a clean and efficient database structure.

Additional Notes

• Subscribe to the channel for more content and suggestions for further topics are welcomed in the comments. Good luck!