Re: WWW/Internet 2009: 2nd CFP until 21 September x
From: paul c <toledobythesea_at_oohay.ac>
Date: Tue, 18 Aug 2009 00:19:23 GMT
Message-ID: <fsmim.41822$PH1.41291_at_edtnps82>
>
> 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.
>
>
>
Date: Tue, 18 Aug 2009 00:19:23 GMT
Message-ID: <fsmim.41822$PH1.41291_at_edtnps82>
Walter Mitty wrote:
> "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.
>
>
>
Thanks for that detailed construction, now we've gone full circle, in other words the answer to my question is T1 = T3 OUTER JOIN T2. The repetitive application of Heath's Theorem is a red herring when sophistry is the essential ingredient of outer join!
(Date had some fun with comments Chamberlain made about nulls being a religious issue. Unlike people who're forever telling you they're your friend, the most devout people I've met never talked of religion.) Received on Tue Aug 18 2009 - 02:19:23 CEST