Re: Multiple keys and transition constraints

Brian Selzer wrote:
> "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.

Okay so initially:

r = { {emp:1, position:9, pay:3}

        {emp:2, position:8, pay:4} }

and then later:

r' = { {emp:1, position:8, pay:4}

        {emp:2, position:9, pay:3} }

What problem is there with this? All looks fine to me.

>
> >>
> >> 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 Sat Sep 16 2006 - 04:09:34 CEST