Re: a union is always a join!

From: Tegiri Nenashi <>
Date: Tue, 17 Mar 2009 19:38:25 -0700 (PDT)
Message-ID: <>

On Mar 17, 2:56 pm, "Brian Selzer" <> wrote:
> "Walter Mitty" <> wrote in message
> news:EGNvl.685$
> > "Brian Selzer" <> wrote in message
> >news:c4Fvl.26433$
> >> As an exercise, try to write a set-based update trigger (a statement
> >> trigger in Oracle) on a table with only a composite natural key.  You'll
> >> find that when a column in the key may be the target of an update, that
> >> you can't just join deleted to inserted (old to new in Oracle) to
> >> determine exactly what is different.  (That's one of the main reasons
> >> Oracle and many other implementations support row triggers.)
> > The idea behind entity keys is that they are immutable.  When analyzing
> > the universe of discourse into entities, it's important to discover a
> > reliable identifier for each entity.  In practice, it's sometimes
> > necessary to synthesize identifiers, because the identifiers in common use
> > are unsuitable.  An example might be the use of common names for people at
> > the time when information systems were first being computerized.
> > Foreign keys are immutable during the period of time that they refer to
> > the same thing, for the same reason that entity keys are immutable.  It
> > follows that foreign keys should not have to be updated as a cascade from
> > the update of the entity keys they refer to.  An update to a foreign key
> > that represents negation of the old relationship and assertion of a new
> > one ought to be expressble as a delete followed by an insert.  I could be
> > wrong on this, as I haven't completed the exercise you proposed.
> > The tables I have built with only composite natural keys have all been
> > relationship tables, and not entity tables.  In these cases, the composite
> > key that identifies rows in the relationship table are also foreign keys
> > that reference entities.
> An instance of a relationship isn't any less a thing than an instance of an
> entity, so I think we should try to avoid all of the baggage associated with
> the terms "entity" and "relationship."
> > By the way, are you claiming that all attributes are mutable?
> I'm claiming that whether an attribute is mutable or not is orthogonal to
> the Relational Model.  What about a relation for which the entire heading is
> the key?  Shouldn't it still be possible to issue an update?

Consider R at two time moments R(t') and R(t"). Define

Delta_i = R(t")-R(t')
Delta_d = R(t')-R(t")

Those are informally insertions and deletions when one ignores keys. Now suppose we have a key, that is empty relation K. Consider projections of Delta_i and Delta_d onto K:

Delta_i v K
Delta_i v K

These relations have the same header so their intersection

(Delta_i v K) ^ (Delta_i v K)

is a set of keys that "stays the same", that is update. Morale: you can't possibly define what update is without a key. Exercise: prove that updates are "well defined" regardless of the key choice. Received on Wed Mar 18 2009 - 03:38:25 CET

Original text of this message