• Tom.Thomson (3/2/2010)


    I'd like to see you explain (a) how you would do this without any sort of NULL and (b) in what sense this two column table is not fully normalised.

    (a)

    CREATE TABLE WidgetWithName (widget_id INT NOT NULL PRIMARY KEY /* REFERENCES Widget ?? */);

    CREATE TABLE WidgetName (widget_id INT NOT NULL REFERENCES WidgetWithName (widget_id), widget_principal_name_id INT NOT NULL);

    Using a null in place of a value that exists but is unknown would be a less accurate representation because null in SQL does not accurately represent the unknown case. In my example I can accurately represent a widget with a name attribute, a widget without and a widget with a name which is not known.

    (b) No table with a null is normalized because all the classic normal forms are defined only for relations without nulls (see E.F.Codd quoted earlier in this thread). To have a sound basis for saying that a table with a null is normalized you would therefore have to reinvent those definitions, in which case I think you ought to state clearly what definitions you are going to use. For example what is a join dependency on a table with nulls? In SQL "nulls don't join" therefore a SQL table with a null is not the join of the projections on its superkeys and therefore it is not in Fifth Normal Form according to the conventional definition of a join dependency.