# Re: WWW/Internet 2009: 2nd CFP until 21 September x

Date: Tue, 18 Aug 2009 14:35:05 -0400
Message-ID: <i5WdnRK1WL_HaRfXnZ2dnUVZ_qudnZ2d_at_giganews.com>

"Walter Mitty" <wamitty_at_verizon.net> wrote in message news:kIjim.2570\$Jg.25_at_nwrddc01.gnilink.net...
>
> "paul c" <toledobythesea_at_oohay.ac> wrote in message
> news:RQcim.41657\$PH1.8769_at_edtnps82...
>> paul c wrote:
>>> Walter Mitty wrote:
>>>> "rpost" <rpost_at_pcwin518.campus.tue.nl> wrote in message
>>>> news:h69p4s\$2no2\$1_at_mud.stack.nl...
>>>>
>>>>> This describes tables with NULLs as shorthands
>>>>> for sets of tables without any NULLs at all,
>>>>> so it introduces in way that fits perfectly
>>>>> into the relational model.
>>>>>
>>>> You got what I was trying to express. Exactly. Thanks.
>>>>
>>>>
>>>
>>> Are you saying that the table
>>>
>>> T1:
>>> K C
>>> 1 null
>>> 2 3
>>>
>>> is shorthand for the two tables
>>>
>>> T2:
>>> K C
>>> (empty)
>>>
>>> and
>>>
>>> T3:
>>> K C
>>> 2 3
>>>
>>> ?
>>>
>>> If so, how does T1 = T2 JOIN T3?
>>
>>
>> Or do you mean that the table
>>
>> T1:
>> K C
>> 1 null
>> 2 3
>>
>> is shorthand for the two tables
>>
>> T2:
>> K
>> 1
>>
>> and
>>
>> T3:
>> K C
>> 2 3
>>
>> ?
>>
>> If that's what you mean, I still need to ask how T1 = T2 JOIN T3?
>
> no, that's not what I'm talking about.
>
> Let's take tihs table (not a very good example, but it'll do):
>
> create table Employee
> (Employee_Id integer pk,
> Firsst_Name char 25 not null,
> Last_Name char 25 not null,
> Middle_Initial char 1)
>
> I've dropped out all the other columns.that would be in a real case.
>
> Noe let's say that the rules are that you can't put an employee into the
> table unless you know the First Name and Last Name, and you get assigned
> to the employee an Employee_Id that's never been used before. But you
> can put in an employee who either doesn't have a middle initial, or where
> you don't know what it is.
>
> No I'm saying that this is equivalent to
>
> create table Employee
> (Employee_Id integer primary key,
> Firsst_Name char 25 not null,
> Last_Name char 25 not null)
>
>
> create table Employee_Middle_Initial
> (Employee_Id integer primary key foreign key references
> Employee(Employee_Id),
> Middle_Initial char 1 not null)
>
> Employee. Employee_Id serves two purposes. It's the primary key for its
> own table, and its the reference point for any uses on Employee_Id as a
> foreign key elsewhere in the database.
>
> Employee_Middle_Initial. Employee_Id is the primary key for its own table,
> but it isn't the reference point for any other foreign key. In fact, it's
> a foriegn key itself.
>
> Now, if you had these two tables, how would you enter an employee with no
> middle initial? Easy. Make the entry in the Employee table as per usual,
> and leave out the row for the Employee_Middle_Inital table entriely.
> There's no nulls allowed in the two table solution, but there's no law
> that says you can't leave an entire row out.
>
> Now if you were to do an outer join on the two tables given in the two
> table solution, what you'd get is the one table solution where in every
> case where there's no row ion the middle_initial table, you end up with a
> NULL in the Middle_Initial column, after the outer join so you can
> consider the one table solution to be a materialized outer join on the two
> table solution. In the real world, I'm not going to add a whole other
> table for some dink column like Middle_Initial. So I'm going to build the
> one table solution.
>
> All I was saying in my earlier post is that considering the one table
> solution to be a materialized outer join gives a way of conceptualizing
> the two tables, where no NULLS are necessary or permitted. It also cuts
> back on the proliferation of tables. I'm all for decomposing tables when
> it will really do some good, but there's no need to make a religious
> ritual of it.

I'm now wondering if there are really three kinds of null. There is the applicable null, which when submitted in an update indicates 'I know there is supposed to be a value here but I don't know what it is,' there is the inapplicable null, which when submitted in an update indicates 'I know there is not supposed to be a value here,' and now there is the I-don't-have-a-clue null, which when submitted in an update indicates 'I don't know if there is supposed to be a value here.' In order to simulate the I-don't-have-a-clue null that can be in your Employee table, your Employee_Middle_Initial table has to have the open world interpretation, so that whenever a row is missing it indicates that either there should be a value here but it is at present unknown or that there shouldn't be a value here. Received on Tue Aug 18 2009 - 20:35:05 CEST

Original text of this message