Re: Proposal: 6NF

On Sat, 7 Oct 2006 12:45:57 +0300, Sampo Syreeni wrote:

>On 2006-10-05, Hugo Kornelis wrote:
>
>> In that case, you should model it as such - in a relational table, you
>> add a column "CanLayEggs" with a domain consisting of the values Yes
>> and No.
>
>After that you have a form of redundancy in the table, because only
>knowing CanLayEggs=No allows you to fully determine eggs_laid. From the
>point of view of information content, fields that are fully determined
>are not informative and they shouldn't be included.

Hi Sampo,

The column eggs-laid is not fully determined in this case, since there will also be animals that can lay eggs. You can't remove the column without losing relevant information for those animals.

There are alternatives, though - you could model egg-laying animals as a subtype of animals and move the EggsLaid column to the table for the egg-laying animals. Such an approach has booth positive and negative consequences. Other apporaches are possible as well - for instance with a CHECK constraint.

> Or, from another
>point of view, you've essentially made eggs_laid optional, i.e. a
>repeating group which occurs from zero to one times.

I'd rather say that I've made eggs_laid mandatory for egg-laying animals and prohibited for others.

(snip)
>Also, how do you then enforce the constraint that camels always have
>CanLayEggs=No?

Same as you enforce the constraint that Hugo Kornelis always has country of birth = Netherlands: you don't.

Constraints can only guard the logical relationships between facts, never the truth of facts. If you enter country of birth = Belgium for Hugo Kornelis, it will be accepted. But if you also enter state of birth as Illinois, you'll get an error, since Illinois is not in Belgium.

Similarly, the computer should believe you if you tell it that yes, cales can lay eggs - but don't try to tell it that any animal thhat can not lay eggs has just laid four eggs; that would violate a constraint.

>
>> Don't try to make NULL mean "there may not be a value here" - it
>> doesn't mean that. The *only* meaning NULL has is "value is missing".
>
>That depends on whom you ask, how many different kinds of nulls you
>think there really are

I'm convinced that from a database point of view, NULL can and should only mean "no value here". The application and the human operator might have different interpretaions, depending on context - same as the number "2" is also interpreted different when in the "Grade" column or when in the "Age" column.

Any attempt to bring the different possible meanings of NULL into the database itself is doomed to fail, since it breaks the foundation of 3VL and as a result, breaks SQL itself.

Best, Hugo Received on Tue Oct 10 2006 - 01:42:53 CEST