Re: Multiple keys and transition constraints

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.

>

>


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


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

>

>


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


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

>

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. Received on Mon Sep 18 2006 - 03:05:36 CEST