Re: 1NF, missing/unknown data, and structured values

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 02 May 2006 04:53:25 GMT
Message-ID: <9BB5g.1783$A26.49380_at_ursa-nb00s0.nbnet.nb.ca>


ralphbecket_at_gmail.com wrote:

> My understanding is that a table is in 1NF if
> - it is a set of rows (i.e., without duplicates)
> - the rows in the table all have the same type
> signature
> - a primary key (i.e., a set of column names)
> exists for the table such that there is a (partial)
> functional relationship from keys to rows (i.e.,
> each key value maps to at most one row in
> the table).
>
> Can someone confirm for me that there are no
> other constraints on column values under 1NF?

There is a constraint on the dbms that it not decompose the values for instance by having a list pointer that iterates through a list.

> In particular, is there any reason why column
> values should not themselves have structure?

A character string has internal structure. An integer has internal structure (an array of bits). A float has internal structure (mantissa, exponent, sign).

However, the dbms treats these as single values whose types define specific operations.

> For example, dates seem like a perfectly good
> type for a column, but dates are structured
> values from which I can project day, month, or
> year.

More precisely, one can define day, month or year operations defined on the date data type. This is true even if one physically represents the date as a julian integer which does not itself have a structure representing days, months or years per se.

> If this is so, surely other structures such as lists
> and various user-defined data types should
> also be supported, provided they support tests
> for (in)equality as required by the RM (so
> higher order types are probably out). Admittedly
> you'd probably need a richer language than SQL
> to manipulate these structure values effectively.

Personally, I have doubts that I would ever prefer a list over a relation valued attribute.

> Taking this idea further, we could go on to define
> a collection of "maybe" types (to borrow from
> functional programming parlance) to handle
> situations where we have missing or inapplicable
> data, without the need to break the RM logic with
> nonsense like NULLs.

This would be possible. I am unsure how often such a design would be a good idea.

> I believe such an approach avoids both the
> problems with NULLs (they break everything) and
> the problem of entirely forbidding structured data
> in columns (normalisation can lead to a large
> number of tables).

If we are talking about relations, then I fail to see any problem with having lots of them.

> Question: is this already supported in SQL and
> the various products out there? If so, why would
> anyone still use NULLs? If it is not supported,
> what is the reason?

Dunno. Received on Tue May 02 2006 - 06:53:25 CEST

Original text of this message