Course contentsShow
← Computer Science
Lesson 1641 of 2,87237. Database Design and NormalizationFree lesson

Achieving BCNF

Identifying violations where non-candidate-key determinants exist and decomposing tables to satisfy BCNF.

Achieving BCNF

What you'll learn: How to identify BCNF violations in a relation and decompose it into BCNF-compliant tables.

Understanding the Problem

You've learned that BCNF (Boyce-Codd Normal Form) requires every determinant in a functional dependency to be a candidate key. Now you need to fix relations that violate this rule.

A BCNF violation occurs when you have a functional dependency X → Y where X is not a candidate key. Think of it like having an unauthorized person (non-candidate-key) making decisions (determining values) in your database—this creates redundancy and update anomalies.

The Decomposition Process

To achieve BCNF, follow these steps:

  1. Identify the violating FD: Find a functional dependency X → Y where X is not a candidate key
  2. Decompose the relation: Split the original table into two:
    • Table 1: Contains X and Y (the problematic FD becomes safe here because X becomes a key)
    • Table 2: Contains X and all remaining attributes (excluding Y, but keeping X as a foreign key)
  3. Repeat if needed: Check each resulting table for BCNF violations and decompose again if necessary

Example Scenario

Consider a relation CourseSchedule(Course, Instructor, Room, Time) with FDs:

  • {Course, Time} → {Instructor, Room} (candidate key)
  • Instructor → Room (violation! Instructor is not a candidate key)

The second FD causes redundancy. Decompose:

  • InstructorRoom(Instructor, Room) ← Now Instructor is the key here
  • Schedule(Course, Instructor, Time) ← Links courses to instructors and times

Both tables are now in BCNF because all determinants are candidate keys.

Key Takeaway: Achieving BCNF means decomposing tables whenever a non-candidate-key determinant exists, creating smaller relations where every determinant is a candidate key, eliminating redundancy and anomalies.