Re: Examples of SQL anomalies?

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.

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.

-- 
Roy