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

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.

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.

Damien Received on Wed Sep 13 2006 - 14:35:48 CEST