Finding a relation in 3NF but not in BCNF

Question

I've been reading many different sources on how to differentiate relations that are in 3NF/BCNF. And I've so far this is my understanding...

I will use this relation as an example...

R = {A, B, C, D, E}

and

F = {A -> B, B C - > E, E D -> A}.

Firstly we must find the keys of the relation. I used this video to help me do that. And I got

Keys = {ACD, BCD, CDE}

Now to make sure R is in BCNF, we must make sure that the left hand side of every functional dependency in F is one of the Keys. We instantly know this is not the case, because the first FD is A -> B and A is not one of the keys. So it is not in BCNF.

Now to make sure R is in 3NF, we must make sure that the left hand side of every functional dependency in F is one of the Keys OR the right hand side of every functional dependency in F is a subset of one of the Keys. If you look at the right hand side of every FD, they are B, E and A. These are each a subset of a Key, so this means that it is in 3NF.

So this is one of the rare cases (according to wiki) where a relation is in 3NF but not in BCNF. Is this method correct? Is it reliable? Am I missing anything?

Corydalis

Yep, you did everything right. – Strobila

you may want to check out this: class2go.stanford.edu/db/Winter2013 – Strobila

Your "I have these FDs" doesn't make sense. "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. "These are some FDs that hold"?--Question can't be answered. Find out what a cover is & what the exact conditions are to apply a particular definition/rule/algorithm. To determine CKs & NFs we must be given FDs that form a cover. Sometimes a minimal/irreducible cover. And the set of all attributes must be given. See this answer. – Minesweeper

Answer 1

First you need to learn superkeys, candidate keys, and primary attributes.

However, this rule of thumb helps:

A 3NF table that does not have multiple overlapping candidate keys is guaranteed to be in BCNF.

In other words, if the candidate keys in a 3NF relation are

• all atomic, or
• non-atomic but non-overlapping,

it is guaranteed that the relation is in BCNF.

The simplest relation which violates BCNF but meets 3NF has the following functional dependencies:

A,B -> C C -> B

In this case, candidate keys are (A,B) and (A,C).
It meets 3NF because

• the right-hand-side of all functional dependencies is a primary attribute (a.k.a. a prime attribute).

It violates BCNF because

• C -> B, but the left-hand-side is not a superkey.

Mountaineering

Answer 2

BCNF:

X->Y where Y can be prime or non-prime
Here,X must be a super key

3NF:

X->Y where Y is non-prime
then,
X must be a super key
else
X need not be super key

Hope,this helps

Freddiefreddy

These are not clear or correct. Look at some textbook definitions, or even wikipedia. – Minesweeper

The definitions are vague. For example, for BCNF you could simply state that X must be a super key. The definition of 3NF is correct, but this is the most complicated way of describing it! – Mountaineering

Answer 3

Basically we remove all the transitive dependencies in non prime attributes in the 3NF but there can be dependencies among the prime attributes(where prime attributes are on left and right side both). This happens only when there are multiple overlapping candidate keys. This is when the relation is not in BCNF and further normalisation is possible by removing the redundancy due to those dependencies among prime attributes.

Reverberatory