Re: Informal Survey #1 -- joins on foreign keys

On 2 okt, 19:58, Rob <rmpsf..._at_gmail.com> wrote:
> On Oct 2, 10:07 am, paul c <toledobythe..._at_gmail.com> wrote:
>
>
>
> > On Oct 1, 2:07 pm, Rob <rmpsf..._at_gmail.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.

> 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.

> 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). Received on Mon Oct 03 2011 - 13:27:52 CEST