Databases Reference
In-Depth Information
need to ensure that if a certain supplier appears in SP_FROM_TO, then that
same supplier appears in S_FROM_TO as well:
CONSTRAINT AUG_SP_TO_S_FK_AGAIN1
SP_FROM_TO {S#}
Í
S_FROM_TO {S#};
But constraint AUG_SP_TO_S_FK_AGAIN1 is not enough by itself;
we also need to ensure that (even if all desired coalescing of tuples has been
done) if SP_FROM_TO shows some supplier as being able to supply some
part during some interval of time, then S_FROM_TO shows that same sup-
plier as being under contract during that same interval of time. We might try
the following:
CONSTRAINT AUG_SP_TO_S_FK_AGAIN2 /* Warning incorrect! */
IS_EMPTY ((S_FROM_TO RENAME FROM AS SF, TO
AS ST) JOIN
(SP_FROM_TO RENAME FROM AS SPF, TO AS
SPT))
WHERE SPF < SF OR SPT > ST);
As the comment indicates, however, this specification is in fact incorrect.
To
see
why,
let
S_FROM_TO
be
as
shown
in
Table
5.3,
and
let
SP_FROM_TO include a tuple for supplier S2 with, say, FROM
=
d03
and TO
d04. Such an arrangement is clearly consistent, yet constraint
AUG_SP_ TO_S_FK_AGAIN2 as stated actually prohibits it.
We will not try to fix this problem here, deferring it instead to a later
section (Section 5.9). However, we remark as a matter of terminology that
if (as noted earlier) attribute combination {S#, FROM, TO} in relvar
S_FROM_TO is regarded as a temporal candidate key, then attribute
combination {S#, FROM, TO} in relvar SP_FROM_TO might be regarded
as a temporal foreign key (though it is not in fact a foreign key as such).
Again, see Section 5.9 for further discussion.
=
5.3.2.2 Queries (First Temporal Database)
Here now are fully temporal versions of Queries 1.1 and 1.2:
Query 3.1: Get S#-FROM-TO triples for suppliers who have been
able to supply some part at some time, where FROM and TO
together designate a maximal continuous period during which sup-
plier S# was in fact able to supply some part. Note: We use the term
·
