Re: A real world example

From: JOG <jog_at_cs.nott.ac.uk>
Date: 18 Aug 2006 09:01:51 -0700
Message-ID: <1155916911.675206.120990@75g2000cwc.googlegroups.com>

Brian believes tuples in seperate relation values should correspond to each other, even when /none of the items/ they discuss have even a single key attribute in common.

I don't believe as bob does that I have to scream 'bullshitter' (I'm british, and quite frankly I find it vulgar), but having tried to help, at some point you just have to give up.

There is just no way to reason with Brian's logic about hiding artificial keys, as it is based on a flawed assumption about identification, and means he genuinely can't see that the problems he is imagining are just due to poor design.

erk wrote:
> 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.
> > >
> >
> > 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?

>

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

> >

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

>

> 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

> > > 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.
>
> - erk
Received on Fri Aug 18 2006 - 11:01:51 CDT