Explain BCNF with example.
In : BE Subject : Database Management SystemsBCNF is a strict rule for organizing database tables to avoid redundancy and problems like duplicate data. It's a stronger version of 3NF.
The Rule:
If something (e.g., Lecturer) determines another thing (e.g., Department), then that "something" (Lecturer) must be a unique key for the whole table.
Example:
Original Table (Teaching):
| Lecturer | Subject | Department | |
|---|---|---|---|
| Dr. Smith | Databases | CS | |
| Dr. Smith | OS | CS | ← Department repeats for Dr. Smith |
Problem:
-
Lecturer → Department(A lecturer determines their department). -
But
Lectureris not a unique key (the key is(Lecturer, Subject)). -
This violates BCNF.
Fix: Split the Table
1. Lecturer_Department Table:
| Lecturer | Department | |
|---|---|---|
| Dr. Smith | CS | ← Department stored only once per lecturer! |
| Dr. Jones | CS |
2. Lecturer_Subject Table:
| Lecturer | Subject |
|---|---|
| Dr. Smith | Databases |
| Dr. Smith | OS |
Result:
-
No more duplicate department info.
-
Both tables now follow the BCNF rule.
-
Every determinant (
Lecturerin the first table) is now a unique key.
In short: BCNF means "no repeating facts." If X tells you Y, then X must be the unique identifier for its table.