Re: Relation Schemata vs. Relation Variables

-CELKO- wrote:
> I did a short article on transition constraints in www.DBAzine.com
>
> http://www.dbazine.com/ofinterest/oi-articles/celko35/view?searchterm=Celko%20transition
>
> I examined triggers, CHECK() and DRI actions. The last one seems to be
> the best.
>
> CREATE TABLE StateChanges
> (previous_state VARCHAR(15) NOT NULL,
> current_state VARCHAR(15) NOT NULL,
> PRIMARY KEY (previous_state, current_state));
>
> INSERT INTO StateChanges VALUES ('Grape Juice', 'Grape Juice');
> INSERT INTO StateChanges VALUES ('Grape Juice', 'Wine');
> INSERT INTO StateChanges VALUES ('Wine', 'Vinegar');
> INSERT INTO StateChanges VALUES ('Wine', 'Spoilage');
> INSERT INTO StateChanges VALUES ('Vinegar', 'Spoilage');
> INSERT INTO StateChanges VALUES ('Spoilage', 'Spoilage');
>
> -- 'Grape Juice' is the initial state and a DEFAULT. 'Spoilage' is a
> terminal state.
>
> CREATE TABLE WineCellarChanges
> ( ..
> previous_state VARCHAR(15) DEFAULT 'Grape Juice' NOT NULL,
> current_state VARCHAR(15) DEFAULT 'Grape Juice' NOT NULL,
> FOREIGN KEY (previous_state, current_state)
> REFERENCES StateChanges (previous_state, current_state)
> ON UPDATE CASCADE,
> transition_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> ..);
>
> >> For instance, consider the following states for a relation describing people's marital status, and a transition constraint that says: Single people can't become Divorced: <<
>
> You are wording it in the negative. I would 'single' the initial state
> and 'dead' the (very) terminal state for the transitions. Basically
> the StateChanges table is a directed graph written as an adjacency list
> model.
>
> >> Since a transition that violates a transition constraint can result in the same /possible state/ as a transition that doesn't, the notions of relational assignment and multiple assignment are broken: <<
>
> Using DRI, I think we avoid an illegal transition. I can also use the
> transition_time to keep a history and do a "rollback" if needed.
> Notice that state changes can be temporal -- graph juice becomes wine
> or vinegar in (n) days, so the time stamp can be important.
>
> --CELKO--
Sorry for the delay in posting, my ISP's news server hiccupped, so several posts I sent before didn't make it here.

I looked at your article. I found several issues with it.

The trigger is broken because "FROM 01, N1" is a cross join and the trigger is not a row trigger. If more than one row is
affected, it may reject a transition that should be allowed.

CHECK constraints require that the user be cooperative, for example:

If a row for WineCellarChanges contains (...,'Vinegar', 'Spoilage') and someone issues an update: (...,'Grape Juice', 'Wine') Then obviously, the transition shouldn't be allowed, but the check constraint would pass it.

The same may not be true for DRI constraints in SQL (I haven't really looked hard at this, because the rest of the article is either broken code,
or leaves gaping holes that do not prevent updates that should be prevented.), but this does not apply to the Relational Model, because the
entire set of relations are replaced, so it's possible to issue a relational
or multiple assignment that would circumvent the transition constraint.

If you wouldn't mind a suggestion, perhaps you should make sure that your
code actually does what it says it does before you publish it with your name
on it. Considering the lambasting I've received on this forum just for

using the wrong terminology, I'm really surprised that you included that link at
all. Received on Fri Aug 25 2006 - 07:48:06 CEST