Re: a union is always a join!

From: Brian Selzer <brian_at_selzer-software.com>
Date: Wed, 18 Mar 2009 02:31:11 -0400
Message-ID: <Sy0wl.22503$Ws1.17406_at_nlpi064.nbdc.sbc.com>

"Tegiri Nenashi" <TegiriNenashi_at_gmail.com> wrote in message news:f578bb12-97bd-4280-a420-140ae35261f3_at_u18g2000pro.googlegroups.com...
> On Mar 17, 2:56 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > "Walter Mitty" <wami..._at_verizon.net> wrote in message
> >
> > news:EGNvl.685$SU3.443_at_nwrddc02.gnilink.net...
> >
> > > "Brian Selzer" <br..._at_selzer-software.com> wrote in message
> > >news:c4Fvl.26433$ZP4.2289_at_nlpi067.nbdc.sbc.com...
> >
> > >> 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.

I disagree. Your argument rests on the premise that keys permanently identify things in the universe of discourse. But by definition, all that is required is that no two tuples in the same relation in the same database have the same key components. What that means is that what identifies something in the universe of discourse at t' could identify something totally different at t''.

In other words, just because keys are the same at different times doesn't necessarily mean that they map to the same thing in the universe of discourse, and similarly, just because keys are different at different times doesn't necessarily mean that they don't map to the same thing in the universe of discourse.

> Morale: you
> can't possibly define what update is without a key.

Yes, you can: Consider the equivalence between the two D expressions from /TTM Third Edition/ pages 112-113:

UPDATE r (Ai := X, Aj := Y)

where i != j is equivalent to

( ( EXTEND r ADD ( X AS Bi, Y AS Bj ) )

{ ALL BUT Ai, Aj } RENAME { Bi AS Bk, Bj AS Aj, Bk AS Ai}

Now let's look a bit more closely at this expression. The first part