Boyce/Codd Normal Form (BCNF)

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 XY 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.

BCNF TestQuestion
Let’s define the superkeys:

{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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s