Boyce-Codd Normal Form (BCNF)

When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. Boyce-Codd normal form is a special case of 3NF. A relation is in BCNF if and only if every determinant is a candidate key.

Consider the following table

St_Maj_Adv table

Semantic Rules:

Each student may major in several subjects
For each major, a given student has only one advisor
Each major has several advisors
Each advisor advises only one major
Each advisor advises several students in one major

Functional dependencies:

Fd1: Student_id,Major ——> Advisor (candidate Key)

Fd2: Advisor ——> Major (not a candidate key)

ANOMALIES

Deleting student deletes advisor info

Insert a new advisor – need a student

Update – inconsistencies

Note: no single attribute is a candidate key

Primary key can be student_id,major or student_id,advisor

To reduce the St_Maj_Adv relation to BCNF, create two new tables:

St_Adv

Adv_Maj