Re: Multiple keys and transition constraints

From: Brian Selzer <brian_at_selzer-software.com>
Date: Tue, 19 Sep 2006 07:51:32 GMT
Message-ID: <8kNPg.3038$IA.1805@newssvr11.news.prodigy.com>

"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:1158616932.002911.67020_at_k70g2000cwa.googlegroups.com...

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

I understand what you're trying to say. In your example, a record of what the employee's id was a month ago would have had to be maintained in some tuple of a relation with a temporal attribute. But a transition involves only two successive states, and in selecting a particular transition, the user has already supplied any information needed to correlate tuples! Unfortunately, that information is lost when an update that affects a prime attribute is transformed into an assignment.

Take a close look at what happens during an UPDATE. First, the WHERE clause identifies the set of tuples that are targeted by the update. Then the result is extended with an additional attribute for each affected attribute together with a value for each additional attribute in each affected tuple, effectively identifying the attributes that are targeted by the update. At this point the result is a relation that contains for each affected tuple the original values for each attribute and an additional value for each affected attribute--the stated intent of the user is still intact. But in order to transform the update into an assignment, three additional things happen--all of which cause information to be lost. First, the affected original attributes are projected away, eliminating any chance of correlation if one of the affected attributes was prime. Then the new attribute for each affected original attribute is renamed to match the original, obscuring which attributes were targeted by the update. Finally the result is unioned with the difference between the original relation and the targeted set of tuples, obscuring the updated tuples.

A candidate key may be composite. Just as it is possible for one key to be updated in a relation with multiple candidate keys, it's possible for one or more attributes of a composite key to be updated in a relation with a single candidate key. This is possible even if the components of the composite key are surrogates or even object identifiers. I find it hard to swallow that there is no correlation between tuples if any component of the composite key is affected. If the user intended an update, then there must be a correlation, even if the only key changed. Also, there may be rules that apply only for an update that do not apply to deletes or inserts, but if the only key was updated, then given only the current and proposed states, you can't differentiate between an update and a multiple assignment consisting of a delete and an insert.

One other thing. Preventing an update that affects an attributes of a key in addition to other attributes either shifts the responsibility for maintaining integrity to the application, or requires that the system recognize that a prime attribute was targeted by an update. In other words, you can't specify a constraint in terms of only the current and proposed states that would in all instances be able to detect a key change.

>> 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?

>
> 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 question is rhetorical. Oh, and by the way, that's exactly how I see tuples.

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

Are you saying that a relation schema with multiple candidate keys is fubar? You may be right about that. I think that a database schema that contains relation schemata with transitive functional dependencies that are not also mutual functional dependencies might be improved through decomposition. The reason is more about simplification than identification, though.

>> 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 - 02:51:32 CDT