Re: A real world example

"J M Davitt" <jdavitt_at_aeneas.net> wrote in message news:HiaFg.65037$u11.64869_at_tornado.ohiordc.rr.com...
> Brian Selzer wrote:
>> "JOG" <jog_at_cs.nott.ac.uk> wrote in message
>> news:1155809294.447326.279260_at_m73g2000cwd.googlegroups.com...
>>
>>>Brian Selzer wrote:
>>>
>>>>[snip]
>
> [more snip]
>
>> Then the model should take this into account in its definition. That may
>> embody changing the definition of a key, or changing its treatment of
>> attributes in the definition of a relation schema, or both. You can
>> define multiplicity constraints, and that is defined in the model. Maybe
>> you could define mutability constraints, and include that in the model.
>> Maybe the entity integrity rule could be changed to include restrictions
>> against mutable attributes as well as nullable attributes. I don't know.
>> All I know is that I can break it, and that should be addressed somehow.
>
> Stop right there!
>
> On "conceptual model of transactions" we learned of /replacement/
> updates and /modification/ updates and some obscure theory of
> transactions.
>

In the context of a transaction, there is a definite difference between the semantics of replacement and modification. And that difference can affect concurrency. If you can't perceive that difference, let me know, and I'll try again to explain it.

> Earlier we got /individual/ and /universal/ attributes and some
> vague requirement that the relational model - or DBMS - keep track
> of which was which and somehow treat them differently.
>

Those terms stuck in my head. I'm not sure where I got them, but I think it was in a discussion or paper about onatology that talked about the categories of urelements. If I remember correctly, (it was a long time ago), individual urelements, like substance, are part of the essence of something, whereas universal urelements, such as time, location, etc., are situational, that is, they depend on the state of the universe.

> Now we see there exist /multiplicity constraints/ and /mutability
> constraints/ and /entity integrity/ rules and /restrictions against
> nullable attributes/.
>

Isn't NULL/NOT NULL a multiplicity constraint? Isn't the entity integrity rule one of Codd's rules? Doesn't it restrict the use of nullable attributes? I didn't make any of that up. Mutability is a common programming concept, all I suggested was that it might improve the situation. And what's wrong with suggesting improvements?

> [Such a fertile field, this database theory; so much is unknown!]

>
> Brian, please stop making this stuff up! You say, "I can break
> it, and that should be addressed somehow." Then you carry on trying
> to convince us that the database should provide a solution to the
> problem you face. All along, we've been saying, "If these things
> are problems, your design is broken."
>

And I'm saying that no matter how good your design is, if it includes a transition constraint without an immutable key, I can issue a multi-tuple update that will violate that constraint, leaving the database in an inconsistent state. Doesn't that violate the Golden Rule? There are implementation-specific extensions that can prevent it, but that would tie the database to a particular implementation.

I've illustrated the problem in I don't even remember how many ways. I've been begging Microsoft to implement FOR EACH ROW triggers in Sql Server to work around a problem I've encountered many times that mirrors exactly the limitation I perceive in the model as it is defined. If the only key can change, then you can't correlate the rows in the deleted pseudotable with the inserted pseudotable, and therefore, you cannot determine with certainty what changed (unless there's only one row). If the only key can change, then you can't correlate the tuples in the current instance with those in the proposed instance, and therefore, you cannot enforce transition constraints (unless there's only one tuple).

> Relational theory provides all you need to meet the requirements
> you've described here.
>

>> I would have said, "If no natural key is both recordable and immutable
>> then the designer must use an artifical surrogate for it."
>
> Let me ask: is the surrogate immutable?

It should be. The value, once assigned, shouldn't change for as long as the entity is referenced by the database, and even after it's not, that value shouldn't be used again. Received on Fri Aug 18 2006 - 11:18:37 CEST