Boyce-Codd Normal Form: BCNF

A table/relation is in BCNF if, for every non-trivial functional dependency X → Y, X must be a super key in the table. BCNF is the stronger version of 3NF.

Definition

Only two shapes of FD are allowed:

super key → prime attribute
super key → non-prime attribute

Here X = determinant, Y = dependent attribute, and a super key is an attribute or set of attributes that can uniquely identify each row.

In simple words: for every functional dependency, the left side must be able to uniquely identify a row.

Compare with 3NF, which also allowed non-super key → prime attribute. That single extra allowance is the whole difference between 3NF and BCNF.

Example — Teacher / Course / Room

Teacher_ID	Course_ID	Room
T01	CSE101	R1
T02	CSE102	R2
T03	CSE101	R1
T04	CSE103	R3

Assume the following rules: one teacher teaches one course; each course is always held in one fixed room.

a. Find all possible functional dependencies:

Teacher_ID → Course_ID
Course_ID → Room

b. Find the super key:

Teacher_ID → Course_ID → Room
Therefore Teacher_ID is the candidate key (super key).

Teacher_ID → Course_ID
This FD is in BCNF, as Teacher_ID is a super key.

Course_ID → Room
This FD is not in BCNF, as Course_ID is not a super key.

Therefore, the table is not in BCNF.

Issues if a table is not in BCNF

Anomaly	What goes wrong
Update	If the room of CSE101 changes from R1 to R5, it must be updated in multiple rows (T01 and T03). If one row is updated and another is not, data inconsistency occurs.
Insertion	A new course with a fixed room cannot be inserted unless a teacher is assigned — CSE104 is held in R4, but if no teacher has been assigned yet this cannot be stored properly.
Deletion	If teacher T04 leaves and that was the only row for CSE103, then deleting T04 also removes the information that CSE103 is held in R3.

Solution — break the table based on functional dependency

Teaching table

Teacher_ID	Course_ID
T01	CSE101
T02	CSE102
T03	CSE101
T04	CSE103

Course table

Course_ID	Room
CSE101	R1
CSE102	R2
CSE103	R3

Both tables are in BCNF, as in both tables Teacher_ID and Course_ID are the candidate key (super key) of their own relation.

A table might be in 3NF but not in BCNF

Student_ID	Course_ID	Instructor
S01	CSE101	Dr. Hasan
S02	CSE101	Dr. Hasan
S03	CSE102	Dr. Karim
S04	CSE102	Dr. Karim
S01	CSE102	Dr. Karim

Rules: a student can take many courses; a course can be taught by only one instructor. Candidate keys: (Student_ID, Course_ID) and (Student_ID, Instructor).

(Student_ID, Course_ID) → Instructor
satisfies super key → non-prime attribute, so this FD is in 3NF and BCNF.

Instructor → Course_ID
satisfies non-super key → prime attribute, so this FD is in 3NF —
but this condition is NOT allowed in BCNF: X must be a super key in X → Y.

The table is in 3NF · The table is NOT in BCNF.

Fixing it

Enrollment table

Student_ID	Course_ID
S01	CSE101
S02	CSE101
S03	CSE102
S04	CSE102
S01	CSE102

Teaching table

Course_ID	Instructor
CSE101	Dr. Hasan
CSE102	Dr. Karim

Both tables are in BCNF.

The price of BCNF. The dependency (Student_ID, Course_ID) → Instructor now spans two tables — you can no longer check it without a join. BCNF decomposition is always lossless but not always dependency-preserving; 3NF decomposition can always be both. That is why textbooks say “normalize to 3NF, go to BCNF only if the anomaly actually hurts”.

The four forms side by side

Form	Condition	Removes
1NF	Atomic values, no repeating groups, unique rows	Multi-valued cells
2NF	1NF + no partial dependency	Non-key depending on part of a composite key
3NF	2NF + no transitive dependency (or: X is a super key OR Y is prime)	Non-key depending on another non-key
BCNF	Every determinant X is a super key	Non-super-key → prime attribute

Next: Validation, merits & demerits →