Re: a union is always a join!

On Mar 10, 11:35 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "Tegiri Nenashi" <TegiriNena..._at_gmail.com> wrote in message
>
> news:28078b81-9b60-412d-915c-cbac79fcaf4b_at_l16g2000yqo.googlegroups.com...
>
>
>
>
>
> > On Mar 8, 3:45 pm, "Brian Selzer" <br..._at_selzer-software.com>
> > > During a database update, assuming that streetCrossIds are permanent
> > > identifiers, the relations,
>
> > > TrafficLights- {streetCrossId, state},
> > > TrafficLights% {streetCrossId, state, streetCrossId', state'}, and
> > > TrafficLights+ {streetCrossId, state}
>
> > > would be populated with tuples representing those traffic lights that
> > > have
> > > been removed from service, those traffic lights whose state is now
> > > different, and those traffic lights that have been placed into service
> > > respectively.
>
> > This is not a good example of database update. Why do we care what
> > state traffic light was during maintenance upgrade? I suspect they
> > turn it out before actually unplugging the electrics and removing
> > other mechanical artifacts. Moreover, the state is probably a
> > calculated value, function of time and perhaps some other conditions.
>
> If the state of a traffic light were always a function of time, then the
> constraint could possibly be written as a state constraint, but not all
> traffic lights follow a regular schedule.  Some depend upon traffic
> conditions, others allow for manual intervention. (Those buttons you push to
> get the walk signal come to mind.)
>
> What I thought was obvious apparently isn't: a traffic light that is being
> removed from service would have to be switched off and dismantled for parts
> or relegated to the trash heap; a traffic light that is being placed into
> service would have to be assembled, installed and switched on, and so would
> also have to have an intial state; a traffic light that is neither being
> removed from nor placed into service must have been and must still be in
> service, though its state may or may not have changed.  So the transition
> constraint,
>
> NOT EXISTS TrafficLights%
>   (TrafficLights%.state = green AND TrafficLights%.state' = red)
>
> would apply only to traffic lights that have been and still are in service.
>
> > Any other realistic example of transition constraint?
>
> The transition constraint,
>
> NOT EXISTS TrafficLights+
>   (TrafficLights+.state != red)
>
> would require that whenever a traffic light is placed into service that its
> initial state be red.
>
> > > I think you've misinterpreted my intent. I did not intend that state
> > > constraints be rewritten as transition constraints. I intend instead
> > > that
> > > both state constraints and transition constraints can be specified
> > > declaratively.
>
> > Good luck with that. For transition constraints you'd have to summon
> > automata theory? That is not one of the prettiest subjects of computer
> > science...
>
> I don't think we need to summon automata theory.  A transition only ever
> involves two states: that which has obtained up to but not including the
> instant of change and that which obtains at that instant.  How those states
> and the transition fits into an abstract state machine is incidental.  For
> any implementation to support transition constraints, however, requires both
> a syntactic and semantic definition of what constitutes a state and what
> constitutes a transition.
>
> A state is simply a database--a collection of relations that together
> represent and by extension assert just what things have been in the world
> and exactly how those things have been related to each other.  A transition
> is also a collection of relations, specifically three relations for each
> relation in the database, that together represent and by extension assert
> what in the world is different and exactly how, or more specifically, that
> which has been in the world but no longer is, that which is still in the
> world but appears different, and that which hadn't been in the world but now
> is.
>
> For example, if there are just relations P{A, B, C}, Q{A, D} and R{A, E} in
> the database, then a proposed transition will consist of the following
> relations
>
> P-{A, B, C} -- tuples to be "deleted" from P
> P%{A, B, C, A', B', C'} -- tuples in P to be "updated" with new components
> P+{A, B, C} -- tuples to be "inserted" into p
> Q-{A, D} -- tuples to be "deleted" from Q
> Q%{A, D, A', D'} -- tuples in Q to be "updated" with new components
> Q+{A, D} -- tuples to be "inserted" into Q
> R-{A, E} -- tuples to be "deleted" from R
> R%{A, E, A', E'} -- tuples in R to be "updated" with new components
> R+{A, E} -- tuples to be "inserted" into R
>
> One or more or even all of these relations may contain tuples during a
> database update.  Just not none.  The proposed relations are:
>
> P'{A, B, C} =
>     (P MINUS (P- UNION P%{A, B, C}))
>     UNION (P%{A', B', C'} RENAME {A' AS A, B' AS B, C' AS C'})
>     UNION P+
> Q'{A, D} =
>     (Q MINUS (Q- UNION Q%{A, D}))
>     UNION (Q%{A', D'} RENAME {A' AS A, D' AS D})
>     UNION Q+
> R'{A, E} =
>     (R MINUS (R- UNION R%{A, E}))
>     UNION (R%{A', E'} RENAME {A' AS A, E' AS E})
>     UNION R+
>
> During a database update, and only during a database update, the relations
>
> P, P-, P%, P+, P', Q, Q-, Q%, Q+, Q', R, R-, R%, R+, R'
>
> contain the current database (P, Q, R),
> the transition (P-, P%, P+, Q-, Q%, Q+, R-, R%, R+),
> and the proposed database (P', Q', R').
>
> Since constraints only need to be checked during a database update, they can
> reference any of the above relations because it is only during a database
> update that they are all populated with data.

Brian,

I have a major objection to your "transition-constraints".

It is my understanding that databases are models of the real world, and database values are collections of assertions (tuples) that fit the database model (relations). At any given time, a database value may contain assertions which are factually incorrect. When this is detected, database users need to correct the database value by updating/inserting/deleting appropritate tuples. Modifying a database value from V1 to V2 is ONLY asserting that V1 is incorrect, and V2 is correct. Nothing more.

Modifying a database value from V1 to V2 is NOT the same as 1) asserting that V1 was a fully correct description of the real world at some earlier time,
2) V2 is a correct description of the real world at present, and 3) that the real-world transitioned directly from state V1 to V2 (e.g. no other database value V3 exists such that V3 correctly described the real world after V1 and before V2).

However (and please identify/correct the straw-man here if it is one), it seems like this is precisely how you think a modification of a database value from V1 to V2 should be interpretted. I'll try to use your example to illuminate how absurd this is:

Let us assume that we need to model the predicate "The traffic light at <cross_road> is <color>". I'll leave aside any attempt to find any use for such a predicate, but let's assume this is all the information our client cares to store about traffic lights at cross-roads.

We model the predicate with the aformentioned TrafficLight relation.

At 1:00 p.m., database user Alice asserts "The traffic light at <1st and Elm> is <Green>"

The database receives :

UPDATE TrafficLights
SET color = 'Green'
WHERE crossroad='1st and Elm';

5 minutes later database user Bob asserts, "The traffic light at <1st and Elm> is <Red>"

The database receives:

UPDATE TrafficLights
SET color = 'Red'
WHERE crossroad='1st and Elm';

Nobody has asserted any proposition that fits "The traffic light at <crossroad> transitioned directly from <color1> to <color2>". Specifically, Bob is NOT asserting "The traffic light at 1st and Elm transitioned directly from green to red".

Perhaps
1) the light had always been red, but Alice is color-blind and mis- reported it as 'Green', or
2) between when Alice and Bob reported its state, it was yellow (but nobody noticed).
3) the light was green or yellow when Bob saw it, but he clicked the wrong dropdown item and asserted 'Red'
Since our databse does not model the real world in a way that allows us to infer 1, 2 or 3 (or any other plausible alternative), it cannot use your "transition constraints" to assume and prevent #3.

Now, consider an alternative: the client actually *cares* about realworld  transitions of traffic lights and wants them to be modeled explicitly. Peraps the client is a traffic department's QC division, and needs to model the predicate, "The traffic light at <crossroad> is transitioning too quickly from <original_color> to <final_color>".

This might be modeled with relation QuickLights {crossroad, original_color, final_color}.

For this model, it may make sense to have constraints about real-world state-transitions, perhaps with a foreign-key of QuickLights {original_color,final_color} to

ValidLightTransitions {

 {original_color='Green', final_color='Yellow'},
 {original_color='Yellow', final_color='Red'},
 {original_color='Red', final_color='Green'}}

Unlike in the first example, this "state-transition-constraint" makes sense because now our database is modeling real-world statetransitions;  and can thus (rightly) forbid Bob from asserting: "The traffic light at <1st and Elm> is transitioning too quickly from <Green> to <Red>".

Thanks,
Kevin Received on Thu Mar 19 2009 - 19:13:00 CET