3

We know a relation is in 2NF iff it is in 1NF and no non-prime attribute is dependent on a proper subset of the candidate key. But is "dependent" in this definition strict or loose in the following sense:

Given the set of dependencies

A -> B
B -> C

Is the relation

([D],[A],C)

(i.e., the candidate key is {D,A}) in 2NF?

The reason I ask is that there are no direct dependencies from the given set of dependencies with a proper subset of {D,A} on the left-hand side, but the transitive dependency A -> C does violate 2NF. So my question is:

In the definition of 2NF, are the "disallowed" dependencies direct ones only or do transitive dependencies "count" as violations? I think transitive dependencies do count (and that the relation above is not in 2NF), but in the sequence of 1NF, 2NF, 3NF, etc., as usually presented in the academic literature, transitive dependencies are typically only "removed" at the 3NF stage.

  • You have left attribute B out of the relation. – philipxy Dec 13 '15 at 7:59
3

"Loose" dependency is the right idea

You seem to be thinking of "strict" vs "loose" or "direct" vs "direct or transitive" FDs where the explicitly given FDs are taken as the "strict"/"direct" ones. Such a notion of "transitivity" is only an artifact of how you chose to define the situation. The set of given FDs is only relevant in that their closure is relevant.

The definition of 2NF refers to all partial FDs and all full FDs. What particular ones given to describe a bunch that hold is irrelevant. When certain sets of FDs are present, certain other FDs are too. They are all just present together. By definition they are the ones in the closure of the given ones.

Re given FDs

It is a common problem that people will just say "these FDs hold". Note that that means "all the FDs in their closure hold", because if the given ones hold, the closure's hold--by definition of the closure. But if they don't say that the ones in the closure are the only ones that hold then you are (in general) limited in what you can determine about the relation. So usually they really mean, the only ones are the ones in the closure of the given ones. Ie that the given ones form a cover for the ones that hold.

Moreover if they say this in practice beware that they may not actually know that there aren't any other FDs than in the closure of the given ones, ie that they form a cover. Without knowing that there are no others it is possible to answer "what is the closure of these", but it may not be possible to answer "what are the CKs".

Transitive vs non-transitve FDs

The term "transitive" FD and "non-transitive" FD are defined and play a role in the definition of 3NF. (A non-transitive FD is one that's not transitive.) But they are not defined in terms of given FDs as "direct" either.

FD (functional dependency) A-> C is transitive when there is an X where A -> X and X -> B and NOT X -> A. (Likely that last conjunct is unexpected.)

Transitive FDs are those that hold as a consequence of all the FDs that hold, not just the ones given.

Similarly the definition of 3NF refers to all transitive FDs and all FDs. (2NF plus every non-prime attribute non-transitively dependent on every candidate key gives 3NF.) There is no notion of "direct" FD except "non-transitive" FD.

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.