Bob Badour wrote:

Given a relation with a key K1, ... Kn and remaining attributes
L1, ... Lm, that relation is always the join of the m projections
on K1, ..., Kn, L1, ..., K1, ..., Kn, Lm. What Walter means is
that we can describe 'missing information' NULLs (not all NULLs,
mind you) as the result of allowing such projections to have
fewer rows than the full relation.

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.

If you actually had a clue, rather than just pretending, you wouldn't
call these projections 'unnamed', 'implicit' or 'ephemeral'.

--
Reinier

Bob Badour wrote:

Given a relation with a key K1, ... Kn and remaining attributes
L1, ... Lm, that relation is always the join of the m projections
on K1, ..., Kn, L1, ..., K1, ..., Kn, Lm. What Walter means is
that we can describe 'missing information' NULLs (not all NULLs,
mind you) as the result of allowing such projections to have
fewer rows than the full relation.

This describes tables with NULLs as shorthands
for sets of tables without any NULLs at all,
so it introduces them in a way that fits
perfectly into the relational model.

If you actually had a clue, rather than just pretending, you wouldn't
call these projections 'unnamed', 'implicit' or 'ephemeral'.

--
Reinier

"rpost" <rpost (AT) pcwin518 (DOT) campus.tue.nl> wrote

Walter Mitty wrote:
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?

paul c wrote:

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?

"paul c" <toledobythesea (AT) oohay (DOT) ac> wrote

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.

Walter Mitty wrote:


[...]

And it is not what I was saying, either.
But thanks for explaining further.

--
Reinier

Walter Mitty wrote:
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.)

paul c wrote:

<nonsense snipped>

And by doing so, Walter would turn an actual assertion that the person
has no middle initial into something that nominally says it is unknown
whether the person has a middle initial.

Logical differences are big differences.

Personally, I would include a non-NULL middle initial for everyone where
the middle initial, of course, can be an empty string.

The 1-table solution without any NULLs at all would work just fine.

"Walter Mitty" <wamitty (AT) verizon (DOT) net> wrote

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.