ID field as logical address
Walter Mitty
auto-generated field (column) called ID amounted to reference by location
rather than reference by content. Some people correctly point out to me
that there is a level of indirection gained by using ID fields instead of
pointers. That means the records (rows) "pointed to" by references to the
ID are not pinned, and can still be shuffled. That's good, but it's
trivial, IMO, when compared to the advantages claimed by Codd and refined by
Date for the Relational model over alternative models. As far as I can see,
a database where every foreign key refers to an auto-generated ID column is
just a graph database disguised to look like a relational database.
In this thread, I want to further explore the use of ID fields, and approach
the question of whether using ID fields is relevant to the discussion of
whether an UPDATE is somehow different from a DELETE followed by an INSERT.
I declined to engage Brian in this question when he posted a response in the
earlier thread. I'm willing to dialogue about that here.
Let's say that the design has followed the practice of including an
auto-generated key called ID in every table, and declaring the ID as the
PRIMARY KEY. Let's further assume a practice that says that you never
update the ID field of any tuple, once it's been assigned. Now let's say
you have a table that records values that look like (ID, value). In an
actual case, value would probably ityself be a tuple, so our tagged tuples
might look like, for example, (ID, LastName, FirstName, PhoneNumber).
Now, if we update the LastName, the FirstName, and the Phone number of an
entry, it's still the same entry, because the ID field retains the same
value.
Now what happens if instead of updating the three dependent fields, we
delete the row, and insert a new one. Here, I'm making an assumption about
how auto-generate works. I'm assuming that, once used, and ID will never be
auto-generated again, even if it's no longer in use. So, when we delete a
row and insert a new row, we get a brand new ID. All of the references to
the deleted row remain as orphans, unless they got deleted by a cascaded
delete.
So, Brian's claim that deleting a value and inserting a new one is somehow
really different from updating an existing value works in this model, if
you regard ID as somehow "special", and not "part of the value recorded in
the table".
Disclaimer: I use auto-generated keys only when there are no natural keys
that I trust. And I tend to do the autogeneration in the application, and
not in the table itself. I might use a sequence generator built into the
database to obtain an unused value for a synthetic key, but I don't cause
the DBMS to autogenerate new keys in the table.
Roy Hann
> In this thread, I want to further explore the use of ID fields, and approach
> the question of whether using ID fields is relevant to the discussion of
> whether an UPDATE is somehow different from a DELETE followed by an INSERT.
So we're explicitly talking about SQL here, is that right?
--
Roy
Walter Mitty
"Roy Hann" <spec...@processed.almost.meat> wrote in message
news:t_udnWPkOKTjf4LX...@pipex.net...
My bad. I'm using SQL terminology because it comes naturally to me. But I
don't intend to limit the discussion to SQL.
There are interface languages other than SQL where the same distinction
might be made. I'd be interested to know whether this distinction is
meaningful at all in the D language. If so, what terminology would be used
in that context?
Bernard Peek
<wam...@verizon.net> writes
>Let's say that the design has followed the practice of including an
>auto-generated key called ID in every table, and declaring the ID as the
>PRIMARY KEY. Let's further assume a practice that says that you never
>update the ID field of any tuple, once it's been assigned. Now let's say
>you have a table that records values that look like (ID, value). In an
>actual case, value would probably ityself be a tuple, so our tagged tuples
>might look like, for example, (ID, LastName, FirstName, PhoneNumber).
>
>Now, if we update the LastName, the FirstName, and the Phone number of an
>entry, it's still the same entry, because the ID field retains the same
>value.
This appears to be an update anomaly but it's not as simple as that. If
we think of the logical data structure the ID isn't in the same entity
as the data we want to manage. It's in a separate entity with only one
attribute. There is supposed to be a 1:1 relationship between the ID
entity and the data entity, but this is not usually maintained by the
database. The cardinality of that relationship is managed either
programmatically or by external business processes.
Updating the data without making a simultaneous update to the ID field
is perfectly permissible within the relational model. They are after all
in separate entities with only a tenuous connection. It's an update
anomaly and it's caused by failure to fully normalise the data
structure.
--
Bernard Peek
Walter Mitty
"Bernard Peek" <b...@shrdlu.com> wrote in message
news:QFOfTQMZ...@shrdlu.com...
> In message <xWQTl.1481$9L2...@nwrddc02.gnilink.net>, Walter Mitty
> <wam...@verizon.net> writes
>
>>Let's say that the design has followed the practice of including an
>>auto-generated key called ID in every table, and declaring the ID as the
>>PRIMARY KEY. Let's further assume a practice that says that you never
>>update the ID field of any tuple, once it's been assigned. Now let's say
>>you have a table that records values that look like (ID, value). In an
>>actual case, value would probably ityself be a tuple, so our tagged
>>tuples
>>might look like, for example, (ID, LastName, FirstName, PhoneNumber).
>>
>>Now, if we update the LastName, the FirstName, and the Phone number of an
>>entry, it's still the same entry, because the ID field retains the same
>>value.
>
> This appears to be an update anomaly but it's not as simple as that. If we
> think of the logical data structure the ID isn't in the same entity as the
> data we want to manage. It's in a separate entity with only one attribute.
> There is supposed to be a 1:1 relationship between the ID entity and the
> data entity, but this is not usually maintained by the database. The
> cardinality of that relationship is managed either programmatically or by
> external business processes.
>
Bongo! I think you got the nail on the head.
The problem is that certain tools that I'll call "application generators"
blur the distinction between an ID field and the entity that it serves as an
indirect identifier for.
In particular, MA Access, a fairly popular application generator, has a a
datatype called "autonumber". If you make your primary key an autonumber
field, several things happen.
first, if you let a forms wizard build your data entry form for you, it
will omit the autonumbered field. Good idea, since you can't specify in
anyway. If you get in there with SQL and try to update the autonumber
field, Access will flag you with an error. I could go on.
So, even if the relational model doesn't forbid updates on any attribute,
the neophyte learning on MS Access is apt to learn that autonumber fields
can't be updated.
I don't know what the situation is for SQL Server. My Oracle knowledge is
out of date.
paul c
coming from you, it's tedious to dismantle every sentence, so I mention
only a couple:
Walter Mitty wrote:
> ... Now let's say
> you have a table that records values that look like (ID, value). In an
> actual case, value would probably ityself be a tuple, ...
Sounds like you are talking about a tuple within a tuple, the tuple that
'value' stands for being somehow 'within' the tuple that '(ID, value)'
stands for. I don't think there is a relational notion that matches
this, maybe you actually have RVA's in mind.
...
> So, Brian's claim that deleting a value and inserting a new one is somehow
> really different from updating an existing value works in this model, if
> you regard ID as somehow "special", and not "part of the value recorded in
> the table".
> ...
He is not the only one who talks about one thing and means something
diifferent. Here, at least one of you is dreaming of a table that
doesn't stand for one of Codd's relations. (Apparently SQL is capable
of the same thing, for reasons such as unnamed columns. According to
Date another flaw was that SQL is based on 'bags', not sets so it
wouldn't be a surprise if Codd shunned the System R department when he
worked for IBM.)
The first thing the mystics forget is that they can't make their claims
without admitting that they are talking about something other than
conventional relational theory. Their claims should be dismissed until
they develop their own formal theory for whatever machinations they have
in mind and make their assumptions and variations precise. Relational
theory is still developing but people who refuse to give substitutes for
the very few formal concepts only get in the way of progress.
Meanwhile, aspects of Codd's theory have loopholes such as whether some
views can be 'updated', constraint theory is mostly undeveloped, etc.
The majority of posts to c.d.t. are about physical questions. Regarding
the topic at hand, I've never seen it asked here whether there is any
logical reason why views can't have generated attribute values If a
view can have D&D-style extend-ed attributes, is there a use for
generating values? One possible use might be to simplify quota queries.
(Depending on how you read Codd's 1970 paper, he might have left the
door slightly ajar when he described his reduction algorithm, because
one can imagine hierarchies where arbitrary attributes need to be
introduced. If there are hierarchies that can't be shown as relations
without introduced attributes, are there relations that can't be shown
as tables without introduced attributes? I think Codd might have said
that if there are such relations, they should be avoided, but that's
just a guess. I'm certain he would have said such situations weren't
the problem he was after solving. Nevertheless, Codd never precluded
such attributes as fas as I know, eg. he didn't forbid an attribute's
value being generated by the machine.)
paul c
> ...
> So, even if the relational model doesn't forbid updates on any attribute,
> the neophyte learning on MS Access is apt to learn that autonumber fields
> can't be updated.
The neophyte learning MS Access is learning MS Access, not relational
theory. Probably the same can said of whoever designed MS Access. It's
true of other fields too that the average practitioner knows very little
theory but what is strange to me about this one is that compared to some
fields, there is very little relational theory to learn! The question
of how to implement 'updates', if asked with relational theory in mind,
should be put as how to replace one relation with another. There are
lots of ways to program that, how too pick one is a different question.
In fact, such comments aren't about relational theory, they would be
about 'table theory' if there were such a thing.
Bob Badour
> Just a couple of comments about a post that is unusually vague and fuzzy
> coming from you, it's tedious to dismantle every sentence, so I mention
> only a couple:
>
> Walter Mitty wrote:
>
>> ... Now let's say you have a table that records values that look like
>> (ID, value). In an actual case, value would probably ityself be a
>> tuple, ...
>
> Sounds like you are talking about a tuple within a tuple, the tuple that
> 'value' stands for being somehow 'within' the tuple that '(ID, value)'
> stands for. I don't think there is a relational notion that matches
> this, maybe you actually have RVA's in mind.
Tuples as data types and data values are perfectly fine. What's wrong
with them?
> ...
>
>> So, Brian's claim that deleting a value and inserting a new one is
>> somehow really different from updating an existing value works in this
>> model, if you regard ID as somehow "special", and not "part of the
>> value recorded in the table".
>> ...
>
> He is not the only one who talks about one thing and means something
> diifferent. Here, at least one of you is dreaming of a table that
> doesn't stand for one of Codd's relations. (Apparently SQL is capable
> of the same thing, for reasons such as unnamed columns. According to
> Date another flaw was that SQL is based on 'bags', not sets so it
> wouldn't be a surprise if Codd shunned the System R department when he
> worked for IBM.)
>
> The first thing the mystics forget is that they can't make their claims
> without admitting that they are talking about something other than
> conventional relational theory. Their claims should be dismissed until
> they develop their own formal theory for whatever machinations they have
> in mind and make their assumptions and variations precise. Relational
> theory is still developing but people who refuse to give substitutes for
> the very few formal concepts only get in the way of progress.
Very well said.
> Meanwhile, aspects of Codd's theory have loopholes such as whether some
> views can be 'updated', constraint theory is mostly undeveloped, etc.
> The majority of posts to c.d.t. are about physical questions. Regarding
> the topic at hand, I've never seen it asked here whether there is any
> logical reason why views can't have generated attribute values If a
> view can have D&D-style extend-ed attributes, is there a use for
> generating values? One possible use might be to simplify quota queries.
Values are values. What difference does it matter how one arrives at them?
> (Depending on how you read Codd's 1970 paper, he might have left the
> door slightly ajar when he described his reduction algorithm, because
> one can imagine hierarchies where arbitrary attributes need to be
> introduced. If there are hierarchies that can't be shown as relations
> without introduced attributes, are there relations that can't be shown
> as tables without introduced attributes? I think Codd might have said
> that if there are such relations, they should be avoided, but that's
> just a guess. I'm certain he would have said such situations weren't
> the problem he was after solving. Nevertheless, Codd never precluded
> such attributes as fas as I know, eg. he didn't forbid an attribute's
> value being generated by the machine.)
Of course not. Why would he forbid anything without cause?
Bernard Peek
<toledob...@oohay.ac> writes
>Walter Mitty wrote:
>> ...
>> So, even if the relational model doesn't forbid updates on any
>>attribute, the neophyte learning on MS Access is apt to learn that
>>autonumber fields can't be updated.
>> ...
>
>The neophyte learning MS Access is learning MS Access, not relational
>theory. Probably the same can said of whoever designed MS Access.
Access is a great product but I do wish that it had been designed by a
data-modeller.
--
Bernard Peek
paul c
...
> Tuples as data types and data values are perfectly fine. What's wrong
> with them?
> ...
I don't know that doing so is some kind of theoretical error. Since
they are used to define relations, then presumably a single tuple could
be an attribute value. However, when we already have relations and a
set of relation operators, doing so seems superfluous.
(I have often mused to myself about sets of tuples, constituting a
bastard attribute that is unnamed, which may have uses, but it wouldn't
be allowed in Codd's model.)
Bob Badour
> Bob Badour wrote:
> ...
>
>> Tuples as data types and data values are perfectly fine. What's wrong
>> with them?
>> ...
>
> I don't know that doing so is some kind of theoretical error. Since
> they are used to define relations, then presumably a single tuple could
> be an attribute value. However, when we already have relations and a
> set of relation operators, doing so seems superfluous.
The only necessary data type is boolean. All other data types are
superfluous. That doesn't reduce the utility of other data types.
> (I have often mused to myself about sets of tuples, constituting a
> bastard attribute that is unnamed, which may have uses, but it wouldn't
> be allowed in Codd's model.)
If it is unnamed, how would you refer to it? Names are important things.
paul c
...
> If it is unnamed, how would you refer to it? Names are important things.
That was sort of the point, I wouldn't need to refer to a set-valued
attribute. One motive had to do with finding a recursive structure that
might implement TCLOSE more efficiently at least in some cases. Another
had to do with missing information. Of course the admittedly vauge idea
I had in mind would mean that the names in the tuple triples would need
to be unique within the relation as a whole and I would certainly need
to come up with a definition for this that would be of similar quality
to TTM's Appendix A, which I never succeeded at.
There is very little implementation theory, for example about
optimization, most of what I've seen is adhoc.
paul c
...
> The only necessary data type is boolean. All other data types are
> superfluous. That doesn't reduce the utility of other data types.
I think it is possible to construct a 'toy' or 'educational' system that
would demonstrate that it is not essential for a strictly minimal
implementation to present any kind of boolean result, only producing
relations, and supporting constraints as propositions, as long as
projection on zero attributes is implemented. It depends on the
beholder. Sure, I wouldn't argue that a system that supports relations
with different headings isn't implicitly recognizing different relation
types but exposing, say, the 'two-halves' of a comparison is not the
same as exposing the result of the comparison. Maybe one day I'll
finish trying to do that, thinking about it is one of my very few
theoretical interests..
For example, just indulge me and take the common KEY ddl shorthand and
express it algebraically. There are several 'constraining' relations
that could result, depending on how the constraint is phrased. One
might have only the non-offending tuples, one might have the offending
tuples, one might simply produce a relation with no attributes. The
first of those three constraining relations joined with the subject
relation would produce a relation that satisfies the constraint. While
extremely convenient for some languages to have a way of knowing the
subject and the join were in fact the same relation, it is not
absolutely essential to achieve a practical result. After all, if some
tuples get 'dropped', it is only because they shouldn't have been
present in the first place. In other words, it is not essential for an
arbitrary constraint to result in 'true/false' or 'yes/no'. This is why
I think the theory people usually talk about is over-enlarged compared
to Codd's basic ideas, enlarged for admittedly reasonable and practical
reasons but it's not always made clear that the reasons are practical
and not entirely theoretical. So, at least for this case, I'd dispute
that the boolean type is absolutely necessary. Unlike Date, I don't see
Codd's model as being probably eternal, I'd have to see a lot more work
done with constraints before that. (I know Date doesn't say exactly
that, but he hints at it very confidently.)
One might say doing all that is silly, why should we when KEY's have
already been 'solved'? If that's silly, then so is examining the nature
of electrons. I think it's possible that some clues as to what a
coherent optimization theory might require could result from that.
Whereas I think it's very likely that every SQL implementation simply
echoes the behaviour of the keys in file systems of the 1950's and
1960's.
While I'm on about this, Codd got a lot of flak for talking about
'time-varying' relations which is a mangled term. Then the OO language
manglers turned the intransitive verb 'persist' into a transitive one, I
think as a way to disguise the fact that they weren't quite sure what
they meant. For purposes of a toy implementation, I don't have any
problem pretending I live in a world where every operation materializes
the result and makes up a name which can then be used to query the
result. When you really get down to it, his theory is as spare as they
come.
Bob Badour
> Bob Badour wrote:
> ...
>
>> If it is unnamed, how would you refer to it? Names are important things.
>
> That was sort of the point, I wouldn't need to refer to a set-valued
> attribute.
What's the point of any attribute if one cannot refer to the attribute?
It would be useless and essentially not there at all.
paul c
I should remember to hesitate about talking about stuff that isn't fully
formed, when I criticize others for similar, and which is so easily
questioned and so easily can be stillborn, but since I brought it up, I
guess I'm obligated to show some token perserverence. in this case the
point would be that the set of tuples in the 'unnamed' attribute would
indeed have names, except, I suppose, if they in turn were 'tuple'
set-valued. This would all need a recursive relation definition, I
remember reading a comment of Date's where he specifically restricted
his view of things to exclude such definitions.
Brian Selzer
"paul c" <toledob...@oohay.ac> wrote in message
news:9tUTl.29817$PH1.24288@edtnps82...
Not according to Codd: I suggest you read his papers and his book.
Bob Badour
Of course he would. You have basically re-invented NF-squared, and that
was a dumb idea the first time around. Just look at the mess PICK is
because it tries to do implicit shit and prevents one from making
explicit statements. Names have a purpose.
paul c
You're too late!
toby
> ...I don't have any
> problem pretending I live in a world where every operation materializes
> the result and makes up a name which can then be used to query the
> result.
Is this just a long way of saying "functional" or algebraic? ...
Walter Mitty
What would the data structure look like if it were fully normalized?
Walter Mitty
> Just a couple of comments about a post that is unusually vague and fuzzy
> coming from you, it's tedious to dismantle every sentence, so I mention
> only a couple:
I apologize for how vague and fuzzy this topic is. If my wording is vague
and fuzzy, my thinking is even more vague and fuzzy.
The first thing is whether or not I'm talking about a tuple within a tuple.
This was a digression. As far as I'm concerned, the difference between the
following two is not fundamental:
(714, Joe, Friday, 234-5678)
(714, (Joe, Friday,234-5678))
At least not fundamental for the purposes of figuring out how "714" comes to
be associated with Joe Friday and not with some other policeman.
Next, the question arises whether the association is managed by the
database or whether its managed programatically or by some external
function, manual or automatic.
Some responder addressed precisely that question. I think it was Bernard.
In my experience discussing this topic with fans of the ID field concept,
I've found that, contrary to Bernard's opinion, they turn over management
of the association to the database. I smell a rat at this point, because I
think they are asking a database to perform a function that is not really a
database function. I think that assigning 714 to Joe Friday is inventing
data, and it's not the database system's job to invent data. It's the
database system's job to manage the data it's given. That's not to say that
some popular tool like MS Access might not do it anyway.
And it's not clear to me whether MS Access is really a DBMS. I think it's
an application management system.
I think the process by which 714 comes to be associated with Joe Friday is
actually somewhat mysterious. Googling "Badge 714" confirmed this opinion.
BTW, I an NOT using "mysterious" as a code word for "mystical". The one
thing I can say is that it's not a consequence of the data. It's either
part of the data as given, or it's part of some fairly arbitrary process.
Next, my statement of the issue is fogged by my own ignorance of
relational theory. My introduction to relational theory was only as a
backdrop to my introduction to database design. At the time, I was learning
how to design SQL databases, which all of us called "relational databases".
I don't recall if I heard Ed Codd's name, but I know I never read anything
he wrote until much later. This informal and spotty education
notwithstanding, I continue to view my own interpretation of relational
theory to be largely orthodox, when compared to some of the crazy things
that have been accepted as "best practices" in database design.
Anyway, how would relational theory decribe the operation that I called
"UPDATE" (using the SQL keyword intentionally.)? would it be something like
the following?
relvar R gets assigned relvar R minus relation(tuple T) plus relation(tuple
U).
How does this differ from a DELETE followed by an INSERT?
I don't think this post clears up any of the fuzziness. But maybe it lays
the groundwork for clearingf it up.
paul c
...
> I think the process by which 714 comes to be associated with Joe Friday is
> actually somewhat mysterious. Googling "Badge 714" confirmed this opinion.
> BTW, I an NOT using "mysterious" as a code word for "mystical". The one
> thing I can say is that it's not a consequence of the data. It's either
> part of the data as given, or it's part of some fairly arbitrary process.
There will always be 'mysteries' that machines don't deal with. I'd say
requirements should always trump any notion of some data being innately
different from other data. Requirements are arbitrary, to say they
aren't is to say any system will suit any purpose. For me if it's a
requirement that a system generate badge numbers, that's pretty much the
same thing as a requirement that it generate invoice numbers. If it's a
requirement that a clerk generate the numbers instead, so be it.
Whether the numbers are calculated or sequential or reversible might be
another requirement.
If it's a requirement that the formula for Coca-Cola must never be
recorded in a database, so be it. Every once in a while a word comes up
here that I have trouble spelling, anthrop...morphizing or somesuch. And
I have seen 'requirements' that some aspect of a system be
'personalized'! The literalists might say that a system that is not
personalized, must be impersonal. Both are inapt. Machines are neither
personal nor impersonal.
Walter Mitty
> Walter Mitty wrote:
> ...
>> I think the process by which 714 comes to be associated with Joe Friday
>> is actually somewhat mysterious. Googling "Badge 714" confirmed this
>> opinion. BTW, I an NOT using "mysterious" as a code word for "mystical".
>> The one thing I can say is that it's not a consequence of the data. It's
>> either part of the data as given, or it's part of some fairly arbitrary
>> process.
>> ...
>
> There will always be 'mysteries' that machines don't deal with. I'd say
> requirements should always trump any notion of some data being innately
> different from other data. Requirements are arbitrary, to say they aren't
> is to say any system will suit any purpose. For me if it's a requirement
> that a system generate badge numbers, that's pretty much the same thing as
> a requirement that it generate invoice numbers. If it's a requirement
> that a clerk generate the numbers instead, so be it. Whether the numbers
> are calculated or sequential or reversible might be another requirement.
>
>
distinguish between application requirements and database requirements.
I think that associating a new instance of any entity with an abitrary
"next" element of some sequence that's suitable for identification, is an
application requirement.
There's one aspect of autogeneration that ought to be persistent.
Remembering where the sequence generator was in its sequence so that it can
resume at some later time where it left off last time, is a legitimate
database function.
All of this raises yet another question. Should application development
swallow database development? (Whee! Now we can fire all the DBAs!) Or,
alternatively, should database development swallow up applications
development? (Whee! Now we can fire all the programmers!). Or should they
remain collaborators neither of which fully controls the other.
Brian Selzer
"Walter Mitty" <wam...@verizon.net> wrote in message
news:fv9Ul.1465$Cc1....@nwrddc01.gnilink.net...
Many on this newsgroup adhere with blind zeal to Date and Darwin's
interpretation of relational theory that has at its roots the notions that a
database is a collection of relation variables (relvars) and that insert,
update and delete are somehow "shortcuts" for relational assignments. They
refuse to recognize the limitations these notions impose, such as the
requirement that every instance of every key be a permanent identifier for
something in the Universe of Discourse. If instances of a key do not
permanently identify things in the Universe, then transition constraints
that join the relation values before and after an assignment to a relvar
would not be sound. For example, suppose that you have a relation, {L, F,
Stat} Key {L, F}that starts out with the following value:
{{L:Smith, F:Mary, Stat:Single},
{L:Jones, F:Mary, Stat:Married}}
and is then assigned the value,
{{L:Smith, F:Mary, Stat:Divorced},
{L:Jones, F:Mary, Stat:Married}}
You might be wondering how someone who is Single can become Divorced?
Should the assignment be rejected on that basis? Or is it possible that
Mary Jones' maiden name is Smith, and that she became Divorced while at the
same time the Mary Smith who was Single Married Robert Jones? It might also
be that the Mary Smith who was Single moved out of the area while a
different Mary Smith, who is Divorced, moved in.
The D&D adherents would try to claim that this is a bad design, and as such
should be dismissed, but Codd, even as early as 1970, recognized that key
components of tuples would be the target of updates, which should be obvious
given that there can be relation schemes in which the entire heading is the
key. The ambiguity inherent in relational assignment that is illustrated by
the above example is exhibited by every relation in which instances of the
key do not permanently identify things in the Universe, so their claim
becomes just noise.
Note that the introduction of an autogenerated ID eliminates all confusion:
If you start out with,
{{ID:1, L:Smith, F:Mary, Stat:Single},
{ID:2, L:Jones, F:Mary, Stat:Married}}
and then assign
{{ID:1, L:Smith, F:Mary, Stat:Divorced},
{ID:2, L:Jones, F:Mary, Stat:Married}}
then clearly the assignment should be rejected because Single people can't
get Divorced, but if you assign
{{ID:1, L:Jones, F:Mary, Stat:Married},
{ID:2, L:Smith, F:Mary, Stat:Divorced}}
then the assignment should be allowed, because Single people can get Married
and Married people can get Divorced. Similarly, if you assign,
{{ID:2, L:Jones, F:Mary, Stat:Married},
{ID:3, L:Smith, F:Mary, Stat:Divorced}}
Then it is clear that there is a different Mary Smith, and the assignment
should be allowed.
It is a consequence of adopting D&D's notions that demands the introduction
of surrogates, such as autogenerated IDs. If, on the other hand, insert,
update and delete are not shortcuts, but rather primitive operations, then
the introduction of the autogenerated IDs is not necessary. All that is
needed is to correlate the tuples in a relation that are the target of an
update with those in its successor. For example, the update
{{L:Smith, F:Mary, Stat:Single, L':Smith, F':Mary, Stat':Divorced}}
should clearly be rejected, but the update,
{{L:Smith, F:Mary, Stat:Single, L':Jones, F':Mary, Stat':Married},
{L:Jones, F:Mary, Stat:Married, L':Smith, F':Mary, Stat':Divorced}}
should be allowed. Similarly, the delete and insert,
{{L:Smith, F:Mary, Stat:Single},
{L':Smith, F':Mary, Stat':Divorced}}
should be allowed.
Brian Selzer
Just to be clear, one can assert what in the world is different and how, but
one cannot "update" a value. Delete, update and insert are primitive
assertions that state, in the context of what has up to now been the case,
what has just ceased to exist, what has just changed in appearance and what
has just come into existence, respectively. A value is the output of the
valuation function which under an interpretation maps the terms of a formal
language to things in the Universe of Discourse; therefore, each value is
fixed at the instant of interpretation and therefore not subject to
modification.
It is not that ID is somehow "special;" it is that each ID symbol
permanently identifies (rigidly designates) something in the Universe of
Discourse. Note that I am not arguing for or against the introduction of
autogenerated ID fields, but when they /are/ introduced, they are not
pointers: they are names. They are names in the same way that a sales order
number names a particular sales order; they are names in the same way that a
particular VIN names a particular vehicle. Contrast that with instances of
compound keys (and even some unary keys), which are descriptions rather than
names.
Walter Mitty
"Brian Selzer" <br...@selzer-software.com> wrote in message
news:O4mUl.12059$im1....@nlpi061.nbdc.sbc.com...
Thank you for a clear explanation of a concept that you have been trying to
get across for lo these many months.
The example is well chosen, and illustrates the concept you are trying to
get across better than your previous abstract explanations. At least that's
true for me. Maybe I just learn by moving from the concrete to the abstract
more than the other way around.
I'd rather mull this over for a while before responding.
Meanwhile, a parallel conundrum is whether a key identifies something in the
Universe of Discourse or a row in a table. Many of the folks in
stackoverflow and similar places seem to take the latter view. Once you
take that view, assigning the row an immutable ID and referencing the row by
that ID starts to make a lot of sense. The idea that by doing so they have
readopted the graph model of data and jettisoned the relational model of
data doesn't seem to have reached their consciousness at all, as far as I
can tell. If I'm right about this, then maybe they are trying to
unconsciously model the data using a syncretism between two irreconcilable
models of data.
paul c
>> {{L:Smith, F:Mary, Stat:Single},
>> {L:Jones, F:Mary, Stat:Married}}
>>
>> and is then assigned the value,
>>
>> {{L:Smith, F:Mary, Stat:Divorced},
>> {L:Jones, F:Mary, Stat:Married}}
...
>> Note that the introduction of an autogenerated ID eliminates all
>> confusion:
"autogenerated ID eliminates all confusion", what a laugh, almost sounds
like a sleeping pill slogan.
Walter, I suggest the only important claim here is that somehow
relational theory doesn't offer a way to constrain such values.
Certainly, since we are presented with the two different relation
values, such a constraint could be written algebraically and I presume
also in FOL, without resort to generated id's. Nothing wrong with
generated ID's per se, but they aren't essential in this context. BTW,
one of the many other confusions I snipped is that assignment is part of
relational theory, whereas it's not, it is a concept certain kinds of
languages need. But the basic confusion remains - the wilfull imputing
of unspoken requirements, almost as if the existence of some app with
some arbitrary requirement requires that all apps support that
requirement..
Walter Mitty
I am confused. Your comment is directed at me but you quote Brian. What's
going on?
paul c
...
> I am confused. Your comment is directed at me but you quote Brian. What's
> going on?
There is no point directing anything to Brian "BS" Selzer, nor Joe "Sgt.
Bilko" Celko, because they have shown themselves incapable of
recognizing that RT isn't yet fully-formed and will always react with
the same tired misconceptions, misappropriations and bogus distractions.
It is one thing to be ignorant, which we all are at times, but those
two don't recognize it. They aren't alone and have had a number of
predecessors here over the few years I've been watching.
If you don't perceive the clues I offer, I don't mind and you needn't
even thank me, ha ha. Speaking of the lessons of history, I am starting
to see how accurate Fabien Pascal was if he agreed that the world isn't
ready for the RM!
Bernard Peek
<wam...@verizon.net> writes
>> Updating the data without making a simultaneous update to the ID field is
>> perfectly permissible within the relational model. They are after all in
>> separate entities with only a tenuous connection. It's an update anomaly
>> and it's caused by failure to fully normalise the data structure.
>
>What would the data structure look like if it were fully normalized?
Essentially removing the superfluous ID field would normally be a step
towards it. This assumes that the fields listed in your example are
sufficient to form a key. If the database permits multiple people with
the same first and second names and the same phone number then you have
a problem. I can imagine scenarios where that situation might exist.
Adding an ID field makes the problem appear to go away, but in fact it
just moves it. You may have William Gates II and William Gates III
living at the same address and so sharing a phone number. Adding an ID
field does at least allow the database to record the fact that two
different people with the same name and phone number exist. It doesn't
solve the second part of the problem, which is how to ensure that the
database can reliably distinguish data on these two people.
Adding an ID field is only useful if you can reliably link the ID to the
real person. The traditional way to do this is to tell each person the
ID that you have created for them and insist that they remember it for
you. This is how SSNs and payroll numbers work. This system is not
foolproof.
--
Bernard Peek
Brian Selzer
> Walter Mitty wrote:
> ...
>> I am confused. Your comment is directed at me but you quote Brian.
>> What's going on?
>
> There is no point directing anything to Brian "BS" Selzer, nor Joe "Sgt.
> Bilko" Celko, because they have shown themselves incapable of recognizing
> that RT isn't yet fully-formed and will always react with the same tired
> misconceptions, misappropriations and bogus distractions.
Hmm.... I don't recall ever asserting that RT is fully-formed.
Misconceptions? Misappropriations? Distractions? Bogus? I would, if it
were even possible for you to formulate a reasonable answer, demand that you
cite examples of such misconceptions, misappropriations and bogus
distractions, and justify that they are indeed misconceived,
misappropriated, or even distracting, if not also bogus, but it would be
more likely to rain beer.
> It is one thing to be ignorant, which we all are at times, but those two
> don't recognize it. They aren't alone and have had a number of
> predecessors here over the few years I've been watching.
>
It is quite another to be willfully ignorant, but that seems to be an
affliction that has reached epidemic proportions.
Kevin Kirkpatrick
> "Brian Selzer" <br...@selzer-software.com> wrote in message
>
> news:O4mUl.12059$im1....@nlpi061.nbdc.sbc.com...
>
>
>
>
>
>
>
> >news:fv9Ul.1465$Cc1....@nwrddc01.gnilink.net...
>
> >> "paul c" <toledobythe...@oohay.ac> wrote in message
What is the poor HR person to do when Mary Smith returns from a week
long Vegas trip and reports that she'd gotten married on Monday and
divorced on Friday?
> > should clearly be rejected, but the update,
>
> > {{L:Smith, F:Mary, Stat:Single, L':Jones, F':Mary, Stat':Married},
> > {L:Jones, F:Mary, Stat:Married, L':Smith, F':Mary, Stat':Divorced}}
>
> > should be allowed. Similarly, the delete and insert,
>
> > {{L:Smith, F:Mary, Stat:Single},
> > {L':Smith, F':Mary, Stat':Divorced}}
>
> > should be allowed.
>
> Thank you for a clear explanation of a concept that you have been trying to
> get across for lo these many months.
> The example is well chosen, and illustrates the concept you are trying to
> get across better than your previous abstract explanations. At least that's
> true for me. Maybe I just learn by moving from the concrete to the abstract
> more than the other way around.
>
> I'd rather mull this over for a while before responding.
>
> Meanwhile, a parallel conundrum is whether a key identifies something in the
> Universe of Discourse or a row in a table. Many of the folks in
> stackoverflow and similar places seem to take the latter view. Once you
> take that view, assigning the row an immutable ID and referencing the row by
> that ID starts to make a lot of sense. The idea that by doing so they have
> readopted the graph model of data and jettisoned the relational model of
> data doesn't seem to have reached their consciousness at all, as far as I
> can tell. If I'm right about this, then maybe they are trying to
> unconsciously model the data using a syncretism between two irreconcilable
> models of data.- Hide quoted text -
>
> - Show quoted text -
Bob Badour
> On May 31, 2:12 am, "Walter Mitty" <wami...@verizon.net> wrote:
>
>>"Brian Selzer" <br...@selzer-software.com> wrote in message
>>
>>news:O4mUl.12059$im1....@nlpi061.nbdc.sbc.com...
>>
>>>"Walter Mitty" <wami...@verizon.net> wrote in message
>>>news:fv9Ul.1465$Cc1....@nwrddc01.gnilink.net...
>>
>>>>"paul c" <toledobythe...@oohay.ac> wrote in message
>>>>news:vdUTl.29814$PH1.1299@edtnps82...
>>>>
>>>>>Just a couple of comments about a post that is unusually vague and fuzzy
>>>>>coming from you, it's tedious to dismantle every sentence, so I mention
>>>>>only a couple:
>>
>>>>I apologize for how vague and fuzzy this topic is. If my wording is
>>>>vague and fuzzy, my thinking is even more vague and fuzzy.
<snip>
>>>{{L:Smith, F:Mary, Stat:Single, L':Smith, F':Mary, Stat':Divorced}}
>
> What is the poor HR person to do when Mary Smith returns from a week
> long Vegas trip and reports that she'd gotten married on Monday and
> divorced on Friday?
Wouldn't it make more sense to just ask the OP to re-post in
comp.databases.vague.fuzzy ?
Bernard Peek
<toledob...@oohay.ac> writes
>Walter Mitty wrote:
>> "Brian Selzer" <br...@selzer-software.com> wrote in message
>...
>>> {{L:Smith, F:Mary, Stat:Single},
>>> {L:Jones, F:Mary, Stat:Married}}
>>>
>>> and is then assigned the value,
>>>
>>> {{L:Smith, F:Mary, Stat:Divorced},
>>> {L:Jones, F:Mary, Stat:Married}}
>>> ...
>...
>>> Note that the introduction of an autogenerated ID eliminates all
>>>confusion:
>>> ...
>
>"autogenerated ID eliminates all confusion", what a laugh, almost
>sounds like a sleeping pill slogan.
I think I'd say that an autogenerated ID hides the problem, or at least
moves it around. Instead of asserting that the key always refers to an
instance within the universe of discourse, you substitute an autonumber
field and make the same assertion.
In an entity the key refers unambiguously to an instance within the
universe. When you build a database table that is an aspiration.
--
Bernard Peek
rpost
>Adding an ID field is only useful if you can reliably link the ID to the
>real person. The traditional way to do this is to tell each person the
>ID that you have created for them and insist that they remember it for
>you. This is how SSNs and payroll numbers work. This system is not
>foolproof.
There is an alternative: keeping the schema such that persons can always
be identified based on non-ID fields, either directly within the table
(i.e. it has a key that doesn't involve ID fields), or indirectly
(i.e. queries to distinguish all persons exist, but may involve
multiple tables, even joins over ID fields).
>--
>Bernard Peek
--
Reinier Post
Brian Selzer
"Kevin Kirkpatrick" <kvnkr...@gmail.com> wrote in message
news:670a228b-b752-47eb...@s31g2000vbp.googlegroups.com...
> What is the poor HR person to do when Mary Smith returns from a week
> long Vegas trip and reports that she'd gotten married on Monday and
> divorced on Friday?
That should be obvious: issue two updates in succession, one for each event.
Brian Selzer
> In message <ImwUl.30133$PH1.14509@edtnps82>, paul c
> <toledob...@oohay.ac> writes
>>Walter Mitty wrote:
>>> "Brian Selzer" <br...@selzer-software.com> wrote in message
>>...
>>>> {{L:Smith, F:Mary, Stat:Single},
>>>> {L:Jones, F:Mary, Stat:Married}}
>>>>
>>>> and is then assigned the value,
>>>>
>>>> {{L:Smith, F:Mary, Stat:Divorced},
>>>> {L:Jones, F:Mary, Stat:Married}}
>>>> ...
>>...
>>>> Note that the introduction of an autogenerated ID eliminates all
>>>> confusion:
>>>> ...
>>
>>"autogenerated ID eliminates all confusion", what a laugh, almost sounds
>>like a sleeping pill slogan.
>
> I think I'd say that an autogenerated ID hides the problem, or at least
> moves it around. Instead of asserting that the key always refers to an
> instance within the universe of discourse, you substitute an autonumber
> field and make the same assertion.
Not so: addition is not the same as substitution.
>
> In an entity the key refers unambiguously to an instance within the
> universe. When you build a database table that is an aspiration.
>
Consider an ordered set, such as a deck of cards. When implemented in a
relation schema, {Position, Card}, there are two candidate keys,
{Position}, and {Card}. Now suppose that the deck is shuffled, and a
database update issued to reflect the new state of the deck. The card at
the bottom of the deck before shuffling may not be the card at the bottom of
the deck after shuffling. So despite the fact that Position is a candidate
key of the representative relation schema, instances of it do not
permanently identify things in the Universe.
> --
> Bernard Peek
Kevin Kirkpatrick
> "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
Am I reading that right? You think the data entry person should
enter, as a fact to the database, "Mary Smith is currently Married",
at a time when Mary Smith is definitely not married?
Bernard Peek
<br...@selzer-software.com> writes
>
>"Bernard Peek" <b...@shrdlu.com> wrote in message
>news:2hlWqCcL...@shrdlu.com...
>> In message <ImwUl.30133$PH1.14509@edtnps82>, paul c
>> <toledob...@oohay.ac> writes
>>>Walter Mitty wrote:
>>>> "Brian Selzer" <br...@selzer-software.com> wrote in message
>>>...
>>>>> {{L:Smith, F:Mary, Stat:Single},
>>>>> {L:Jones, F:Mary, Stat:Married}}
>>>>>
>>>>> and is then assigned the value,
>>>>>
>>>>> {{L:Smith, F:Mary, Stat:Divorced},
>>>>> {L:Jones, F:Mary, Stat:Married}}
>>>>> ...
>>>...
>>>>> Note that the introduction of an autogenerated ID eliminates all
>>>>> confusion:
>>>>> ...
>>>
>>>"autogenerated ID eliminates all confusion", what a laugh, almost sounds
>>>like a sleeping pill slogan.
>>
>> I think I'd say that an autogenerated ID hides the problem, or at least
>> moves it around. Instead of asserting that the key always refers to an
>> instance within the universe of discourse, you substitute an autonumber
>> field and make the same assertion.
>
>Not so: addition is not the same as substitution.
You misunderstand me. An ID field is added to the table but is
substituted in the assertion.
>
>>
>> In an entity the key refers unambiguously to an instance within the
>> universe. When you build a database table that is an aspiration.
>>
>
>Consider an ordered set, such as a deck of cards. When implemented in a
>relation schema, {Position, Card}, there are two candidate keys,
>{Position}, and {Card}. Now suppose that the deck is shuffled, and a
>database update issued to reflect the new state of the deck. The card at
>the bottom of the deck before shuffling may not be the card at the bottom of
>the deck after shuffling. So despite the fact that Position is a candidate
>key of the representative relation schema, instances of it do not
>permanently identify things in the Universe.
Within a single deck of cards at any point either {Position} or {Card}
are keys. Within that universe of discourse either would be valid. But
if you open this out to include the result of shuffling the deck then
you also need to consider {Time}. Having done that you find that a
compound key of {Time}{Position} or {Time}{Card} permanently identifies
a thing in the universe.
You could take this a step further and expand the universe of discourse
to include every state of every deck at every time. In which case you
need to stipulate which deck of cards you are referring to as well as
the time that you take your readings. I could make this arbitrarily
complex by insisting that the model also covers Tarot cards and decks
which are in the state of being shuffled.
In many cases a database may not store all of the information required
to for a key, and this is the norm when databases hold data about
people. Nobody has yet come up with a practical key which uniquely
identifies people. I think the closest that anyone has come is my
suggestion in this newsgroup; Latitude, Longitude, Altitude and Time of
birth.
--
Bernard Peek
Brian Selzer
Yes, you're reading that right. The data entry person should enter, as a
fact into the database, "Mary Smith got Married", at a time when it is true
that Mary Smith got Married, and then "Mary Smith got Divorced" at a time
when it is true that Mary Smith got Divorced. The database has to be
informed that a marriage occurred and then that a divorce occurred, since it
is not just states that are being constrained, but changes of state.
Kevin Kirkpatrick
> "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
>
> news:895891d5-fe55-478d...@r13g2000vbr.googlegroups.com...
>
> > On Jun 1, 11:39 am, "Brian Selzer" <br...@selzer-software.com> wrote:
> > > "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
>
> > >news:670a228b-b752-47eb...@s31g2000vbp.googlegroups.com...
>
> > > > What is the poor HR person to do when Mary Smith returns from a week
> > > > long Vegas trip and reports that she'd gotten married on Monday and
> > > > divorced on Friday?
>
> > > That should be obvious: issue two updates in succession, one for each
> > > event.
>
> > Am I reading that right? You think the data entry person should
> > enter, as a fact to the database, "Mary Smith is currently Married",
> > at a time when Mary Smith is definitely not married?
>
> Yes, you're reading that right. The data entry person should enter, as a
> fact into the database, "Mary Smith got Married", at a time when it is true
> that Mary Smith got Married, and then "Mary Smith got Divorced" at a time
> when it is true that Mary Smith got Divorced.
Sorry, I misinterpretted the relation {L, F, Stat} to indicate
"The person with last name <L> and first name <F> is currently <Stat>"
It seems like you intended the relation to mean:
"The person with last name <L> and first name <F> got <Stat>"
But I'm still confused... at the time it was true that "Mary Smith got
Divorced", and the database tuple changed from {"Smith", "Mary",
"Married"} to {"Smith", "Mary", "Divorced"}, doesn't that imply that
it was no longer true that "Mary Smith got Married"?
> The database has to be
> informed that a marriage occurred and then that a divorce occurred, since it
> is not just states that are being constrained, but changes of state.
"The database has to be informed that a marriage occurred"
I thought the UoD was limited to a person's current marital status
(hence the single relation {L, F, Stat}). Is the fact that "a
marriage occured" part of the UoD? If so, how is this fact
represented in the data model?
Brian Selzer
That's an interesting way to express the "meaning" of a relation, but the
verb tenses are wrong.
The "meaning" of the relation {L, F, Stat} is:
"The person that has up to now had last name <L> and first name <F> has up
to now been <Stat>."
The "meaning" of an update {L, F, Stat, L', F', Stat'} is:
"The person that had up to now had last name <L> and first name <F> and had
up to now been <Stat> now has last name <L'> and first name <F'> and is
<Stat'>."
In the same way, the "meaning" of a delete {L, F, Stat} is:
"The person that had up to now had last name <L> and first name <F> and had
up to now been <Stat> no longer exists."
and the "meaning" of an insert {L', F', Stat'} is:
"There exists a person that has last name <L'> and first name <F'> and is
<Stat'>."
Just one point I need to make about these "meanings:" formally, in a fixed
Universe, existence must be in fact a predicate, not a quantifier. To have
existed is to have become actual; to exist is to still actually be.
Distinguishing existence from being permits the quantifier, "there is" to
range over all kinds of things, including things that have a past, present
or possible locus in time as well as things that are independent of time.
> But I'm still confused... at the time it was true that "Mary Smith got
> Divorced", and the database tuple changed from {"Smith", "Mary",
> "Married"} to {"Smith", "Mary", "Divorced"}, doesn't that imply that
> it was no longer true that "Mary Smith got Married"?
>
"Mary Smith got Divorced" implies that "Mary Smith got Married" as a
consequence of the transition constraint that prohibits Single people from
getting Divorced.
Brian Selzer
That's not what the definition of a candidate key requires. The uniqueness
property applies to /each/ possible relation, not to /every/ possible
relation.
David BL
> one of the many other confusions I snipped is that assignment is part of
> relational theory, whereas it's not, it is a concept certain kinds of
> languages need.
If by "relational theory" you mean the relational algebra then it's
true it doesn't include assignment. But obviously the sub-topic
concerns the changes to a relational /database/ over time.
This news group by definition concerns database theory. The main
purpose of a database is to record values in variables. Whenever a
variable is changed it has by definition been assigned with a
different value.
I doubt you'll take my word for, but perhaps you'll listen to Bob. The
following quote comes from a post by Bob in Oct 2003:
All D's are imperative. Imperative languages comprise
those languages that operate by changing the state of
variables. Since a database is a variable, imperative
languages are entirely appropriate for data management.
Regardless whether one writes expressions using the
calculus or the algebra, one manages the data by
changing the state of the database variable.
Brian Selzer
>
> "Kevin Kirkpatrick" <kvnkr...@gmail.com> wrote in message
> news:a5076075-7ae9-4ab1...@n21g2000vba.googlegroups.com...
>>
>> I thought the UoD was limited to a person's current marital status
>> (hence the single relation {L, F, Stat}). Is the fact that "a
>> marriage occured" part of the UoD? If so, how is this fact
>> represented in the data model?
>
The fact need not be represented because it is implied. The transition
constraint that enumerates the changes in marital status that are allowed
also augments the "meaning" of each tuple. Single implies that no marriage
occurred; Married and Divorced both imply that at least one marriage
occurred.
The arguments against implementing transition constraints seem to fall into
two categories: The first and most often expressed is the fact that users
screw up and there has to be a way to fix those screw ups, and sometimes the
transition constraints get in the way. The second is the one you've
expressed, which is that there may be a delay in issuing an update that is
long enough so that more than one change to the thing in the Universe could
have occurred.
The first argument can be answered by providing a mechanism for
differentiating between a transition and a correction. The simplest
mechanism is to just require that an explanation be given when the database
update is a correction. The explanation could be recorded as tuples in a
separate relation or as components in the same relation. Transition
constraints could then be written so that they return True whenever an
explanation is provided in the same database update.
The second argument can be worked around by simply providing an explanation
in the same way as a correction, but I would argue for issuing successive
updates, since the database did not reflect what had actually been the case
since the start of the delay, so it is more important in my opinion that the
order in which events occurred be preserved, so the transition constraints
could be checked in the same order that the events occurred.
paul c
If you're trying to say RT allows languages that support pointers, okay.
If you're trying to say RT requires an imperative style, eg.,
languages that support pointers, you're wrong. Tutorial D takes the
arbitrary choice of aiming for the imperative style. You may as well
say that a cpu that has some instructions that modify registers and
some that indirect register contents can only support imperative
languages. This may be hard for people who've never used assembler to
see, but that's not my problem. Date calls Tutorial D algebraic but he
doesn't say it's entirely algebraic. Functional languages are equally
appropriate for an algebraic theory. Personally, I prefer names that
amount to nothing more than macro-ized labels, this helps me think about
programs instead of worrying about languages. SICP at the mit site,
chapter three if I recall, talks about the sea change in
comprehensibility that arises once assignment is introduced. Not only
do I prefer to not have to think about statement order, I think most
programmers are really no good at it. Just because db values vary
doesn't mean one must implement variables and assignment. I think it is
quite plausible that future generations will come to this view and laugh
at today's self-imposed obstacles.
Brian Selzer
I suggest that you re-read chapter three of SICP. I think I've suggested
that before, but if you really don't want to be the subject of ridicule, I
recommend a close revisitation. In particular, I suggest you focus on their
example involving the current balance of a bank account, since housing
current state is the primary function of a database. I suggest, as does the
author of SICP, that the functional programming paradigm is contraindicated
for this purpose.
It's humorous, really, that you juxtaposed me and Mr. Celko in a previous
post, but now provided me an opportunity to reciprocate by pointing out that
in the same way that Joe Celko, in his canned posts, cited works that he
thought supported his argument when they in fact undermined it, your
reference to chapter 3 of SICP effectively undermines your argument.
cim...@hotmail.com
> quite plausible that future generations will come to this view and laugh
> at today's self-imposed obstacles.
A reasonnable assumption...I would go further than that into assuming
that future civilizations can only take advantage of what relational
theory has to offer. I came to believe that relational theory is
still at infancy stage and that too many aspects still require
mathematical clarification to reach its full potential.
The self-imposed relational exploration of the last ten years have
raised more questions than they brought questions. Such questions
have not been adressed specifically neither by D&D algebra nor in any
similar serious work I could put my hand onto. IMHO, RM can not be
explored any further with the existing tools and must get back to math
for inspiration, not just algebra but set theory and even geometry.
Chances are also that by the time that happens, it won't be called
relational theory anymore but will evolve to something that is not
just set theory inspired.
Regards...
cim...@hotmail.com
> The self-imposed relational exploration of the last ten years have
> raised more questions than they brought questions.
my world of self doubt...
.
David BL
Any instruction to update the database state is imperative by
definition. What else do you think imperative means?
[snip]
> Just because db values vary
> doesn't mean one must implement variables and assignment.
Values don't vary by definition. Only variables can vary.
How can you accuse folks of being confused when you don't even appear
to have grasped the most basic definitions?
[snip]
David BL
> Now, if we update the LastName, the FirstName, and the Phone number of an
> entry, it's still the same entry, because the ID field retains the same
> value.
That's rather fuzzy until you say what you mean by 'entry'. Does it
mean value or variable? It cannot be referring to the tuple because
after the update it is a different tuple. Evidently 'entry' means
'variable' in the above. Substitute and you will see it makes sense.
> So, Brian's claim that deleting a value and inserting a new one is somehow
> really different from updating an existing value works in this model, if
> you regard ID as somehow "special", and not "part of the value recorded in
> the table".
For a given variable that holds a set of values you can indeed delete
a value or insert a value but you cannot update a value. You can only
update a variable.
rob...@gmail.com
...
> If instances of a key do not
> permanently identify things in the Universe, then transition constraints
> that join the relation values before and after an assignment to a relvar
> would not be sound. For example, suppose that you have a relation, {L, F,
> Stat} Key {L, F}that starts out with the following value:
>
> {L:Jones, F:Mary, Stat:Married}}
>
> and is then assigned the value,
>
> {{L:Smith, F:Mary, Stat:Divorced},
> {L:Jones, F:Mary, Stat:Married}}
>
> Should the assignment be rejected on that basis? Or is it possible that
> Mary Jones' maiden name is Smith, and that she became Divorced while at the
> same time the Mary Smith who was Single Married Robert Jones? It might also
> be that the Mary Smith who was Single moved out of the area while a
> different Mary Smith, who is Divorced, moved in.
A very good example indeed. However let’s imagine the following
conversation:
-“Do you know that Mary Smith got divorced?”
-“It must be a mistake, Mary Smith has never been married!”
-“Well, actually it is about Mary Jones that married and became Mary
Smith. The Mary Smith you know changed his name to Jones before that.”
As can be seen an ambiguous statement can be confusing for humans as
well (and requiring supplementary explanations). A database (loosely
speaking) is just a collection of statements. The system is not
supposed to know (or guess) the truth, but just to check the new
statements for consistency with the existing statements in the
database.
So in specified context a statement “Mary Smith got divorced” should
be rejected due the transitional constraint. A correct sequence of
statements is “Mary Smith changed his name to Jones”, “Mary Jones got
married and changed his name to Smith” and “Mary Smith got divorced”.
This is unambiguous for both database system and a human as well.
...
> If, on the other hand, insert,
> update and delete are not shortcuts, but rather primitive operations, then
> the introduction of the autogenerated IDs is not necessary. All that is
> needed is to correlate the tuples in a relation that are the target of an
> update with those in its successor. For example, the update
>
> {{L:Smith, F:Mary, Stat:Single, L':Smith, F':Mary, Stat':Divorced}}
>
> should clearly be rejected, but the update,
>
> {{L:Smith, F:Mary, Stat:Single, L':Jones, F':Mary, Stat':Married},
> {L:Jones, F:Mary, Stat:Married, L':Smith, F':Mary, Stat':Divorced}}
>
The relation variable changes its state from one set of statements to
a different one. No one should care which tuple was replaced by which
tuple. It is just a new set of statements consistent with the current
state of the database. Just as in mathematics if you have a set A =
{1,2,3} you cannot update 3 to become 4. You can however update the
value of A by an assignment A = {1,2,4}. The effect might be the same
but it is something different.
Now let’s consider a foreign key DEP {L, F} references EMP {L, F} and
the following states:
EMP: {{L:Smith, F:Mary, Stat:Single}}
DEP: {{L:Smith, F:Mary, Dept:HR}}
An assignment
EMP := {{L:Jones, F:Mary, Stat:Single}}
will fail due the foreign key constraint. However the assignment can
be rewritten as
EMP := {{L:Jones, F:Mary, Stat:Single}}, DEP := {{L:Jones, F:Mary,
Dept:HR}}
A foreign key is not a tuple reference. It is just a prerequisite that
the employee relation should contain a statement about Mary Jones
before we can enter a statement about her in departments. This is a
completely different thing from a department object that has a pointer
to an employee object. And requires a different way of thinking about.
Another example:
Employees := {{L:Jones, F:Mary, Stat:Single}}, Departments := { }
is also a perfectly valid assignment. We cannot tell however if Mary
Smith has been fired and Mary Jones was hired without being assigned
to a department yet. Or maybe Mary Smith changed his name to Jones but
she is not working for HR anymore and not yet assigned to another
department.
However we cannot blame the relational model for that. It is the
limited set of statements existing in the database that is limiting
our knowledge. But that was a design decision. We can choose different
set of keys, we can make more statements, or make our statements more
precise (by adding more attributes) if we need.
Now back to the surrogate keys.
Surrogate keys give as for free stability and uniqueness. However is
that it all what we need?
Suppose:
I tell you my name is Mary Jones.
I tell you my name is Mary Jones.
This is the same statement stated twice.
I tell you for the first time that my name is Mary Jones.
I tell you for the seccond time that my name is Mary Jones.
Those are definitely different statements. If we really need the
“first time”, “second time” information then it is ok. If we just need
“name is Mary Jones” then we’re in trouble. (Perhaps someone can
reformulate that in terms of predicates and variables, but I hope I
was clear).
Regarding stability it is more a performance (hence physical) problem.
If we “change” a value in one tuple we should “change” that value in
all related tuples in the same assignment to prevent constraint
violations.
Just my two cents. Hopefully someone with a deeper knowledge of
relational theory will correct me if I made a mistake or used an
excessively loose language.
Brian Selzer
> <rob...@gmail.com> wrote in message
> news:43a5ac21-08f1-4922...@r13g2000vbr.googlegroups.com...
I disagree. It is my contention that each tuple in a database--regardless
of whether it is in a base or a derived relation--maps to some thing in the
Universe of Discourse but no two tuples in the same database map to exactly
the same thing. When presented with a proposed database, there must be some
mechanism to correlate the things that each tuple in the current database
maps to with the things that each tuple in the proposed database maps to.
Keys are not sufficient because their scope is /each/ possible relation for
a relation schema instead of /every/ possible relation. In the example,
this is evidenced by the fact that the Mary Smith at one database may not be
the Mary Smith at its successor. As a consequence, a join of a relation in
the proposed database to its corresponding relation in the current database
would compare the appearances of two different people instead of two
appearances of the same person. It is only when every instance of every key
permanently identifies some thing in the Universe that such a join can be
considered valid, and that severely limits the expressiveness of the model.
If, on the other hand, we simply make use of the information supplied by the
user when they issue deletes, inserts and updates, then there is no need for
the join because the requisite correlation of old to new values is in fact
supplied.
<snip>
rob...@gmail.com
> > The relation variable changes its state from one set of statements to
> > a different one. No one should care which tuple was replaced by which
> > tuple. It is just a new set of statements consistent with the current
> > state of the database.
>
> I disagree. It is my contention that each tuple in a database--regardless
> of whether it is in a base or a derived relation--maps to some thing in the
> Universe of Discourse but no two tuples in the same database map to exactly
> the same thing. When presented with a proposed database, there must be some
> mechanism to correlate the things that each tuple in the current database
> maps to with the things that each tuple in the proposed database maps to.
> Keys are not sufficient because their scope is /each/ possible relation for
> a relation schema instead of /every/ possible relation. In the example,
> this is evidenced by the fact that the Mary Smith at one database may not be
> the Mary Smith at its successor. As a consequence, a join of a relation in
> the proposed database to its corresponding relation in the current database
> would compare the appearances of two different people instead of two
> appearances of the same person. It is only when every instance of every key
> permanently identifies some thing in the Universe that such a join can be
> considered valid, and that severely limits the expressiveness of the model.
> If, on the other hand, we simply make use of the information supplied by the
> user when they issue deletes, inserts and updates, then there is no need for
> the join because the requisite correlation of old to new values is in fact
> supplied.
Well, I think there are two different views (or understandings) of the
same problem.
When looking at the Employees and the Department relations one might
see a collection of objects (or entities) and a collection of
references to employees working in one department. Hence the need to
uniquely identify an entity.
However when looking at the Employees and Departments I see two sets
of statements, one about employees and one about employees that are
known to work in a certain department. The foreign key tells me that
the Mary Smith in the statement “Mary Smith works in HR department” is
the same as Mary Smith in the statement “Mary Smith is single”. To me
they are just statements and not objects, that’s why they don’t need
identities.
Continuing with examples, if we fire Mary Smith without removing the
information from Departments, and add in the same time a new Mary
Smith in the Employee, the update will be perfectly valid. The
constraint happen to be satisfied so there is no reason to reject the
change.
Now, while the database is consistent, the information does not
reflect reality. But it is not system’s fault, it is ours because we
made a mistake by not deleting the information from Departments. A
database reflects what was told. It just records the statements we
made if they conform to some constraints. It is our job to make sure
that we tell it the truth.
The system doesn’t care which Mary Smith we are talking about since
all the constraints are fulfilled. And it doesn’t care that there was
sometime another Mary Smith that worked for the company. If such kind
of information is required, then it is the designer job to model the
database in such way.
Bob Badour
> On Jun 3, 4:06 pm, "Brian Selzer" <br...@selzer-software.com> wrote:
>
>>>The relation variable changes its state from one set of statements to
>>>a different one. No one should care which tuple was replaced by which
>>>tuple. It is just a new set of statements consistent with the current
>>>state of the database.
>>
>>I disagree.
BFD.
<boring shit snipped>
Bernard Peek
<688c5bfa-d377-40c4...@k8g2000yqn.googlegroups.com>,
rob...@gmail.com writes
>
>Now, while the database is consistent, the information does not
>reflect reality. But it is not system’s fault, it is ours because we
>made a mistake by not deleting the information from Departments. A
>database reflects what was told. It just records the statements we
>made if they conform to some constraints. It is our job to make sure
>that we tell it the truth.
As database designers we have an additional responsibility. We are
supposed to build enough intelligence into the system that it can detect
at least some of the lies people might tell it. We understand that the
name "Mary Smith" is not sufficient to identify an individual so we
design systems that can cope with holding data on two people with the
same name.
--
Bernard Peek
Walter Mitty
"David BL" <dav...@iinet.net.au> wrote in message
news:2196de54-5f7f-49d8...@q37g2000vbi.googlegroups.com...
> On May 29, 8:54 pm, "Walter Mitty" <wami...@verizon.net> wrote:
>
>
>> Now, if we update the LastName, the FirstName, and the Phone number of an
>> entry, it's still the same entry, because the ID field retains the same
>> value.
>
> That's rather fuzzy until you say what you mean by 'entry'. Does it
> mean value or variable? It cannot be referring to the tuple because
> after the update it is a different tuple. Evidently 'entry' means
> 'variable' in the above. Substitute and you will see it makes sense.
>
Walter Mitty
> I disagree. It is my contention that each tuple in a database--regardless
> of whether it is in a base or a derived relation--maps to some thing in
> the Universe of Discourse but no two tuples in the same database map to
> exactly the same thing. When presented with a proposed database, there
> must be some mechanism to correlate the things that each tuple in the
> current database maps to with the things that each tuple in the proposed
> database maps to.
Why?
Brian Selzer
This is an example of the noise I referred to in a previous post. The
problem exhibited here occurs for /any/ key in which instances of it do not
permanently identify something in the Universe. This even includes keys
that are supposedly stable, such as part numbers or GL account numbers. If
it is possible to issue an update that targets one or more prime attributes,
then a join of the relations before and after the update can't be relied
upon for defining transition constraints.
>
>
> --
> Bernard Peek
paul c
...
> Any instruction to update the database state is imperative by
> definition. What else do you think imperative means?
When it comes to people who prefer imperative programs because they can
see no other way, it ends up meaning religious.
In order to truly follow their chosen faith, they really need to ask
themselves what is the advantage of some definition or other as well as
ask what complications it introduces. Date calls the lack of
assignment in functional languages a "cheat ... in effect they perform
come kind of assignment ... even though assignment as such isn't part of
the logic or functional programming style". I'm not saying at all that
Date is a religious nut, but some of his followers are. I would defend
his right to think that given his aim, even though I think the massive
integration of concepts he's after is beyond what most people can
assimilate, nor do they need to. Personally, I have no problem with a
logical view that assumes all relations are persistent and that some can
be referred to by name.
The "dictatorship of reason" as Ralston Saul puts it, has become its own
parody, like most religion which exists to solve the problems it has
created.
Assigning a name in the context of a program is not the same thing as
assigning to a pointer to a value. One trouble many imperative
programmers have is that they don't have a useful notion of program in
the first place, in fact the arrangement of statements they will call a
program practically always stands for several dozen programs. The
particular arrangement is nearly always dictated by the devices they
prefer, such as repetitive assignment and not by the logical
application's purpose but SICP says this better than I can.
> [snip]
>
>> Just because db values vary
>> doesn't mean one must implement variables and assignment.
>
> Values don't vary by definition. Only variables can vary.
>
> How can you accuse folks of being confused when you don't even appear
> to have grasped the most basic definitions?
I can't claim all the credit, the seemingly endless supply of pedants
here helps enormouslly.
David BL
> "David BL" <davi...@iinet.net.au> wrote in message
In the RM there is no such thing as a row that contains a value. Paul
was right when he suggested you were thinking about tables not
relations.
paul c
... I would defend
> his right to think that given his aim,...
To try to be clear, I meant his logical right, I didn't mean some USA
constitutional caveat. It's just the aim that I think is too ambitious,
given the typical human lifespan. All credit to Date for being so
determined and for trying to be precise when so many in the field, like
the remaining SQL originator, are rather cavalier and hand-wavy about
what they missed and for trying to take it so far. What he's done is
full of insight that is useful to everybody, but it is still up to us
what we choose to implement.
David BL
>
> When it comes to people who prefer imperative programs because they can
> see no other way, it ends up meaning religious.
If someone (even very intelligent) /always/ prefers an imperative
style it suggests both ignorance and a closed mind. I guess you could
call that attitude "religious" - although I wonder whether it's better
to reserve that word for blind faith in something unknown rather than
what's known.
I find the different styles of writing programs fascinating, and wish
I understood better when and why one approach is simpler than
another. I certainly don't believe that an imperative style is always
inferior. Sometimes an ordered sequence of state changes is exactly
what is appropriate - just like when following a cooking recipe. In
some situations expressing a sequence of steps indirectly using
logical declarations as constraints can increase complexity.
I also don't believe that a program should restrict itself to one
style. An ideal language is simple yet supports a wide range of
styles. Unfortunately these appear to be conflicting aims.
Brian Selzer
"Walter Mitty" <wam...@verizon.net> wrote in message
news:RzwVl.15$tr5...@nwrddc02.gnilink.net...
Because the set of symbols that is an instance of a key can map to different
things in the universe at different points in time, and more importantly,
different sets of symbols that are instances of a key can map to the same
thing at different points in time. In the example I provided, the set of
symbols, {"Mary", "Smith"}, that maps to the particular person that is
Single before the update cannot map to the same person after the update
because the transition constraint prohibits Single people from becoming
Divorced, yet after one valid update, the set of symbols, {"Mary", "Jones"},
maps to the person that the set of symbols, {"Mary", "Smith"}, mapped to
before the update. So obviously, simply joining the relation before to the
relation after an update could yield an incorrect result, thereby permitting
garbage to get in or preventing valid information from being recorded.
rob...@gmail.com
> This is an example of the noise I referred to in a previous post. The
> problem exhibited here occurs for /any/ key in which instances of it do not
> permanently identify something in the Universe. This even includes keys
> that are supposedly stable, such as part numbers or GL account numbers. If
> it is possible to issue an update that targets one or more prime attributes,
> then a join of the relations before and after the update can't be relied
> upon for defining transition constraints.
I see your point, but the problem has a different and subtle cause.
You change the value of both the key and dependent attributes in the
same time. So two logical operations are combined in a single
relational assignment. This two-in-one approach hides some information
from the system. The problem can easily be avoided by performing the
operation in two steps (as shown earlier).
But let’s consider your suggestion and use a surrogate key. Now
suppose the person with id #1 with state “single” gets married and
divorced in the same weekend. The operator thinks that it makes no
sense to issue two update operations so he changes the state in
“divorced”. The operation will obviously fail. This is the same
logical error, a two-in-one operation.
In the absence of the transition constraint we can make arbitrary
relational assignments. However when transition constraints are
involved, not only the state but also the state change itself is
information. By combining several logical operations in one relational
assignment we hide the state change information from the system. As a
consequence we should expect transition constraint failures.
Still I can see a case where using surrogate keys might be useful
(when using transition constraints).
Suppose we change the key to {L, F, State} (a silly example since the
state is clearly a dependent attribute).
Now the transition constraint for changing the state from “single” to
“divorced” will never be fired. Since state is part of the key the
system has no way to join the old and new values. More over, if we put
a constraint such as “initial value of the state must be single” then
we will not be able to set the state to a different value.
It is hard to find a reason to put a transition constraint on
something (the primary key) that is not supposed to change (except by
accident), but probably there are some legitimate cases.
In such a particular case, when we need a transition constraint on an
attribute that is part of all the keys, there will be no other
solution than adding a surrogate key (adding and not replacing
existing keys).
rob...@gmail.com
> On Jun 3, 7:04 pm, "Brian Selzer" <br...@selzer-software.com> wrote:
>
> > This is an example of the noise I referred to in a previous post. The
> > problem exhibited here occurs for /any/ key in which instances of it do not
> > permanently identify something in the Universe. This even includes keys
> > that are supposedly stable, such as part numbers or GL account numbers. If
> > it is possible to issue an update that targets one or more prime attributes,
> > then a join of the relations before and after the update can't be relied
> > upon for defining transition constraints.
>
> I see your point, but the problem has a different and subtle cause.
> You change the value of both the key and dependent attributes in the
> same time. So two logical operations are combined in a single
> relational assignment. This two-in-one approach hides some information
> from the system. The problem can easily be avoided by performing the
> operation in two steps (as shown earlier).
In the given example you changed both name and state, or it was a
different person with the same name. This kind of problems is easily
solvable by performing the operation in several steps.
However I have to admit that your point sustains in one case even if
you change only the primary key attribute.
If “Mary Smith, divorced” changes his name in “Mary Jones” (for
whatever reason not related to marital status), and “Mary Jones,
single” changes his name in “Mary Smith” (idem) then the constraint
fails even if there are no changes in their states.
Swapping two key values is solvable only by a multiple assignment and
an override of the transition constraint. This situation is very
unlikely, but we have to admit that there is non-zero probability.
Adding a surrogate key might help but I don’t think it should replace
the natural keys.
This shows that the transition constraints are different from state
constraints and a method for overriding them is sometime required
(another example is making corrections).
Brian Selzer
<rob...@gmail.com> wrote in message
news:6793e7cf-38d0-4792...@r33g2000yqn.googlegroups.com...
Indeed, there can be relation schemata in which the entire heading is the
only candidate key.
> If �Mary Smith, divorced� changes his name in �Mary Jones� (for
> whatever reason not related to marital status), and �Mary Jones,
> single� changes his name in �Mary Smith� (idem) then the constraint
> fails even if there are no changes in their states.
>
> Swapping two key values is solvable only by a multiple assignment and
> an override of the transition constraint. This situation is very
> unlikely, but we have to admit that there is non-zero probability.
> Adding a surrogate key might help but I don�t think it should replace
> the natural keys.
>
It need not. Simply adopt the paradigm in which delete, insert and update
are primitive operations and relational assignment is a shortcut for a
delete and an insert. As I've pointed out many times in past threads,
information is lost when an update is coerced into being an assignment.
That information is in fact exactly how each tuple in the current relation
differs from its corresponding tuple in the proposed relation--that is,
exactly which components are different, tuple by tuple. When a user issues
an update, he targets a specific set of tuples and states exactly which
components of each targeted tuple differ in the proposed relation and
exactly what those new components are. But to prepare for assignment, the
components from the current relation are projected away, and thus the
correspondence is lost. For example, in the following update, which I
borrowed from a previous post,
{{L:Smith, F:Mary, Stat:Single, L':Jones, F':Mary, Stat':Married},
{L:Jones, F:Mary, Stat:Married, L':Smith, F':Mary, Stat':Divorced}}
It can be determined precisely which components of each tuple in the current
relation are different from those of its corresponding tuple in the proposed
relation because that correspondence is explicitly stated. But in preparing
for assignment, the components from the current relation are projected away,
leaving
{{L':Jones, F':Mary, Stat':Married},
{L':Smith, F':Mary, Stat':Divorced}}
which upon renaming becomes
{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Divorced}}
In the above example, the update targeted the entire relation, but in
general, there would be additional steps required to transform an update
into an assignment. One of those involves a union of the set of tuples not
targeted by the update. Here more information is lost in that it cannot be
determined by simply comparing the current and proposed relations whether or
not a tuple was indeed the target of an update. For example, the update,
{{L:Smith, F:Mary, Stat:Single, L':Smith, F':Mary, Stat':Divorced}}
targets just the tuple,
{L:Smith, F:Mary, Stat:Single}
But project away the current components and rename,
{{L:Smith, F:Mary, Stat:Divorced}}
and then union in the set of tuples that weren't targeted,
{{L:Jones, F:Mary, Stat:Married}}
and the result is
{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Divorced}}
Clearly, from
{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Single}}
and
{{L:Jones, F:Mary, Stat:Married},
{L:Smith, F:Mary, Stat:Divorced}}
It can no longer be determined whether the tuple
{L:Jones, F:Mary, Stat:Married}
was the target of the update, as that distinction is lost as a result of the
union that is required to prepare for assignment.
It is clear, therefore, that the notions of Date and Darwen that a database
is a collection of relvars and that insert, update and delete are shortcuts
for relational assignment should be discarded, or perhaps relegated to being
just teaching devices.
<snip>
Brian Selzer
One shouldn't use a screwdriver to drive a nail, nor a hammer to drive a
screw. While it may be possible to do it, there is a significant risk of
screwing things up.
JOG
> I disagree. It is my contention that each tuple in a database--regardless
> of whether it is in a base or a derived relation--maps to some thing in the
> Universe of Discourse but no two tuples in the same database map to exactly
> the same thing. When presented with a proposed database, there must be some
> mechanism to correlate the things that each tuple in the current database
> maps to with the things that each tuple in the proposed database maps to.
> Keys are not sufficient because their scope is /each/ possible relation for
> a relation schema instead of /every/ possible relation.
> In the example, this is evidenced by the fact that the Mary Smith at one
> database may not be the Mary Smith at its successor.
> As a consequence, a join of a relation in
> the proposed database to its corresponding relation in the current database
> would compare the appearances of two different people instead of two
> appearances of the same person. It is only when every instance of every key
> permanently identifies some thing in the Universe that such a join can be
> considered valid, and that severely limits the expressiveness of the model.
> If, on the other hand, we simply make use of the information supplied by the
> user when they issue deletes, inserts and updates, then there is no need for
> the join because the requisite correlation of old to new values is in fact
> supplied.
>
> <snip>
Ahhh, the fun me and Brian used to have, arguing about this till we
were blue in the face. Why, it warms my cockles to see new people
taking over the mantle! For those posters, who continue to fight the
good fight, I always held that his examples were simply of flawed
schema designs (and excellent examples at that, but merely ones where
the DDL chosen was insufficient to model the world he was
considering). For instance, in the 'divorcee' example, I'm sure most
can see that a name is neither unique nor stable enough to identify a
person in real life, never mind within the RM. So its hardly a shock
when the database breaks down because of that bad key choice.
Anyhow here's an important question:
Q. What happens in Brian's model when that transition history it
relies on can't be determined?
Brian Selzer
"JOG" <j...@cs.nott.ac.uk> wrote in message
news:467475e3-b073-43a7...@b1g2000vbc.googlegroups.com...
This is another example of the noise I referred to earlier. Out of respect
for him and those fun arguments we had, I will sketch out another example
just to illustrate that the problems with Date and Darwen's notions are not
the result of poor key choice. Let's start with the example transition
constraint from TTM 3rd edition, page 220-221:
Here is an example ("no supplier's status must ever decrease"):
CONSTRAINT TRC1 IS_EMPTY
( ( ( S' { S#, STATUS } RENAME ( STATUS AS STATUS' ) )
JOIN
( S {S#, STATUS } ) )
WHERE STATUS' > STATUS ) ;
/Explanation:/ We introduce the convention that a primed relvar name,
such as S' in the example, is understood to refer to the corresponding
relvar as it was /prior to the update under consideration./ ....
Now, suppose that relvar S contains the following relation,
{S# := '123', STATUS := 30}
So, should the following update be rejected?
UPDATE S (S# := '456', STATUS := 15)
When coerced into being an assignment, S would contain the relation
{S# := '456', STATUS := 15}
and then under the convention, S' would contain
{S# := '123', STATUS := 30}
As a consequence, the constraint above returns TRUE because
(S' {S#, STATUS} RENAME (STATUS AS STATUS' ) )
JOIN (S {S#, STATUS})
is empty despite the fact that the status of the supplier whose S# was '123'
before the update and '456' after the update decreased. It should be
obvious from this that joining the relations before and after an assignment
to a relvar is not a sound means of defining transition constraints.
> Anyhow here's an important question:
> Q. What happens in Brian's model when that transition history it
> relies on can't be determined?
What transition history are you referring to? A transition is an assertion
that states, in the context of what has since the last update and up to now
been the case, exactly what is now different and how. There is no reliance
on history, since the context of both the current database, which states
what has since the last update and up to now been the case, and the
transition are the same.
JOG
At risk of repeating myself, S# has merely proven to be a bad key
again - it is clearly an unstable identifier for a supplier (just as
'name' was in the 'divorcee' example). This is just another flawed
schema, not a problem with the RM.
>
> > Anyhow here's an important question:
> > Q. What happens in Brian's model when that transition history it
> > relies on can't be determined?
>
> What transition history are you referring to? A transition is an assertion
> that states, in the context of what has since the last update and up to now
> been the case, exactly what is now different and how. There is no reliance
> on history, since the context of both the current database, which states
> what has since the last update and up to now been the case, and the
> transition are the same.
What would happen if noone knew that supplier 123 had become supplier
456 (i.e. there was no fact available representing that change, so
nothing that could be used in an update)?
You always pick examples where there is someone to ask in order that
some disparity in identification can be ameliorated, but there are
many situations where this information may simply not be available, or
even exist at all. It therefore simply has to be all about picking
good reliable keys.
rob...@gmail.com
> At risk of repeating myself, S# has merely proven to be a bad key
> again - it is clearly an unstable identifier for a supplier (just as
> 'name' was in the 'divorcee' example). This is just another flawed
> schema, not a problem with the RM.
I agree with you, in practice a key that has a probability of change
of 10^(-1000000) is far better than one which has 10^(-10). However in
theory we deal with a zero or non-zero probability. If we accept that
a key value can change (and the relational model accepts that – more
over, offers referential actions to help us deal with) then we should
accept that a transition constraint check could fail.
For state constraints such kind of problems are gracefully solved by
multiple assignments. But how they will be handled in case of
transition constraints? The theory would not be complete without that
specification. I really hope the next D book (or an article) will
clarify things.
Brian Selzer
> On Jun 5, 4:12 am, "Brian Selzer" <br...@selzer-software.com> wrote:
> > "JOG" <j...@cs.nott.ac.uk> wrote in message
> >
> > news:467475e3-b073-43a7...@b1g2000vbc.googlegroups.com...
<snip>
I didn't say it was a problem with the RM. I said it was a problem with
Date and Darwen's notions that a database is a collection of relvars and
that insert, update and delete are shortcuts for relational assignments.
You're underscoring my point, by the way, which is that adopting those
notions requires that every instance of every key be a permanent identifier
for something in the Universe of Discourse, thereby limiting the
expressiveness of the model.
> >
> > > Anyhow here's an important question:
> > > Q. What happens in Brian's model when that transition history it
> > > relies on can't be determined?
> >
> > What transition history are you referring to? A transition is an
> > assertion
> > that states, in the context of what has since the last update and up to
> > now
> > been the case, exactly what is now different and how. There is no
> > reliance
> > on history, since the context of both the current database, which states
> > what has since the last update and up to now been the case, and the
> > transition are the same.
>
> What would happen if noone knew that supplier 123 had become supplier
> 456 (i.e. there was no fact available representing that change, so
> nothing that could be used in an update)?
If the user didn't know that fact, then they wouldn't have issued an update
stating that fact. They would have attempted to issue an update targetting
supplier 456, and the update would of course have been rejected.
> You always pick examples where there is someone to ask in order that
> some disparity in identification can be ameliorated, but there are
> many situations where this information may simply not be available, or
> even exist at all. It therefore simply has to be all about picking
> good reliable keys.
If it is possible for a prime attribute to be the target of an update, then
one must assume that it will. Whenever it should not be possible, there
should be a transition constraint that prevents it. Of course, if one
adopts Date and Darwen's notions that a database is a collection of relvars
and that inserts, updates and deletes are shortcuts for relational
assignments, then it isn't possible to define let alone enforce such a
constraint.
Question: what if the entire heading is the key. Should updates be
prevented altogether for such relvars?
Kevin Kirkpatrick
> "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
>
> news:a5076075-7ae9-4ab1...@n21g2000vba.googlegroups.com...
>
>
>
>
>
> > On Jun 1, 2:27 pm, "Brian Selzer" <br...@selzer-software.com> wrote:
> > > "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
>
> > >news:895891d5-fe55-478d...@r13g2000vbr.googlegroups.com...
>
> > > > On Jun 1, 11:39 am, "Brian Selzer" <br...@selzer-software.com> wrote:
> > > > > "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
>
> > > > >news:670a228b-b752-47eb...@s31g2000vbp.googlegroups.com...
>
> > > > > > What is the poor HR person to do when Mary Smith returns from a
> > > > > > week
> > > > > > long Vegas trip and reports that she'd gotten married on Monday
> > > > > > and
> > > > > > divorced on Friday?
>
> > > > > That should be obvious: issue two updates in succession, one for
> > > > > each
> > > > > event.
>
> > > > Am I reading that right? You think the data entry person should
> > > > enter, as a fact to the database, "Mary Smith is currently Married",
> > > > at a time when Mary Smith is definitely not married?
>
> > > Yes, you're reading that right. The data entry person should enter, as a
> > > fact into the database, "Mary Smith got Married", at a time when it is
> > > true
> > > that Mary Smith got Married, and then "Mary Smith got Divorced" at a
> > > time
> > > when it is true that Mary Smith got Divorced.
>
> > Sorry, I misinterpretted the relation {L, F, Stat} to indicate
> > "The person with last name <L> and first name <F> is currently <Stat>"
>
> > It seems like you intended the relation to mean:
>
> > "The person with last name <L> and first name <F> got <Stat>"
>
> That's an interesting way to express the "meaning" of a relation, but the
> verb tenses are wrong.
>
> The "meaning" of the relation {L, F, Stat} is:
>
> "The person that has up to now had last name <L> and first name <F> has up
> to now been <Stat>."
>
> The "meaning" of an update {L, F, Stat, L', F', Stat'} is:
>
> "The person that had up to now had last name <L> and first name <F> and had
> up to now been <Stat> now has last name <L'> and first name <F'> and is
> <Stat'>."
>
Let me first address the excess vebiage surrounding the {L,F} key.
The fact that {L,F} has been chosen as the only key for the relation
at hand, coupled with the assumption of competent data modeling, means
we are operating in a UoD where {L,F} is the most reasonable key per
data modeling principles. How can this be? Perhaps this is a UoD
where all employees of a company are internally and forever refered to
by unique hired names (coerced into uniqueness by disambiguation
policies such a middle initial inclusion, if need be) rather than
current legal names. Or, more plausibly, the UoD may be a highschool
classmate database, where {L,F} refers to the names of each
individual's official high-school record, and either
1) the high-school in question had disambiguation policies in place
for all names; or
2) it's a simple historic fact that no duplicate names exist for the
classes being tracked.
Having gotten that off my chest, let's jump into the unusual world
fostered by "transition constraints". A data entry person will adhere
to the transition constraints policy, and following the data modeler's
advice, do 2 updates to set Mary Smith's marital status correctly.
Let's assume a 1 minute lag between updates, and look at what the
updates mean (per Brian's definitions above).
At 8:00 a.m., according to the meaning of an update, the data entry
person should assert:
"The person that had up to 8:00 a.m. had last name Smith and first
name Mary and had up to 8:00 a.m. been Single now has last name Smith
and first name Mary and is Married."
She should submit this fact to the database, then reload her data
entry form in order to assert at 8:01 a.m.:
"The person that had up to 8:01 a.m. had last name Smith and first
name Mary and had up to 8:01 a.m. been Married now has last name Smith
and first name Mary and is Divorced."
Do I have the intended meaning of both of these updates correct? If
so, would you agree that both assertions are falsehoods about the real
world?
> In the same way, the "meaning" of a delete {L, F, Stat} is:
>
> "The person that had up to now had last name <L> and first name <F> and had
> up to now been <Stat> no longer exists."
>
> and the "meaning" of an insert {L', F', Stat'} is:
>
> "There exists a person that has last name <L'> and first name <F'> and is
> <Stat'>."
>
> Just one point I need to make about these "meanings:" formally, in a fixed
> Universe, existence must be in fact a predicate, not a quantifier. To have
> existed is to have become actual; to exist is to still actually be.
> Distinguishing existence from being permits the quantifier, "there is" to
> range over all kinds of things, including things that have a past, present
> or possible locus in time as well as things that are independent of time.
>
> > But I'm still confused... at the time it was true that "Mary Smith got
> > Divorced", and the database tuple changed from {"Smith", "Mary",
> > "Married"} to {"Smith", "Mary", "Divorced"}, doesn't that imply that
> > it was no longer true that "Mary Smith got Married"?
>
> "Mary Smith got Divorced" implies that "Mary Smith got Married" as a
> consequence of the transition constraint that prohibits Single people from
> getting Divorced.
>
>
It implies no such thing. Perhaps {Mary ,Smith , Divorced} was
inserted. But you do make the point that transition constraints allow
for incorrect inferences when meaningful observations from the UoD are
not exposed in the data model. If the UoD is concerned about whether
Mary Smith got Married while being tracked by the database, then the
data model ought to expose a history of marital statuses, period. And
it goes without saying that in such a revised model, the marital-
status "transition constraint" is unnecessary (simple rules about
allowable chronological histories of marital statuses are trivially
enforced).
>
> > > The database has to be
> > > informed that a marriage occurred and then that a divorce occurred,
> > > since it
> > > is not just states that are being constrained, but changes of state.
>
> > "The database has to be informed that a marriage occurred"
>
> > I thought the UoD was limited to a person's current marital status
> > (hence the single relation {L, F, Stat}). Is the fact that "a
> > marriage occured" part of the UoD? If so, how is this fact
> > represented in the data model?- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
I must say, given the freedom to pick any example to illuminate the
value of "transition constraints", it's telling that you have chosen
one which trivially leads to both:
1) Requirements that people assert falsehoods in order for the
database to function
2) False inferences.
I think I now better understand the "mystic" charges others have
leveraged towards Brian's approach to database theory. His approach
would seem to assume a mystical "shadow world", wherein a virtual
'Mary Smith' lives as a 'Single' person in the database, and remains
'Single' right up until she gets 'Married' by the data entry person at
8:00 a.m. (and subsequently "Divorced" by the same data entry person a
few seconds / keystrokes later). The mentality treats the 1's and 0's
stored in the humming box not as simple, structured recordings of
observations of the real world, but as real entities that themselves
need to obey the laws of our real world directly.
JOG
> "JOG" <j...@cs.nott.ac.uk> wrote in message
> > At risk of repeating myself, S# has merely proven to be a bad key
> > again - it is clearly an unstable identifier for a supplier (just as
> > 'name' was in the 'divorcee' example). This is just another flawed
> > schema, not a problem with the RM.
>
> I didn't say it was a problem with the RM. I said it was a problem with
> Date and Darwen's notions that a database is a collection of relvars and
> that insert, update and delete are shortcuts for relational assignments.
>
> You're underscoring my point, by the way, which is that adopting those
> notions requires that every instance of every key be a permanent identifier
> for something in the Universe of Discourse
Yup, if you talk about something in a proposition use a stable
identifier for it. It's not just desirable, but essential. Use a nice
stable EMP# not a person's name. It is about integrity not
'expressiveness'.
> , thereby limiting the
> expressiveness of the model.
>
> > > > Anyhow here's an important question:
> > > > Q. What happens in Brian's model when that transition history it
> > > > relies on can't be determined?
>
> > > What transition history are you referring to? A transition is an
> > > assertion
> > > that states, in the context of what has since the last update and up to
> > > now
> > > been the case, exactly what is now different and how. There is no
> > > reliance
> > > on history, since the context of both the current database, which states
> > > what has since the last update and up to now been the case, and the
> > > transition are the same.
>
> > What would happen if noone knew that supplier 123 had become supplier
> > 456 (i.e. there was no fact available representing that change, so
> > nothing that could be used in an update)?
>
> If the user didn't know that fact, then they wouldn't have issued an update
> stating that fact. They would have attempted to issue an update targetting
> supplier 456, and the update would of course have been rejected.
This is the crucks of the matter. You'd then be stuck with a fact in
your database that is false. Why the integrity failure? Because a
poor, unstable key was used. There is no solving this with rowid's, or
any other kind of shims.
>
> > You always pick examples where there is someone to ask in order that
> > some disparity in identification can be ameliorated, but there are
> > many situations where this information may simply not be available, or
> > even exist at all. It therefore simply has to be all about picking
> > good reliable keys.
>
> If it is possible for a prime attribute to be the target of an update, then
> one must assume that it will. Whenever it should not be possible, there
> should be a transition constraint that prevents it. Of course, if one
> adopts Date and Darwen's notions that a database is a collection of relvars
> and that inserts, updates and deletes are shortcuts for relational
> assignments, then it isn't possible to define let alone enforce such a
> constraint.
>
> Question: what if the entire heading is the key. Should updates be
> prevented altogether for such relvars?
Aye.
JOG
> "JOG" <j...@cs.nott.ac.uk> wrote in message
> Question: what if the entire heading is the key. Should updates be
> prevented altogether for such relvars?
Misread - of course updates are fine.
Brian Selzer
"Kevin Kirkpatrick" <kvnkr...@gmail.com> wrote in message
news:1aa37bb4-cf9a-47b8...@o20g2000vbh.googlegroups.com...
<snip>
> Let me first address the excess vebiage surrounding the {L,F} key.
> The fact that {L,F} has been chosen as the only key for the relation
> at hand, coupled with the assumption of competent data modeling, means
> we are operating in a UoD where {L,F} is the most reasonable key per
> data modeling principles. How can this be? Perhaps this is a UoD
> where all employees of a company are internally and forever refered to
> by unique hired names (coerced into uniqueness by disambiguation
> policies such a middle initial inclusion, if need be) rather than
> current legal names. Or, more plausibly, the UoD may be a highschool
> classmate database, where {L,F} refers to the names of each
> individual's official high-school record, and either
> 1) the high-school in question had disambiguation policies in place
> for all names; or
> 2) it's a simple historic fact that no duplicate names exist for the
> classes being tracked.
Noise and more noise. Complain all you want about the choice of key. The
fact remains that no transition constraint can be effectively written or
enforced whenever any prime attribute can be the target of an update.
Moreover no transition constraint that prohibits updates to prime attributes
can be effectively written or enforced under Date and Darwen's
databases-as-collections-of-relvars paradigm because there's no way to
determine whether a particular assignment was the result of a delete and an
insert or an update. There are a myriad of examples I could have used but
they would have required a lot more explanation or expertise in subjects
that are not germane to database theory, furthermore, it just so happens
that Date in /Introduction/ uses a similar example when introducing the
concept of transition constraints.
>Having gotten that off my chest, let's jump into the unusual world
>fostered by "transition constraints". A data entry person will adhere
>to the transition constraints policy, and following the data modeler's
>advice, do 2 updates to set Mary Smith's marital status correctly.
>Let's assume a 1 minute lag between updates, and look at what the
>updates mean (per Brian's definitions above).
>
>At 8:00 a.m., according to the meaning of an update, the data entry
>person should assert:
>
>"The person that had up to 8:00 a.m. had last name Smith and first
>name Mary and had up to 8:00 a.m. been Single now has last name Smith
>and first name Mary and is Married."
>
>She should submit this fact to the database, then reload her data
>entry form in order to assert at 8:01 a.m.:
>
>"The person that had up to 8:01 a.m. had last name Smith and first
>name Mary and had up to 8:01 a.m. been Married now has last name Smith
>and first name Mary and is Divorced."
>
>Do I have the intended meaning of both of these updates correct? If
>so, would you agree that both assertions are falsehoods about the real
>world?
>
I see what you're driving at, but at the time immediately prior to the first
assertion, what the database asserted about the real world was false and had
been false for about a week. As a consequence every possible update would in
fact be a falsehood about the real world, as would the result of many
database queries during that time.
I think you're wrong: since it is common knowledge that every person starts
out single and since single people can't get divorced, then only married
people can get divorced, and since only married people can get divorced,
then obviously, everyone who is divorced must have at some point gotten
married--even if that fact isn't recorded in the database.
<snipped false conclusion drawn from false premise>
> If the UoD is concerned about whether
> Mary Smith got Married while being tracked by the database, then the
> data model ought to expose a history of marital statuses, period. And
> it goes without saying that in such a revised model, the marital-
> status "transition constraint" is unnecessary (simple rules about
> allowable chronological histories of marital statuses are trivially
> enforced).
First of all, the UoD is not sentient, so it cannot be concerned about
anything. Second, the point of the transition constraint is to keep garbage
out, but storing the histories of marital statuses would clutter up the
database with irrelevancies, which are in my opinion just another kind of
garbage. Third, even if there were a reasonable way to eliminate the need
for the marital-status transition constraint, in no way does that prove that
there is never any need for transition constraints. Even Date and Darwen,
as evidenced by their "RM Very Strong Suggestion 4," acknowledge the need
for transition constraints.
<snip>
> I must say, given the freedom to pick any example to illuminate the
> value of "transition constraints", it's telling that you have chosen
> one which trivially leads to both:
> 1) Requirements that people assert falsehoods in order for the
> database to function
As indicated above, that would be required even without the transition
constraint.
> 2) False inferences.
Also debunked above.
<snipped inane ad-hominem attack>
Brian Selzer
> On Jun 5, 2:10 pm, "Brian Selzer" <br...@selzer-software.com> wrote:
>> "JOG" <j...@cs.nott.ac.uk> wrote in message
>> <snip>
>> > At risk of repeating myself, S# has merely proven to be a bad key
>> > again - it is clearly an unstable identifier for a supplier (just as
>> > 'name' was in the 'divorcee' example). This is just another flawed
>> > schema, not a problem with the RM.
>>
>> I didn't say it was a problem with the RM. I said it was a problem with
>> Date and Darwen's notions that a database is a collection of relvars and
>> that insert, update and delete are shortcuts for relational assignments.
>>
>> You're underscoring my point, by the way, which is that adopting those
>> notions requires that every instance of every key be a permanent
>> identifier
>> for something in the Universe of Discourse
>
> Yup, if you talk about something in a proposition use a stable
> identifier for it. It's not just desirable, but essential. Use a nice
> stable EMP# not a person's name. It is about integrity not
> 'expressiveness'.
>
It is not essential. Language terms can denote different things at
different times. "The President of the United States" is Barack Hussein
Obama now, but was George Walker Bush just five months ago. And by the way,
an EMP# can change and may indeed be required to change if payroll
processing is outsourced. That would make EMP# even less stable than a
person's name, since it is usually only women who are marrying or divorcing
that change their names, whereas even the men's EMP# might have to change.
The stability of EMP# could be even more severely affected if the payroll
outsourcing is done piecemeal, such as the employees for one plant or
warehouse at a time.
It's not so much an integrity failure. Issuing a delete or update that
targets a nonexistent tuple is a different kind of nonsense than issuing a
delete, update or insert that violates a constraint. I would call the
constraint violation an integrity failure, but I'm not sure what name fits
the other kind of nonsense.
This is not key stability problem--it's not even a database problem: it's a
communication problem. If the direct deposit of your paycheck into your
bank was delayed because the internet was down, but the automatic bill-pay
of your mortgage and equity line occurred on time because they're from the
same bank, then you'll be charged bounced-check fees for your mortgage and
equity line, and late payment fees for your mortgage and equity line, and
because you were late on your payments your credit rating will be hosed and
your other creditors may as a consequence increase your interest rates--all
due to a communication problem.
>>
>> > You always pick examples where there is someone to ask in order that
>> > some disparity in identification can be ameliorated, but there are
>> > many situations where this information may simply not be available, or
>> > even exist at all. It therefore simply has to be all about picking
>> > good reliable keys.
>>
>> If it is possible for a prime attribute to be the target of an update,
>> then
>> one must assume that it will. Whenever it should not be possible, there
>> should be a transition constraint that prevents it. Of course, if one
>> adopts Date and Darwen's notions that a database is a collection of
>> relvars
>> and that inserts, updates and deletes are shortcuts for relational
>> assignments, then it isn't possible to define let alone enforce such a
>> constraint.
>>
>> Question: what if the entire heading is the key. Should updates be
>> prevented altogether for such relvars?
>
> Aye.
I think that's a severely limited and limiting point of view.
Brian Selzer
Oops, I missed your last post.
Brian Selzer
Suppose that you have a relvar for bins in warehouses. In this example,
they are in fact plastic bins that fit on shelves in warehouses. In the
interest of brevity, let's assume that the bins are all the same size and
that all inventory is stored in bins. Here's some necessary additional
info: there are 3 warehouses, each warehouse has between 24 and 30 rows of
shelves, each row can have between 1 and 5 shelves and each shelf can house
perhaps 20 plastic bins. Labels with extra large numerals are affixed to
the bins so that the pickers (the employees that pull inventory to satisfy
orders and put away inventory after it has been received and inspected) can
rapidly locate them.
For each bin there is a tuple in the current value of the relvar,
Bins {Warehouse, Row, Shelf, Bin}
(Again for brevity, I'm leaving out the specifications of the Warehouses,
Rows, and Shelves relvars, which should in fact be obvious, as well as the
foreign key heirarchy, as it should also be obvious. Let's assume for now
that the foreign keys defined should prevent inserting a nonexistent
{Warehouse, Row, Shelf} combination.)
The entire heading of Bins is its only key.
Now suppose that there a business rule that states that bins can't leave the
building--that is, the Warehouse of a bin can never change. This doesn't
mean that inventory can't be moved between warehouses, but for accounting
purposes, such a move requires inventory transfer transactions, as well as
the Bill of Lading and other documentation required by law for transporting
material by truck, etc. The business rule is designed to prevent bypassing
those steps.
But the fact that bins aren't allowed to be moved between warehouses doesn't
mean that bins can't be moved /within/ a warehouse. In fact, the efficiency
of the pickers can be increased by relocating bins to reduce the average
number of rows that must be visited to satisfy an order or by reducing the
number of times stairs on wheels need to be climbed to access inventory.
(Moving an entire bin is best because it reduces handling of the products in
the bin and is less labor intensive.)
An update can target just one bin or a set of bins. For example, in
Warehouse 2 all of the bins on the top (fifth) shelf of row 9 could be moved
to the empty second shelf of row 12, thus eliminating the need to climb up
to access that inventory:
UPDATE Bins
WHERE ( Warehouse = 2 AND Row = 9 AND Shelf = 5 )
( Row := 12, Shelf := 2 )
A move of a bin might require relabeling. For example, there might already
be a bin 14 on shelf 3 of row 22 in warehouse 3, so moving bin 14 from shelf
5 of row 20 to shelf 3 of row 22 would require an unused bin number to be
chosen and a label to be printed. So assuming that there isn't already a
bin 18 on shelf 3 of row 22:
UPDATE Bins
WHERE ( Warehouse = 3 AND Row = 20
AND Shelf = 5 AND Bin = 14 )
( Row := 22, Shelf := 3, Bin := 18 )
Back to the business rule. If an update is just a shortcut for a relational
assignment, then how can the rule be implemented? In Oracle, I would use a
row-level trigger; in Sql Server I would use an IDENTITY column and a
set-based trigger, since row-level triggers are not supported. But in
theory, using just the before and after values of the Bins relvar, how can
one declare the transition constraint?
vadi...@gmail.com
> In another thread, I suggested that prefixing every tuple with an
> auto-generated field (column) called ID amounted to reference by location
> rather than reference by content. ...
Yes, this is quite a natural question to wonder. If table columns are
labeled by column names, why rows can't be labeled by some sort of
IDs. If table header can be viewed as a hidden row, then why can't one
introduce a hidden ID column? A while ago there was a discussion
thread here on c.d.t where someone (I believe "VC") convincingly
argued that there are fundamental obstacles for such a construction.
The discussion was in the RL context, and my latest understanding of
the issue is documented on page 11 of
http://arxiv.org/abs/0902.3532
paul c
...
> IDs. If table header can be viewed as a hidden row, then why can't one
Vadim, I would object to that argument on the grounds that the header is
not hidden, otherwise projection would violate the Information
Principle. Another reason it isn't hidden is that it is in fact derived
from tuples that have triples that have all the attribute names and
types that a so-called header has.
Personally I don't object to hidden ID 'columns' as long as the algebra
can't refer to them, if it did then they wouldn't really be hidden. Nor
do I object to generated ID 'columns' as long as views can reference
them and as long as views can have a generated ID even when the
referenced base 'tables' don't. I wouldn't prescribe any particular
content for generated ID's either, as far as an algebra is concerned, it
should make no difference whether they are physical addresses or
sequence numbers of some sort. If users choose to interpret the ID
content in some way that the algebra doesn't depend on, that's okay by me.
JOG
> "JOG" <j...@cs.nott.ac.uk> wrote in message
> > On Jun 5, 2:10 pm, "Brian Selzer" <br...@selzer-software.com> wrote:
> >> "JOG" <j...@cs.nott.ac.uk> wrote in message
> >> <snip>
> >> > At risk of repeating myself, S# has merely proven to be a bad key
> >> > again - it is clearly an unstable identifier for a supplier (just as
> >> > 'name' was in the 'divorcee' example). This is just another flawed
> >> > schema, not a problem with the RM.
>
> >> I didn't say it was a problem with the RM. I said it was a problem with
> >> Date and Darwen's notions that a database is a collection of relvars and
> >> that insert, update and delete are shortcuts for relational assignments.
>
> >> You're underscoring my point, by the way, which is that adopting those
> >> notions requires that every instance of every key be a permanent
> >> identifier
> >> for something in the Universe of Discourse
>
> > Yup, if you talk about something in a proposition use a stable
> > identifier for it. It's not just desirable, but essential. Use a nice
> > stable EMP# not a person's name. It is about integrity not
> > 'expressiveness'.
>
> It is not essential. Language terms can denote different things at
> different times. "The President of the United States" is Barack Hussein
> Obama now, but was George Walker Bush just five months ago.
Those are not merely 'language terms'. The "President of the US" and
"Barack Obama" are different things, with different properties. The
fact that they currently happen to coincide is what is confusing you
(imo of course).
It is /essential/ one knows which of those things one wants to keep
track of in order to pick a key that will be stable over time, and
hence construct a schema that will maintain integrity over time. If
you are concerned with the "person" then that should be the chosen
key, and their "post of office" will change over time. If you are
concerned with the "post of office" then that is the key, and the
"person" holding that position will change over time.
You've missed the point that we were left (forever) with a tuple in
the database that was false (concerning the supplier 123 who no longer
existed). That is the integrity failure, and it is irrevocable because
of the poor choice of key. Unless you address how this problem can be
averted (without intervening updates, which as we have seen may not be
available), your position must be acceded.
none Reinier Post
>[...] But in
>theory, using just the before and after values of the Bins relvar, how can
>one declare the transition constraint?
You're right that it's impossible without making some further assumption,
Designing your database schema to have immutable keys is clearly
preferable but I agree with you that in some situations this isn't
possible. Changing the situation to allow it would still be a good idea,
as I think your bins example clearly shows: it's a design begging
for trouble.
The alternative you suggest, if I understand you correctly, is to
impose a strict policy of use for update, insert and delete statements,
in which an update to a primary key value must always mean that the
identification of the entity is being changed, while a sequence of
deletions and insertions never changes this identification.
I think this does resolve the issue, not that of formulating transition
constraints in general, but the more specific problem of formulating
transition constraints in terms of entity names ("supplier", "bin")
that map one-to-one, but not immutably, to the tuples of a relation.
But that doesn't mean it is the only way of addressing this issue.
A simpler way is to just not use such designs or formulations.
--
Reinier
Brian Selzer
> > It is not essential. Language terms can denote different things at
> > different times. "The President of the United States" is Barack Hussein
> > Obama now, but was George Walker Bush just five months ago.
>
> Those are not merely 'language terms'. The "President of the US" and
> "Barack Obama" are different things, with different properties. The
> fact that they currently happen to coincide is what is confusing you
> (imo of course).
I have to disagree. The Office of the President and the President are two
distinct things. The President is a person--the person that currently holds
the Office. If I state, "The President of the United States used to be a
United States Senator." It is a true statement and will remain true for as
long as Barack Obama holds the office, but five months ago, it was not a
true statement because George Walker Bush never was a United States Senator.
<snip>
> >
> > It's not so much an integrity failure. Issuing a delete or update that
> > targets a nonexistent tuple is a different kind of nonsense than issuing
> > a
> > delete, update or insert that violates a constraint. I would call the
> > constraint violation an integrity failure, but I'm not sure what name
> > fits
> > the other kind of nonsense.
>
> You've missed the point that we were left (forever) with a tuple in
> the database that was false (concerning the supplier 123 who no longer
> existed). That is the integrity failure, and it is irrevocable because
> of the poor choice of key. Unless you address how this problem can be
> averted (without intervening updates, which as we have seen may not be
> available), your position must be acceded.
I still think it is a communication problem and has nothing to do with key
choice. Choosing a "stable" key merely reduces the frequency of key
updates. It does not prevent them altogether. To prevent them altogether,
there must be a transition constraint. But under Date and Darwen's
databases-as-collections-of-relvars paradigm, it is not possible to declare
nor enforce such a constraint.
Constraints shape the Universe. Declaring a constraint that prevents key
updates fixes the "meaning" of the set of symbols that comprise an instance
of that key. Without the constraint it is possible for that set of symbols
to "mean" different things at different times.
> This is not key stability problem--it's not even a database problem: it's
> a
> communication problem. If the direct deposit of your paycheck into your
> bank was delayed because the internet was down, but the automatic bill-pay
> of your mortgage and equity line occurred on time because they're from the
> same bank, then you'll be charged bounced-check fees for your mortgage and
> equity line, and late payment fees for your mortgage and equity line, and
> because you were late on your payments your credit rating will be hosed
> and
> your other creditors may as a consequence increase your interest
> rates--all
> due to a communication problem.
<snip>
Brian Selzer
"none (Reinier Post)" <rp@raampje.> wrote in message
news:4a2c2344$0$6254$703f...@news.kpn.nl...
> Brian Selzer wrote:
>
>>[...] But in
>>theory, using just the before and after values of the Bins relvar, how can
>>one declare the transition constraint?
>
> You're right that it's impossible without making some further assumption,
> Designing your database schema to have immutable keys is clearly
> preferable but I agree with you that in some situations this isn't
> possible. Changing the situation to allow it would still be a good idea,
> as I think your bins example clearly shows: it's a design begging
> for trouble.
I don't see how it begs for trouble. The system works: pickers can rapidly
find the bins where inventory is to be or is stored, and that information is
easily and reliably maintained by the system. Trouble only rears up as a
direct consequence of adopting Date and Darwen's
databases-as-collections-of-relvars paradigm under which deletes, updates
and inserts are shortcuts for relational assignments.
> The alternative you suggest, if I understand you correctly, is to
> impose a strict policy of use for update, insert and delete statements,
> in which an update to a primary key value must always mean that the
> identification of the entity is being changed, while a sequence of
> deletions and insertions never changes this identification.
I'm not sure that you do understand me correctly. The alternative is not to
impose policy, but rather to treat deletes, updates and inserts as primitive
operations instead of relational assignments. A transition is a set of
deletes, updates and inserts that together assert what in the Universe is
different and exactly how. A delete targets a set of tuples and asserts
that the thing in the Universe that each targeted tuple maps to no longer
exists. An update targets a set of tuples and asserts that the thing in the
Universe that each targeted tuple maps to has changed in appearance. An
insert does not target any tuples in the database, but instead asserts that
some things in the Universe have come into existence.
> I think this does resolve the issue, not that of formulating transition
> constraints in general, but the more specific problem of formulating
> transition constraints in terms of entity names ("supplier", "bin")
> that map one-to-one, but not immutably, to the tuples of a relation.
>
I think it does both. The way I see it, a transition can be represented as
a set of relations, three for each relation schema in a database schema.
One for "deleted" tuples; one for "updated" tuples; and one for "inserted"
tuples. For example, suppose that the database schema consists of the
following relation schemata
R {A, B, C} and S {A, D},
then the database is comprised of an instance of each of these schemata,
r and s;
a transition can then be represented by an instance of each of the following
relation schemata,
R- {A, B, C}, R~ {A, B, C, A', B', C'}, R+ {A', B', C'},
S- {A, D}, S~ {A, D, A', D'}, S+ {A', D'},
r-, r~, r+, s-, s~, s+;
and the result of applying the transition to the database can be represented
as instances of the following schemata,
R' {A, B, C} and S' {A, D},
r' and s'.
It should therefore be possible to write constraints in terms of any or all
of
r, r-, r~, r+, r', s, s-, s~, s+, s'.
Constraints written in terms of just r' and s' are state constraints; all
others are transition constraints.
This provides a means for declarative specification of not only state
constraints but also transition constraints--all transition constraints,
because every transition can be exactly represented.
> But that doesn't mean it is the only way of addressing this issue.
> A simpler way is to just not use such designs or formulations.
>
Abstinence is the only certain method to prevent pregnancy, but where's the
fun in that?
Bernard Peek
<b766171d-7b30-4251...@l28g2000vba.googlegroups.com>, JOG
<j...@cs.nott.ac.uk> writes
Let's get metaphysical. There is an attribute of every unique object
called "Identity." This is a non-numeric dimensionless constant. What
makes this difficult to deal with is that there is no function that can
be applied to {Identity} which returns a meaningful text string.
We therefore choose a range of surrogate keys which can be manipulated
as text strings and to a greater or lesser extent map 1:1 to the
Identity value. In some cases we have natural keys where the mapping is
enforced by the laws of physics. In other cases we issue an invented
value to identify an object, and we attempt to maintain the 1:1 mapping
by processes that take place outside the database. So we issue a
National Insurance number and tell the person it identifies to remember
on pain of dire consequences.
In every case that I can think of the mapping is maintained by processes
that are outside the database and outside the relational model. The
relational model takes it as axiomatic that this mapping is somehow
maintained. It does not deal with how it is maintained.
In this it is no different from any other branch of algebra. If an
equation asserts that 3X=6 then we assume that all three values of X are
identical and map on to the same value. In relational algebra it is
assumed that whatever {Identity} maps to {Key} is always the same.
What we are discussing in this thread is pathological conditions where
we assume that the mapping may change over time. This is essentially the
same problems as we would face if we tried to perform simple algebra
when a variable can have multiple different values at the same time and
in the same equation.
--
Bernard Peek
Walter Mitty
"Bernard Peek" <b...@shrdlu.com> wrote in message
news:6ciFVPRa...@shrdlu.com...
Bernard,
Your reply is crystal clear except for one minor point. It's not clear (at
least to me) who the pronoun "we" refers to in the phrase "we therefore
choose a range of surrogate keys."
It seems to me that, in some cases, the people who choose surrogates and
assign a new surrogate value to a newly discovered entity instance are
"outside the application", such as a hiring clerk in HR assigning a new
employee badge number to an employee on his/her first day of service. In
some cases, the assignment is "inside the application, but outside the
dataabase". In these cases, HR sees the new badge number as having been
assigned by some process behind the data entry screen. But the programmers
see the assignment has having been done by their programs.
In yet other cases, the database designer has chosen to use an autogenerated
datatype for the column that holds the surrogate. This might be called
"inside the DBMS but outside the database". But to most people, inside the
DBMS and inside the database are tantamount to the same thing.
So, what do you mean by "we", kemosave?
Bernard Peek
<wam...@verizon.net> writes
>Your reply is crystal clear except for one minor point. It's not clear (at
>least to me) who the pronoun "we" refers to in the phrase "we therefore
>choose a range of surrogate keys."
I'm referring to the data modellers and database designers who have to
turn theoretical concepts into database schemas.
>
>It seems to me that, in some cases, the people who choose surrogates and
>assign a new surrogate value to a newly discovered entity instance are
>"outside the application", such as a hiring clerk in HR assigning a new
>employee badge number to an employee on his/her first day of service. In
>some cases, the assignment is "inside the application, but outside the
>dataabase". In these cases, HR sees the new badge number as having been
>assigned by some process behind the data entry screen. But the programmers
>see the assignment has having been done by their programs.
>
>In yet other cases, the database designer has chosen to use an autogenerated
>datatype for the column that holds the surrogate. This might be called
>"inside the DBMS but outside the database". But to most people, inside the
>DBMS and inside the database are tantamount to the same thing.
>
>So, what do you mean by "we", kemosave?
When we talk about "systems" we generally have at least some idea of
where the boundaries of the systems lie. But in different contexts
people can use different definitions which may include or exclude
different components. If I decide, as a data modeller, that a particular
table should have an Autonumber column I may add it to the logical data
structure. The job of converting that to a physical structure may fall
to someone else; who may add implementation-specific elements. One of
those may be an external mechanism that generates the key values. So
whether this is inside or outside the database depends on who you ask,
and at what stage through the development process. In brief: it depends.
--
Bernard Peek
Brian Selzer
In fact, identity is a relation. The word you're looking for is haecceity,
"thisness." The haecceity of something is essentially just that which
distinguishes it from everything else. Haecceity can be embodied by a rigid
designator, or a rigid definite description, but it is in fact a separate
property that is dependent upon neither. What can be said about haecceity
is that everything that can be has one--one that is different than that of
every other thing that can be.
> We therefore choose a range of surrogate keys which can be manipulated as
> text strings and to a greater or lesser extent map 1:1 to the Identity
> value.
Not exactly. We include enough of the properties of each in the set of
things being represented in order to just be able to distinguish one from
another at any given point in time, which is all that is required in order
to represent them in a relation or reason about them using first order
predicate logic. Surrogates need only be introduced when some of those
properties are not relevant to the problem at hand.
> In some cases we have natural keys where the mapping is enforced by the
> laws of physics. In other cases we issue an invented value to identify an
> object, and we attempt to maintain the 1:1 mapping by processes that take
> place outside the database. So we issue a National Insurance number and
> tell the person it identifies to remember on pain of dire consequences.
>
> In every case that I can think of the mapping is maintained by processes
> that are outside the database and outside the relational model. The
> relational model takes it as axiomatic that this mapping is somehow
> maintained. It does not deal with how it is maintained.
I have to disagree. Codd anticipated the need for key updates when he
introduced the Relational Model to the world. He wrote:
The totality of data in a data bank may be viewed as a
collection of time-varying relations. These relations are of
assorted degrees. As time progresses, each n-ary relation
may be subject to insertion of additional n-tuples, deletion
of existing ones, and alteration of components of any of its
existing n-tuples.
--A Relational Model of Data for Large Shared Data Banks,
Communications of the ACM, June 1970, page 379.
Note that there is no differentiation between key and non-key components.
In fact, that would unnecessarily restrict the kinds of variations that can
occur--especially when there is more than one key. Later on in the paper he
referred directly to key updates:
There are, of course, several possible ways in which a
system can detect inconsistencies and respond to them.
In one approach the system checks for possible inconsist-
ency whenever an insertion, deletion, or key update occurs.
--page 387.
If any prime attribute can be the target of an update, then, obviously,
multiple instances of a key can map to the same thing but only at different
times, and a single instance of a key can map to different things but only
at different times.
>
> In this it is no different from any other branch of algebra. If an
> equation asserts that 3X=6 then we assume that all three values of X are
> identical and map on to the same value. In relational algebra it is
> assumed that whatever {Identity} maps to {Key} is always the same.
This statement is true (if one interprets "always" to be the scope of any
algebraic expression), but it is vacuously true, because expressions in both
the relational algebra and the relational calculus apply to just one
instantaneous state of the data bank at a time. So every appearance of an
instance of a key /at one instantaneous state of the data bank/ maps to the
same thing in the Universe. But that doesn't preclude an appearance of that
instance of the key /at another instantaneous state of the data bank/ from
mapping to a different thing.
>
> What we are discussing in this thread is pathological conditions where we
> assume that the mapping may change over time. This is essentially the same
> problems as we would face if we tried to perform simple algebra when a
> variable can have multiple different values at the same time and in the
> same equation.
I don't think they are pathological conditions. The real pathology is that
there seems to be a pervading implication in the posts here on CDT that
every relation should if at all possible have just one unary key and that
there is even always a choice of a key, when there are in fact a myriad of
instances that require composite keys, multiple unary keys, both unary and
composite keys or even multiple overlapping composite keys, and when much of
the time key specifications arise as a consequence of normalization. If a
relation schema has two keys, do you allow updates that target just one of
the keys? If it has three, do you allow updates to all but one? If you do
allow updates that target just one key, or all but one key, then why
shouldn't you allow updates that target any or even all keys? Why
artificially impose limitations on the model?
none Reinier Post
>"Bernard Peek" <b...@shrdlu.com> wrote
>> Let's get metaphysical. There is an attribute of every unique object
>> called "Identity." This is a non-numeric dimensionless constant. What
>> makes this difficult to deal with is that there is no function that can be
>> applied to {Identity} which returns a meaningful text string.
Worse, such absolute identity cannot be observed at all.
I don't think it really *is* an attribute.
What *does* exist is *relative* identity: an attribute explicitly assigned
to objects to distinguish them by some mechanism that guarantees that
the assignment is unique - within the given context and under certain
assumptions.
Brian Selzer adds:
>In fact, identity is a relation. The word you're looking for is haecceity,
>"thisness." The haecceity of something is essentially just that which
>distinguishes it from everything else. Haecceity can be embodied by a rigid
>designator, or a rigid definite description, but it is in fact a separate
>property that is dependent upon neither. What can be said about haecceity
>is that everything that can be has one--one that is different than that of
>every other thing that can be.
But what is the nature of this relation? Is it something we observe,
or something we mentally construct to simplify talking about the world?
I think it's the latter.
>> We therefore choose a range of surrogate keys which can be manipulated as
>> text strings and to a greater or lesser extent map 1:1 to the Identity
>> value.
>
>Not exactly. We include enough of the properties of each in the set of
>things being represented in order to just be able to distinguish one from
>another at any given point in time, which is all that is required in order
>to represent them in a relation or reason about them using first order
>predicate logic. Surrogates need only be introduced when some of those
>properties are not relevant to the problem at hand.
Surrogates need to be introduced when no set of natural properties is
sufficiently distinctive.
>> In some cases we have natural keys where the mapping is enforced by the
>> laws of physics. In other cases we issue an invented value to identify an
>> object, and we attempt to maintain the 1:1 mapping by processes that take
>> place outside the database. So we issue a National Insurance number and
>> tell the person it identifies to remember on pain of dire consequences.
>>
>> In every case that I can think of the mapping is maintained by processes
>> that are outside the database and outside the relational model. The
>> relational model takes it as axiomatic that this mapping is somehow
>> maintained. It does not deal with how it is maintained.
I would consider autonumbering of autonumbered keys to take place
within the database, but an external process can be used as well.
>I have to disagree. Codd anticipated the need for key updates when he
>introduced the Relational Model to the world. He wrote:
[...]
> There are, of course, several possible ways in which a
> system can detect inconsistencies and respond to them.
> In one approach the system checks for possible inconsist-
> ency whenever an insertion, deletion, or key update occurs.
> --page 387.
Nice catch.
>If any prime attribute can be the target of an update, then, obviously,
>multiple instances of a key can map to the same thing but only at different
>times, and a single instance of a key can map to different things but only
>at different times.
Yes, and this makes it hard to specify transition constraints.
It's a little kludgy to disable them whenever key updates are made.
[...]
>I don't think they are pathological conditions. The real pathology is that
>there seems to be a pervading implication in the posts here on CDT that
>every relation should if at all possible have just one unary key and that
>there is even always a choice of a key, when there are in fact a myriad of
[...]
But don't you agree that a relation with a mutable key is really
impractical? It truly makes it more difficult to maintain a correct
relationship between database content and the state of affairs outside.
There is a reason people use surrogates whenever they get the chance.
--
Reinier
Kevin Kirkpatrick
> "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
So basically, transition constraints require immutable prime
attributes and immutable prime attributes require transition
constraints. This may be true - the thing is, I contend that neither
one is needed (and if they seem to be, then the data model is off).
> There are a myriad of examples I could have used but
> they would have required a lot more explanation or expertise in subjects
> that are not germane to database theory, furthermore, it just so happens
> that Date in /Introduction/ uses a similar example when introducing the
> concept of transition constraints.
>
>
I'll review that example - I just can't conceive of a real-world case
where constraints need to refer to attributes which are not themselves
in the data model. Such constraints seem to violate the mantra, "The
DBMS can't enforce truth, only consistency."
>
>
>
> >Having gotten that off my chest, let's jump into the unusual world
> >fostered by "transition constraints". A data entry person will adhere
> >to the transition constraints policy, and following the data modeler's
> >advice, do 2 updates to set Mary Smith's marital status correctly.
> >Let's assume a 1 minute lag between updates, and look at what the
> >updates mean (per Brian's definitions above).
>
> >At 8:00 a.m., according to the meaning of an update, the data entry
> >person should assert:
>
> >"The person that had up to 8:00 a.m. had last name Smith and first
> >name Mary and had up to 8:00 a.m. been Single now has last name Smith
> >and first name Mary and is Married."
>
> >She should submit this fact to the database, then reload her data
> >entry form in order to assert at 8:01 a.m.:
>
> >"The person that had up to 8:01 a.m. had last name Smith and first
> >name Mary and had up to 8:01 a.m. been Married now has last name Smith
> >and first name Mary and is Divorced."
>
> >Do I have the intended meaning of both of these updates correct? If
> >so, would you agree that both assertions are falsehoods about the real
> >world?
>
> I see what you're driving at, but at the time immediately prior to the first
> assertion, what the database asserted about the real world was false and had
> been false for about a week. As a consequence every possible update would in
> fact be a falsehood about the real world, as would the result of many
> database queries during that time.
>
You're equivocating on the word "falsehood". There is a vast
difference between a database returning a false statement about the
real world (the results of any query of any database are only as
accurate as the observations recorded therein), and users asserting
something they known to be untrue. I would consider it a catastrophic
failure of the data model if, for the database to function, the end
users needed to lie.
Obviously, as a consequence of common knowledge, we can infer that if
Mary Smith is divorced then she was married at some prior time in her
life. However, I was responding to your statement that the history of
Mary's marital statuses could be inferred "as a consequence of the
transition constraint". In that light, it seemed you were claiming,
"Given a current tuple <Mary, Smith, Divorced>, one could use the
transition constraint to infer that tuple <Mary, Smith, Married> had
been recorded at some prior time." That is the false inference to
which I alluded.
> <snipped false conclusion drawn from false premise>
>
> > If the UoD is concerned about whether
> > Mary Smith got Married while being tracked by the database, then the
> > data model ought to expose a history of marital statuses, period. And
> > it goes without saying that in such a revised model, the marital-
> > status "transition constraint" is unnecessary (simple rules about
> > allowable chronological histories of marital statuses are trivially
> > enforced).
>
> First of all, the UoD is not sentient, so it cannot be concerned about
> anything.
Apologies for the sloppy wording. I simply meant that if 'Mary Smith
got Married' is a meaningful piece of information within the UoD, then
the data model ought to allow such information to be tracked
explicitly.
> Second, the point of the transition constraint is to keep garbage
> out, but storing the histories of marital statuses would clutter up the
> database with irrelevancies, which are in my opinion just another kind of
> garbage.
I disagree. In all of the database modeling I've done, I have yet to
encounter a situation where some attribute X is so important that we
can use it as a basis for rejecting some proposed value for attribute
Y, but so un-important that we don't want to store it in the
database. It seems obvious to me that if "prior marital status" is
sufficient grounds for rejecting an asserted "current marital status",
then "prior marital status" is a part of the UoD and should be exposed
in the data model.
> Third, even if there were a reasonable way to eliminate the need
> for the marital-status transition constraint, in no way does that prove that
> there is never any need for transition constraints. Even Date and Darwen,
> as evidenced by their "RM Very Strong Suggestion 4," acknowledge the need
> for transition constraints.
>
Well, yes... proving negatives is impossible. On the other hand, you
need only provide *one* example where no reasonable alternative to
transition constraints exists (and, of course, where transition
constraints will not require end users to lie to the database).
> <snip>
>
> > I must say, given the freedom to pick any example to illuminate the
> > value of "transition constraints", it's telling that you have chosen
> > one which trivially leads to both:
> > 1) Requirements that people assert falsehoods in order for the
> > database to function
>
> As indicated above, that would be required even without the transition
> constraint.
Say what? If {F, L, Stat} models the predicate "The employee hired
with unique name <F> <L> is <Stat>", then the data entry person who
updates Mary Smith's marital status from "Single" to "Divorced" at a
time that she is divorced is *not* asserting a falsehood.
If prior marital statuses were part of the UoD, and statuses were
modeled with something akin to:
MAR_CURR {F, L, Stat}: "The employee hired with unique name <F> <L> is
<Stat>"
MAR_PRIOR {F, L, Prior_Stat}: "The employee hired with unique name <F>
<L> had prior status <Prior_stat>"
then non-sensical marital status transitions could be prevented *and*
the database could be updated from the state:
MAR_CURR: {<MARY, SMITH, SINGLE>}
MAR_PRIOR: {}
to the state:
MAR_CURR: {<MARY, SMITH, DIVORCED>}
MAR_PRIOR: {<MARY, SMITH, MARRIED>}
with no need for lying to the database.
>
> > 2) False inferences.
>
> Also debunked above.
>
> <snipped inane ad-hominem attack>- Hide quoted text -
Brian Selzer
"none (Reinier Post)" <rp@raampje.> wrote in message
Does it matter?
>>> We therefore choose a range of surrogate keys which can be manipulated
>>> as
>>> text strings and to a greater or lesser extent map 1:1 to the Identity
>>> value.
>>
>>Not exactly. We include enough of the properties of each in the set of
>>things being represented in order to just be able to distinguish one from
>>another at any given point in time, which is all that is required in order
>>to represent them in a relation or reason about them using first order
>>predicate logic. Surrogates need only be introduced when some of those
>>properties are not relevant to the problem at hand.
>
> Surrogates need to be introduced when no set of natural properties is
> sufficiently distinctive.
Though there are things that do not occur in nature, due to Leibniz Law,
everything that does has a sufficiently distinctive set of natural
properties.
It should be possible to specify all transition constraints declaratively,
even for updates that target prime attributes. It's more than a kludge to
disable them, it's an anathema.
>
> [...]
>
>>I don't think they are pathological conditions. The real pathology is
>>that
>>there seems to be a pervading implication in the posts here on CDT that
>>every relation should if at all possible have just one unary key and that
>>there is even always a choice of a key, when there are in fact a myriad of
> [...]
>
> But don't you agree that a relation with a mutable key is really
> impractical? It truly makes it more difficult to maintain a correct
> relationship between database content and the state of affairs outside.
> There is a reason people use surrogates whenever they get the chance.
>
I really don't. Maintaining the relationship during a transition from one
state to another is not a problem, provided delete, insert and update are
primitive operators and not just shortcuts for assignment, and if there is a
need to maintain the history of something, then the instance of the key at
the time it first appeared can be used as permanent identification.
> --
> Reinier
Brian Selzer
<snip>
>> Third, even if there were a reasonable way to eliminate the need
>> for the marital-status transition constraint, in no way does that prove
>> that
>> there is never any need for transition constraints. Even Date and
>> Darwen,
>> as evidenced by their "RM Very Strong Suggestion 4," acknowledge the need
>> for transition constraints.
>>
>
> Well, yes... proving negatives is impossible. On the other hand, you
> need only provide *one* example where no reasonable alternative to
> transition constraints exists (and, of course, where transition
> constraints will not require end users to lie to the database).
>
I posted this earlier in this thread, but it does seem to meet your
criteria.
Suppose that you have a relvar for bins in warehouses. In this example,
they are in fact plastic bins that fit on shelves in warehouses. In the
interest of brevity, let's assume that the bins are all the same size and
that all inventory is stored in bins. Here's some necessary additional
info: there are 3 warehouses, each warehouse has between 24 and 30 rows of
shelves, each row can have between 1 and 5 shelves and each shelf can house
perhaps 20 plastic bins. Labels with extra large numerals are affixed to
the bins so that the pickers (the employees that pull inventory to satisfy
orders and put away inventory after it has been received and inspected) can
rapidly locate them.
For each bin there is a tuple in the current value of the relvar,
Bins {Warehouse, Row, Shelf, Bin}
(Again for brevity, I'm leaving out the specifications of the Warehouses,
Rows, and Shelves relvars, which should in fact be obvious, as well as the
foreign key heirarchy, as it should also be obvious. Let's assume for now
that the foreign keys defined should prevent an insert of or an update that
results in a nonexistent {Warehouse, Row, Shelf} combination.)
set-based trigger, since row-level triggers are not supported. But in
theory, using just the before and after values of the Bins relvar, how can
one declare the transition constraint?
Perhaps you can come up with a reasonable alternative to a transition
constraint, one that doesn't require extraneous information to be stored,
and one that doesn't require changing the business process. Neither of the
triggers mentioned above would change the business process, and the IDENTITY
column would be used exclusively to preserve the stated correlation between
the old and new values for each tuple targeted by an update, making it
possible to join the deleted and inserted pseudotables in the set-based
trigger. Changing Bin to be a candidate key would constitute a change in
the business process because it would require more than two numerals to be
printed on the label, requiring a smaller font and thus making it more
difficult for the pickers to discern. (It's also easier to count to 20 than
to 5000, and pickers are generally not rocket scientists, or they wouldn't
be pickers.)
If we discard Date and Darwen's databases-as-collections-of-relvars
paradigm, and treat insert, update and delete as primitive operations, then
the transition constraint can be specified declaratively:
CONSTRAINT NoWarehouseChange
IS_EMPTY ( Bins~ WHERE NOT ( Warehouse' = Warehouse ) );
returns FALSE only when Bins~, which is populated only when an update is
issued and only with the old and new (primed) components for each tuple
actually targeted by an update, has at least one tuple in which the old and
new Warehouse components differ.
Kevin Kirkpatrick
> "Kevin Kirkpatrick" <kvnkrkpt...@gmail.com> wrote in message
If your data model does not allow for assertions about bin movement,
then you cannot coherently enforce constraints on bin movement. In
this case, the model doesn't support propositions of the form
BIN_MOVEMENT {warehouse, row, shelf, bin, prior_warehouse, prior_row,
prior_shelf, prior_bin [...,picker_number, movement_time,...] }
(to be interpretted as: "The bin currently in <warehouse> in <row> on
<shelf> labeled <bin> was moved there directly from <prior_warehouse>
in <prior_row> on <prior_shelf> labeled <prior_bin> ...", or something
similar). This is only needless clutter if the client is compared
about the prior location of a bin (in which case, he can't sensically
request a constraint referring to that property).
In short: if you want transition constraints, then model transitions,
and constrain them.
Your "transition constraint" solution is really just "take a database
tool that isn't expected to be used for routine business procedures,
and completely disable it for all users of all applications in all
contexts" (never minding the non-routine issues that crop up from time
to time; e.g. the sudden need to move 30 empty bins from WH1 to WH2,
or the need to correct an insert of 5000 new bins into WH2 that were
actually placed in WH3 - just assume these thing will never, ever
happen, or clutter the database with un-requested "update explanation"
relations and the additional complex application screens and user
training they'll require).
If you don't want an application to update the warehouse, then don't
write code in that application that updates the warehouse. If you
have a specific application (or user) that should *never* be able to
update warehouse under any conditions, then it is a security issue:
REVOKE UPDATE ON BINS FROM APP_USER_NAME;
GRANT UPDATE ON BINS(SHELF, ROW, BIN) TO APP_USER_NAME;
REVOKE INSERT ON BINS FROM APP_USER_NAME;
REVOKE DELETE ON BINS FROM APP_USER_NAME;
Or, restrict your application to specific views, e.g.
CREATE VIEW WAREHOUSE1 AS SELECT * FROM BINS WHERE WAREHOUSE=1 WITH
CHECK OPTION
(etc.)
Having said that, as this is a thread about Identities, allow me to
make a comment about your business example, which highlights an exact
situation where the natural world is going to dictate
1) a unique, scannable bin identifier permanenty adhered to each bin
(in addition to the large numeric labels),
2) scannable shelf identifiers attached to each shelf, and
3) business practice that involves scanning both bin and shelf with
each pick up and drop-off and
4) (probably) business practice that involves scanning inventory items
taken out of and put into each bin.
Suddenly, you've got a natural "Bin_Id" key to work with; an easy way
to track who moved which bins, from where, to where, and when; a model
that can be constrained so it doesn't accept a movement of bins from
one warehouse to another, and so on, and so on...
Why would you want all of this? In your current approach, small
mistakes will have catastrophic consequences. Consider what might
happen if a picker, perhaps weary from a long day, accidentally moves
Bin 3 to Shelf 6 instead of Shelf 7. Any subsequent activity could
make this little accident difficult to detect, and difficult if not
impossible to set straight (especially if there was already a bin 3
on shelf 6). Or consider the situation where a picker must move bin 4
from row 8 to row 32 and bin 4 from row 9 to row 33... he moves them
in a single trip, and finds himself at between row 32 and 33 with two
Bin 4's. Will he call his boss, and waste 10 minutes figuring things
out with an inventory count / comparison (and risk getting docked for
hurting producitvity), or will he just figures the top box was
probably the one meant for row 33? As bin movements aren't tracked
(remember, that information just clutters the database), there'd be no
way to trace these incidents back, so you couldn't even determine the
culpable parties or track careless employees in incidents like these.
I understand that, as a database designer, it isn't my place to
dictate how the business should work. And I'm not saying that your
approach couldn't be modeled (it could), but in the interest of
keeping my source of income from risking bankrupcy, you can bet your
bottom dollar I'd be asking these and a lot of other questions to the
SE (with SE's boss visibly cc'd, if need be).
Kevin Kirkpatrick
>
> If your data model does not allow for assertions about bin movement,
> then you cannot coherently enforce constraints on bin movement. In
> this case, the model doesn't support propositions of the form
> BIN_MOVEMENT {warehouse, row, shelf, bin, prior_warehouse, prior_row,
> prior_shelf, prior_bin [...,picker_number, movement_time,...] }
> (to be interpretted as: "The bin currently in <warehouse> in <row> on
> <shelf> labeled <bin> was moved there directly from <prior_warehouse>
> in <prior_row> on <prior_shelf> labeled <prior_bin> ...", or something
> similar). This is only needless clutter if the client is compared
> about the prior location of a bin (in which case, he can't sensically
> request a constraint referring to that property).
>
This is only needless clutter if the client is not concerned
Brian Selzer
<snip>
> If your data model does not allow for assertions about bin movement,
> then you cannot coherently enforce constraints on bin movement. In
> this case, the model doesn't support propositions of the form
> BIN_MOVEMENT {warehouse, row, shelf, bin, prior_warehouse, prior_row,
> prior_shelf, prior_bin [...,picker_number, movement_time,...] }
> (to be interpretted as: "The bin currently in <warehouse> in <row> on
> <shelf> labeled <bin> was moved there directly from <prior_warehouse>
> in <prior_row> on <prior_shelf> labeled <prior_bin> ...", or something
> similar).
In fact, the data model does allow for assertions about bin movement.
UPDATE Bins
WHERE ( Warehouse = 3 AND Row = 20
AND Shelf = 5 AND Bin = 14 )
( Row := 22, Shelf := 3, Bin := 18 )
represents the sentence,
"Each bin 14 on shelf 5 of row 20 of warehouse 3 is now bin 18 on shelf 3 of
row 22 of warehouse 3."
And if there has up to the time of the update been a bin 14 on shelf 5 of
row 20 of warehouse 3, then the sentence is the equivalent of the assertion,
"Bin 14 on shelf 5 of row 20 of warehouse 3 is now bin 18 on shelf 3 of row
22 of warehouse 3."
Also,
UPDATE Bins
WHERE ( Warehouse = 2 AND Row = 9 AND Shelf = 5 )
( Row := 12, Shelf := 2 )
represents the sentence,
"Each bin on shelf 5 of row 9 of warehouse 2 is now on shelf 2 of row 12 of
warehouse 2."
And if, for instance, just bins 3, 7 and 10 have up to the time of the
update been on shelf 5 of row 9 of warehouse 2, then the sentence is the
equivalent of the set of assertions,
"Bin 3 on shelf 5 of row 9 of warehouse 2 is now bin 3 on shelf 2 of row 12
of warehouse 2."
"Bin 7 on shelf 5 of row 9 of warehouse 2 is now bin 7 on shelf 2 of row 12
of warehouse 2."
"Bin 10 on shelf 5 of row 9 of warehouse 2 is now bin 10 on shelf 2 of row
12 of warehouse 2."
So it is clear to me that since the user is able to express them, the data
model /does/ allow for assertions about bin movement, even though it is not
the assertions themselves but the results of evaluating those assertions in
the context of the database that are actually recorded.
<plugging in your correction>
> This is only needless clutter if the client is not concerned
> about the prior location of a bin (in which case, he can't sensically
> request a constraint referring to that property).
The prior location of a bin is only relevant during a transition, once the
transition has occurred, there is no more use for it, at least as far as the
client is concerned.
> In short: if you want transition constraints, then model transitions,
> and constrain them.
Not all transition constraints involve updates. Some involve inserts and
deletes. For instance, how would you implement a business rule that states
that only empty bins can be binned? For example, the following relvar could
be for the current location of inventory stored in bins,
BinInventory { Warehouse, Row, Shelf, Bin, Item, Quantity }
with key { Warehouse, Row, Shelf, Bin, Item }
and referential constraint,
CONSTRAINT BinInventory_Bins
BinInventory { Warehouse, Row, Shelf, Bin }
|<=| Bins { Warehouse, Row, Shelf, Bin };
Given that the key can be the target of an update and that when an update is
coerced into being a relational assignment, it can no longer be determined
from just the before and after values of a relvar whether an assignment is
the result of an update or of a delete and an insert, how can a business
rule that states, "Only empty bins can be discarded." be implemented. One
must first be able to definitively determine that a bin is in fact being
discarded, not moved, correct?
It is a simple matter to declare a transition constraint if delete, insert,
and update are primitive operations and not shortcuts for assignment.
Here's the constraint that prevents a delete that targets a bin that
contains inventory:
CONSTRAINT NoDeleteNonEmptyBin
IS_EMPTY ( BinInventory- WHERE Quantity > 0 );
> Your "transition constraint" solution is really just "take a database
> tool that isn't expected to be used for routine business procedures,
> and completely disable it for all users of all applications in all
> contexts"
In what way does the solution do that? What "tool" is disabled? The
solution does not prevent the specification of any state constraints. All
it does is allow transition constraints to be specified declaratively. Of
course that requires that transitions be precisely characterized, which
relegates relational assignment to being just a shortcut for a delete and an
insert. But there is obviously no loss of expressiveness in so doing
because every possible relational assignment and multiple assignment under
the databases-as-collections-of-relvars paradigm can be expressed (and more
precisely, I should add) as a set of primitive delete, update and/or insert
operations.
> (never minding the non-routine issues that crop up from time
> to time; e.g. the sudden need to move 30 empty bins from WH1 to WH2,
I had originally sought to simplify the example as much as possible, but
since the purpose of the transition constraint is to prevent /inventory/
from being moved without the necessary documentation and accounting
transactions, it is a simple matter to alter the constraint so that only
/empty/ bins can be moved between warehouses.
CONSTRAINT NoNonEmptyWarehouseChange
IS_EMPTY ( BinInventory~
WHERE NOT ( Warehouse' = Warehouse
AND Quantity' > 0 ) );
Note that this doesn't prevent a bin from being emptied /and/ moved. If it
is also a requirement that the bin be empty /before/ it can be moved, then
replace
"Quantity' > 0" with "( Quantity' > 0 OR Quantity > 0 )" in the above
constraint.
Note also that the NoDeleteNonEmptyBin defined above would prevent an
attempt to bypass the constraint by issuing a delete and an insert.
> or the need to correct an insert of 5000 new bins into WH2 that were
> actually placed in WH3 - just assume these thing will never, ever
> happen, or clutter the database with un-requested "update explanation"
> relations and the additional complex application screens and user
> training they'll require).
What is so complicated about it? It is not essential that the explanation
be supplied by the user directly: it could be supplied by the application as
a response to a simple "Are you sure you want to do this?" type of message
box. How is that so different from a window popping up informing them that
some other user made a change while they were diddling around?
How does the addition of /one/ relation schema for explaining
corrections--only one is needed to be able to differentiate between a
transition and a correction, and that one can be used to account for /all/
corrections--clutter up the database? Moreover, I would argue that the
explanation relation schema is not un-requested, but a consequence of the
requirement of being able to correct mistakes.
> If you don't want an application to update the warehouse, then don't
> write code in that application that updates the warehouse.
Am I reading you correctly? Are you suggesting that the constraint should
be enforced in the application, not the database?
<snip>
The introduction of barcodes has merit, but I balk at the introduction of
the Bin_Id for a number of reasons. First of all, it doesn't eliminate the
need for transition constraints because just adding a supposedly "stable"
key doesn't eliminate the need to prevent updates that target that key.
Secondly, it doesn't eliminate the weary employee scenario because the
employee could have simply failed to scan the shelf and bin, requiring the
entire warehouse to be searched, though you are correct to some extent in
that finding the bin in question would be at least be definitely possible.
Third, I don't think it is necessary: a solution that doesn't involve
christening each bin would be to simply include the warehouse, row and shelf
on the label, in a smaller font, of course, and perhaps also a barcode that
is a concatenation of all of the components, something like "W3R23S2B14"
would be useful. Then every bin in every warehouse can be positively
identified, so that when the picker is faced with two Bin 4's, he can simply
read the labels to find out which bin goes where. In addition, since the
system, as a proxy for management, directs the pickers not only where to
pull and put away inventory, but also where to move bins, it can help ensure
that no two bins have the same barcode at the same time. Lastly, it is not
impossible to track bin movements over time, should that at some point in
the future become a requirement, even with a volitile key. The key
components at a singular event for each bin, such as when the bin was placed
into service, or when the tracking requirement was instituted, combined with
a reference to that event, such as a time stamp, permanently identifies the
bin. That permanent identification could be used to track bin movements
over time, making it possible to determine culpable parties or to track
careless employees in the incidents described above. Note that a transition
constraint preventing updates that target the attributes for the permanent
identification would still need to be specified.
<snip>
Walter Mitty
> represents the sentence,
>
> "Each bin 14 on shelf 5 of row 20 of warehouse 3 is now bin 18 on shelf 3
> of row 22 of warehouse 3."
>
> "Each bin 14 on shelf 5 of row 20 of warehouse 3 will henceforth be bin 18
> on shelf 3 of row 22 of warehouse 3."
>
?
Brian Selzer
"Walter Mitty" <wam...@verizon.net> wrote in message
news:QJlYl.2020$u86...@nwrddc01.gnilink.net...
I don't think so. In tense logic, henceforth is a operator with an intended
meaning that is something like, "at all future times," and it is possible
that the bin could be moved again at some point in the future. I am
uncomfortable with treating assertions about what /will/ be as factual, so I
don't think they belong in either the database or a transition. I think I
understand your concern, though. The sentence, using tense operators, would
be something like,
"Each bin 14 that has since the time of the previous transition and until
the time of this transition been on shelf 5 of row 20 of warehouse 3 is at
the time of this transition bin 18 on shelf 3 of row 22 of warehouse 3." To
address one of Kevin's prior concerns, "the time of this transition" could
be taken to mean, "the time that this transition should have been
submitted," provided transitions are submitted in the same order in which
the changes they are supposed to represent occurred.