Re: Newbie question about db normalization theory: redundant keys OK?

"Brian Selzer" <brian@xxxxxxxxxxxxxxxxxxx> wrote in message
news:pAIcj.788$pA7.550@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"David Cressey" <cressey73@xxxxxxxxxxx> wrote in message
news:iI7aj.7472$1X.1681@xxxxxxxxxxx

"Brian Selzer" <brian@xxxxxxxxxxxxxxxxxxx> wrote in message
news:542aj.25103$4V6.21112@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx


You're right about DELETE and INSERT, but not about UPDATE. If you look
at
UPDATE in TTM (pps. 112-113), you can see what happens:

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

where i <> j is supposedly 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 )

where Bi, Bj, and Bk are arbitrary distinct attribute names that do not
appear in r.

Now if you look at the result of

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

you'll notice that each tuple has both the old values and the new values
for
each affected attribute. Clearly if Ai or Aj is prime, then that
information that ties each tuple in the result to its corresponding
tuple
in
r is projected away by "{ ALL BUT Ai, Aj }". Information is lost. The
result may be the same, but how that result was arrived at is lost in
translation, and therefore cannot be verified.


It seems to me that, in many discussions over the past few months, you
have
been emphasizing the difference between DELETE followed by INSERT on the
one
hand, and UPDATE on the other, even when the final result of both is a
database in the same state.

The question arises whether the database communicates any data other than
the data contained in its state. I'm going to pull a quote from Codd's
1970
paper. Even though this quote is about consistency, rather than
identity,
I want to draw your attention to the wording.

"It is important to note that consistency as defined above
is a property of the instantaneous state of a data bank, and
is independent of how that state came about. Thus, in
particular, there is no distinction made on the basis of
whether a user generated an inconsistency due to an act of
omission or an act of commission. "

It seems to me that all of us who have been building or working with
relational databases, regardless of whether we use SQL as an interface,
or
some tool that might be superior to SQL, have been treating database
state
as independent of history in exactly the above sense. Not only when we
deal
with consistency, but also with regard to entity identity. I don't know
of
any revision made by D&D or others to that perspective.

The languages described in this newsgroup differ from SQL in at least one
important respect. SQL distinguishes between actions and transactions.
For
reasons that they themselves have outlined, D&D have come up with a
language in which every transaction can be expressed as a single action.
I
can see the advantages of such a pattern, even without any prectical
experience in such a language. But even with this difference, the
difference between history and state is just about identical between SQL
and
the relational language.

It seems to me that, if you are going to assert that the history of a
database is part of the interpretation of its content, it's going to be
up
to you to explain how a database can serve up any relevant portions of
its
own history as data.

If it isn't data, we don't know it.

I think that until this matter gets resolved somehow, there is going to
be
a continuing disconnect between you and a lot of the regulars in c.d.t.


It's not about history, it's about continuity. Like it or not, from a
logical standpoint, during a successful modification there are two
separate database values which represent what has been the case and what
is the case. They cannot both /be/ the case, due to the closed world
assumption. Also, key values were always intended to map to individuals
in the universe of discourse, so since each tuple contains at least one
key value, each tuple maps to at least one individual. The domain closure,
unique name and closed world assumptions make it possible to establish
both existence and identity independent of any specific interpretation, so
it should be possible to track an individual throughout a modification,
even if its identifying features differ before and after. The problem is
that a key value that maps to a particular individual before the
modification may map to a different individual after the modification, and
consequently, any comparison that involves values from before and after
requires greater scrutiny.

Here is the problem with treating assignment as the only primitive
operation. With assignment, all you have available is the before and
after images of the data, but there may be many different transformations
that could have produced the after image from the before image, and
there's no way to tell which transformation actually occurred.


Yes there is. You say you would solve this using a "for each row trigger"
mechanism. But a trigger is just a procedural construct for carrying out
other relational assignments (if not then I'd like to know what else it
does). Presumably the purpose of those other assigments is to record the
information that was lost from the transformation represented by some
hypothetical UPDATE operator. So why not record that information EXPLICITLY
as part of the same operation that performed the original update - ie. an
assignment?

Instead, you seem to prefer an UPDATE operation of a kind that you think is
flawed - and then propose using a trigger as a crutch to protect the user
from leaving out essential information!!

RM is fundamentally a type system. If you cannot agree that assignment is a
primitive operation then I think you must sacrifice that very foundation.
You are not describing an RDBMS as I know it.

--
David Portas


.

Relevant Pages

  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... The question arises whether the database communicates any data other ... relational databases, regardless of whether we use SQL as an interface, ... difference between history and state is just about identical between SQL ... Here is the problem with treating assignment as the only primitive ...
    (comp.databases.theory)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... The question arises whether the database communicates any data other than ... some tool that might be superior to SQL, ... as independent of history in exactly the above sense. ... language in which every transaction can be expressed as a single action. ...
    (comp.databases.theory)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... The question arises whether the database communicates any data other than ... some tool that might be superior to SQL, ... difference between history and state is just about identical between SQL ... to a particular individual before the modification may map to a different ...
    (comp.databases.theory)
  • Re: About Hashtable...
    ... But my assignment simply does not allow me to use ... > any SQL database... ... what I targeting is to use ...
    (comp.lang.java.programmer)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
Loading