Re: So what's null then if it's not nothing?

Alexandr Savinov wrote:
> > An RDBMS as a set of know facts. If I have dogs with
> > and without colour then I have at least two relvars one with and one
> > without a colour attribute. If I need to distinguish between colourless
> > dogs and dogs of which I don't know the colour then I add an attribute
> > to indicate that fact. I don't need to add a null.
>
> Sometimes you need. For example, if you are not allowed to create more
> tables.

!! Adding a condition that you aren't allowed to use a data model has absolutely nothing to do with the validity of that model! If my client tells me that 2+2 = 5 that does that mean mathematics is inadequate because it doesn't accommodate his wishes?

> Such design can be more or less attractive but nothing changes
> semantically: removing an attribute from a table (into the second table)
> and storing nulls is one and the same thing.

Not at all the same thing because nulls violate the Information Principle, which is a foundation of RM. Nulls as formulated in SQL also create logical problems that are very hard to solve - at least SQL hasn't succeeded in solving them very satisfactorily.

> For example, assume you
> create a separate table T2 for colourless dogs because you do not want
> to write nulls into T1 (where normal dogs are stored). What if you now
> ask: what colour has a dog from T2 (a colourless dog)? RM says that this
> question does make sense so it is illigal (effectively it prohibits such
> questions from being asked). But I think it is more natural to answer:
> the colour is null.

RM does not prohibit such questions. If it is more natural to give some answer then the user can demand such an answer. The user can decide what he wants to see in the case of a dog with colour or a dog without colour. I would challenge your implication that users find nulls"more natural". I bet most DBMS users wouldn't do a very good job of explaining what a null is. In fact in many database projects the developers go to a lot of trouble to hide nulls from regular users.

> Thus adding new table does change anything except
> that it imposes addition constraints on objects in T2 (they physically
> cannot be assigned a colour which is guaranteed to be null). Right?
>
> Another example, we have two tables Apples and Oranges with different
> attributes. Ask a question: What value takes an Orange attribute for an
> object from Apples? Again my answer is null. Having two tables is only a
> structural constraint and nothing else. We can get an equivalent design
> with only one tables and combined set of attributes.

What would be the key of such a table? Integrity is fundamental. Given the high level of redundancy in your "super-table" and the extereme difficulty of applying constraints to such a design I would say that, except for trivial cases, this probably *cannot* be equivalent to a normalized relational data model. Integrity is fundamental. You can't forget integrity and then say the two models are equivalent.

-- 
David Portas 
SQL Server MVP 
--