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

From: Walter Mitty <wamitty_at_verizon.net>
Date: Mon, 17 Aug 2009 21:11:44 GMT
Message-ID: <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. Received on Mon Aug 17 2009 - 16:11:44 CDT

Original text of this message