When is relation in 3NF but not in BCNF?

Question

A book by Korth et al. defines BCNF as follows:

A relation schema $R$ is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in $F^+$ of the form $α→β$, where $α⊆R$ and $β⊆R$, at least one of the following holds:

• $α→β$ is a trivial functional dependency (that is, $β⊆α$).
• $α$ is a superkey for schema $R$.

It then defines 3NF as follows:

A relation schema $R$ is in third normal form with respect to a set $F$ of functional dependencies if, for all functional dependencies in $F^+$ of the form $α→β$, where $α⊆R$ and $β⊆R$, at least one of the following holds:

• $α→β$ is a trivial functional dependency.
• $α$ is a superkey for $R$.
• Each attribute $A$ in $β-α$ is contained in a candidate key for $R$

So 3NF definition adds an additional 3rd condition to the BCNF definition: "Each attribute $A$ in $β-α$ is contained in a candidate key for $R$". I think we can correctly interpret this condition as $β-α$ are key attributes.

Now I was thinking about which relations are in 3NF but not in BCNF. Clearly the relation which contains the dependency that satisfies this 3rd condition can be in 3NF but not in BCNF.

The book gives following example:

The schema dept_advisor : {s_ID, i_ID, dept_name} with FDs

• i_ID $→$ dept_name
• s_ID, dept_name $→$ i_ID

Here, first FD is non trivial. Also i_ID is not a super key. Hence schema is not in BCNF. Now dept_name is a part of the candidate key (s_ID,dept_name). Hence, dept_advisor is in 3NF.

Now I came across following statement on this site, which I think I have also read in the book:

3NF states that all data in a table must depend only on that table’s "primary key", and not on any other field in the table. At first glance it would seem that BCNF and 3NF are the same thing. However, in some rare cases it does happen that a 3NF table is not BCNF-compliant. This may happen in tables with two or more overlapping composite candidate keys.

I have two doubts:

• What does it imply by using "primary key" in first sentence. Cant there be two or more candidate keys in a 3NF relation with some FDs involving one CK and remaining FDs involving other CK. This must not be the case due to the last sentence in above paragraph, which is source of 2nd doubt below. But then why use "primary key"? Should the sentence rather be "3NF states that all data in a table must depend only on that table’s candidate keys or better to say superkey"?

• From definitions of 3NF and BCNF, what I felt is the relation which is in 3NF but not in BCNF should be the one having FD following 3rd condition of 3NF definition and there can be no other situation which can lead to non-BCNF but 3NF relation. If so, how is "two or more overlapping composite candidate keys" (as stated in last sentence of above paragraph) is equivalent to/lead to such FD? Or to put in different way, where are overlapping composite candidate keys in above example of dept_advisor?

Am I thinking stupidly? Am I giving unnecessary importance to these points and are in fact senseless?

Answer 1

"I think we can correctly interpret this condition as β−α are key attributes."

No. β−α is a set of attributes. What that statement is saying is,

Every attribute in that set is part of some (at least one) candidate key for the relation.

Also, if by 'key' attribute, you mean an attribute that is a superkey, i.e., an attribute whose value uniquely determines the tuple, then NO, every element in β−α is NOT a key attribute. It is just a prime attribute (in other words, part of some candidate key).

For example, if we write your schema as:

Dept_advisor : {A, B, C},

then the FD's can be written as:

1. B → C (α = {B}, β = {C}, β−α = {C} - {B} = {C})
2. AC → B (α = {A, C}, β = {B}, β−α = {B} - {A, C} = {B})

Here, [1] is neither a trivial dependency nor is α (here, B) a superkey of the relation. In other words, none of the two conditions for BCNF hold for this FD. For BCNF, you need all the FD's to satisfy at least one of the two conditions, but here you have one FD which satisfies none. So this schema is not in BCNF. You got this right.

Now, testing it for 3NF, you can confirm that both [1] and [2] satisfy at least one of the 3 conditions for 3NF. [2] satisfies the 2nd condition (α = {A,C} is a superkey), so we are good so far. [1] does not satisfy any of the first two conditions, which are also the conditions for BCNF. But, it does satisfy the third condition, i.e., each attribute in β−α (here, only one attribute, namely B) is part of some candidate key.

What candidate key is that, you ask? You can easily find one.

Take {A, B}. It is a candidate key because you have the FD B → C, which means {A, B} → {A, B, C} gives all the attributes of the relation, which means it is a superkey. But no proper subset of {A, B}, i.e., neither A nor B alone, are superkeys themselves. In other words, neither {A} → {A, B, C}, nor {B} → {A, B, C}. So yes, you got this one right too, but for the wrong reasons.

As for the other statement, about 3NF tables being dependent only on the "primary key", you are better off just ignoring it and sticking to the definition and trying to understand and use it correctly.