Re: Multiple keys and transition constraints

From: Brian Selzer <brian_at_selzer-software.com>
Date: Mon, 18 Sep 2006 14:53:14 GMT
Message-ID: <upyPg.3767$7I1.1057@newssvr27.news.prodigy.net>

"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. 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}}

Did the pay rate change? 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'? 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 Mon Sep 18 2006 - 09:53:14 CDT