Re: foreign key constraint versus referential integrity constraint

#71

paul c wrote:

Quote:

Tegiri Nenashi wrote:
...

Is view definition a constraint? IMO it's purely terminological
matter. Consider relations x and y defined by some algebraic
identities. Is adding new view z (as a function of x and y) adding a
constraint to the system?

Let's analyze a simpler example. Consider two real values constrained
by the equality:

x + y = 5

Is introducing a new variable z, say

z = x - 2y

a new constraint imposed onto the system? Not really, because,
variable z is redundant and can be eliminated, and it doesn't affect
the formal property of the system of being under constrained.

That is a form of argument that I've seen quite often regarding various
RM questions, not just this one. I'd have no problem with it were it
not called an "example". Since it is about arithmetic, it's at best a
mere analogy to relations and we need to decide whether the analogy
should apply.

Ahem.

x + y = 5 is a relation. z = x - 2y is a relation. They are linear
polynomial functions, and all functions are relations.

x*x + y*y + z*z - r*r = 0 is also a relation. It is a relation
describing a sphere of radius r centered at the origin. It is also a
polynomial. While it is not a function, it is a relation.

Quote:

To try to answer that I would ask when do we ever record
"extensions" of arithmetic equations?

Whenever anyone writes the word "let":

Let u = x-3, v=y+2, w=z-1...

Quote:

In other words, just because we
have abstract operations for both numbers and relations doesn't mean one
should mimic the other. If that's so, maybe somebody else can put it
better.

Whether involving numbers or no numbers, a relation is a relation. What
we can do with relations doesn't change because some of them involve
numbers and some of them do not.

#72

Bob Badour wrote:

Quote:

paul c wrote:

Tegiri Nenashi wrote:
...

Is view definition a constraint? IMO it's purely terminological
matter. Consider relations x and y defined by some algebraic
identities. Is adding new view z (as a function of x and y) adding a
constraint to the system?

Let's analyze a simpler example. Consider two real values constrained
by the equality:

x + y = 5

Is introducing a new variable z, say

z = x - 2y

a new constraint imposed onto the system? Not really, because,
variable z is redundant and can be eliminated, and it doesn't affect
the formal property of the system of being under constrained.

That is a form of argument that I've seen quite often regarding
various RM questions, not just this one. I'd have no problem with it
were it not called an "example". Since it is about arithmetic, it's
at best a mere analogy to relations and we need to decide whether the
analogy should apply.

Ahem.

x + y = 5 is a relation. z = x - 2y is a relation. They are linear
polynomial functions, and all functions are relations.

x*x + y*y + z*z - r*r = 0 is also a relation. It is a relation
describing a sphere of radius r centered at the origin. It is also a
polynomial. While it is not a function, it is a relation.

To try to answer that I would ask when do we ever record "extensions"
of arithmetic equations?

Whenever anyone writes the word "let":

Let u = x-3, v=y+2, w=z-1...

In other words, just because we have abstract operations for both
numbers and relations doesn't mean one should mimic the other. If
that's so, maybe somebody else can put it better.

Whether involving numbers or no numbers, a relation is a relation. What
we can do with relations doesn't change because some of them involve
numbers and some of them do not.

That's very good, accurate up to a point and no argument except for a
couple of things i) when he mentioned "variable" Tegiri didn't make it
clear whether he was talking about one of Codd's non-binary relations, I
presume traditional math philosophy would have to have some recasting
for that (don't ask me how!) ii) even if I'm wrong about i), Codd's
relations are slightly different in usage both because they use
different operators than the arithmetic ones and because we can 'bend'
the relational ops to produce a certainty from an uncertainty, notably
when we use union to 'insert' to a relation - this seems quite different
to me from what arithmetic allows.

#73

On Wed, 28 Oct 2009 10:42:53 -0300, Bob Badour
<bbadour (AT) pei (DOT) sympatico.ca> wrote:

Quote:

Marshall wrote:

On Oct 27, 12:41 pm, TroyK <cs_tr... (AT) juno (DOT) com> wrote:

On Oct 27, 11:41 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

I didn't skip anything, but I missed Ollie's radio collar, which I took
off him before bathing him. I have to mix up another batch of peroxide
and baking soda just for it. I should have just left it on him.- Hide quoted text -

- Show quoted text -

I mostly lurk, but on the subject of skunks and dogs, I can offer some
meaningful advice (since, like mine, your dogs will undoubtedly play
this game again)

Next time, try Dawn dishwashing liquid.

I don't think he's going to like that advice; Bob has gone on record
repeatedly saying mean things about Dawn.

I am actually allergic to Dawn. I use Sunlight. I started with Sunlight
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Beautiful line.

Quote:

to wash out as much of the oil as possible before I used the peroxide
^

... and hierarchical stains ...

Quote:

and baking soda.

Sincerely,

Gene Wirchenko

#74

"paul c" <toledobythesea (AT) oohay (DOT) ac> wrote

Quote:

Mr. Scott wrote:
"Marshall" <marshall.spight (AT) gmail (DOT) com> wrote in message
news:386975f9-472c-4184-8661-5c3d1e2f7621 (AT) r24g2000prf (DOT) googlegroups.com...
On Oct 24, 10:53 am, Keith H Duggar <dug... (AT) alum (DOT) mit.edu> wrote:
Anyhow, the question here is not one of our imagination but rather
simply this: if it makes sense for the RM to support constraints
on relational /values/ (taken on by variables) why does it not
make sense to support constraints on relational /expressions/?
That is a question of general principle not specific design.
This question, it seems to me, is clear and to the point.
And I would answer it by saying that we shouldn't really
even make the distinction! (At least not formally.)

I think we should make the distinction, and formally.

(p /\ q) -> r is not the same as (p -> r) /\ (q -> r)
but (p \/ q) -> r is the same as (p -> r) \/ (q -> r)

A view consisting of a natural join, for example, represents a set of
conjunctions. Each row of the join represents a conjunction of
propositions, one for each operand. A constraint defined on a join would
be of the form (p /\ q) -> r. That is definitely not the same as
constraints defined on one or more tables, which would take the form (p
\/ q) -> r.
...

Forgot to mention that I don't see that a "a constraint defined on a join"
would necessarily be "of the form (p /\ q) -> r". I had thought that many
people think it could be any truth-valued expression such as "(p /\ q) =
r".

The form (p /\ q) -> r applies because whenever r does not hold, neither can
(p /\ q). r is in fact your "any truth valued expression." In the case of
a join view, the antecedent is a conjunction, not a disjunction.

Quote:

This leads me to think that most, if not all, view definitions can be
interpreted as constraints. It is interesting to me to then ask what
makes a view different from a base. Is it enough to say that a view
always has one constraint (of possibly several) that is an equality and a
view may be 'updated' without reference to the view?

Tables house data; views just present it. That is in a nutshell the
difference between tables and views. What is presented by a view implies
what is in the operands of the view's definition. As a consequence, in
order to be fully updatable and therefore interchangable, each and every set
of inserts, updates and deletes applied to a view must map one-to-one to a
set of inserts, updates and deletes applied to those operands. Views that
are joins or unions or restrictions or projections in general aren't fully
updatable. There are exceptions, of course. A view defined on a pair of
tables that participate in mutual foreign keys is fully updatable because
each and every set of inserts, updates and deletes applied to the view maps
one-to-one to a set of inserts, updates and deletes applied to the tables.

Quote:

A more opaque way but perhaps less useful way of saying this is that a
relation's definition in the first place amounts to nothing more than a
constraint.

#75

Mr. Scott wrote:

Quote:

...
Tables house data; views just present it. That is in a nutshell the
difference between tables and views. What is presented by a view implies
what is in the operands of the view's definition. As a consequence, in
order to be fully updatable and therefore interchangable, each and every set
of inserts, updates and deletes applied to a view must map one-to-one to a
set of inserts, updates and deletes applied to those operands. Views that
are joins or unions or restrictions or projections in general aren't fully
updatable. There are exceptions, of course. A view defined on a pair of
tables that participate in mutual foreign keys is fully updatable because
each and every set of inserts, updates and deletes applied to the view maps
one-to-one to a set of inserts, updates and deletes applied to the tables.
...

Doesn't this amount to saying that tables are stored and views are not?
(whereas I don't see why a view couldn't be stored because of some
practical reason or other.)

#76

paul c wrote:

Quote:

Bob Badour wrote:
paul c wrote:

Tegiri Nenashi wrote:
...

Is view definition a constraint? IMO it's purely terminological
matter. Consider relations x and y defined by some algebraic
identities. Is adding new view z (as a function of x and y) adding a
constraint to the system?

Let's analyze a simpler example. Consider two real values constrained
by the equality:

x + y = 5

Is introducing a new variable z, say

z = x - 2y

a new constraint imposed onto the system? Not really, because,
variable z is redundant and can be eliminated, and it doesn't affect
the formal property of the system of being under constrained.

That is a form of argument that I've seen quite often regarding
various RM questions, not just this one. I'd have no problem with it
were it not called an "example". Since it is about arithmetic, it's
at best a mere analogy to relations and we need to decide whether the
analogy should apply.

Ahem.

x + y = 5 is a relation. z = x - 2y is a relation. They are linear
polynomial functions, and all functions are relations.

x*x + y*y + z*z - r*r = 0 is also a relation. It is a relation
describing a sphere of radius r centered at the origin. It is also a
polynomial. While it is not a function, it is a relation.

To try to answer that I would ask when do we ever record "extensions"
of arithmetic equations?

Whenever anyone writes the word "let":

Let u = x-3, v=y+2, w=z-1...

In other words, just because we have abstract operations for both
numbers and relations doesn't mean one should mimic the other. If
that's so, maybe somebody else can put it better.

Whether involving numbers or no numbers, a relation is a relation.
What we can do with relations doesn't change because some of them
involve numbers and some of them do not.

That's very good, accurate up to a point and no argument except for a
couple of things i) when he mentioned "variable" Tegiri didn't make it
clear whether he was talking about one of Codd's non-binary relations, I
presume traditional math philosophy would have to have some recasting
for that (don't ask me how!) ii) even if I'm wrong about i), Codd's
relations are slightly different in usage both because they use
different operators than the arithmetic ones and because we can 'bend'
the relational ops to produce a certainty from an uncertainty, notably
when we use union to 'insert' to a relation - this seems quite different
to me from what arithmetic allows.

(Not to discount Bob B's reply - I may be stepping over some unmarked
line in the above, which would make it a provocation but I often find
those useful for seeing things more clearly.)

#77

On Oct 28, 2:57*pm, paul c <toledobythe... (AT) oohay (DOT) ac> wrote:

Quote:

Bob Badour wrote:
paul c wrote:

Tegiri Nenashi wrote:
...

Is view definition a constraint? IMO it's purely terminological
matter. Consider relations x and y defined by some algebraic
identities. Is adding new view z (as a function of x and y) adding a
constraint to the system?

Let's analyze a simpler example. Consider two real values constrained
by the equality:

x + y = 5

Is introducing a new variable z, say

z = x - 2y

a new constraint imposed onto the system? Not really, because,
variable z is redundant and can be eliminated, and it doesn't affect
the formal property of the system of being under constrained.

That is a form of argument that I've seen quite often regarding
various RM questions, not just this one. *I'd have no problem with it
were it not called an "example". *Since it is about arithmetic, it's
at best a mere analogy to relations and we need to decide whether the
analogy should apply.

Ahem.

x + y = 5 is a relation. z = x - 2y is a relation. They are linear
polynomial functions, and all functions are relations.

x*x + y*y + z*z - r*r = 0 is also a relation. It is a relation
describing a sphere of radius r centered at the origin. It is also a
polynomial. While it is not a function, it is a relation.

To try to answer that I would ask when do we ever record "extensions"
of arithmetic equations?

Whenever anyone writes the word "let":

Let u = x-3, v=y+2, w=z-1...

In other words, just because we have abstract operations for both
numbers and relations doesn't mean one should mimic the other. *If
that's so, maybe somebody else can put it better.

Whether involving numbers or no numbers, a relation is a relation. What
we can do with relations doesn't change because some of them involve
numbers and some of them do not.

That's very good, accurate up to a point and no argument except for a
couple of things i) when he mentioned "variable" Tegiri didn't make it
clear whether he was talking about one of Codd's non-binary relations,

Ok, equality relation in u = x-3 certainly contributed to the
confusion. I meant to bring in algebraic analogy without any reference
to relations. It was just an algebra of real numbers (aka real number
field). The idea was that the topic of [linear] constraints over real
number field is well understood (so it can be viewed as a role model
for development in database field).

Certainly, in database field we have different algebraic axioms (they
are somewhat similar to boolean algebra!), but the other concepts stay
the same (variables, constants, operations, and equations). The
objects of the algebra are relations and not numbers, of course -- yet
another source of confusion because relations structured into tables
might have numbers in them!

#78

paul c wrote:

Quote:

Mr. Scott wrote:

...
Tables house data; views just present it. That is in a nutshell the
difference between tables and views. What is presented by a view
implies what is in the operands of the view's definition. As a
consequence, in order to be fully updatable and therefore
interchangable, each and every set of inserts, updates and deletes
applied to a view must map one-to-one to a set of inserts, updates and
deletes applied to those operands. Views that are joins or unions or
restrictions or projections in general aren't fully updatable. There
are exceptions, of course. A view defined on a pair of tables that
participate in mutual foreign keys is fully updatable because each and
every set of inserts, updates and deletes applied to the view maps
one-to-one to a set of inserts, updates and deletes applied to the
tables.
...

Doesn't this amount to saying that tables are stored and views are not?
(whereas I don't see why a view couldn't be stored because of some
practical reason or other.)

Ignore Mr. Scott. He doesn't know what he is talking about. Base
relations and views equally represent data and neither houses anything,
because housing implies something physical. A view can be stored or not
stored. Regardless, a view is derived from base relations. Base
relations, themselves, are derived from physical storage structures and
might not be stored anywhere as is either.

It's easy enough to construct various schema with identical predicates
where what is base in each is derived in all the others. The database
designer generally chooses the base relations as a matter of his own
convenience.

#79

"Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote

Quote:

paul c wrote:

Mr. Scott wrote:

...
Tables house data; views just present it. That is in a nutshell the
difference between tables and views. What is presented by a view
implies what is in the operands of the view's definition. As a
consequence, in order to be fully updatable and therefore
interchangable, each and every set of inserts, updates and deletes
applied to a view must map one-to-one to a set of inserts, updates and
deletes applied to those operands. Views that are joins or unions or
restrictions or projections in general aren't fully updatable. There
are exceptions, of course. A view defined on a pair of tables that
participate in mutual foreign keys is fully updatable because each and
every set of inserts, updates and deletes applied to the view maps
one-to-one to a set of inserts, updates and deletes applied to the
tables.
...

Doesn't this amount to saying that tables are stored and views are not?
(whereas I don't see why a view couldn't be stored because of some
practical reason or other.)

Ignore Mr. Scott. He doesn't know what he is talking about. Base relations
and views equally represent data and neither houses anything, because
housing implies something physical. A view can be stored or not stored.
Regardless, a view is derived from base relations. Base relations,
themselves, are derived from physical storage structures and might not be
stored anywhere as is either.

Ignore Mr. Badour. He doesn't know what he is talking about. Table
definitions are constraints. View definitions are queries. The difference
is clear to anyone who has a clue. (Obviously, Mr. Badour doesn't.)
Constraints specify what can be true, not what is supposed to be true.
Queries manipulate what is supposed to be true. Base relations (what are
housed in tables) are not derived from physical storage structures: they are
instead a logical expression of what is supposed to be true. How they are
physically represented is irrelevant. What is presented by a view is the
result of querying that logical expression of what is supposed to be true.

Quote:

It's easy enough to construct various schema with identical predicates
where what is base in each is derived in all the others. The database
designer generally chooses the base relations as a matter of his own
convenience.

#80

Mr. Scott wrote:

Quote:

... Table
definitions are constraints. View definitions are queries. ...

I detect some mysticism here. There has to be a reason to distinguish b
between 'table' and 'view' this way, otherwise we don't need both terms.
What is the reason (or reasons)?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: foreign key constraint versus referential integrity constraint

comp.databases.theory comp.databases.theory

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 05:41 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 05:57 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 05:59 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 06:19 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 06:51 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 06:53 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 07:10 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 07:41 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 08:40 PM

Re: foreign key constraint versus referential integrity constraint - 10-28-2009 , 09:58 PM