Re: Multiple keys and transition constraints

"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:1158251660.983027.98520_at_e3g2000cwe.googlegroups.com...
> Brian Selzer wrote:
>> 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?
>
> This requires more information to answer.
>
> Can you tell us which attribute identifies the subject [of each
> proposition] in real life over the updates. Then I can determine /what/
> exactly has experienced the transition.
>
> No other questions, just that.
>

Well, that's the dilemma, there are two subjects because there are two keys. Consider the following relation:

EMPPOS {EMP#, RATE, POS#} where EMP# refers to an employee (presumably specified in an EMPLOYEE relation which has information like name, address, etc.) and POS# refers to a position in the company (presumably specified in a POSITION relation which has information like job description, shift, etc.). RATE has two different meanings that coincide: from one perspective it's an employee's current pay rate; from the other it's what's currently being paid for a position in the company. Now assume that employee #1 was switched to position #8 which is on the evening shift and employee #2 was switched to position # 9 which is on the day shift. So, from the perspective of positions, only EMP# is different--employee #1 is now filling position #8 and employee #2 is now filling position #9. From the perspective of employees, both RATE and POS# are different--employee #1 got a $1/hr pay increase and employee #2 got a $1/hr pay cut.

>>
>> 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 Fri Sep 15 2006 - 02:30:09 CEST