2

I was reading Database Systems Design, Implementation and Management by Carlos Coronel. In the chapter of Normalization after he explained 3NF, he writes

The existence of multiple candidate keys can also influence the identification of transitive dependencies. Previously, a transitive dependency was defined to exist when one nonprime attribute determined another nonprime attribute. In the presence of multiple candidate keys, the definition of a nonprime attribute as an attribute that is not a part of any candidate key is critical. If the determinant of a functional dependence is not the primary key but is a part of another candidate key, then it is not a nonprime attribute and does not signal the presence of a transitive dependency.

I am having hard time to understand what he wants to say. Can anyone explain this paragraph in simpler terms.

1

I do not know if the following can be useful to explain the involuted phrase that you cited, however all the following facts are true.

  1. A relation must have at least a candidate key and can have more then one candidate key.

  2. By definition, an attribute is prime if it is part of any candidate key.

  3. A transitive dependency is a dependency that derives from two other dependencies through the “transitive” Armstrong’s axiom (if X -> Y and Y -> Z, then X -> Z), so for instance if in a relation R(A, B, C, D) we have that A -> BC and BC -> D, the dependency A -> D is a transitive dependency.

  4. A transitive dependency can be problematic (that is can produces data anomalies) if the second dependency of the transitive rule (the “Y -> Z” dependency of the rule) has a determinant which is not a full candidate key, but (a) a subset of a candidate key, or (b) contains attributes that are not prime.

Consider the relation R(A, B, C, D E), with dependencies AB -> CDE, CD -> ABE, C -> E. In this case there are two candidate keys, AB and CD, so C, the determinant of C -> D, is a prime attribute, since it is an attribute of the candidate key CD (“it is not a nonprime attribute”).

Note that in the previous example the attribute E is transitively dependent from AB (from AB -> CDE we can derive AB -> C, and from AB -> C and C -> E, we can derive AB -> E) and the relation is not in 3NF, neither in BCNF.

  • 1
    I think your last example is not even in 2NF. – ypercubeᵀᴹ Oct 13 '18 at 16:26
  • @ypercubeᵀᴹ yes the example is not even in 2NF because C -> E is a partial dependency because since C is a prime attribute and subset of candidate key CD and determines E a non prime attribute. Am i right? – Navjot Waraich Oct 13 '18 at 17:03
  • 1
    @ypercubeᵀᴹ and Jot Waraich, yes, the relation is neither in 2NF. – Renzo Oct 13 '18 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.