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.

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.

### Like this:

Like Loading...

*Related*