Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> comp.databases.theory -> Re: circular relationships ok?
No. You can't avoid or eliminate circular relationships. It should be
possible to contain one in a single relation, provided the set of attributes
in the referenced relation in each segment of the circular relationship is a
candidate key, because candidate keys are logically equivalent. For
example, if A and B are distinct candidate keys for a relation R, then by
definition A --> B and B --> A hold, which means that if a and b are values
for A and B respectively in a tuple of R, a implies b and b implies a,
therefore, a iff b. So, if you have three relations, R{A, B}, S{B, C} and
T{C, A} where A is a candidate key for R, B for S, and C for T, then by
definition,
A --> B, B --> C and C --> A.
Assuming that the circular relationship is defined as follows:
R(B) --> S(B), S(C) --> T(C), T(A) --> R(A).
Then for any set of values, a, b and c for A, B, and C respectively,
a implies b, b implies c and c implies a.
Because implication is transitive,
a implies c, b implies a and c implies b.
Consequently, a iff b iff c.
Therefore, a relation U{A, B, C} is equivalent to the above three relations in the presence of the circular relationship iff A, B, and C are each candidate keys for U.
Note that any additional attributes in R, S, and T come along for the ride, meaning that the heading of U is the union of the heading of R, S and T.
I must emphasize that the set of attributes in the referenced relation in each segment of the circular relationship must be a candidate key. In other words, each segment must be a foreign key constraint. If one or more of the segments is an inclusion dependency, which does not require that the set of attributes in the referenced relation be a candidate key, then it may not be possible to contain the relationship in a single relation.
"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message
news:du4k8t$te7$1_at_nntp.fujitsu-siemens.com...
> Hi!
> Just in general, are circular relationships something that
> can always be avoided?
> Or, given a model with a circular relationship, possibly
> spanning several tables, is there a way to get rid of them?
>
> Lots of Greetings and thanks!
> Volker
Received on Thu Mar 02 2006 - 23:28:50 CST