Re: Examples of SQL anomalies?

On Jul 10, 3:26 pm, "David Cressey" <cresse..._at_verizon.net> wrote:
> "Roy Hann" <specia..._at_processed.almost.meat> wrote in message
>
> news:U9qdnWeUwPYBmuvVnZ2dnUVZ8qfinZ2d_at_pipex.net...
>
>
>
> > David Cressey wrote:
>
> > > 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?
>
> > To some extent it does, but I think this entire discussion is utterly
> > confused about something more fundamental than open- or closed-world
> > hypotheses.
>
> > The database is not reality. It is (only) a faithful record of our
> > testimony *about* reality.
>
> You are right. The divergence between reality and what the database says
> about reality covers a lot more ground than this discussion covers.

I think the Roy is actually making a stronger distinction than this David. A database does not model reality at all. It models propositions (that may or may not comment on reality. who knows. the database sure doesn't care about it). I think it is the very act of *not* making this distinction which generates the confusion of thinking one can ask a database questions like:

"How many employees are over 30"

when we in fact can only ever ask:

"How many propositions are there listing employees over 30".

I think we ought emphasize this distinction more imo. Databases model propositions, not reality.

>
> > A database design that presumes to model reality instead of modeling
> > what we will actually say about reality is not fit for purpose. And if
> > a model of the latter is more complicated, tough. Simple but wrong is
> > not right.
>
> In my practice, what NULL meant, pretty much all the time, was roughly
> this:
>
> "In an ideal world, this NULL would not be here. Either the cell that
> contains it would be missing, or the cell that contains it would contain a
> value instead of a NULL. This isn't an ideal world. Let the reader deal
> with it."
>
> Many times, I dealt with schemas that I did not design, and applications I
> did not write. In those situations, NULL might have meant whatever those
> in control wanted it to mean.
>
>
>
> > --
> > Roy
Received on Thu Jul 10 2008 - 18:04:44 CEST