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

On 15 ožu, 23:40, com..._at_hotmail.com wrote:
> 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.
>

We do not understand one another regarding some important issues. I'll try to explain it using the following simplified scheme:

      The Real World                                 (Data) Model

Regarding above schema we can ask the following questions: 1. What stands in this space that is between the real world and Model? 2. How to make a mathematical theory that connects all these pieces?

I think in the future, (new) mathematics will deal extensively with these two issues.
I also think that you pay attention only to the model. However db also works with real word objects.
In my model, I connected the real world, with a data model, using the concepts (and still with many other 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

If Y is name of an attribute, then X cannot be its part, because the attributes are atomic.

> 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-re...http://www.fecundity.com/logic/download.html
>

I appreciate the work of Date & Darwen. Relational model that they built and implemented is well done.
I also think that your technique in logic is very good. However, there are some parts of the theory of databases, which in my opinion are not resolved in the relational model.

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

Characterization of an object is very different from giving the names of the object. For example, characterization of an object can give a wrong meaning for that object.

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.

Today I released a new paper, which I think resolves some problems in the theory of databases. If you want to become informed, I suggest you read the paper at http://www.dbdesign11.com called Semantic databases and semantic machines.

> good luck,
> philip

Vladimir Odrljin Received on Thu Apr 05 2012 - 01:06:47 CEST