Re: temporal data constraints

On Apr 25, 2:05 pm, davis.jeff..._at_gmail.com wrote:
> Now, I'd like to make the relation temporal, in the way that CJ Date
> suggests in "Temporal Data and the Relational Model".
>
> It's already in 6NF. I can create "since" and "during" relations like:
>
> RELVAR R_SINCE { SINCE TIMESTAMP, A INTEGER, B INTEGER } KEY { A }
> RELVAR R_DURING { DURING INTERVAL, A INTEGER, B INTEGER } KEY { A,
> DURING }
> RELVAR S_SINCE { SINCE TIMESTAMP, A INTEGER, C INTEGER } KEY { A }
> RELVAR S_DURING { DURING INTERVAL, A INTEGER, C INTEGER } KEY { A,
> DURING }
>
> That seems like a great solution, but the problem is constraints.
> Every A must have a corresponding B at all times that A itself exists,
> but A may or may not have a C at any given time. I'd like some simple
> constraints to illustrate that in a temporal database the same way I
> represented it in the non-temporal database.

I have a chapter on constraints where the emphasis is on enforcing ANSI SQL style assertions via materialized views. One section is devoted to temporal referential integrity constraints. Criticism is welcome.

Temporal Foreign Key Constraint
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Audit trail is a database design where records are never deleted. All the data modifications are logged in temporal tables. Every record in a temporal table obtains two timestamp attributes: CREATED and DELETED. The values of the other attributes are valid during the interval starting with CREATED date and ending with DELETED date. Now that the same record of values is scattered into many records, how do we enforce constraints? Specifically, given two tables with parentchild  relationship, how do we enforce referential constraint between their "temporalized" versions?

table HistParent (

   id integer,
   ...,
   created date,
   deleted date
);

table HistChild (

   pid integer, -- foreign key to HistParent.id???    ...,
   created date,
   deleted date
);

Let's formulate the constraint, first informally in English, then in SQL. A child record can be created only if its parent record already exists. Likewise, a parent record can't be deleted until it has at least one child. Informally,
A child lifespan must be contained within the parent lifetime The critical issue is defining the parent and child lifetimes.

Since each parent is identified by the id attribute, it's quite easy to define its lifetime. The lifetime of a parent is the longest span of time covered by the chain of [created, deleted] intervals. Now we can invoke the interval coalesce technique from chapter 1, and obtain the parent lifetime view

view ParentLifetime (

   id integer,
   birth date,
   death date
);

Please note that all the attributes marked by ellipsis in the HistParent table are gone. In a way the interval coalesce operation is similar to aggregation, but unlike aggregation, coalesce produces more than one aggregate value.

If we have a set of attributes identifying the child, then we could just define its lifetime the same way we defined the parent's. We don't have to, though! Instead of gluing the smaller [created, deleted] child intervals into the larger [birth, death], we just observe that if each individual [created, deleted] interval is contained in the parent lifetime, so also is the child lifetime.

Now everything is ready for formal constraint expression. The query

select * from HistChild c where not exists

   (select * from ParentLifetime p
    where p.id = c.pid
    and c.created between p.birth and p.death     and c.deleted between p.birth and p.death    )

enumerates all the child records that violate the temporal referential integrity constraint. Therefore, it should be empty. Received on Thu Apr 26 2007 - 19:37:30 CEST