Re: Multiple keys and transition constraints

Brian Selzer wrote:
> "JOG" <jog_at_cs.nott.ac.uk> wrote in message
> news:1158541536.167231.219810_at_m73g2000cwd.googlegroups.com...
> > JOG wrote:
> >> 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.
> >> [snip]
> >
> > Still had no answer to this, so let me try and convince you once and
> > for all.
> >
> > Pretend you keep a company's books, all in your head. If an employee
> > comes to you and says "I have had a pay cut and quite frankly that's
> > against our contracts" (i.e. a transition constraint), you can normally
> > ask him his employee_id (that's the only thing the you use to identify
> > an employee - you're not good with faces) and check to your memory to
> > see if there has been a mistake, by remembering 'his id and pay' for
> > last month against his 'his id and pay' this month.
> >
> > But this employee is a different, as his 'id' has just changed after a
> > rejig (hence its proven an unstable key). So the only way to determine
> > anything about his pay problem is to find out what his old id was, and
> > utilize that in the comparison.
> >
> > Except he's an idiot and can't remember what his old one was. (here you
> > are remembering 3 years of payroll, and this fool can't even recall his
> > old id. pfffft) The situation is now fubar - you can't compare his old
> > pay rate as it was under a different id, and the only choice is to fire
> > him and pretend the whole thing never happened.
> >
> > In retrospect three solutions could have been employed:
> > 1) either id's should not have been changed in the first place
> > (unstable identifiers, eh? you did tell them you were crap with names
> > and faces...)
> > 2) if id's /had/ to be changed old records should have been rememorized
> > to reflect this. Its only a label after all. Then when that guy had
> > come along his identifier could still be corresponded between states.
> > 3) The id's should have been updated in isolation to any other
> > attributes to ensure continuity.(i.e. one round of memorizing the id's
> > rejig, without any other changes).
> >
> > At no point would anyone suggest that reality is broken. It was just a
> > logistical issue, that we could have prevented and that's all. The
> > point is /you as a person in real life/ couldn't identify him, never
> > mind a database - that was the real problem.
> >
> > That's my best attempt at an analogy brian. It's just not a problem
> > with the model.
> >
>
> Your entire argument is based on a false assumption.

Apologies brian, you have completely misunderstood. The analogy did not concern the example you gave, and I should have made that clear -it was a seperate description of why the problems you imagine are to do with identification in the real world, and external to whatever model one is using. I hope you can reexamine it, in that light.

> The employee's id did
> not change. I thought I made that clear when I suggested that there were
> presumably two additional relations, EMPLOYEE and POSITION. The problem is
> that on the relation EMPPOS, there are two candidate keys: EMP# and POS#.
> So, did each employee get a new position and pay rate? Or did each position
> get a new employee?

>


> emppos:  {{EMP#:1, RATE:3, POS#:9}


>                  {EMP#:2, RATE:4, POS#:8}}

>


> emppos': {{EMP#:1, RATE:4, POS#:8}


>                  {EMP#:2, RATE:3, POS#:9}}

>

But this question is incoherent. If what you meant to ask was 'is the rate now different for propositions constaining emp#1' then yes. If you are asking 'is the rate now different inpropositions containing pos#9' then no. If your transition constraint concerns what emp#1 is paid then check that. If it concerns what a position offers, then check that.There is still absolutely no problem with this scenario, so long as you see tuples as they are - just values representing statements about the world.

> The projections of emppos and emppos' onto {RATE}
> are identical. Does that mean that RATE did not change? Also, the
> projections of emppos and emppos' onto {POS#, RATE} are identical. Does
> /that/ mean that RATE did not change? On the other hand, the projections of
> emppos and emppos' onto {EMP#, RATE} are /not/ identical. Does that mean
> that RATE /did/ change? Given only emppos and emppos', can you specify a
> transition constraint on the allowable values for RATE in emppos'?

? If I can do it in the real world, I can do it via the model. The only problems you highlight are one's where identification in the real world is somhow already fubar.

> The answer is obviously no. You can specify one on the allowable values for the
> projection of emppos' onto {EMP#, RATE}, or you can specify one on the
> allowable values for the projection of emppos' onto {POS#, RATE}, or both,
> but you can't specify a transition constraint that governs the allowable
> values for RATE by itself.
Received on Tue Sep 19 2006 - 00:02:12 CEST