Re: Examples of SQL anomalies?
Date: Thu, 10 Jul 2008 12:28:22 GMT
"Marshall" <marshall.spight_at_gmail.com> wrote in message
> 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...> > > > This is of course
> > > > > > 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.
> > > > not a defence of null markers (obviously not. its me), but rather
> > > > 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
> > right?
> > This amounts to the "open world assumption" with regard to weights of
> > If one is going to adopt a "closed world assumption" with regard to
> > of entered items, then it would seem to me that the weights column
> > have to be declared not nullable. Meaning that, if an item is entered
> > 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
> what does or does not constitute CWA/OWA under a system with
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
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".
Let's call the decomposed tables the "normalized equivalent" of the table with a NULLABLE column.
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 - 14:28:22 CEST