Re: relationship in the database

From: Costin Cozianu <c_cozianu_at_hotmail.com>
Date: Wed, 25 Sep 2002 16:23:41 -0700
Message-ID: <3D92457D.1030608@hotmail.com>

Jan.Hidders wrote:
> In article <61c84197.0209250311.5660da8c_at_posting.google.com>,
> Peter Koch Larsen <pkl_at_mailme.dk> wrote:
>

>>hidders_at_hcoss.uia.ac.be (Jan.Hidders) wrote in message
>>news:<3d8b67a4$1_at_news.uia.ac.be>...
>>
>>>A solution could be that the trigger only fires if on tuple has been
>>>removed and another has been added. Of course this is somewhat of a trick
>>>because how do you know that the one is replaced by the other and not one
>>>has been removed and another added? The answer would be that something
>>>like for the latter you would use two assignments. So actually what you
>>>are doing is making assumption about the databases is updated, i.e.,
>>>assuming certain dynamic constraints.
>>
>>But the consequence is then, that relational assignment is nothing
>>more than a single tuple update.

>
>
> No, the consequence is that some assignments are recognized as a single
> tuple update.

This is an unwanted and unwarranted limitations. For a (very practical example) I have phone numbers in a country comprising of ten digits. Of course, the phone number is the natural key for the "phone line" (or whatever is the proper denomination), and I'd argue that no other key is really necessary, so we can imagine that a phoen number is foreign key in _lots_ of places.

Then a need is recognized to have 11 digits in order to make room for more numbers. The existing numbers will be recognized by prefixing them with, let's say, the digit "5". Quite easy, right ? UPDATE ........ SET number= '5' || number.

No big deal so far, the changes *should* be propagated in all places.

Relational assignment doesn't support this. Therefore, I couldn't care less if (some) relational model is well defined and consistent and sound with only relational assignment as the one and only mean to change the database state.

It turns out that it will not be powerful enough to express some basic needs of users in an efficient way. Need to think of a better model.

What's so fishy about UPDATE after all.

I'd say that we can define an operator over relvars

        UPDATE (relvar, transformation)
where relvar has to be the name of a relvar (later we can think of views, maybe), and /transformation/ should be a function TUPLETYPE_OF(relvar) -> TUPLETYPE_OF(relvar). I ommitted the WHERE clause because it can eb easily expressed by letting the transformation be the identity when restricted to tuples that don't match.

Now the semantics is clear (kind of), but I try, we have to cases:

• transformation is injective with regards to the keys (all the candidate keys) -- formally that means the projection of tuple over the candidate key composed with the transformation is injective. In this case the new tuple values clearly correspond uniquely toold tuple values and we can propagate the foreign keys all over the place. The phone number transformation is such an example. Other can be doubling of an integer key, (legally) changing a business name, and there are tons of practical situations.
• the transformation is not injective. Then the operation *should* fail. If the user (programmer) wants the operation not to fail for some semantical reason (it's valid as far as business rules are concerned ), then he should not seek to apply the UPDATE operator.

In the second case I took the safe way. An alternative risky way (it puts more dangerous stuff in the hands of the programmer) is in the second case to propagate all the new keys to all the foreign keys corresponding to the pre-image of the new key.

>>I will repeat myself (from another post): relational assignment sounds
>>good at first sight, but not all practical problems can be solved via
>>assignment as the transitional aspects are lost. In my mind, the
>>consequence is that that what corresponds to an SQL UPDATE can not be
>>supported via relational assignment and that some kind of
>>update-operator must come into play.

>
>
> But I have just shown how it can be done! What was wrong with that, except
> that it is kinda ugly?

It's unnecessarily limited.

>>Those favouring relational assignment (and I actually am one of them)
>>must define this operation in a formal way, taking into account in
>>particular how entities are identified (when there are multiple
>>candidate (or using Jan Hidders terminology: super-) keys). They must
>>define the meaning of "new" tuples and tuples that disappear, and they
>>must determine which triggers are called and when.

>
>
> I used the term "strong key" (a super-key is already something else). A
> fancier name is "diachronic key" as it tells you accross time when two
> tuples represent the same entity. Note that every diachronic key is also a
> candidate key but not every candidate key is necessarily a diachronic key.
> However, this notion may not always solve the problem because often it is
> exactly this key that is updated, and then you still have the problem that
> one entity has disappeard and another one (with a different diachronic key)
> has appeared after the assignment.

I think I've made a strong argument elsewhere that such a strong key is unnecessary. You definitely don't need it in the relational model, and you don't need it in the ER models. AFAIK it is a pre-requisite in most OODB models.

>>Purists may reject this on the grounds, that the purity of the model
>>suffer, but the alternative is a model that does not reflect reality,
>>and if you must choose between simplicity and reality, the choice
>>should not be so difficult.

>
>
> That's funny, because that is exactly why I suggested that the ER model is
> often a better data model then the Relational model. There the notion of
> update of an entity is much more natural.

It "smells" natural, but it ain't. It all boils down to the "object identity", the much cried for notion of OO developers. This thing may exist in reality, but since our software model are merely a description of reality, I don't think we can drag the identity of things into our descriptions.

And it then even if we do that we risk lots of things, like confounding the identity of our programming constructs used to reflect things with the identity of the things themselves. One example is that many OO books on design that have a chapter on relational "mapping", attach an OID to every table to represent the objects, and that is the one and only key in the table. And of course, I've seen it in practice quite a few times.

I'm sure that if you offer them an ER-model database with "strong keys" as you propose, they won't bother to define any other key anymore.

> -- Jan Hidders

Costin Cozianu Received on Wed Sep 25 2002 - 18:23:41 CDT