Re: A real world example

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@xxxxxxxxxx> wrote in message
news:HiaFg.65037$u11.64869@xxxxxxxxxxxxxxxxxxxxxxxxx
Brian Selzer wrote:
"JOG" <jog@xxxxxxxxxxxxx> wrote in message
news:1155809294.447326.279260@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

.

Loading