Re: ID field as logical address

"Walter Mitty" <wamitty_at_verizon.net> wrote in message news:fv9Ul.1465$Cc1.1327_at_nwrddc01.gnilink.net...
>
> "paul c" <toledobythesea_at_oohay.ac> wrote in message
> news:vdUTl.29814$PH1.1299_at_edtnps82...
>> Just a couple of comments about a post that is unusually vague and fuzzy
>> coming from you, it's tedious to dismantle every sentence, so I mention
>> only a couple:
>
> I apologize for how vague and fuzzy this topic is. If my wording is vague
> and fuzzy, my thinking is even more vague and fuzzy.
>
> The first thing is whether or not I'm talking about a tuple within a
> tuple. This was a digression. As far as I'm concerned, the difference
> between the following two is not fundamental:
>
> (714, Joe, Friday, 234-5678)
> (714, (Joe, Friday,234-5678))
>
> At least not fundamental for the purposes of figuring out how "714" comes
> to be associated with Joe Friday and not with some other policeman.
>
> Next, the question arises whether the association is managed by the
> database or whether its managed programatically or by some external
> function, manual or automatic.
> Some responder addressed precisely that question. I think it was Bernard.
> In my experience discussing this topic with fans of the ID field concept,
> I've found that, contrary to Bernard's opinion, they turn over management
> of the association to the database. I smell a rat at this point, because
> I think they are asking a database to perform a function that is not
> really a database function. I think that assigning 714 to Joe Friday is
> inventing data, and it's not the database system's job to invent data.
> It's the database system's job to manage the data it's given. That's not
> to say that some popular tool like MS Access might not do it anyway.
>
> And it's not clear to me whether MS Access is really a DBMS. I think it's
> an application management system.
>
> I think the process by which 714 comes to be associated with Joe Friday is
> actually somewhat mysterious. Googling "Badge 714" confirmed this
> opinion. BTW, I an NOT using "mysterious" as a code word for "mystical".
> The one thing I can say is that it's not a consequence of the data. It's
> either part of the data as given, or it's part of some fairly arbitrary
> process.
>
> Next, my statement of the issue is fogged by my own ignorance of
> relational theory. My introduction to relational theory was only as a
> backdrop to my introduction to database design. At the time, I was
> learning how to design SQL databases, which all of us called "relational
> databases". I don't recall if I heard Ed Codd's name, but I know I never
> read anything he wrote until much later. This informal and spotty
> education notwithstanding, I continue to view my own interpretation of
> relational theory to be largely orthodox, when compared to some of the
> crazy things that have been accepted as "best practices" in database
> design.
>
> Anyway, how would relational theory decribe the operation that I called
> "UPDATE" (using the SQL keyword intentionally.)? would it be something
> like the following?
>
> relvar R gets assigned relvar R minus relation(tuple T) plus
> relation(tuple U).
>
> How does this differ from a DELETE followed by an INSERT?
>
> I don't think this post clears up any of the fuzziness. But maybe it lays
> the groundwork for clearingf it up.
>

Many on this newsgroup adhere with blind zeal to Date and Darwin's interpretation of relational theory that has at its roots the notions that a database is a collection of relation variables (relvars) and that insert, update and delete are somehow "shortcuts" for relational assignments. They refuse to recognize the limitations these notions impose, such as the requirement that every instance of every key be a permanent identifier for something in the Universe of Discourse. If instances of a key do not permanently identify things in the Universe, then transition constraints that join the relation values before and after an assignment to a relvar would not be sound. For example, suppose that you have a relation, {L, F, Stat} Key {L, F}that starts out with the following value:

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

and is then assigned the value,

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

You might be wondering how someone who is Single can become Divorced? Should the assignment be rejected on that basis? Or is it possible that Mary Jones' maiden name is Smith, and that she became Divorced while at the same time the Mary Smith who was Single Married Robert Jones? It might also be that the Mary Smith who was Single moved out of the area while a different Mary Smith, who is Divorced, moved in.

The D&D adherents would try to claim that this is a bad design, and as such should be dismissed, but Codd, even as early as 1970, recognized that key components of tuples would be the target of updates, which should be obvious given that there can be relation schemes in which the entire heading is the key. The ambiguity inherent in relational assignment that is illustrated by the above example is exhibited by every relation in which instances of the key do not permanently identify things in the Universe, so their claim becomes just noise.

Note that the introduction of an autogenerated ID eliminates all confusion:

If you start out with,

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

and then assign

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

then clearly the assignment should be rejected because Single people can't get Divorced, but if you assign

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

then the assignment should be allowed, because Single people can get Married and Married people can get Divorced. Similarly, if you assign,

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

Then it is clear that there is a different Mary Smith, and the assignment should be allowed.

It is a consequence of adopting D&D's notions that demands the introduction of surrogates, such as autogenerated IDs. If, on the other hand, insert, update and delete are not shortcuts, but rather primitive operations, then the introduction of the autogenerated IDs is not necessary. All that is needed is to correlate the tuples in a relation that are the target of an update with those in its successor. For example, the update

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

should clearly be rejected, but the update,

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

should be allowed. Similarly, the delete and insert,

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

should be allowed. Received on Sun May 31 2009 - 04:38:37 CEST