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:
- Identify the violating FD: Find a functional dependency
X → YwhereXis not a candidate key - Decompose the relation: Split the original table into two:
- Table 1: Contains
XandY(the problematic FD becomes safe here becauseXbecomes a key) - Table 2: Contains
Xand all remaining attributes (excludingY, but keepingXas a foreign key)
- Table 1: Contains
- 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)← NowInstructoris the key hereSchedule(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.