Summary of All Normal Forms

Jul 14, 2024

Summary of All Normal Forms

Introduction

  • Summary of all Normal Forms: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF
  • Detailed videos available separately
  • Real-life examples provided to understand scenarios in each Normal Form
  • Useful for exams and interviews

First Normal Form (1NF)

  • Condition: No Multi-Valued Attribute (Atomic/Single Value Attribute)
  • Example:
    • Roll number with multiple courses (e.g., "1, C C++") is not in 1NF
  • Solution: Split multi-valued attribute into different rows or tables

Second Normal Form (2NF)

  • Condition:
    • Table must be in 1NF
    • No Partial Dependency
  • Explanation:
    • AB (candidate key) determines C
    • No subset of candidate key should partially determine a non-unique attribute
  • Example: Scenario of a company with co-founders A and B

Third Normal Form (3NF)

  • Condition:
    • Table must be in 2NF
    • No Transitive Dependency
  • Explanation:
    • X (candidate key) determines Y (non-unique)
    • Y determines Z (non-unique)
    • X indirectly determines Z (transitively)
  • Problem: Y being non-prime causes issues
  • Example: Classroom scenario with topper X, friend Y, and another friend Z

Boyce-Codd Normal Form (BCNF)

  • Condition:
    • Table must be in 3NF
    • Left side of every dependency must be a candidate key or super key
  • Goal: Eliminate redundancy completely

Fourth Normal Form (4NF)

  • Condition:
    • Table must be in BCNF
    • No Multi-Valued Dependency
  • Explanation: Example with person "Varun" having multiple phone numbers and emails
  • Solution: Decompose into separate tables to avoid redundancy

Fifth Normal Form (5NF)

  • Condition:
    • Table must be in 4NF
    • Lossless Decomposition
  • Goal: Avoid lossy decomposition and spurious tuples
  • Solution: Ensure common attribute is a candidate key

Conclusion

  • Basic summary of all normal forms provided
  • Detailed explanations available through provided links in the description