Re: 3 value logic. Why is SQL so special?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 13 Sep 2006 12:47:29 GMT
Message-ID: <B5TNg.19701$9u.208745@ursa-nb00s0.nbnet.nb.ca>

Damien wrote:

> David Portas wrote:
>

>>Alexandr Savinov wrote:
>>
>>>So if you put Actual_Departure_Time in table A, B, C or whatever other
>>>table you find appropriate then it will still describe entities from
>>>Flights table (if linked appropriately of course). Moreover, formally
>>>these alternative schemas are equivalent (again, if we link the tables
>>>appropriately).
>>>
>>
>>What model is that? Relational tables are not "linked" other than in
>>the conceptual sense that their propositions describe some subsets of
>>the same reality.
>>
>>
>>
>>>In this sense there are two extremes:
>>>
>>>1. putting all the data in one wide table (a kind of canonical
>>>representation), and
>>>
>>>2. putting all the data in separate tables (a kind of "one table - one
>>>attribute" approach)
>>>
>>>In the first case all absent attributes will (have to) be denoted by
>>>nulls. In the second case the absent attributes are denoted by the
>>>physical absence of the records. From the point of view of users (data
>>>semantics) nothing changes.
>>
>>Again, a model without nulls cannot be equivalent to one with nulls.
>>One represents information as values in relations and the other does
>>not. From the user's point of view *everything* changes - that after
>>all is the essence of the OP's question.
>>
>>
>>
>>>Real world schemas are normally somewhere in between because both
>>>extreme designs are not efficient: the first due to space, the second
>>>because of time.
>>>
>>
>>If the models were truly equivalent then in principle they could have
>>the same physical representation and therefore any hypothetical
>>difference in storage space and efficiency would disappear.
>>
>>--
>>David Portas

>
>
> I normally just read CDT rather than posting, but I think Alexandr is
> getting at the fact that the following two models are notionally
> equivalent:
>
> (Apologies for posting SQL here, it's closer to how I think than
> relations, plus I've more experience creating examples in it. Hopefully
> you guys can translate)
>
> Model 1:
>
> Create table PartA (KeyColumn int not null, DataColumn1 datetime not
> null);
> Create table PartB (KeyColumn int not null, DataColumn2 datetime not
> null);
> Create view PartNullable(KeyColumn,DataColumn1,DataColumn2) as select
> KeyColumn,DataColumn1,DataColumn2 from PartA full outer join PartB on
> PartA.KeyColumn = PartB.KeyColumn;
>
> Model 2:
> Create table PartNullable(KeyColumn int not null, DataColumn1 datetime
> null, DataColumn2 datetime null);
> Create view PartA(KeyColumn,DataColumn1) as select
> KeyColumn,DataColumn1 from PartNullable where DataColumn1 is not null;
> Create view PartB(KeyColumn,DataColumn2) as select
> KeyColumn,DataColumn2 from PartNullable where DataColumn2 is not null;
>
> So if you're needing to perform operations considering both DataColumn1
> and DataColumn2, you have to accept that one or other may be absent.

But since full outer join is just a shortcut for a UNION statement, a better solution for the first view is to use an explicit union with an actual value of some sort.

Regardless, Savinov is an idiot. One can cluster data for performance without using NULL.

> Of course, all of the above is purely from a practical perspective,
> whereas this is comp.databases.THEORY, so I guess I should just shut up
> and go back to lurking.

Theory is practical. Practice in ignorance is anything but practical. Received on Wed Sep 13 2006 - 07:47:29 CDT