Re: Examples of SQL anomalies?

From: David Cressey <cressey73_at_verizon.net>
Date: Thu, 10 Jul 2008 12:28:22 GMT
Message-ID: <Gfndk.1169$Ae3.1064@trnddc05>

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:4b16829a-8848-4b92-8afd-6d5951db91f2_at_34g2000hsf.googlegroups.com...
> On Jul 9, 1:31 pm, "David Cressey" <cresse..._at_verizon.net> wrote:
> > "Marshall" <marshall.spi..._at_gmail.com> wrote in message
> >
> > news:19040b82-0130-479e-ab80-dc1f1597ac02_at_56g2000hsm.googlegroups.com...
> >
> > > On Jul 7, 1:21 pm, JOG <j..._at_cs.nott.ac.uk> wrote:
> > > > On Jul 5, 1:09 pm, "David Cressey" <cresse..._at_verizon.net> wrote:
> >
> > > > > "Marshall" <marshall.spi..._at_gmail.com> wrote in message
> >
> > > >

>news:6ad51b62-e66a-4daa-b21c-c361fd6b22f8_at_8g2000hse.googlegroups.com...

> >
> > > > > > What can be meaningfully asked is determined by the schema.
> >
> > > > > > If the schema specifies that the weight attribute is nullable,
> > > > > > then the question of how much a shipment weighs in total
> > > > > > is a question that cannot be asked.
> >
> > > > > > Marshall
> >
> > > > > Bingo!
> >
> > > > However, one /can/ validly ask "please give me the minimum the
> > > > shipment weighs" and this may still be very useful.
> >
> > > Well, that's assuming that all the shipments have a positive
> > > weight. What if we might ship helium baloons? Then you
> > > can't set the minimum.
> >
> > > OK I was just having some fun there.
> >
> > > > This is of course
> > > > not a defence of null markers (obviously not. its me), but rather
just
> > > > a precaution against ruling out all questions of irregular data in
> > > > blanket fashion. Our aim should be to provide frameworks that allows
> > > > us to ask these questions with syntactic correctness /and/ as
> > > > parsimoniously as possible.
> >
> > > Sure.
> >
> > > My idea of the phrasing of the sum() over a nullable weight
> > > is "what is the sum of the weights of the items in the shipment
> > > for which the weight has been entered into the db?"
> >
> > That is, IIUYC , the sum of all the wieghts, as far as the db knows
them,
> > right?
> >
> > This amounts to the "open world assumption" with regard to weights of
items.
> >
> > If one is going to adopt a "closed world assumption" with regard to
weights
> > of entered items, then it would seem to me that the weights column
would
> > have to be declared not nullable. Meaning that, if an item is entered
nto
> > a row, the weight column may not be left null.
> >
> > In the case of not nullable columns, this entire subthread is moot.
> >
> > Brian seems to be willing to bounce back and forth between OWA and CWA,
> > without making any entries in the schema to reflect which assumption is
> > operative. Or maybe it's just a disconnect between Brian and SQL.
>

> I think I agree with all of that. (But I am not so confident that I
> understand
> what does or does not constitute CWA/OWA under a system with
> NULLs.)
>
>

> Marshall

Here's the best I can do with OWA/CWA as it applies to NULLS. Treat every table with NULLABLE columns as a materialized outer join between a table containing the PK of that table and the nullable column, and some other table with the rest of the data in it. (For purposes of this discussion, I'd like to consider a column "non nullable" in the situation where the schema does not forbid NULLS but every application that inserts or updates data is guaranteed to weed out NULLS. This isn't necessarily a good practice.)
Let's call the decomposed tables the "normalized equivalent" of the table with a NULLABLE column.

It can be seen fairly easily that the omission of a row in the {PK, Optional Data} table corresponds to the inclusion of a NULL in the optional data column of the composed table.

Now the question of "What does the NULL mean" devolves down to the question of what the omission of a row means in the equivalent normlaized tables. This is where OWA/CWA comes in. Under CWA, the omission of a given row is tantamount to denying the existence of such a row. The NULL must therefore mean "not applicable" under a CWA assumption. Under an OWA assumption, the NULL could mean, "value unknown".

Does this make sense? Received on Thu Jul 10 2008 - 07:28:22 CDT

Original text of this message