DBMS Notes
A relation R is in BCNF (Boyce-Codd Normal Form) if, for every non-trivial functional dependency X → Y in R, the determinant X is a superkey of R. This is a...
Definition
A relation R is in BCNF (Boyce-Codd Normal Form) if, for every non-trivial functional dependency X → Y in R, the determinant X is a superkey of R. This is a stricter version of Third Normal Form — BCNF removes the "prime attribute exception" that 3NF allows.
In plain language: every determinant (left side of an FD) must be capable of uniquely identifying all rows in the table. If any non-key attribute or even a prime attribute is determined by something that is not a superkey, the relation violates BCNF.
Classic Example — CourseTeacher
| Relation | CourseTeacher(Student, Course, Teacher) |
| FD1 | (Student, Course) -> Teacher |
| FD2 | Teacher -> Course |
| Candidate Keys | {Student, Course} and {Student, Teacher} |
| Prime Attributes | Student, Course, Teacher (ALL attributes are prime) |
3NF Check:
- FD2: Teacher -> Course. Teacher is not a superkey, BUT Course is a prime attribute.
- 3NF allows this -> Relation IS in 3NF.
BCNF Check:
- FD2: Teacher -> Course. Teacher is not a superkey.
- BCNF does NOT allow exceptions -> Relation VIOLATES BCNF.
BCNF Decomposition Algorithm
When a relation violates BCNF, we decompose it:
Applying to CourseTeacher
| Violating FD | Teacher -> Course (Teacher is not a superkey) |
| Step | Decompose using Teacher -> Course |
| Verify R1 | TeacherCourse(Teacher, Course) |
| FD | Teacher -> Course, Teacher is PK (superkey) -> BCNF satisfied |
| Verify R2 | StudentTeacher(Student, Teacher) |
| StudentTeacher(Student, Teacher, PK | (Student, Teacher)) |
Another Example — Delivery System
| Relation | Delivery(City, Street, ZipCode) |
| Candidate Keys | {City, Street} and {ZipCode, Street} |
| Prime Attributes | City, Street, ZipCode (all prime) |
| ZipCode -> City | ZipCode alone is NOT a superkey -> VIOLATES BCNF |
| R2 = StreetZip(Street, ZipCode, PK | (ZipCode, Street)) |
BCNF Decomposition May Not Preserve Dependencies
An important trade-off: BCNF decomposition always guarantees lossless join but may NOT guarantee dependency preservation.
This is the fundamental trade-off between BCNF and 3NF:
- 3NF: Always achieves both lossless join AND dependency preservation
- BCNF: Always achieves lossless join but may sacrifice dependency preservation
Comparison: 3NF vs. BCNF
| Property | 3NF | BCNF |
|---|---|---|
| Strictness | Less strict | Stricter |
| Prime attribute exception | Yes (allows FD to prime) | No exceptions |
| Lossless decomposition | Guaranteed | Guaranteed |
| Dependency preservation | Always achievable | May be lost |
| Redundancy | Some possible | Minimal |
| When to prefer | Need dependency preservation | Need minimal redundancy |
Practical Decision Guide
| -> Check every non-trivial FD | is the determinant a superkey? |
| -> If YES for all | done, R is in BCNF. |
| -> If NO | proceed to step 2. |
| -> If YES | decompose into BCNF (best of both worlds). |
| -> If NO | choose between: |
In practice, most database designers prefer BCNF when possible and fall back to 3NF only when dependency preservation is critical and cannot be achieved in BCNF.
Testing if a Relation is in BCNF — Quick Method
For each functional dependency X -> Y in the relation:
- Compute X+ (closure of X)
- If X+ contains all attributes of R, then X is a superkey -> this FD is fine
- If X+ does NOT contain all attributes, this FD violates BCNF
If all FDs pass this test, the relation is in BCNF. If even one fails, it is not.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Boyce-Codd Normal Form (BCNF) — Database Management Systems.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, bcnf, boyce-codd normal form (bcnf) — database management systems
Related Database Management Systems (DBMS) Topics