9.2 Normal forms (1NF, 2NF, 3NF, BCNF)

Normal forms are essential rules for organizing databases efficiently. They help eliminate redundancy and ensure data integrity. From 1NF to BCNF, each form builds upon the previous one, creating increasingly structured and reliable database designs.

Understanding normal forms is crucial for creating robust databases. By applying these rules, you can minimize data anomalies, improve data consistency, and make your databases more flexible and easier to maintain over time.

Normal Forms

First Normal Form (1NF)

• Ensures all attributes in a relation are atomic (single-valued)
• Requires each column to contain only one value per row (no repeating groups)
• Eliminates duplicate columns from the same table
• Identifies each record uniquely using a primary key
• Example: A table storing customer orders with columns

  OrderID

  ,

  CustomerName

  ,

  Product

  , and

  Quantity

  is in 1NF if each column contains only one value per row and

  OrderID

  is the primary key

Second and Third Normal Forms (2NF and 3NF)

• 2NF builds upon 1NF and requires all non-key attributes to depend on the entire primary key
  • If the primary key is a composite key, no non-key attribute should depend on only a part of the key
  • Eliminates partial dependencies
  • Example: In a table with columns

    StudentID

    ,

    CourseID

    , and

    CourseInstructor

    ,

    CourseInstructor

    should depend on both

    StudentID

    and

    CourseID

    , not just

    CourseID

• 3NF builds upon 2NF and requires no transitive dependencies
  • A non-key attribute should not depend on another non-key attribute
  • Ensures all non-key attributes depend directly on the primary key
  • Example: In a table with columns

    StudentID

    ,

    StudentName

    , and

    StudentAddress

    ,

    StudentAddress

    should depend directly on

    StudentID

    , not on

    StudentName

Boyce-Codd Normal Form (BCNF)

• Stricter version of 3NF
• Requires every determinant (attribute that determines the value of another attribute) to be a candidate key
• Ensures no functional dependencies between non-key attributes
• Example: In a table with columns

  StudentID

  ,

  CourseID

  ,

  CourseInstructor

  , and

  InstructorDepartment

  , if

  CourseInstructor

  determines

  InstructorDepartment

  , then

  CourseInstructor

  must be a candidate key for the table to be in BCNF

Key Concepts

Keys in Relational Databases

• Primary key uniquely identifies each record in a table
  • Can be a single attribute or a combination of attributes (composite key)
  • Must be unique and not null
  • Example:

    StudentID

    in a

    Students

    table
• Candidate key is an attribute or set of attributes that can uniquely identify a record
  • A table can have multiple candidate keys
  • The chosen candidate key becomes the primary key
  • Example:

    Email

    and

    StudentID

    can both be candidate keys in a

    Students

    table
• Superkey is a set of attributes that can uniquely identify a record
  • Includes candidate keys and additional attributes
  • Example:

    {StudentID, StudentName}

    is a superkey in a

    Students

    table, but

    StudentName

    is not necessary for unique identification

Atomic Values

• Atomic values are single, indivisible values that cannot be further subdivided
• Ensuring atomic values is a requirement for 1NF
• Example: A

  Name

  column should be divided into

  FirstName

  and

  LastName

  columns to ensure atomic values
• Non-atomic values can lead to data integrity issues and make querying and updating data more difficult

Data Integrity Issues

Data Redundancy

• Occurs when the same data is stored in multiple places (tables or columns)
• Can lead to inconsistencies and anomalies when updating, inserting, or deleting data
• Normalization helps reduce data redundancy by organizing data into separate tables based on dependencies
• Example: Storing

  StudentAddress

  in both

  Students

  and

  Enrollment

  tables can lead to redundancy and inconsistencies

Data Anomalies

• Inconsistencies or errors that can occur due to poor database design or lack of normalization
• Update anomalies occur when data is updated in one place but not in others, leading to inconsistencies
  • Example: Updating a student's address in the

    Students

    table but not in the

    Enrollment

    table
• Insertion anomalies occur when data cannot be inserted due to missing information in another table
  • Example: Inability to insert a new course into the

    Courses

    table because no student has enrolled in it yet
• Deletion anomalies occur when deleting data from one table unintentionally removes related data from another table
  • Example: Deleting a student from the

    Students

    table also removes their enrollment records from the

    Enrollment

    table