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_at_f1node01.rhrz.uni-bonn.de>
>
> 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.
Date: Wed, 13 Sep 2006 15:13:11 +0200
Message-ID: <ee9059$fgc$1_at_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.comReceived on Wed Sep 13 2006 - 15:13:11 CEST