Multiple keys and transition constraints
Date: Thu, 14 Sep 2006 07:10:02 GMT
Message-ID: <ef7Og.962$TV3.401_at_newssvr21.news.prodigy.com>
Given a relation schema R {A, B, C}, where A and B are each candidate keys.
If the current extension is
r {{A:1, B:9, C:3}
{A:2, B:8, C:4}}
and the proposed extension is
r' {{A:1, B:8, C:4}
{A:2, B:9, C:3}}
is only A different? Or are both B and C different? From one perspective, both B and C remain constant but A is different. From another perspective, A remains constant but both B and C are different. If you're trying to
specify the allowable transitions for C, which key do you use? A, B, the superkey, AB, or none of the above?
Is the problem one of normalization? The above schema is in 5NF. Is there
an eqivalent but "better" database schema that leaves no doubt as to which
key to use? Some of the functional dependencies implied by the keys are:
A --> B
B --> A
A --> C
B --> C
AB --> C
A --> B --> C
B --> A --> C
Note the transitive FDs that are a result of the cyclical relationship between A and B.
Should the attributes participating in a "mutual functional dependency," that is, A <--> B (both A --> B and B --> A), be separated into another relation schema? For example, R above could be decomposed into S {A, B} and T {A, C}. One obvious advantage would be that each and every datum in the database would have one and only one direct, minimal and nontrivial determinant, so there would be no problem choosing a key when specifying a transition constraint. On the other hand, a circular inclusion dependency would need to be specified and enforced. In addition, if one of the keys in S were updated along with T in a multiple assignment, you still couldn't tell given only s, s', t and t' whether A alone is different or both B and C.
Does the stated intent of the user matter? If a user identifies a datum by using the Guaranteed Access Rule and then asserts a new value, then the user's intent is clear and stated. The same is true if a user targets a restricted projection of a relation and then for each element asserts a new value. But even if the intent were to update only A in the example above, the effect is that C would be different with respect to the original value for A. Does a constraint that limits the allowable transitions for C imply constraints that limit the allowable transitions for A or B or both? Or should the transition be allowed because C was not targeted? Received on Thu Sep 14 2006 - 09:10:02 CEST