Database Design – How to Prove a Relation is in 3NF

database-designnormalization

Consider the relation R(A,B,C,D,E) and the set of functional dependencies F={A->D,D->C,BC->AE}

I need to prove this relation is in the 3FN, however I don't think it is; in fact, I don't even think it is in the 2FN.

My reasoning is the following:

I think my candidate key is AB, as AB->A,B, through the reflective property. Because it determines A, it also determines D and C, by transitivity. By pseudo transitivity AB->AE, so AB also determines E; I can't find any subset of AB that also determines every other non key attribute, so I think AB is a candidate key.

Because AB is the candidate key, the relation is in the 3FN if it's in the 2FN and to be in the 2FN, for every non key attribute X, AB->X is elementary, i.e., there can't be a subset of AB that also determines X; this condition already fails in AB->D, as A->D.

Even if it was in the 2FN, it can't also be in the 3FN, as there can't be any non key attribute that depends on another non key attribute, but there cleary are, e.g., D->C.

Am I doing anything wrong, as I can't see how this relation is in the 3FN, but I have to prove it is so.

Best Answer

A relation is in third normal form when for all the FDs X -> Y of a cover F of the dependencies of the relation we have that either X is a superkey, or Y is prime. So we can prove that R is in 3NF if we can show that this condition is true for all the dependencies of F.

The relation has three candidate keys (you can easily see that the closure of each of them contains all the attributes of the relation):

AB
BC
BD

So the only non-prime attribute is E. So, BC->AE has a determinant which is a superkey, while A->D and D->C have a determinate which is a prime attribute.

So all the dependencies satisfy the condition and the relation is in 3NF.

Related Question