Re: NULLs: theoretical problems?

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Thu, 9 Aug 2007 22:25:51 +0100
Message-ID: <KrOdna4V_eT9GybbRVnyugA@giganews.com>

"paul c" <toledobythesea_at_oohay.ac> wrote in message news:vnIui.42734$_d2.2251_at_pd7urf3no...

> Hugo Kornelis wrote:

>> On Wed, 08 Aug 2007 20:42:52 GMT, paul c wrote:
>>
>>> Hugo Kornelis wrote:
>>>> ...
>>>> The ANSI standard defines NULL as follows:
>>>>
>>>> "null value: A special value that is used to indicate the
>>>> absence of any data value."
>>>>
>>>> Thus, the only concept collapsed into NULL is that of "no value here".
>>>> ...

> ...

>> <nitpicking>
>> The ANSI text doesn't define "the null value" (as they call it) as a
>> value that conveys that the value is not _a_ value, but as a _special_
>> value that conveys that there is no _data_ value.
>> </nitpicking>
>>
>> Personally, I never call NULL a value, and I prefer to describe NULL as
>> a "marker that indicates the absence of any value". But I couldn't
>> really write that and still claim to be citing ANSI, eh?
>> ...

>
> I noticed in your blog you said that the table with a null-able birthday 
> column was in 2NF.  If saying that is right, I suppose we must be careful 
> to regard functional dependencies as determining values sometimes and 
> non-values other times.

But Hugo is not right. From the Alice Book, p163:

"A relation I over U satisfies X -> Y, if for each pair s, t of tuples in I, s{X} = t{X} implies s{Y} = t{Y}."

Since null = null is not true, no attribute with nulls satisfies any FD, not even the trivial one {A}->{A}!

Furthermore, no relation with nulls satisfies any join dependency since join dependency requires a natural join and a natural join involving nulls will exclude some tuples. Chris Date this time:

"Let A1, A2,., An be subsets of the heading of relvar r. Then r satisfies the join dependency (JD) *{A1,A2,.,An} if and only if every relation that's a legal value for R is equal to the join of its projections on A1, A2,., An."

Naturally enough it follows that any relation hypothetically containing null values ought to be decomposed so as to eliminate them, ie. 5NF.

-- 
David Portas

Received on Thu Aug 09 2007 - 16:25:51 CDT