Re: temporal data constraints
- From: Vadim Tropashko <vadimtro_invalid@xxxxxxxxx>
- Date: 26 Apr 2007 10:37:30 -0700
On Apr 25, 2:05 pm, davis.jeff...@xxxxxxxxx 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 parent-
child 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.
.
- References:
- temporal data constraints
- From: davis . jeffrey
- temporal data constraints
- Prev by Date: Re: Why relational division is so uncommon?
- Next by Date: Re: Why relational division is so uncommon?
- Previous by thread: temporal data constraints
- Next by thread: Newbie question on table design.
- Index(es):
Relevant Pages
|
