In my article on 3NF we saw that it was the same as 2NF but with one additional restriction. BCNF, similarly, is 3NF with one more restriction:
BCNF:
“Relvar R is in Boyce/Codd normal form (BCNF) if and only if, for every nontrivial FD X→Y that holds in R, X is a superkey.”
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)
The table below defines test questions. Each test has an Id and a name, and each Question has an Id and the actual question text.
{TestId, Test, QuestionId, Question}
{TestId, Test, QuestionId}
{TestId, QuestionId, Question}
{TestId, QuestionId}
The candidate keys:
{TestId, QuestionId}
{Test, QuestionId}
The subkeys:
{TestId, QuestionId}
{Test, QuestionId}
{TestId}
{Test}
{QuestionId}
And the non-trivial functional dependencies:
{TestId, Test, QuestionId}→{Question}
{TestId, QuestionId, Question}→{Test}
{TestId, QuestionId}→{Test}
{TestId, QuestionId}→{Question}
{TestId}→{Test}
{Test}→{TestId}
Now let’s test these FDs to see if this table is in BCNF. The requirement is that the determinant must be a superkey.
In FD {TestId, Test, QuestionId}→{Question}, is the determinant a superkey? Yes.
In FD {TestId, QuestionId, Question}→{Test}, is the determinant a superkey? Yes.
In FD {TestId, QuestionId}→{Test}, is the determinant a superkey? Yes.
In FD {TestId, QuestionId}→{Question}, is the determinant a superkey? Yes.
In FD {TestId}→{Test}, is the determinant a superkey? No!
In FD {Test}→{TestId}, is the determinant a superkey? No!
So we know this table is NOT in BCNF.
It is in 3NF, because in 3NF either the determinant must be a superkey OR the dependent must be a subkey. In FD {TestId}→{Test} the dependent is a subkey, and in FD {Test}→{TestId} the dependent is a subkey.