Informal Survey #1 -- joins on foreign keys

#1

First, let me qualify my interests. I am primarily interesested in the
mechanics of
relational database management, not the semantics. I am interested in
the evaluation
of sql queries without regard to their meaning.

1. without racking your brain or searching the archives of your
previous work, does
your gut feeling tell you have ever authored or seen an sql query in
which two relations
were joined on a foreign key in each?

2. using your knowledge and understanding of (any) relational algebra,
is a join
between two relations on a foreign key in each ever meaningful? (Keep
in mind
the definition of the domain of a foreign key.)

3. in the semantic realm you use to "think about" relational databases
(i.e., E-R,
facts, etc.), does a join between two relations on a foreign key in
each make sense?

I request that you spend at least 10 minutes thinking about this
before you flame
me.

Rob

#2

On 1 oct, 19:09, Rob <rmpsf... (AT) gmail (DOT) com> wrote:

Quote:

First, let me qualify my interests. I am primarily interesested in the
mechanics of
relational database management, not the semantics. I am interested in
the evaluation
of sql queries without regard to their meaning.
A contradiction in your statement. You seem interested in relational

management but not enough to put some effort to understand what it is
about.

Quote:

1. without racking *your brain or searching the archives of your
previous work, does
your gut feeling tell you have ever authored or seen an sql query in
which two relations
were joined on a foreign key in each?
Yes. No big deal.

Quote:

2. using your knowledge and understanding of (any) relational algebra,
is a join
between two relations on a foreign key in each ever meaningful? (Keep
in mind
the definition of the domain of a foreign key.)
None particular.

Quote:

3. in the semantic realm you use to "think about" relational databases
(i.e., E-R,
facts, etc.), does a join between two relations on a foreign key in
each make sense?
JOIN is a relational operator. It can be used between any relation

column subset.

Quote:

I request that you spend at least 10 minutes thinking about this
before you flame
me.
No flames. Just simple answers as you requested.
Rob

#3

Rob wrote:

Quote:

First, let me qualify my interests. I am primarily interesested in the
mechanics of
relational database management, not the semantics. I am interested in
the evaluation
of sql queries without regard to their meaning.

1. without racking your brain or searching the archives of your
previous work, does
your gut feeling tell you have ever authored or seen an sql query in
which two relations
were joined on a foreign key in each?

Obviously, I have.

Quote:

2. using your knowledge and understanding of (any) relational algebra,
is a join
between two relations on a foreign key in each ever meaningful? (Keep
in mind
the definition of the domain of a foreign key.)

Yes, of course, it is.

Quote:

3. in the semantic realm you use to "think about" relational databases
(i.e., E-R,
facts, etc.), does a join between two relations on a foreign key in
each make sense?

Yes, it does.

Quote:

I request that you spend at least 10 minutes thinking about this
before you flame
me.

I don't know why you think it would take 10 minutes to answer such basic
questions.

#4

On Oct 1, 2:07*pm, Rob <rmpsf... (AT) gmail (DOT) com> wrote:
....

Quote:

I've designed hundreds of databases. After contemplating it long and
hard, I seriously
doubt I ever used a foreignkey-foreign key join in the queries I
authored for use with
these databases. Not because it was disallowed, but because
it lacks meaning. ...

There must be thousands of db's that have Shipments, Invoices and
Receivables tables with a foreign key referencing a Customer table.
Obviously there will be people who will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount.

Of course there are probably thousands, maybe millions, of people who
have designed hundreds of databases that used no such join. And,
probably thousands of people who have designed dozens of databases
that used only such joins.

#5

On Oct 1, 2:07*pm, Rob <rmpsf... (AT) gmail (DOT) com> wrote:
....

Quote:

Mechanically, there is nothing to it. ...

Mechanically, there is plenty to it. The first SQL implementers were
obviously terrified of joins, which in part led to the conceptual mess
we have today such as screwball concepts like inner and outer joins,
ON DELETE CASCADE, etc. The official SQL standard is of such a size
that no single person could master it and have time left over for
anything else.

#6

On Oct 2, 10:07*am, paul c <toledobythe... (AT) gmail (DOT) com> wrote:

Quote:

On Oct 1, 2:07*pm, Rob <rmpsf... (AT) gmail (DOT) com> wrote:
...

I've designed hundreds of databases. After contemplating it long and
hard, I seriously
doubt I ever used a foreignkey-foreign key join in the queries I
authored for use with
these databases. Not because it was disallowed, but because
it lacks meaning. ...

There must be thousands of db's that have Shipments, Invoices and
Receivables tables with a foreign key referencing a Customer table.
Obviously there will be people who will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount.

Of course there are probably thousands, maybe millions, of people who
have designed hundreds of databases that used no such join. *And,
probably thousands of people who have designed dozens of databases
that used only such joins.

In my original post, I asked (first) for a gut reaction. Yours is a

gut
reaction and happens to coincide with my own. But gut reactions
can be misguided.

For example, those people who "will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount" will likely do so THROUGH the Customer
table so as to provide a Customer attribute that identifies the rows
of the
result. How many would do it without the Customer table?

As for the thousand, millions, hundreds, thousands and
dozens of which you write, that is more gut feeling. Let's
leave the monkeys-and-typewriters and statistical arguments
behind. The semantics (as opposed to the mechanics) of
foreignkey-foreignkey joins are not without questions.

What I'm really trying to get at is whether a foreignkey-foreignkey
join makes sense algebraically, and if so, do operations in higher-
level
abstractions (like E-R, facts) translate to them?

#7

On 2 okt, 19:58, Rob <rmpsf... (AT) gmail (DOT) com> wrote:

Quote:

On Oct 2, 10:07*am, paul c <toledobythe... (AT) gmail (DOT) com> wrote:

On Oct 1, 2:07*pm, Rob <rmpsf... (AT) gmail (DOT) com> wrote:
...

I've designed hundreds of databases. After contemplating it long and
hard, I seriously
doubt I ever used a foreignkey-foreign key join in the queries I
authored for use with
these databases. Not because it was disallowed, but because
it lacks meaning. ...

There must be thousands of db's that have Shipments, Invoices and
Receivables tables with a foreign key referencing a Customer table.
Obviously there will be people who will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount.

Of course there are probably thousands, maybe millions, of people who
have designed hundreds of databases that used no such join. *And,
probably thousands of people who have designed dozens of databases
that used only such joins.

In my original post, I asked (first) for a gut reaction. Yours is a
gut
reaction and happens to coincide with my own. But gut reactions
can be misguided.

For example, those people who "will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount" will likely do so THROUGH the Customer
table so as to provide a Customer attribute that identifies the rows
of the
result. How many would do it without the Customer table?

A (natural) JOIN is an associative operation. To speak of joins as if
they are "THROUGH a table" is to expose ones ignorance of the
relational algebra, or it is to expose ones failure to properly
distinguish between model (the JOIN itself) and implementation (the
evaluation strategy as chosen by the engine that executes the query),
or it exposes yet some other important piece of knowledge that is
lacking in your brain.

Take any set of 6NF relvars. For example, CUST {CUST_ID} , CUST_DOB
{CUST_ID, DOB} , CUST_SAL {CUST_ID, SAL} , with key CUST_ID in each
and FK CUST_ID in the latter two. Now write the query that gives me
the customer ID of all the customers for which both date of birth and
salary are known. Hint : if you include my CUST relvar, then you've
got a relvar too many.

Quote:

What I'm really trying to get at is whether a foreignkey-foreignkey
join makes sense algebraically,

Contradiction of terms. Nothing ever makes sense "algebraically".
"Making sense" refers to "having some meaning", and that requires, by
definition, the consideration of semantics, which you say you do not
intend to consider.

"Algebraically", one can, at best, only speak of "valid expressions of
the algebra" or so. Which is a bit of a superfluous qualification,
because "invalid expressions of the algebra" just aren't expressions
of the algebra to begin with.

Quote:

and if so, do operations in higher-level
abstractions (like E-R, facts) translate to them?

You've got things the wrong way round. Relvars are associated with an
external predicate, which "documents" the "meaning" that is
represented by [the tuples in] it. Expressions of the relational
algebra that have such relvars for constituent components, also have
an external predicate associated with it, documenting in turn what
"meaning" is represented by [the tuples in] the relation value
resulting from evaluating the expression.

For example, if we have relvars R1 and R2 with predicates P(R1) and
P(R2), respectively, then the external predicate associated with R1
INTERSECT R2 is "P(R1) AND P(R2)".

Whether that external predicate is useful to the end user is another
matter (in fact it is the matter of whether or not the expression will
ever get written : if that predicate is useful, then the expression
will get written).

#8

On Oct 2, 10:58*am, Rob <rmpsf... (AT) gmail (DOT) com> wrote:
....

Quote:

For example, those people who "will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount" will likely do so THROUGH the Customer
table so as to provide a Customer attribute that identifies the rows
of the
result. How many would do it without the Customer table?
...

Plenty. For one, the preparation of financial statements doesn't
require Customer information. That is a simple fact, not a gut
reaction.

....

Quote:

What I'm really trying to get at is whether a foreignkey-foreignkey
join makes sense algebraically, ...

It makes sense in the same sense that 2 + 2 makes sense in a numerical
algebra, whereas 2 - / 2 doesn't make sense. Looks like you are
changing questions, the latest seems quite insensible.

Quote:

... and if so, do operations in higher-level
abstractions (like E-R, facts) translate to them?

What the heck are the operations of E-R? Now it's even harder to make
sense of your theme, can't tell what you mean by 'translate' -
mechanical, logical, mystical or what? Looks like you don't even know
what your question is, which will make it impossible to assess
answers.

#9

On Oct 3, 5:27*am, Erwin <e.sm... (AT) myonline (DOT) be> wrote:

Quote:

On 2 okt, 19:58, Rob <rmpsf... (AT) gmail (DOT) com> wrote:

On Oct 2, 10:07*am, paul c <toledobythe... (AT) gmail (DOT) com> wrote:

On Oct 1, 2:07*pm, Rob <rmpsf... (AT) gmail (DOT) com> wrote:
...

I've designed hundreds of databases. After contemplating it long and
hard, I seriously
doubt I ever used a foreignkey-foreign key join in the queries I
authored for use with
these databases. Not because it was disallowed, but because
it lacks meaning. ...

There must be thousands of db's that have Shipments, Invoices and
Receivables tables with a foreign key referencing a Customer table.
Obviously there will be people who will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount.

Of course there are probably thousands, maybe millions, of people who
have designed hundreds of databases that used no such join. *And,
probably thousands of people who have designed dozens of databases
that used only such joins.

In my original post, I asked (first) for a gut reaction. Yours is a
gut
reaction and happens to coincide with my own. But gut reactions
can be misguided.

For example, those people who "will want to join two or more of
those tables to compare Shipment_Value to Invoice_Amount or
Receivable_Amount" will likely do so THROUGH the Customer
table so as to provide a Customer attribute that identifies the rows
of the
result. How many would do it without the Customer table?

A (natural) JOIN is an associative operation. *To speak of joins as if
they are "THROUGH a table" is to expose ones ignorance of the
relational algebra, or it is to expose ones failure to properly
distinguish between model (the JOIN itself) and implementation (the
evaluation strategy as chosen by the engine that executes the query),
or it exposes yet some other important piece of knowledge that is
lacking in your brain.

Take any set of 6NF relvars. *For example, CUST {CUST_ID} , CUST_DOB
{CUST_ID, DOB} , CUST_SAL {CUST_ID, SAL} , with key CUST_ID in each
and FK CUST_ID in the latter two. *Now write the query that gives me
the customer ID of all the customers for which both date of birth and
salary are known. *Hint : if you include my CUST relvar, then you've
got a relvar too many.

In spite of my ignorance, my failures and/or my brain deficiencies,

(or perhaps because of them), you understood EXACTLY what I meant by
(the associative preposition) THROUGH. And, you came to the identical
conclusion: When no "parent" attributes are required, the result could
be obtained without access to the parent relation. The "Hint" just
says that a clever person doesn't need an optimizer to avoid the
additional join. But I believe my observation is basically correct,
that most sql authors would not catch it. (I also doubt any optimizer
would catch it, but that's another matter.)

However, if it were CUST(CUST_ID,CUST_NAME) where CUST_ID was some
opaque value, one could write the query as you suggest without CUST,
but then the result would not indicate which CUST has known date-of-
birth and salary. The result would only indicate the existence of such
CUSTs. And that existence inference would itself be based upon the
existence of a foreign key reference, with no certainty that the
referenced tuple actually exists. (Does anyone else see a problem with
that? What if referential integrity were not declared? Would that not
imply that a well-formed query on a correctly defined database COULD
produce incorrect results?)

As for the rest of your reply, you do not address whether higher-level
abstractions can map to this questionable construction.

#10

On Oct 1, 10:09*am, Rob <rmpsf... (AT) gmail (DOT) com> wrote:

Quote:

First, let me qualify my interests. I am primarily interesested in the
mechanics of
relational database management, not the semantics. I am interested in
the evaluation
of sql queries without regard to their meaning.

1. without racking *your brain or searching the archives of your
previous work, does
your gut feeling tell you have ever authored or seen an sql query in
which two relations
were joined on a foreign key in each?

2. using your knowledge and understanding of (any) relational algebra,
is a join
between two relations on a foreign key in each ever meaningful? (Keep
in mind
the definition of the domain of a foreign key.)

3. in the semantic realm you use to "think about" relational databases
(i.e., E-R,
facts, etc.), does a join between two relations on a foreign key in
each make sense?

I request that you spend at least 10 minutes thinking about this
before you flame
me.

Rob

dsads