Re: circular relationships ok?

Jan Hidders wrote:
> Brian Selzer wrote:
> >
> > In other words, a database schema consisting of the relation schemata,
> >
> > R{A, B}, S{B, C}, and T{C, A}
> >
> > and the foreign key constraints,
> >
> > R(B) --> S(B), S(C) --> T(C), T(A) --> R(A)
> >
> > is equivalent to a database schema consisting of the same relation schemata
> > and the foreign key constraints,
> >
> > R(A) --> T(A), T(C) --> S(C), S(B) --> R(B)
> >
> > is equivalent to a database schema consisting of the relation schema
> >
> > U(A, B, C) where A, B, and C are candidate keys.
>
> Actually, none of them are equivalent. Consider the following database
> instances: (formatted assuming fixed width font)
>
> R : A B S : B C T : C A
> --- --- ---
> 1 2 2 3 3 1
> 4 3
>

Re-reading the original message I think your objection is valid since Brian apparently claimed that *any * set of tables with circular foreign key constraints is equivalent to U which is only the case if the foreign keys involved were also candidate keys as you mentioned below.

> This is an instance of the first schema, but not of the second. A
> similar example shows thre is an instance of the second schema that is
> not an instance of the first schema:
>
> R : A B S : B C T : C A
> --- --- ---
> 1 3 3 2 2 1
> 3 4
>
> Finally, it is clear that these two instances cannot be represented in
> the third schema since the join would lose tuples. So they are really
> all three different.
>
> What *is* true is that the following schema:
>
> - R(A,B), with cand. keys {A} and {B}
> - S(B,C), with cand. keys {B} and {C}
> - T(C,A), with cand. keys {C} and {A}
> - foreign keys:
> R[B] -> S[B], S[B] -> R[B],
> S[C] -> T[C], T[C] -> S[C],
> T[A] -> R[A], R[A] -> T[A]
>
> Is equivalent with the schema:
>
> - U(A,B,C) with cand. keys {A}, {B} and {C}
>
> So perhaps that is what you meant?
>
> -- Jan Hidders
Received on Sun Mar 05 2006 - 14:48:47 CET