Re: Relation Schemata vs. Relation Variables

From: Jon Heggland <jon.heggland_at_idi.ntnu.no>
Date: Sun, 03 Sep 2006 22:08:12 +0200
Message-ID: <edfcr6$uvq$1_at_orkan.itea.ntnu.no>


Brian Selzer wrote:
> "Jon Heggland" <jon.heggland_at_idi.ntnu.no> wrote in message
> news:edecf7$diu$1_at_orkan.itea.ntnu.no...

>> You can pair them in any way that makes sense for the constraint at
>> hand, by specifying how the pairing is to be performed. Tuple identity
>> is a red herring; it is neither needed nor wanted.

>
> This is a contradiction. A transition constraint must either be able to
> pair the elements of two relations for comparison or be given a set of pairs
> to compare.

No, it just needs two relation values to compare. There's no contradiction. What is the problem?

> particular change, but without tuple identifiers it's possible for all keys
> to change at the same time. Do you treat such an occurance as a
> delete...insert?

It is equivalent to to a delete-insert performed as a multiple assignment (i.e. without constraint checking in the middle), yes.

> Just as there may be constraints that apply only to tuples
> that are about to be removed, there may be constraints that apply only to
> tuples that are about to be added. How can the system know whether or not a
> particular transition constraint applies?

That is of course given by how the TC is formulated. With relvar-based constraints there isn't any classification into insert, update and delete constraints. There is just a condition (usually) involving an old relation value, and a new one. It really isn't so complicated.

> What I'm saying is that using a candidate key value beyond a single
> database state, such as in R JOIN R', implies that that value carries with
> it additional meaning beyond what is imparted to it by its definition. When
> you specify a candidate key, you're attaching significance to a particular
> set of attributes, but the significance of a particular set of attribute
> values is limited to a single database instance by the definition of a
> candidate key. Therefore, using a set of attribute values from one state to
> identify a tuple in another implies that that significance exceeds the
> bounds of a single database state.

I have a hard time making sense of the above. Are you saying that a given tuple may not mean the same thing in successive database states?

> I'm not saying that that is wrong, but
> the model must be able to support *all* situations, and "key updates" and R
> JOIN R' just don't mix.

Why not? What is the problem? Earlier, I gave an example that showed how relvar-based TCs handle key updates *better* than tuple-based TCs. How does your scheme fare in that example? Have you given any concrete examples of how your TCs are formulated?

>>> There most certainly is!  If you define a transition constraint that 
>>> states:
>>> you can't delete a tuple if quantity is greater than 0, then splitting 
>>> such
>>> an update into  delete...insert must necessarily fail if any tuple has
>>> quantity greater than zero!
>> Only if you check the constraint *between* performing the delete and the
>> insert. And that would be a logical mistake.

>
> Only if the system knows that the particular modification is an update.
> With assignment, it can't be determined what kind of modification was
> intended.

Please. If I perform an assignment, and the DBMS implements it as a delete followed by an insert, and checks my TCs in the middle, it is clearly faulty. It should check my TCs after the entire statement has been performed. This is rather elementary.

>> The transition should be rejected (of course depending on the actual TC
>> formulation, which you unfortunately don't provide). If you want the
>> database to know that Lynn left the line and then reentered, you must
>> tell it so by performing two updates: a DELETE first, and an INSERT
>> afterwards (or the equivalent relational assignments, of course). The
>> hypothetical TCs would permit this.
>>
>> You do have a small point: that there may be multiple "paths" between
>> two database states, and some of these paths may violate TCs and others
>> not. Thus, you may need to tell the database which path corresponds to
>> reality (or whatever it is you're representing). But as I show above,
>> that is possible---even simple---without resorting to your transition
>> sets. Your scheme adds complexity, but no power.

>
> It is possible only if you assume that keys cannot change.

I assume no such thing. What, precisely, is the problem with TTM-style TCs and relational assignment in your example? Surely you brought it up to prove a point?

> In order to
> reach the level of stability required to define transition constraints based
> on R and R' that are *always* enforcible, you must in many cases resort to
> using object identifiers. Take the example of the line at the bank. For
> POSITION, there's no need for an object identifier, because the value
> represented is simply a natural number and it's meaning is intrinsic. For
> PERSON, however, do you use customer number? What if the customer number
> changes because the user that added the customer keyed in the wrong
> information and is now correcting a mistake?

Like I said, the database cannot enforce truth, only consistency. The TC in this case would say that *customer numbers* may not jump the line, and the DBMS would be quite right in rejecting an update of the customer number of a person in a line. It cannot know that the two customer numbers represent the same person unless you tell it so.

You seem to be hung up on the idea that each tuple in a relvar corresponds to some real-world object or entity, and that while every attribute in the tuple may change, still this mystical connection to the entity remains. It is perhaps natural---and even convenient, I'll admit, if you want the database to handle TCs about "persons" without formalising exactly what a "person" is, though I'll have to see exactly how your transition set TCs actually look and work before I pass final judgement on that---but it is OID thinking.

> Because
> key stability has no bearing on whether or not a constraint can be enforced,
> immutable, system-generated surrogate keys, object identifiers, and tuple
> identifiers are relegated into the physical implementation where they
> belong.

It is a matter of perspective, but I'd say that this schemes *depends* on OIDs, since it seems you want a given tuple to represent the same (say) person even when every attribute is changed. You avoid putting the OIDs explicitly in the logical model, but they must be present in the mind of the user.

Note, though, that my point is not disparaging your scheme---it seems workable, though a bit complex. I just believe your criticism of relational assignment and relvar-based TCs is unfounded.

-- 
Jon
Received on Sun Sep 03 2006 - 22:08:12 CEST

Original text of this message