Coursework 5

Consider a relation R(CustomerNumber, Name, Address, CardType, CardNumber). CustomerNumber is a candidate key, and so is CardNumber (assume that each credit card has exactly one named owner).

Functional dependencies:

Exercise

For each of 2NF, 3NF and BCNF, explain whether the relation above is in that normal form. Normalise the relation to BCNF (list new tables and column names for each table). Explain why the resulting tables are in BCNF.

Solution

The table is actually already in BCNF (sorry for a trick question). I was marking leniently, but it is clear that lots of students should revise candidate keys, primary keys, and normal forms. If you have not been to the revision lecture, it has a summary of normalisation, too. As an extra revision exercise, do last year's normalisation coursework.

In order to be in 2NF, a relation should have no non-trivial functional dependencies where the determinant is a strict subset of a candidate key and the determined attribute is non-key. In this table, the only two candidate keys are single attribute, so no partial dependency is possible.
Common mistake: saying that (CardNumber,CustomerNumber) is the primary key, or a candidate key. Given the dependencies above, each of these attributes alone is a candidate key. The two of them together violate minimality requirement for candidate keys (that no part of a candidate key is a candidate key or has the uniqueness property).

For 3NF, there should be no non-trivial dependencies where the determinant is not a superkey and the determined is a non-key attribute. (The equivalent version of the definition with transitive dependencies says that there should be no transitive dependency of a non-key attribute on a candidate key, where the attribute or set of attributes `in the middle' is not a superkey). There are no such dependencies in the relation.
Common mistake: saying that for example CustomerNumber determines CardNumber and CardNumber determines CardType, so non-key attribute CardType transitively depends on a candidate key CustomerNumber. However this is not a violation of 3NF because CardNumber in the middle is a superkey.

For BCNF, there should be no non-trivial dependencies where the determinant is not a superkey. If you assume that the only non-trivial dependencies are the ones given then the relation is in BCNF and does not need to be decomposed.

Some students assumed that Name determined Address which obviously had implications for it being in 3NF and BCNF. I did not penalise such answers if they were consistent and correct given that assumption. Here is what the answer should have been. Assume that there is an additional dependency between Name and Address, but Name is not part of any candidate key. Then the relation is still in 2NF. However it is not in 3NF because CustomerNumber determines Name and Name determines Address. Here Name is not a superkey and Address is a non-key attribute. The dependency which violates 3NF is Name -> Address. So we need to decompose the original relation into two tables:
R1 (Name,Address)
R2 (CustomerNUmber, Name, CardNumber, CardType)
Now both R1 and R2 are in 3NF and in BCNF: every determinant is a superkey.