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

.

Relevant Pages

  • [ann] SST (SQL Schema Transformer) Project
    ... I've just released the first public release of SST(SQL Schema ... SST is a Common Lisp program that transforms an s-expression ... Invalid FOREIGN KEY reference detection. ...
    (comp.lang.lisp)
  • Re: Foreign key constraints from DB at runtime
    ... OleDbConnection object, and then use the GetOleDbSchemaTablemethod to get ... This will get you all the foreign key definitions inside the schemaTable ... I'm trying to detect them from the database's schema. ... foreign key constraints to other tables. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Business objects, subset of collection
    ... SQL works on not normalised data ... foreign key (vehicleid) references vehicle ... And who did the poor schema design, ...
    (comp.object)
  • Re: circular relationships ok?
    ... both attributes in each relation that participates in the foreign key ... In other words, a database schema consisting of the relation schemata, ... Uwhere A, B, and C are candidate keys. ...
    (comp.databases.theory)
  • Re: Diagrams
    ... How can I setup a foreign key while I'm designing a tabel??? ... > They are a simple ERD tool for visualising the schema of your database. ...
    (microsoft.public.sqlserver.server)