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

On Thursday, 15 March 2012 14:40:15 UTC-7, com..._at_hotmail.com wrote:
> > > > > Modern normalization/dependency theory says that there is a foreign
> > > > > key on attribute set K from a [source] D into a target F (domestic/
> > > > > foreign) in a database when in all possible world situations ie in all
> > > > > valid database states K forms a key in F and all the subtuple values
> > > > > for K in D are also in F.
> > > > > This could happen for any two database named relations;
> > > > > it's just a relationship that happens to always hold for pairs of
> > > > > values of D and F in that database.

> COMPONENT <X part, Y part, Z part> {<leg, pony, 4>, <torso, pony, 1>}
> WANT <Y part> {<pony> , <ball>}

Of course this should be:
 COMPONENT <X part, Y part, Z int> {<leg, pony, 4>, <torso, pony, 1>}  WANT <Y part> {<pony> , <ball>}

> COMPONENT(X, Y, Z)
> a X is an immediate component (or subassembly) of aY,
> and Z units of a X are needed to assemble one unit of a Y
> WANT(Y) I want a Y

If you want to associate a relation variable with some entities, give an appropriate predicate. Clearly the predicates I gave involve wanted parts, composed/assembled parts, component parts, component/assembly counts, ponies, legs, torsos, balls, units, the number 1, etc.

Regarding foreign keys:

Suppose {K} is a key of WANT.

Suppose that in detail COMPONENT is the breakdown of certain toys, wanted or not, and can sometimes contain Y values not in WANT. Then there is not a foreign key from COMPONENT{Y} to WANT{Y}. (Because COMPONENT{Y} subtuples are not always WANT{Y} subtuples.)

But suppose that in detail COMPONENT is the breakdown of wanted toys. Then there is a foreign key from COMPONENT {Y} to WANT {Y}. (Because COMPONENT{Y} subtuples are always WANT{Y} subtuples.)

Either way, WANT JOIN COMPONENT always means the same thing in terms of WANT and COMPONENT: those X-Y-Z tuples for which WANT(Y) AND COMPONENT(X, Y, Z).

All that there being or not being a foreign key from COMPONENT to WANT on {K} tells you is whether a certain thing is true that FOLLOWS FROM THE PREDICATES anyway. Namely the "foreign key" situation, namely whether the first variable projected on the subtuples is always a subset of the second variable projected on the subtuples and the subtuples form a key of the second variable. You don't need to know this to use the database, you just determine whether the predicates are true or false of every possible tuple for each situation as it arises. (But if you understand in detail what world situations can arise then you can determine this.)

So a join always has a meaning: a statement from the tuples that do and don't make its arguments' predicates simultaneously true. And this meaning is otherwise independent of any constraints. (Foreign key or otherwise.) (The only correlation is that the constraints are always true.)

(For implementation optimization, it is helpful to know the constraints.)

again, good luck,
philip Received on Mon Mar 19 2012 - 02:18:13 CET