Re: A real world example

Brian Selzer wrote:
> "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.
> >

>

>


> > Earlier we got /individual/ and /universal/ attributes and some


> > vague requirement that the relational model - or DBMS - keep track

>

>


> > Now we see there exist /multiplicity constraints/ and /mutability


> > constraints/ and /entity integrity/ rules and /restrictions against

>

Nothing is wrong with the suggestion, but it's a programming concept rife with problems, one that many functional languages do without, as it turns a program into a complex state machine and inhibits many useful forms of analysis (not to mention debugging).

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

Here is what you wrote in the original post, and re-reading it, I have questions about it. Pardon me if I'm re-treading well-worn ground; I'm slow on the uptake.

> With natural keys, that is, keys whose value can be
> different in successive database states,
> it is possible for the values of
> one set of identifying attributes of something in the universe to be
> different in successive universe states,

Perhaps my eyes glazed over in a previous discussion of key mutability, but doesn't simply disabling key updates prevent this, with less machinery than mandatory surrogates?

I would suggest that this case indicates design problems - that someone chose predicates poorly. Design problems can't be solved without some redesign. And I'm not sure that the above statement indicates anything about databases at all; the identity problem is hairy at best and intractable at worst. We make decisions, none of which are perfect, and sometimes realize another decision would have been better (although we often aren't considering that choice's inevitable downside as we're wishing away).

> and when this new information is
> imparted to the database, it is possible for there to be propositions in
> successive database states that should correspond,

Why "should" they correspond? Once the database is in a new state, what difference does the correspondence make? If I update two tuples, swapping every bit of information except the surrogate key, then what difference does the correspondence make? If I swap any possibly identifying attributes, I get the same situation - a consistent surrogate key with inconsistent natural keys.

> but cannot because the
> only relevant set of identifying attributes of something is different in
> successive universe states.

The only set? I thought you were talking about a relation that has one or more natural keys, but to which you're adding a surrogate. Maybe I'm mistaken.

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

>

If you only have one key, and it needs to change, then on what basis would you correlate anyway? And I still don't know why you'd have that situation, or why you'd need a transition constraint based on it.

> 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

>


> >> I would have said, "If no natural key is both recordable and immutable


> >> then the designer must use an artifical surrogate for it."

>

• erk