Re: Why spurious tuples with fifth normal form?

stowellt_at_gmail.com wrote:
>
> As far as a join dependency, I'm not sure how to check for that. Is
> there an easy way?

Let's for brevity call the relation R(P,C,U). I think you are right that there are no MVDs, which means that we can already rule out all binary JDs, since those are always equivalent with MVDs (e.g. MVD P->>C is equivalent with a JD {PC, PY}).

Since there are only three columns the only other possible non-trivial JD is JD {PC, PU, CU}. So does this hold? You can formulate this constraint in at least two ways:

(1) It always holds that R = R[PC] JOIN R[PU] JOIN R[CU]

     where R[PC] denotes the projection of R on the columns P and C, etc.

Bascially this says that the associated decomposition is information preserving, and you already saw that this is not always the case. Be sure to check that you have to do *two* joins, not just one.

A reformulation of (1) that some find easier to understand is the following:

(2) If there is a pair (p,c) in R[PC], a pair (p,u) in R[PU] and a pair (c,u) in R[CU] then the triple (p,c,u) appears in R

So it should hold that if a certain parameter p is associated with a classification c, and this parameter is also associated with a URL u, and that URL is itself associated with classification c, then it must hold that (p,c,u) is necessarily in the relation.

Since you got spurious tuples when joining everything back together, that latter statement is apparently not true, and the JD does not hold.

Congratulations, you have reached 5NF. :-)

• Jan Hidders