Re: ID field as logical address

<robur.6_at_gmail.com> wrote in message news:6793e7cf-38d0-4792-9d7e-f3a6b15ff9ba_at_r33g2000yqn.googlegroups.com...
> On Jun 4, 11:19 am, robu..._at_gmail.com wrote:
> > On Jun 3, 7:04 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> >
> > > This is an example of the noise I referred to in a previous post. The
> > > problem exhibited here occurs for /any/ key in which instances of it
> > > do not
> > > permanently identify something in the Universe. This even includes
> > > keys
> > > that are supposedly stable, such as part numbers or GL account
> > > numbers. If
> > > it is possible to issue an update that targets one or more prime
> > > attributes,
> > > then a join of the relations before and after the update can't be
> > > relied
> > > upon for defining transition constraints.
> >
> > I see your point, but the problem has a different and subtle cause.
> > You change the value of both the key and dependent attributes in the
> > same time. So two logical operations are combined in a single
> > relational assignment. This two-in-one approach hides some information
> > from the system. The problem can easily be avoided by performing the
> > operation in two steps (as shown earlier).
>
> In the given example you changed both name and state, or it was a
> different person with the same name. This kind of problems is easily
> solvable by performing the operation in several steps.
>
> However I have to admit that your point sustains in one case even if
> you change only the primary key attribute.

Indeed, there can be relation schemata in which the entire heading is the only candidate key.

> If “Mary Smith, divorced” changes his name in “Mary Jones” (for
> whatever reason not related to marital status), and “Mary Jones,
> single” changes his name in “Mary Smith” (idem) then the constraint
> fails even if there are no changes in their states.
>
> Swapping two key values is solvable only by a multiple assignment and
> an override of the transition constraint. This situation is very
> unlikely, but we have to admit that there is non-zero probability.
> Adding a surrogate key might help but I don’t think it should replace
> the natural keys.
>

It need not. Simply adopt the paradigm in which delete, insert and update are primitive operations and relational assignment is a shortcut for a delete and an insert. As I've pointed out many times in past threads, information is lost when an update is coerced into being an assignment. That information is in fact exactly how each tuple in the current relation differs from its corresponding tuple in the proposed relation--that is, exactly which components are different, tuple by tuple. When a user issues an update, he targets a specific set of tuples and states exactly which components of each targeted tuple differ in the proposed relation and exactly what those new components are. But to prepare for assignment, the components from the current relation are projected away, and thus the correspondence is lost. For example, in the following update, which I borrowed from a previous post,

{{L:Smith, F:Mary, Stat:Single, L':Jones, F':Mary, Stat':Married}, {L:Jones, F:Mary, Stat:Married, L':Smith, F':Mary, Stat':Divorced}}

It can be determined precisely which components of each tuple in the current relation are different from those of its corresponding tuple in the proposed relation because that correspondence is explicitly stated. But in preparing for assignment, the components from the current relation are projected away, leaving

{{L':Jones, F':Mary, Stat':Married},
{L':Smith, F':Mary, Stat':Divorced}}

which upon renaming becomes

{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Divorced}}

In the above example, the update targeted the entire relation, but in general, there would be additional steps required to transform an update into an assignment. One of those involves a union of the set of tuples not targeted by the update. Here more information is lost in that it cannot be determined by simply comparing the current and proposed relations whether or not a tuple was indeed the target of an update. For example, the update,

{{L:Smith, F:Mary, Stat:Single, L':Smith, F':Mary, Stat':Divorced}}

targets just the tuple,

{L:Smith, F:Mary, Stat:Single}

But project away the current components and rename,

{{L:Smith, F:Mary, Stat:Divorced}}

and then union in the set of tuples that weren't targeted,

{{L:Jones, F:Mary, Stat:Married}}

and the result is

{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Divorced}}

Clearly, from

{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Single}}

and

{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Divorced}}

It can no longer be determined whether the tuple

{L:Jones, F:Mary, Stat:Married}

was the target of the update, as that distinction is lost as a result of the union that is required to prepare for assignment.

It is clear, therefore, that the notions of Date and Darwen that a database is a collection of relvars and that insert, update and delete are shortcuts for relational assignment should be discarded, or perhaps relegated to being just teaching devices.

<snip> Received on Thu Jun 04 2009 - 16:10:57 CEST