Re: Multiple keys and transition constraints

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

> Brian Selzer wrote:

>>

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

>>

>
> 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 - 09:51:32 CEST