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

On Monday, March 5, 2012 6:51:50 PM UTC-8, vldm10 wrote:
> On 10 sij, 07:29, com..._at_hotmail.com wrote:

Vladimir,

> My first question was: What does mean the pair (foreign-key, primary-
> key) in terms of database design? What do you design with this pair?

> > > What does mean the pair (foreign-
> > > key, primary-key) in terms of database design? What do you design with
> > > this pair?

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

To understand the answer to your question you have to learn a bunch of things and unlearn a bunch of things.

All primary keys are (candidate) keys. Picking one as primary has no more formal meaning than picking the name of an attribute or variable.

The dba gives a predicate for each relation variable. The user puts into a relation variable the tuples that turn the predicate into a true statement.

The (natural) join of two relations is always on common attributes. The result body holds the tuples that make <<the predicate of one> AND <the predicate of the other>> into a true statement.

Predicates:
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

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

What the database says:
 a leg is an immediate component (or subassembly) of a pony, and 4 units of a leg are needed to assemble one unit of a pony AND a torso is an immediate component (or subassembly) of a pony, and 1 unit of a torso is needed to assemble one unit of a pony AND it is not the case that <a leg is an immediate component (or subassembly) of a pony, and 7 units of a head is needed to assemble one unit of a pony>
AND it is not the case that <a wheel is an immediate component (or subassembly) of a pony, and 18 units of a wheel are needed to assemble one unit of a pony>
AND it is not the case that ... per any other absent COMPONENT tuple either
AND I want a pony AND I want a ball AND I don't want a wheel AND I don't want a leg
AND it is not the case that ... per any other absent WANT tuple either

Query: WANT JOIN COMPONENT
By the definition of JOIN this returns X, Y and Z such that I want a Y AND a X is an immediate component (or subassembly) of a Y, and Z units of a X are needed to assemble one unit of a Y. Result: <X part, Y part, Z part> {<leg, pony, 4>, <torso, pony, 1> What the query says:
I want a pony AND a leg is an immediate component (or subassembly) of a pony, and 4 units of a leg are needed to assemble one unit of a pony AND
I want a pony AND a torso is an immediate component (or subassembly) of a pony, and 1 unit of a torso is needed to assemble one unit of a pony
AND
it is not the case that <I want a pony AND a leg is an immediate component (or subassembly) of a pony, and 3 units of a leg are needed to assemble one unit of a pony>
AND it is not the case that ... per any other absent query tuple either

If the relation variable predicates happen to be such that you could (and did or didn't) declare a foreign key from an attribute set in one relation variable to another then the tuples in the result will be limited in a certain unchanging way describable independently of what tuples are in them at any particular time; the limitation that we call a foreign key constraint. Nevertheless, the result will still be the tuples that make <<the predicate of one> AND <the predicate of the other>> into a true statement. Just declare the keys and foreign keys and other constraints to avoid update errors.

> So in my opinion, a description of
> foreign keys in the terms of relations and predicates is not a theory
> about constructing, building and designing.

I have tried to inform you. Try:
http://bookboon.com/en/textbooks/it-programming/an-introduction-to-relational-database-theory http://www.fecundity.com/logic/download.html

> why I cannot accept your opinion about entities.

Several of us have told you that ER is at best a heuristic to determine predicates.

> In your sentence: "A body is a set of tuples of name-and-value pairs"
> instead of
> name-and-value pairs you can put name-and-name pairs, because the
> value is in fact name (for this value). So when we assign a value to a
> variable, then we can say (semantically) that we "bind" the name of
> the value with the name of the variable.

One can characterize a rational number (a kind of value) as having two parts, a numerator and a denominator, each an integer (a kind of value) (not numeral). I characterized a relation, not a relation variable. A tuple (a kind value) in the body part of a relation (a kind of value) has name and value pairs (a kind of value) as attribute parts.

If you want to become informed, I suggest you read the links and try to understand what I wrote instead of balking.

good luck,
philip Received on Thu Mar 15 2012 - 22:40:15 CET