Re: Examples of SQL anomalies?

"David Cressey" <cressey73_at_verizon.net> wrote in message news:Z5ndk.1168$Ae3.265_at_trnddc05...
>
> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:whadk.31863$ZE5.6401_at_nlpi061.nbdc.sbc.com...
>>
>> "David Cressey" <cressey73_at_verizon.net> wrote in message
>> news:oe9dk.1109$Ae3.832_at_trnddc05...
>> >
>> > "Marshall" <marshall.spight_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 don't think that's right.
>>
>> CWA: "If there ain't no row, then it ain't so."
>>
>> But there is a row:
>>
>> Package {{PackageNumber:12341, Weight:NULL}}
>>
>> Translation: there is a Package with a PackageNumber that is an element
>> of
>> the set of all of PackageNumbers and with a Weight that is an element of
> the
>> set of all Weights; oh, and by the way, the PackageNumber happens to be
>> 12341.
>>
>
> I disagree. Your interpretation is that a package number with no recorded
> weight does, in fact, have a weight drawn for the set of all weights is
> an
> interpretation on your poart of the real world. It isn't inherent in the
> database schema.
>

If NULL can only mean "a value should have been supplied but hasn't" then it /is/ inherent in the database schema.

> There could be a database schema where a NULL in the wieght column is
> intended to convey that weight is inapplicable to this given package
> number.
> This is the same information that would be conveyed by omitting a row in a
> table that consists only of {PackageNumber, Weight}.
>

Not if NULL can only mean "a value should have been supplied but hasn't." And by the way, the information conveyed by omitting a row would depend upon whether the OWA or the CWA applied to that table. Under the OWA, it could be either that the weight is not known or that the weight does not apply. Under the CWA, on the other hand, it can only mean that the weight does not apply.

> NULL means that there is no data here. It doesn't necessarily mean that
> the
> value is unknown.
>

NULL should necessarily mean "a value should have been supplied but hasn't." A schema that allows inapplicable nulls is a schema that has a structural defect. Inapplicable nulls are not placeholders for information that is missing: they are placeholders for nothingness. In a perfect world where every value has been supplied there should be no nulls, but in a schema that allows inapplicable nulls there would still be some. In addition, a schema that allows inapplicable nulls necessarily employs disjunctive predicates.

A table R{X, Y, Z}that allows inapplicable NULLs in the Z column has as a predicate something like,

Pxy \/ (~Pxy /\ Qxyz)

Which can easily be transformed into a pair of relations that do not have disjunctive predicates along with an interrelational constraint.

P{X, Y}, Q{X, Y, Z}, ISEMTPY(P JOIN Q) Received on Thu Jul 10 2008 - 20:13:18 CEST