Re: Relation Schemata vs. Relation Variables

"David Cressey" <dcressey_at_verizon.net> wrote in message news:CohIg.959$aQ4.63_at_trndny06...
>
> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:rn1Ig.12260$1f6.6985_at_newssvr27.news.prodigy.net...
>>
>> "David Cressey" <dcressey_at_verizon.net> wrote in message
>> news:c8YHg.45$8Q6.5_at_trndny01...
>> >
>> > "Brian Selzer" <brian_at_selzer-software.com> wrote in message
>> > news:vYJHg.17542$gY6.10049_at_newssvr11.news.prodigy.com...
>> >> >> > A small digression: all this reminds me of the (in)famous SQL
>> >> > construct:
>> >> >> >
>> >> >> > UPDATE .... WHERE CURRENT of <cursor>
>> >> >>
>> >> >> That's probably because it hasn't clicked yet that a transition is
>> >> >> a
>> >> >> *set*
>> >> >> of triples, where each element represents only a distinct component
> of
>> >> >> the
>> >> >> overall difference between the current database state and a
> /possible
>> >> >> state/. The above construct involves something that passes over a
>> > result
>> >> >> set in a particular sequence. That's a totally different thing
>> >> > altogether.
>> >> >>
>> >> >
>> >> > I'm well aware that you are talking about a set of transitions.
>> >> > Your
>> >> > professional history of dealing with bozos has conditioned you into
>> >> > a
>> >> > habit
>> >> > of condescension that is out of place in this newsgroup.
>> >> >
>> >>
>> >> I ASS-U-MEd that you were confusing triples with transitions because
>> >> of
>> > the
>> >> digression below. I was wrong, and I apologize. I did not intend to
> be
>> >> condescending.
>> >>
>> >
>> > OK. No harm done. The subject of UPDATE ... WHERE CURRENT is worth a
>> > side
>> > discussion here, because
>> > it blurs the distinction between content based addressing and location
>> > based
>> > addressing. The CURRENT row of a result table is like "the can of
>> > Campbell's chicken noodle soup that I have in my left hand", although
> at
>> > a
>> > different level of abstraction.
>>
>> I see your point. On the other hand, since the set of triples in a
>> transition describes the entire difference between two successive
>> database
>> states, it is not exactly the same thing.
>
> Does the set of triples describe the entire difference or does it
> prescribe
> the entire difference?
>

I'm not sure what you're driving at with this question. If you know the one state, and you know what's about to change, then you can determine the next state. From one point of view the set of triples is one description of the differences between one state and the next. From the other it's what the user prescribes by choosing one of those descriptions.

>
>> The idea of location based
>> addressing doesn't have any place in the conceptual or logical models.
>
> Agreed.
>
>> What
>> I'm trying to point out is that how tuples correlate during an update is
>> something that either the database designer must specify, or that the
>> user
>> must specify, or both.
>
> Here's where you're losing me. What does this "correlation" signify?
> Does
> it mean that the old tuple and the new tuple
> both refer to the same item in the universe of discourse (subject
> matter)?

Yes. And because "correlation" is subjective, it should be specified by the database designer or the user.

> Does it mean that the old tuple and the new tuple are both stored in the
> same row of the same table (implementation)?

No. The implementation is irrelevant.

>... Or does it means that the new
> one "replaces" the old one in the sense of overwriting the old one in some
> (part of) a variable? Or something else?
>

I wasn't thinking in terms of variables. A database state is a set of named sets of sets of named values. In other words: it's a value. Given a database state and a description of how the individual components of that state will be different in a proposed database state (a transition), you can derive that proposed database state. Similarly, given a database state and a proposed database state, you can derive a set (possibly infinite) of transitions.

>
>
>> The database designer specifies it with a
>> system-generated surrogate.
>
> would you explain this a little more clearly?
>

Assuming that you have a system-generated surrogate, and assuming that the system can prevent updates to it, then you can use the system-generated value to correlate tuples in one database state with those in another during an update.

>
>> The user specifies it in the WHERE clause. I
>> believe that if the model provided the capability for the user to specify
>> which tuples will be affected, that is, how a specific set of tuples were
>> selected by the user for updating, then a variety of transition
> constraints
>> could be defined that cannot when only using a surrogate.
>
> If a tuple is a value, then you can't update it. If it's a variable, then
> you can't identify it by its contents.
> Which is it?
>

A tuple is a value. So is a relation. So is a database state. Yet Codd included "changes of state" in the types of integrity rules that a data model should include. (D&D also strongly support the idea of transition constraints in TTM, Chapter 4--at least the one that's on the TTM web site--I don't have a copy of the acutal book). Since values can't be updated, either "transition constraint" is meaningless, or a transition must involve something other than just values. Since tuples represent facts that concern things in the universe of discourse, it seems plausible that those things are what Codd, Date and Darwin had in mind when they wrote about allowable changes of state. A transition constraint involves more than one database state, so in order to define one, there must be a way to specify that tuples in one state correspond to tuples in another, while at the same time keeping in mind that a candidate key value within a tuple in one database state that represents a fact concerning something may be different in a tuple in the next database state that represents a fact concerning the same thing.

>
>
>> For example,
>> assume that you were asked to develop a set of job descriptions for every
>> person on the payroll, so you create the following table with two
> candidate
>> keys:
>>
>> ASSIGNED_DUTIES {EMPLOYEE#, DUTY, POSITION#}
>>
>> The candidate keys are {EMPLOYEE#, DUTY} and {POSITION#, DUTY}
>>
>> After conducting interviews of all of the employees and their
>> supervisors,
>> you end up with a complete set of duties and a list of positions from
> which
>> you can create job descriptions. Over time, the duties of an employee
>> may
>> change, which has the effect of altering the duties for the position that
>> the employee is filling. In this way, if someone quits, you know exactly
>> what the company needs to fill the position. Now, consider the following
>> updates:
>>
>> (1) UPDATE ASSIGNED_DUTIES
>> SET DUTY = 'Record labor using barcode terminal'
>> WHERE EMPLOYEE# = 45
>> DUTY = 'Fill out labor ticket'
>>
>> (2) UPDATE ASSIGNED_DUTIES
>> SET DUTY = 'Record labor using barcode terminal'
>> WHERE POSITION# = 22
>> DUTY = 'Fill out labor ticket'
>>
>> (3) UPDATE ASSIGNED_DUTIES
>> SET DUTY = 'Record labor using barcode terminal'
>> WHERE DUTY = 'Fill out labor ticket'
>>
>> The semantics of these updates are different, even though they may affect
>> the same row. In (1) you're changing one of the duties of an employee;
>> in
>> (2) you're changing one of the duties for a position, and in (3) you're
>> making a blanket change. Similarly, consider the following updates:
>>
>> (4) UPDATE ASSIGNED_DUTIES
>> SET EMPLOYEE# = 99
>> WHERE EMPLOYEE# = 45
>>
>> (5) UPDATE ASSIGNED_DUTIES
>> SET EMPLOYEE# = 99
>> WHERE POSITION# = 22
>>
>> From the perspective of (4), you're assigning the duties that employee
>> #45
>> had to employee #99, whereas in (5) you're assigning the duties for
> position
>> #22 to employee #99. It's a subtle difference, I know, but that
> difference
>> can easily be captured if you supply the attributes used to select each
>> tuple in a transition. So instead of a set of triples, you could have a
> set
>> of quadruples,
>>
>> (r, s, t, t')
>>
>> where r is a relation name, s is a set of attribute names that were used
> to
>> select a tuple, t is the tuple selected from the current database state,
> and
>> t' is the corresponding tuple from the proposed state.
>>
>> If this information that is already supplied by the user could be made
>> available, you could define transition constraints based not only on what
> is
>> different on a tuple-by-tuple basis, but also on the perspective of the
> user
>> when they issued the update. In the above example, for (1) you could
>> require documentation from the employee's supervisor; whereas for (2) you
>> could require documentation from the HR manager instead. I realize that
>> this is pushing the limit a bit, and I really haven't given it a whole
>> lot
>> of thought, but I think it could be useful. This information is provided
>> when a user issues an update, so why not make it possible to take
> advantage
>> of it?
>
> In the five updates above, you are updating one or both of the candidate
> keys. Earlier, you said, IIRC, that keys should be immutable. Why isn't
> this a contradiction?
>
What I've been trying to say is that in order to define an enforcible transition constraint, there must be a way to correlate the tuples in the current database state with those in a proposed database state. This means that either the database designer must specify that at least one candidate key will never change by defining an immutable system-generated surrogate that can then be used to correlate tuples or the user must be able to specify how the tuples in one database state correlate to the tuples in the next during an update.

>
>
Received on Mon Aug 28 2006 - 00:31:13 CEST