Re: relationship in the database

In article <3D92457D.1030608_at_hotmail.com>, Costin Cozianu <c_cozianu_at_hotmail.com> wrote:
>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. [...]
>>>
>>>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.

What limitations? There are no limitations.

>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. [...]
>
>Relational assignment doesn't support this.

Neither does the UPDATE statement. It's the triggers that do the job, and relational assignment does not exclude thepossibility of triggers.

>What's so fishy about UPDATE after all.

It assumes a hidden notion of object identity.

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

A transformation is injective if two different relations are always mapped to different relations. I don't think that's what you mean here. I'm guessing you mean that every result of the transformation satsfies the key constraints. That's an undecidable property, by the way, so I don't think you would want to depend on that.

> In this
>case the new tuple values clearly correspond uniquely toold tuple values
>and we can propagate the foreign keys all over the place.

No, that is not enough. If I start with the the keys '1' and '2' and they are mapped to '3' and '4' then I don't know who became '3' and who became '4'.

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

Again, there are no limitations.

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

You *do* need it in the ER model if you are going to consider updates. Normally these aren't considered, so that's why you don't see them usually. In OODB models there is also no such concept because you have OIDs. Please don't confuse diachronic keys with OIDs, they are completely unrelated concepts.

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

Your terminology is very sloppy here, and taken literally you couldn't be more wrong. Determining the identity of things, i.e., determining the keys, is one of the most important things you have to do when you design a data model. I think you are confusing "the identity of things" with OIDs which you seem to think that I'm advocating. I don't. Perhaps you are confused by the fact that I argued that OIDs and surrogate identifiers are not meaningless. That's not the same as saying that they are a good idea. The world is not that simple.

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

Please reread my description of what such keys actually are. You seem to be attacking something that I never proposed.

• Jan Hidders