Determining if a relation is in third normal form 3NF

normalization

I have a ralation R(A,B,C) with the functional dependencies as follows

A->B
A->C
B->C

with A being the candidate key, is this relation in 3NF? well because by analyzing the first two dependencies it seems that both non key attributes B and C are not transitively dependent on A but does the third functional dependency B->C not make C transitively dependent on A?

Best Answer

When looking to see if a relation is in 3NF, two are the questions that you must answer:

Has every functional dependency a left hand side which is a superkey?
If this is not true, are the attributes on the right hand side prime attributes? (i.e. belonging to any key?).

So in your example you can see that both A -> B and A -> C are dependencies in which the left hand side is a key, so the answer to the first question is yes.

In the third dependency, instead, B is not a superkey, (since B+ = BC), so, is C a prime attribute?

To answer to this question, you should know all the keys of the relation, and see if C belongs to one of them. And in this case this is very simple, since A is the unique key of this relation, so you can conclude that the relation is not in 3NF. But note that in general, in more complex cases, it is not so simple to answer to such question, since you can have a very large number of keys, actually a number which is exponential with the number of attributes.

Related Solutions

Why is this relation in 3NF

The mistake is in your understanding of transitive dependency. From wikipedia: Transitive dependency

In mathematics, a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or distinct collections of attributes) in the relation. Suppose all three of the following conditions hold:

1. A → B
2. It is not the case that B → A
3. B → C

Then the functional dependency A → C (which follows from 1 and 3 by the axiom of transitivity) is a transitive dependency.

In your case though, (2) does not hold:

1. U → T                                  -- correct
2. It is not the case that T → U          -- wrong
3. T → V                                  -- correct

Therefore {V} is NOT transitively dependent on {U}.

Questions regarding second normal form

If all your dependencies are those you have shown: {A->GH, B->IJ, C->A, F->B, FC->DK, K->E} then you have come to the wrong conclusion.

Your only candidate key is CF. The C and F are not candidate keys on their own.

Therefore, the F->B dependency (and the C->A as well) means that the relation violates 2NF.

For the other question, if you had for example these dependencies:

BC -> AF
AF -> BC
 F -> DE

where the candidate keys are BC and AF, then again the F -> DE would mean that the relation violates 2NF. To be in 2NF would mean that there is no dependency on any part of any candidate key.

Best Answer

Related Solutions

Why is this relation in 3NF

Questions regarding second normal form

Related Question