Re: NULLs

From: Marshall <marshall.spight_at_gmail.com>
Date: Sat, 29 Dec 2007 10:34:06 -0800 (PST)
Message-ID: <6cb1efcb-9dcb-45f2-9d36-62ec4d8355ed_at_a35g2000prf.googlegroups.com>


On Dec 28, 2:59 pm, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
>
> 3VL is not a result of using NULL to represent missing information, but
> a result of allowing missing information. IMO, there are only two
> options: either you deal with missing information, and with the 3VL that
> results from it -- or you somehow alter reality so that information is
> never missing again, for any reason.

Full normalization is a third option.

> > There has to be a more elegant way....Regards, J.
>
> I doubt it. Given this information:
>
> * "Employee Jack is male"
> * "Employee Mary is female"
> * "Employee Jack is 43 years old"
> * "Employee JJ is 32 years old"
>
> how would you answer the below questions:
>
> * "List all employees aged 40 and above".
> * "What is the average age of our employees?"
> * "For each employee, how many years left until retirement (assume a
> country with laws for retirement age of 65)"
> * "List all employees that are female, under 35 years old, or both"
> * "Is JJ older than Mary?"
> * etc

Given the schema of the database you have supplied above, the questions you propose are not askable, in the same way as if you had proposed the question "which animals are marsupial?"

Asking which employees are 40 and over *requires* the predicate (employee, age) be present, either directly or as a projection or join or whatever. Since it isn't, you can't *ask* the question. Note that I didn't say "answer" the question--you can't even *ask* the question. It's not well formed.

(You could of course ask the question "for those employees for whom we know their age, which are 40 and over?")

> [...]
>
> I can give answers to all questions above, but I have to use 3VL in all
> cases.

Not exactly. You can give answers to questions that are similar to the ones asked above, but modified in the way I mention parenthetically.

> For me, that signifies that 3VL is part of reality and hence
> can't be left out of a database that attempts to model (aspects of)
> reality.

I agree that missing information is often a fact of life, but I don't agree that NULL or 3VL necessarily follows. There are other options, and furthermore, I think our experience with SQL shows that the other options are better ones.

Marshall Received on Sat Dec 29 2007 - 19:34:06 CET

Original text of this message