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

On 6 stu 2011, 01:07, com..._at_hotmail.com wrote:
> On Oct 1, 10:09 am, Rob <rmpsf..._at_gmail.com> wrote:
>
> > is a join
> > between two relations on a foreign key in each ever meaningful?
>
> On Oct 2, 10:58 am, Rob <rmpsf..._at_gmail.com> wrote:
>
> > 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?
>
> Rob,
>
> I will enter this thread as I do most: you really don't understand the
> relational model.
>
> 1.
> (I quote Erwin for his versions of things I am saying.)
> On Oct 3, 4:27 am, Erwin <e.sm..._at_myonline.be> wrote:
>
> >   Relvars are associated with an
> > external predicate, which "documents" the "meaning" that is
> > represented by [the tuples in] it.
>
> Codd 1970:  "The meaning of [predicate logic predicate expression]
> COMPONENT(X, Y, Z) [given ordered-tuple set mathematics 'relation'
> COMPONENT] is that part X is an immediate component (or subassembly)
> of part Y, and Z units of part X are needed to assemble one unit of
> part Y". Such a parameterized statement is a 'predicate'. Codd meant
> that for modern relation (his "relationship") "the predicate for
> COMPONENT is that part SUB-PART is an immediate component (or
> subassembly) of part SUPER-PART, and QUANTITY units of part SUB-PART
> are needed to assemble one unit of part SUPER-PART". And that we might
> want to write "COMPONENT(X, Y, Z)" for "<SUB-PART X, SUPER-PART Y,
> QUANTITY Z> IN COMPONENT".
>
> When you give values for X, Y and Z or SUB.PART, SUPER.PART and
> QUANTITY you get a 'proposition', which is a statement that either
> holds or does not hold in a given world situation. ('Has truth value'
> 'TRUE' or 'FALSE'.)
>
> The (relational) extension of a predicate is the relation whose body
> is the set of tuples that make it into a proposition that holds in a
> given world situation. So we say (informally) that a relation is the
> extension of a predicate. A relation's attributes are the parameters
> of its predicate.
>
> 2.
> Each query relation expression has a predicate as follows. (This
> correspondence is typically described as "informal" (and spoken of
> vaguely if at all) but that is incorrect.)
>
> On Oct 3, 4:27 am, Erwin <e.sm..._at_myonline.be> wrote:
>
> > 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)".
>
> The predicate of a relation expression that is a name of a relation
> variable or constant is its predicate. The predicate of a relation
> expression that is a JOIN is the AND of the predicates its operands;
> of a UNION is the OR; of a MINUS is the AND NOT; of a RESTRICT X=Y or
> of an ADD X AS Y is the AND X=Y; and of a PROJECTALLBUT X is the
> EXISTS X.
>
> The value of a query relation expression that is a relation name is
> its named relation's predicate's extension. Each relation operator is
> defined so that if its operand values are the extensions of their
> predicates then its result is the extension of its predicate. (As can
> be shown. Codd and (ADD) Hall et all defined them so this would be the
> case.) So by induction every query relation expression value is the
> extension of its predicate.
>
> So the predicate and result value of a query relation expression is
> independent of whether any normalization has been done or constraints
> have been defined.
>
> 3.
> Given the named relations' predicates and possible world situations,
> of the syntactically typed database values certain ('valid') values
> could arise and other ('invalid') ones definitely do not.
> The DBMS evaluates a dba-given overall database constraint expression
> and allows an assignment if and only if a proposed database value is
> valid. (This involves further relation operators for equality and
> nesting/aggregation.) So for values that the user got from (correctly)
> evaluating the predicates on every tuple for the world situation (all
> valid by definition), by this policy none is excluded and all are
> included.
>
> On Oct 3, 4:27 am, Erwin <e.sm..._at_myonline.be> wrote:
>
> > You've got things the wrong way round.
>
> The constraint expressions tell the user something if and only if the
> user doesn't know all the possible world situations. But a query
> predicate only ever depends on its relation expression and the named
> relations' predicates and its result value only further on the named
> relations' values. Neither depend on constraint expressions. (Of
> course, they are correlated with them.) Constraint expressions
> constrain updates. They do not constrain valid database values or
> affect query predicates or result values. If the user never made a
> mistake (incorrectly including/excluding tuples contrary to their
> named relations' predicates and the world situation) they would not
> need constraint expressions.
>
> Of course, the constraint expressions are also telling or confirming
> to the user important truths about the possible world situations in
> terms of the named relations' predicates; and can help the user
> understand those predicates and the world; and can help the user to
> rephrase queries and the DBMS to optimize queries. A constraint
> expression just expresses a truth.
>
> 4.
> Modern normalization/dependency theory says that there is a foreign
> key on attribute set K from a target 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. (We can also speak of it being a foreign key
> in a given situation.)
>
> So there is a foreign key constraint on K from D to F if and only if
> certain expressions express certain things that hold in every world
> situation. 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. And just like every constraint
> expression's proposition, the only way in which this has anything to
> do with a query predicate or result value is that the possible world
> situations and the named relations' predicates collectively determine
> them all.
>
> 5.
> The "relationship" with predicate "there is a foreign key on attribute
> set {...} from named relation R and T to named relation S" is a fact
> that either holds or does not hold. As a predicate, it is a
> proposition. It is a relationship on nothing. Its extension ie
> relation is DEE. In practice we don't mean a fact when we say
> "relationship". (Historically, TRUE and FALSE aren't even wffs.)
>
> You seem interested in the constraint-oriented "relationship" FK on K,
> D and F (in that order in wffs) with predicate "there is a foreign key
> on attribute set K from named relation D to named relation F". Note
> that D and F denote the names of named relations, not relation values.
> Note also that a foreign key relationship holds on a K, D and F when a
> certain thing is the case for all valid database values. You seem
> interested in constraint propositions on expression "S JOIN T" that
> can be inferred from certain constraint propositions on S and T when
> EXISTS X, SR, ST: FK(X, R, SR) AND FK(X, T, ST). Ie when the
> proposition "there is set X such that a foreign key on attribute set X
> from named relations R and T to named relations SR and ST
> respectively" holds.
>
> We can have foreign keys on arbitrary expressions. So we can have FKe
> with predicate "there is a foreign key on attribute set K from
> expression D to expression F". It happens to be the case that that
> FKe(k, "R", "SR") AND FKe(k, "T", "ST") implies FKe(k, "R JOIN T",
> "SR"). Also that FKe(k, "R JOIN T", "ST") . Also that R{K} SUBSETOF (R
> JOIN T){K}, and T{K} SUBSETOF (R JOIN T){K}. And lots of other things.
> (But not "relationships".) Though I suspect  you are interested in the
> first two. (Do you care whether both foreign keys are to be to the
> same target? They needn't.) Note that FKe is the relationship; FKe(k,
> "R JOIN T", "S") is a fact that holds when Fe(k, "R", "S") AND Fe(k,
> "T", "S") holds.
>
> But a JOIN does not "reveal" any of this. Evaluating S JOIN T for a
> particular world situation just tells you certain things are true of
> that world situation per the predicates of S and T. The predicates and
> facts above don't even involve the same world as the JOIN and named
> relation predicates. It is the properties of FK and JOIN that have
> relevant consequences.
>
> Nothing "reveals" a relationship. It is meaningless to talk about
> "the" relationship between some attributes/parameters. A predicate or
> an extension/relation each tell you something about a relationship
> independent of a world situation. You are not talking about a
> particular relationship unless you have both its predicate and a world
> situation or its predicate and its extension. Do not confuse
> relationships, predicates, extensions/relations and propositions.
> Don't even use the word 'relationship'.)
>
> 6.
> So every JOIN just says that you want the tuples that make two other
> predicates true at the same time. Regardless of constraint
> expressions. Every relation expression is "meaningful". And you are
> probably actually interested in constraint (proposition) inference.
>
> So most of the sentences in your posts don't make sense. You are not
> clear and you don't seem to have the relational understanding or
> habits of thinking and writing to characterize your problem.
>
> Working through any example you choose might help make this clear to
> you. You might be interested in messagehttp://groups.google.com/group/comp.databases.theory/msg/1963ce6c0d2a...
> . Also give up Codd, especially 40 years ago. (Which you misread,
> although you're right about a certain unnecessary restriction.) Read
> recent Date and Darwen.
>
> 7.
> On Oct 3, 11:05 am, Rob <rmpsf..._at_gmail.com> wrote:
>
> > On Oct 3, 4:27 am, Erwin <e.sm..._at_myonline.be> wrote:
> >> [...]
> > As for the rest of your reply, you do not address whether higher-level
> > abstractions can map to this questionable construction.
>
> Your reply to Erwin basically totally misunderstands him. And his post
> was entirely on-topic.
>
> On Oct 2, 9:07 am, paul c <toledobythe..._at_gmail.com> wrote:
>
> > On Oct 1, 2:07 pm, Rob <rmpsf..._at_gmail.com> wrote:
> > 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.
>
> I believe Paul's point was not about gut reactions but that of course
> such JOINs are meaningful.
>
> On Oct 1, 2:07 pm, Rob <rmpsf..._at_gmail.com> wrote:
>
> > Any help here is genuinely appreciated.
>
> I believe you mean this, but I don't believe you know what it means.
> Read and reread carefully. Think and write and rethink and rewrite
> carefully. Don't disagree. Ask people to clarify what they mean. Every
> time you disagreed you were wrong, and every time you agreed you
> misunderstood. When world views collide, resolve contradictions, doubt
> confirmations.
>
> Thank you for the opportunity to clarify my thoughts and their
> expression. (Ten minutes well spent.) (Joke.) (The ten minutes.)
>
> philip

In contrast to your conclusion, Rob's posts are good; on the other hand I'm not sure that you are clear enough in your statements.

I think that we can generalize Rob's remarks. Therefore I would like to ask you the following question: What does mean the pair (foreignkey,  primary-key) in terms of database design? What do you design with this pair?
For the above-mentioned pair, it may happen that data from "referencing table" or from "referenced table" is incorrect. The attributes in each relation can be changed over time. Many things can happen in real life.

You also commonly used undefined terms that are of fundamental importance for db theory as well as for some others important science.

Could you give a definition (or description) of the following terms: the world, the situation and the possible. For example, in several places you use the term: "possible world situation"? Note that the terms "world", "situation" and "possible" does not exist in the RM.

It seems to me that you are not clear about predicates and that you don’t seem to have understanding about a nature of the predicates. It would be good if you can give us a definition of a predicate. Also give us a definition of the extensionality (of predicate). Do you mean here on the extensionality of atomic predicates? Do you use some "interpretation" here or do you use a definition of truth? Do you use second-order logic? (For example, the Leibniz's Law)

Note that the predicate is a linguistic construct. So, once again, please give us a definition of a predicate.

Also could you give us a definition of the attributes in RM. (and especially of the attributes in the structures determined with the pair (foreign-key, primary-key)).

When you write about the meaning you are actually writing about the truth value of the
corresponding proposition. Note that the truth is related just to sentences. This, however, does not apply to the meaning also. We can also notice that interrogative (query) sentence has no truthvalue.

Since you're trying ”to translate” a formal language for Predicate Logic into English language, I have the following two questions:

1. What are the differences between formal languages and natural languages?
2. Can semantics for formal languages be useful for natural languages?

Rob was asked questions related to time-varying nature of relations. He also asked some questions related to semantics. In your response to Rob you are advising him to "Read recent Date and Darwen". You also give him examples of Codd’s work that are related to the semantics and meaning.
Codd's work related to the database is very important. But his work on semantics and time-varying relations is of no importance. Date and Darwen in "Third Manifesto" have done great and good work. But their work on semantics and time-varying relations is of no importance.
Therefore, your recommendation to Rob that he should read Date, Darwen and Codd has no sense.

--

(To Erwin) On October 3, in this thread, Erwin wrote: “Take any set of
6NF relvars.  For example, CUST {CUST_ID}, CUST_DOB {CUST_ID, DOB},
…”

I would like to say that the identifier (ID) is poorly constructed. In
my opinion, in today’s existing software, these identifiers are
wrongly constructed. In fact for their construction there are no
rules. I am not talking about their industry standards but about db
design related to these identifiers. I explained on my website how to
construct this identifier.
See  http://www.dbdesign11.com

Vladimir Odrljin