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

From: Alexandr Savinov <spam_at_conceptoriented.com>
Date: Wed, 13 Sep 2006 15:13:11 +0200
Message-ID: <ee9059$fgc$1@f1node01.rhrz.uni-bonn.de>

Damien schrieb:
> 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.

This example is a good illustration that one and the same data semantics can be expressed in different forms (structures). Here both Model 1 and Model 2 describe an entity with two attributes. It is already the second question (for theory) how these attributes are distributed among tables.

> 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.

Although we are in comp.databases.THEORY, 80% of posts here have nothing to do with data issues at all :)

> Damien

--
http://conceptoriented.com

Received on Wed Sep 13 2006 - 08:13:11 CDT