Database Design III : General Constraints
This chapter proposes a general framework for dealing with temporal database design questions. To be specific, it describes a set of requirements that apply to the design of the running example and (it’s claimed) can be used as a template for pinning down the requirements that apply to any temporal database. The chapter then shows how those requirements can be expressed in terms of formal integrity constraints for (a) a database consisting of current relvars only, (b) a database consisting of historical relvars only, and (c) a database consisting of a mixture of current and historical relvars. The chapter also offers some hope for automating the definitions of such constraints.
Keywords
“temporal” integrity constraints; redundancy problem; circumlocution problem; contradiction problem; denseness problem; current relvar; historical relvar
You may be consistent or inconsistent, but you shouldn’t switch all the time between the two.
—Anon.
In Chapter 13, we used the during relvars from our preferred (combination) design of Fig. 13.1—relvar S_STATUS_DURING in particular—to illustrate the need for and functionality of the PACKED ON and WHEN / THEN constraints, building up to U_key specifications as a shorthand (and in particular to the realization that regular relational key specifications can be regarded as a special case of that shorthand). In the present chapter, we adopt a rather different strategy. To be specific, we stand back for a while from the specific design of Fig. 13.1 (or Fig. 13.4) and consider instead, in very general terms, all of the constraints that might reasonably be expected to hold in a temporal database involving suppliers and shipments.1 Then we consider in some depth what happens to those constraints if the database contains (a) since (“current”) relvars only, (b) during (“historical”) relvars only, or (c) a mixture of both.
Note: Like Chapter 12 (but not Chapter 13), this chapter is concerned, rather more than most of its predecessors, with temporal data specifically. The principal reason for this state of affairs is that, by its very nature, temporal data often has to satisfy certain “denseness” constraints—meaning, loosely, that certain conditions have to be satisfied at every point within certain intervals. For example, if the database shows supplier S1 as having been under contract ever since day 4, then it must also show supplier S1 as having had some status ever since day 4, and vice versa. Such constraints don’t necessarily apply to other kinds of data for which the concepts of previous chapters, such as U_keys, do apply.
We should also warn you that certain portions of this chapter might seem a little difficult, especially on a first reading. Unfortunately, the difficulties in question seem to be intrinsic. However, we do offer, in the section “Syntactic Shorthands,” a few suggestions as to how it might be possible to conceal some of those difficulties from the user. Then, in the final section, we briefly discuss a few miscellaneous issues.
A note on terminology: In order to avoid confusion, in what follows we’ll refer to the natural language versions of the constraints we want to consider as requirements, and reserve the term constraint to mean the formulation of such a requirement in a formal language such as Tutorial D. We note in passing that some though not all of the requirements we’ll be discussing are in fact implied by the predicates for the pertinent relvars.
The Nine Requirements
There are nine requirements we want to consider. They fall nicely into three groups of three. The first three are all of the form “If the database shows a given supplier as being under contract on some given day or pair of consecutive days, some other condition must also be satisfied”:
■ Requirement R1: If the database shows supplier Sx as being under contract on day d, it must contain exactly one tuple that shows that fact.
■ Requirement R2: If the database shows supplier Sx as being under contract on days d and d+1, it must contain exactly one tuple that shows that fact.
■ Requirement R3: If the database shows supplier Sx as being under contract on day d, it must also show supplier Sx as having some status on day d.
Observe that Requirement R1 has to do with avoiding redundancy and Requirement R2 with avoiding circumlocution (see Chapter 13). Requirement R3 has to do with what we referred to in the opening to this chapter as denseness.
The next three requirements are all of the form “If the database shows a given supplier as having a given status on some given day or pair of consecutive days, some other condition must also be satisfied.” They bear a strong family resemblance to Requirements R1-R3, as you’ll immediately see:
■ Requirement R4: If the database shows supplier Sx as having some status on day d, it must contain exactly one tuple that shows that fact.
■ Requirement R5: If the database shows supplier Sx as having the same status on days d and d+1, it must contain exactly one tuple that shows that fact.
■ Requirement R6: If the database shows supplier Sx as having some status on day d, it must also show supplier Sx as being under contract on day d.
Requirement R4 has to do with avoiding redundancy and also contradiction (again, see Chapter 13). Requirement R5 has to do with avoiding circumlocution, and Requirement R6 has to do with denseness. Note that Requirements R3 and R6 are effectively inverses of each other.
The last three requirements are all of the form “If the database shows a given supplier as able to supply a given part on some given day or pair of consecutive days, some other condition must also be satisfied”:
■ Requirement R7: If the database shows supplier Sx as able to supply some specific part Py on day d, it must contain exactly one tuple that shows that fact.
■ Requirement R8: If the database shows supplier Sx as able to supply the same part Py on days d and d+1, it must contain exactly one tuple that shows that fact.
■ Requirement R9: If the database shows supplier Sx as able to supply some part Py on day d, it must also show supplier Sx as being under contract on day d.
Requirement R7 has to do with avoiding redundancy, Requirement R8 has to do with avoiding circumlocution, and Requirement R9 has to do with denseness. Note: In case you were wondering, we could simplify the phrase “some part Py” in Requirement R9 to just “some part” without changing the overall meaning. The explicit reference to Py is there purely to highlight the parallel between Requirement R9 and Requirements R7 and R8.
We offer a few further explanatory comments on these requirements. First of all, note that:
■ Requirement R1 derives in part from the fact that no supplier can be under two distinct contracts at the same time.
■ Requirement R4 derives in part from the fact that no supplier can have two distinct status values at the same time.
■ Requirement R7 derives in part from the fact that no supplier can have two distinct “abilities to supply” the same part at the same time.
Second, note that Requirements R2, R5, and R8 can’t possibly be satisfied, in general, if full vertical decomposition into 6NF hasn’t been done (why not?). Note finally that analogs of the foregoing requirements will apply to any temporal database we might need to deal with—or, at least, so we conjecture. (In this connection, see Exercise 14.6 at the end of the chapter.)
Since Relvars Only
We now consider the case of a temporal database consisting of since relvars only. The database in question—see the outline definitions in Fig. 14.1—consists essentially of the two since relvars from Fig. 13.1 (or Fig. 13.4) in Chapter 13. Note in particular that because those relvars don’t involve any interval attributes, they don’t involve any PACKED ON or WHEN / THEN constraints either (except for trivial ones, not shown). Sample values are shown in Fig. 14.2, a slightly modified version of Fig. 13.2 (the only changes are in the S_SINCE tuple for S4 and the SP_SINCE tuple for S4 and P5).
Of course, this database is only semitemporal—it can’t represent information about the past at all, apart from what might be conveyed by the “since” values. By contrast, however, it certainly can represent future information. To be specific:
■ Both relvars contain implicit information regarding the future—recall the discussion toward the end of the section “Since Relvars Only” in Chapter 12, which said in effect that if (e.g.) some S_SINCE tuple shows supplier Sx as being under contract since day d, then we can infer that supplier Sx was, is, or will be under contract on every day from day d until “the last day” (pending future updates).
■ What’s more, the relvars might contain explicit information regarding the future as well—again, recall that discussion in Chapter 12, which said in effect that (e.g.) the SNO_SINCE value for some supplier Sx might be some future date d, meaning that the indicated supplier will be placed under contract at that future date. Supplier S4 is a case in point in Fig. 14.2 (once again we’re assuming that today is day 10, an assumption we adhere to—where it makes any difference—throughout this chapter).
We now proceed to consider what formal versions of Requirements R1-R9 might look like for this database.
Requirement R1: If the database shows supplier Sx as being under contract on day d, it must contain exactly one tuple that shows that fact.
The KEY constraint on S_SINCE takes care of this requirement. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Both of these tuples show among other things that supplier S1 was under contract on day 7. If they both appeared, therefore, Requirement R1 would be violated.
Requirement R2: If the database shows supplier Sx as being under contract on days d and d+1, it must contain exactly one tuple that shows that fact.
The KEY constraint on S_SINCE takes care of this requirement, too. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R3: If the database shows supplier Sx as being under contract on day d, it must also show supplier Sx as having some status on day d.
Given the semitemporal database of Fig. 14.1, this requirement can’t be enforced. The predicate for S_SINCE (slightly simplified) is:
Supplier SNO has been under contract ever since SNO_SINCE and has had status STATUS ever since STATUS_SINCE.
Thus, it’s perfectly reasonable for S_SINCE to contain a tuple in which the STATUS_SINCE value d’ is greater than the SNO_SINCE value d (see, e.g., the S_SINCE tuple for supplier S1 in Fig. 14.2). And if supplier Sx is the supplier represented by that tuple, then the database shows supplier Sx as having been under contract throughout the interval [d:d'−1] but does not show supplier Sx as having had some status throughout that interval. This latter is (typically) information about the past that can’t be represented in this database.
Following on from the previous point, suppose now that the SNO_SINCE value d, in that tuple for supplier Sx, is a date in the future. Then we might reasonably want to insist that when that tuple is first inserted into the relvar, the STATUS_SINCE value must be d as well; for if it were greater than d, then Requirement R3 would be violated right away, as it were. We might even want to impose a constraint—let’s call it Constraint X—to the effect that the STATUS_SINCE value, in any tuple, must always be equal to the associated SNO_SINCE value if this latter is a date in the future, thus (we assume here support for a niladic operator called TODAY that returns the date today):
CONSTRAINT X IS_EMPTY
( S_SINCE WHERE SNO_SINCE > TODAY ( ) AND STATUS_SINCE ≠ SNO_SINCE ) ;
Requirement R4: If the database shows supplier Sx as having some status on day d, it must contain exactly one tuple that shows that fact.
The KEY constraint on S_SINCE takes care of this requirement also. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Indeed, it might even contain (e.g.) both of the following tuples at the same time:
Requirement R5: If the database shows supplier Sx as having the same status on days d and d+1, it must contain exactly one tuple that shows that fact.
The KEY constraint on S_SINCE takes care of this requirement as well. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R6: If the database shows supplier Sx as having some status on day d, it must also show supplier Sx as being under contract on day d.
We’ve met this requirement before, near the end of the section “Since Relvars Only” in Chapter 12, where we observed that if the SNO_SINCE and STATUS_SINCE values in some S_SINCE tuple are d and d’, respectively, then we must have d’ ≥ d. Here’s the formal statement:
CONSTRAINT SR6 IS_EMPTY ( S_SINCE WHERE STATUS_SINCE < SNO_SINCE ) ;
Without this constraint, relvar S_SINCE might contain (e.g.) the following tuple:
Requirement R7: If the database shows supplier Sx as able to supply some specific part Py on day d, it must contain exactly one tuple that shows that fact.
The KEY constraint on SP_SINCE takes care of this requirement. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R8: If the database shows supplier Sx as able to supply the same part Py on days d and d+1, it must contain exactly one tuple that shows that fact.
The KEY constraint on SP_SINCE takes care of this requirement, too. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R9: If the database shows supplier Sx as able to supply some part Py on day d, it must also show supplier Sx as being under contract on day d.
The foreign key constraint from SP_SINCE to S_SINCE takes care of part of this requirement (“any supplier able to supply some part on some day must be under contract on some day”), but we also need to ensure that the second of these “some days” doesn’t precede the day when the pertinent supplier was placed under contract:
CONSTRAINT SR9 IS_EMPTY ( ( SP_SINCE JOIN S_SINCE ) WHERE SINCE < SNO_SINCE ) ;
(Compare and contrast Constraint XST1 in Chapter 5.) Without this constraint, S_SINCE and SP_SINCE might respectively contain (e.g.) the following tuples at the same time:
Fig. 14.3 is a completed version of Fig. 14.1 (actually the figures are identical, except for the addition in Fig. 14.3 of Constraints SR6 and SR9):
During Relvars Only
Now we turn to the case of a temporal database consisting of during relvars only. The database in question—see the outline definitions in Fig. 14.4—consists essentially of the during relvars from Fig. 13.1 (or Fig. 13.4) in Chapter 13; however, we now show the various PACKED ON and WHEN / THEN constraints that apply to those relvars (we deliberately avoid use of the U_key shorthand for the time being). The database is fully temporal, but horizontal decomposition hasn’t been done.2 Sample values are shown in Fig. 14.5 (a considerably modified version of Fig. 13.3). Note: The relvars in this design can represent past and/or current and/or future information. However, we remind you from Chapter 12 that one disadvantage of such a design is that we have to use an artificial “end of time” value as the end value for any interval where the actual end time is unknown. Supplier S7 is a case in point in Fig. 14.5 (where we’ve assumed for the sake of the example that d99 is “the last day”).
We now proceed to consider what formal versions of Requirements R1-R9 might look like for this database.
Requirement R1: If the database shows supplier Sx as being under contract on day d, it must contain exactly one tuple that shows that fact.
The PACKED ON constraint on S_DURING takes care of this requirement. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Aside: The foregoing claim re PACKED ON notwithstanding, there’s a point here that might be bothering you. The database under discussion contains during relvars only. As noted in Chapter 12, therefore, the value of S_DURING at any given time is equal to the value of the U_projection using DURING of S_STATUS_DURING on SNO and DURING at the time in question;3 in other words, S_DURING is 100 percent redundant in the design under consideration! It follows that if S_STATUS_DURING shows, implicitly, that supplier Sx is under contract on day d, then S_DURING does so too, explicitly, and Requirement R1 is thereby violated. (Analogous remarks apply to Requirement R2 also.) Well, so be it; as also noted in Chapter 12, we still prefer to include S_DURING in our design, partly just for completeness, and partly to avoid a certain degree of awkwardness and arbitrariness that would otherwise occur. In other words, the nine requirements (or those aspects of the nine requirements having to do with redundancy and circumlocution, at any rate) shouldn’t necessarily be seen as inviolable rules, never to be broken; sometimes there might be good reasons not to enforce them. And here’s as good a place as any to point out that redundancy per se isn’t necessarily bad, anyway—it’s bad only if it’s not properly managed. See reference [43] for a detailed discussion of such matters. End of aside.
Requirement R2: If the database shows supplier Sx as being under contract on days d and d+1, it must contain exactly one tuple that shows that fact.
The PACKED ON constraint on S_DURING takes care of this requirement, too. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R3: If the database shows supplier Sx as being under contract on day d, it must also show supplier Sx as having some status on day d.
Suppliers are shown as being under contract in S_DURING and as having some status in S_STATUS_DURING. Requirement R3 implies that every tuple pairing supplier Sx with day d in the unpacked form of S_DURING must also appear in the unpacked form of the projection of S_STATUS_DURING on SNO and DURING (both unpackings being done on DURING, of course). Hence the following constraint must hold:
CONSTRAINT DR3 USING ( DURING ) : S_DURING ⊆ S_STATUS_DURING { SNO , DURING } ;Without this constraint, S_DURING might contain, e.g., just the tuple for supplier S7 shown on the left below, while at the same time S_STATUS_DURING contains, e.g., just the tuple for supplier S7 shown on the right below:
As we saw in the previous chapter, however, to say Constraint DR3 holds is to say a certain foreign U_key constraint is in effect. In other words, we could if we like replace that constraint by the following specification:
USING ( DURING ) : FOREIGN KEY { SNO , DURING } REFERENCES S_STATUS_DURING(part of the definition of relvar S_DURING).
We’ll have more to say regarding Requirement R3 and Constraint DR3 (or its foreign U_key equivalent) when we discuss Requirement R6, later in this section.
Requirement R4: If the database shows supplier Sx as having some status on day d, it must contain exactly one tuple that shows that fact.
The PACKED ON and WHEN / THEN constraints on S_STATUS_DURING take care of this requirement. Without PACKED ON, that relvar might contain (e.g.) both of the following tuples at the same time:
And without WHEN / THEN, the relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R5: If the database shows supplier Sx as having the same status on days d and d+1, it must contain exactly one tuple that shows that fact.
The PACKED ON constraint on S_STATUS_DURING takes care of this requirement as well. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R6: If the database shows supplier Sx as having some status on day d, it must also show supplier Sx as being under contract on day d.
As noted in the section “The Nine Requirements,” Requirement R6 is effectively the inverse of Requirement R3. Thus, the following specification takes care of matters:
USING ( DURING ) : FOREIGN KEY { SNO , DURING } REFERENCES S_DURING(part of the definition of relvar S_STATUS_DURING). Without this constraint, S_DURING might contain, e.g., just the tuple for supplier S7 shown on the left below, while at the same time S_STATUS_DURING contains, e.g., just the tuple for supplier S7 shown on the right below:
So S_DURING and S_STATUS_DURING each have a foreign U_key referencing the other.
Alternatively, we could define an explicit constraint (DR6), identical to Constraint DR3 but with a “⊇” operator in place of the “⊆” operator, thus:
CONSTRAINT DR6 USING ( DURING ) : S_DURING ⊇ S_STATUS_DURING { SNO , DURING } ;And then we could combine the two explicit constraints like this:
CONSTRAINT DR3 6 USING ( DURING ) : S_DURING = S_STATUS_DURING { SNO , DURING } ;This last (Constraint DR36) is an example of what might be called a U_equality dependency. (Equality dependencies as such—i.e., without that “U_”—are discussed in reference [43].)
Note: Constraint DR6 implies that when information regarding a new contract is first entered into the database, a tuple to say the pertinent supplier is under contract must be inserted into S_DURING and a tuple to say the supplier has some particular status must be inserted into S_STATUS_DURING (see the discussion of multiple assignment in Chapter 16). What’s more, the DURING values in those two tuples must be the same; the begin point must be the date when the supplier was or will be placed under contract, and the end point will be the corresponding date of termination or—perhaps more likely—an artificial “end of time” marker.
Requirement R7: If the database shows supplier Sx as able to supply some specific part Py on day d, it must contain exactly one tuple that shows that fact.
The PACKED ON constraint on SP_DURING takes care of this requirement. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R8: If the database shows supplier Sx as able to supply the same part Py on days d and d+1, it must contain exactly one tuple that shows that fact.
The PACKED ON constraint on SP_DURING takes care of this requirement, too. Without it, that relvar might contain (e.g.) both of the following tuples at the same time:
Requirement R9: If the database shows supplier Sx as able to supply some part Py on day d, it must also show supplier Sx as being under contract on day d.
The foreign U_key constraint
USING ( DURING ) : FOREIGN KEY { SNO , DURING } REFERENCES S_DURING—part of the definition of SP_DURING—takes care of this requirement (compare and contrast Constraint XFT3 in Chapter 5). Without this constraint, SP_DURING might contain the tuple for supplier S7 shown on the right below, while at the same time S_DURING contains the tuple for supplier S7 shown on the left below:
To complete this section, Fig. 14.6 shows a revised version of the original design from Fig. 14.4. Observe that we do now make use of the U_key and foreign U_key shorthands. Observe too, more interestingly, that the figures (Figs. 14.4 and 14.6, that is) are essentially identical!—the differences between them are merely cosmetic. In other words, in the case of a design consisting of during relvars only, the U_key and foreign U_key constraints are together sufficient, in and of themselves, to take care of the nine requirements.4
Both Since and During Relvars
Now we turn to the case of a temporal database consisting of a mixture of since and during relvars (refer to Fig. 14.7):
Fig. 14.7 is basically a repeat of Fig. 13.4 from Chapter 13, except that we now show all applicable PACKED ON and WHEN / THEN constraints explicitly (as in the previous section, we deliberately avoid use of the U_key shorthand for the time being). Sample values (copied from Figs. 13.2 and 13.3) are shown in Fig. 14.8. Note in particular that all of the DURING values in that figure are genuine values; we no longer need those artificial “end of time” markers that we had to use in the “during relvars only” design in the previous section.
We now proceed to consider what formal versions of Requirements R1-R9 might look like for this database. It’s only fair to warn you, however, that some of those requirements are going to be a little more awkward to deal with than they were previously, precisely because the corresponding constraints are going to have to refer to since and during relvars taken in combination.
Requirement R1: If the database shows supplier Sx as being under contract on day d, it must contain exactly one tuple that shows that fact.
As noted in the section “The Nine Requirements,” Requirement R1 has to do with avoiding redundancy—specifically, redundancy within or across S_SINCE and S_DURING, given the database of Fig. 14.7. Now, the KEY constraint on S_SINCE guarantees that S_SINCE by itself can’t violate the requirement, and the PACKED ON constraint on S_DURING guarantees that S_DURING by itself can’t violate it, either (as indeed we saw in the previous two sections). What we need, therefore, is an additional constraint to ensure that those two relvars don’t both show supplier Sx as being under contract on the same day:
CONSTRAINT BR1 IS_EMPTY ( ( S_SINCE JOIN S_DURING ) WHERE SNO_SINCE < POST ( DURING ) ) ;
Without this constraint, S_SINCE and S_DURING might respectively contain (e.g.) the following tuples at the same time:
Both of these tuples show among other things that supplier S1 was under contract on day 7.5
Requirement R2: If the database shows supplier Sx as being under contract on days d and d+1, it must contain exactly one tuple that shows that fact.
Requirement R2 has to do with avoiding circumlocution, within and across S_SINCE and S_DURING, and the analysis that follows thus parallels, somewhat, the one just given for Requirement R1. First, the KEY constraint on S_SINCE guarantees that S_SINCE by itself can’t violate the requirement, and the PACKED ON constraint on S_DURING guarantees that S_DURING by itself can’t violate it, either. What we need, therefore, is an additional constraint to ensure that if S_SINCE shows supplier Sx as being under contract since day d, then S_DURING doesn’t show supplier Sx as being under contract on the day immediately before that day d:
CONSTRAINT BR2 IS_EMPTY ( ( S_SINCE JOIN S_DURING ) WHERE SNO_SINCE = POST ( DURING ) ) ;
Without this constraint, S_SINCE and S_DURING might respectively contain (e.g.) the following tuples at the same time:
We now observe that Constraints BR1 and BR2 obviously can, and probably should, be combined into a single constraint as follows:
CONSTRAINT BR12 IS_EMPTY ( ( S_SINCE JOIN S_DURING ) WHERE SNO_SINCE ≤ POST ( DURING ) ) ;
Requirement R3: If the database shows supplier Sx as being under contract on day d, it must also show supplier Sx as having some status on day d.
This requirement has to do with denseness. Now, the relvars that show a given supplier as being under contract on a given day are S_SINCE and S_DURING; while the relvars that show a given supplier as having a given status on a given day are S_SINCE (again) and S_STATUS_DURING. Thus, the easiest way to think about Requirement R3 in this context is as follows—and this kind of thinking is going to apply to certain of the other requirements too (to Requirements R6 and R9, to be specific):
■ With respect to the property of being under contract, imagine information from S_SINCE and S_DURING being combined in a single relvar S_DURING’ (that relvar will look just like S_DURING in the “during relvars only” case).
■ With respect to the property of having some status, imagine information from S_SINCE and S_STATUS_DURING being combined in a single relvar S_STATUS_DURING’ (that relvar will look just like S_STATUS_DURING in the “during relvars only” case).
■ Then there’ll be a foreign U_key from S_DURING’ to S_STATUS_DURING’ that says that every {SNO,DURING} value appearing in the unpacked form of S_DURING’ on DURING must also appear in the unpacked form of S_STATUS_DURING’ on DURING.
So here’s a possible formulation:6
CONSTRAINT BR3
WITH ( t1 := EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ SNO_SINCE : LAST_DATE ( ) ] ) } , t2 := t1 { SNO , DURING } ,t3 := t2 UNION S_DURING ,
t4 := EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ STATUS_SINCE : LAST_DATE ( ) ] ) } , t5 := t4 { SNO , STATUS , DURING } ,t6 := t5 UNION S_STATUS_DURING ,
t7 := t6 { SNO , DURING } ) :USING ( DURING ) : t3 ⊆ t7 ;
Without this constraint, it would be possible for S_SINCE to contain (e.g.) the tuple shown on the left below, while at the same time the unpacked form of S_STATUS_DURING on DURING did not contain tuples of the form shown on the right below:
Incidentally, the (unspecified) STATUS value st5 in the tuple for supplier S1 and interval [d05:d05] here can’t possibly be 20, thanks to Constraint BR5 (see later).
There are several further points to be made in connection with Requirement R3 and Constraint BR3. First of all, suppose information regarding some specific contract is represented in S_SINCE and not S_DURING (in other words, we’re talking about a current or future contract that has no history yet). Then Requirement R3 implies that the SNO_SINCE and STATUS_SINCE values in the pertinent S_SINCE tuple must be equal.
Second, Requirement R3 also implies that every supplier number appearing in S_DURING must also appear in S_STATUS_DURING (if a supplier has some contract history, that supplier must certainly have some status history):7
CONSTRAINT BR3X S_DURING { SNO } ⊆ S_STATUS_DURING { SNO } ;Constraint BR3X is an example of an inclusion dependency [10]. We mentioned such dependencies in passing in Chapter 5; as noted in that chapter, they can be regarded as a generalization of referential constraints. Some syntactic shorthand for expressing them might be useful in practice.
Third, although (as we’ve just said) every supplier number appearing in S_DURING must also appear in S_STATUS_DURING, note carefully that the converse is false. To be specific, suppose there’s some supplier who (a) is currently under contract, (b) has never previously had a contract, and (c) has changed status since coming under contract. Then that supplier will be represented in S_STATUS_DURING but not in S_DURING. And so now we’ve taken care of a small piece of unfinished business from Chapter 12, where we said that S_DURING was strictly redundant in the design consisting of during relvars only but not in the combination design.8 Well, now we can see why this is so. To be specific, S_DURING isn’t guaranteed in the latter case to be such that its value is always equal to the U_projection, using DURING, of the value of S_STATUS_DURING on SNO and DURING at the time in question.
Fourth, observe that Constraint BR3 as stated contains two interval selector invocations in which the end time is specified as “the last day”—note the LAST_DATE ( ) invocations—and then goes on to ask (in effect) for certain relations containing such intervals to be unpacked. Naturally, we hope the implementation will be smart enough not to materialize the unpacked relations in question! See Appendix E for further discussion of such matters.
Finally, to jump ahead of ourselves for a moment: As noted a couple of times previously in this chapter, Requirement R6 is effectively the inverse of Requirement R3. As a consequence, a suitable formulation of the constraint (“Constraint BR6”) needed to take care of Requirement R6 can be obtained from Constraint BR3 by simply replacing the “⊆” operator in the last line by a “⊇” operator. However, it obviously makes sense to combine the two constraints by using “=” instead, as follows (note the revised constraint name):
CONSTRAINT BR36
WITH ( t1 := EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ SNO_SINCE : LAST_DATE ( ) ] ) } , t2 := t1 { SNO , DURING } ,t3 := t2 UNION S_DURING ,
t4 := EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ STATUS_SINCE : LAST_DATE ( ) ] ) } , t5 := t4 { SNO , STATUS , DURING } ,t6 := t5 UNION S_STATUS_DURING ,
t7 := t6 { SNO , DURING } ) :USING ( DURING ) : t3 = t7 ;
Requirement R4: If the database shows supplier Sx as having some status on day d, it must contain exactly one tuple that shows that fact.
Requirement R4 has to do with avoiding redundancy and contradiction, within or across S_SINCE and S_STATUS_DURING. The KEY constraint on S_SINCE and the PACKED ON and WHEN / THEN constraints on S_STATUS_DURING are sufficient to guarantee that neither of those relvars can violate this requirement by itself. But we need to add:
CONSTRAINT BR4 IS_EMPTY ( ( S_SINCE JOIN S_STATUS_DURING { SNO , DURING } ) WHERE STATUS_SINCE < POST ( DURING ) ) ;Without this constraint, S_SINCE and S_STATUS_DURING might respectively contain (e.g.) the following tuples at the same time:
They might even contain (e.g.) the following tuples at the same time:
Requirement R5: If the database shows supplier Sx as having the same status on days d and d+1, it must contain exactly one tuple that shows that fact.
Requirement R5 has to do with avoiding circumlocution, within and across S_SINCE and S_STATUS_DURING. Again the KEY constraint on S_SINCE and the PACKED ON constraint on S_STATUS_DURING are relevant. In addition, we need:
CONSTRAINT BR5 IS_EMPTY ( ( S_SINCE JOIN S_STATUS_DURING ) WHERE STATUS_SINCE = POST ( DURING ) ) ;
Without this constraint, S_SINCE and S_STATUS_DURING might respectively contain (e.g.) the following tuples at the same time:
Exercise: As we saw earlier, it made sense to combine Constraints BR1 and BR2 into a single constraint, BR12; so why don’t we do something similar with constraints BR4 and BR5?
Requirement R6: If the database shows supplier Sx as having some status on day d, it must also show supplier Sx as being under contract on day d.
This requirement, which has to do with denseness, has already been taken care of under the discussion of Requirement R3 above. However, we give an example of a violation of the requirement that could occur if Constraint BR36 weren’t in effect. Without that constraint, S_STATUS_DURING might contain (e.g.) the following tuple—
—while at the same time neither S_SINCE nor S_DURING contains a tuple showing supplier S1 as being under contract on day 4.
Observe, incidentally, that (contrary to what might have intuitively been expected):
■ It’s not the case that {SNO,DURING} in S_STATUS_DURING is a foreign U_key referencing {SNO,DURING} in S_DURING. (In fact we effectively already know this—see the discussion under Requirement R3 above—but the point bears repeating.)
■ It’s not even the case that every SNO value appearing in S_STATUS_DURING must also appear in S_DURING. (In fact we know this too—again, see the discussion under Requirement R3 above.) However, it’s at least true that every SNO value appearing in S_STATUS_DURING must also appear in either S_DURING or S_SINCE, and possibly in both.
Both of these points are illustrated by the sample values in Fig. 14.8.
Requirement R7: If the database shows supplier Sx as able to supply some specific part Py on day d, it must contain exactly one tuple that shows that fact.
Requirement R7 has to do with avoiding redundancy, within or across SP_SINCE and SP_DURING. The KEY constraint on SP_SINCE and the PACKED ON constraint on SP_DURING are sufficient to guarantee that neither of those relvars can violate this requirement by itself. But we need to add:
CONSTRAINT BR7 IS_EMPTY ( ( SP_SINCE JOIN SP_DURING ) WHERE SINCE < POST ( DURING ) ) ;
Without this constraint, SP_SINCE and SP_DURING might respectively contain (e.g.) the following tuples at the same time:
Requirement R8: If the database shows supplier Sx as able to supply the same part Py on days d and d+1, it must contain exactly one tuple that shows that fact.
Requirement R8 has to do with avoiding circumlocution, within or across SP_SINCE and SP_DURING. Again the KEY constraint on SP_SINCE and the PACKED ON constraint on SP_DURING are relevant. In addition, we need:
CONSTRAINT BR8 IS_EMPTY ( ( SP_SINCE JOIN SP_DURING ) WHERE SINCE = POST ( DURING ) ) ;
Without this constraint, SP_SINCE and SP_DURING might respectively contain (e.g.) the following tuples at the same time:
Constraints BR7 and BR8 can be combined as follows:
CONSTRAINT BR7 8 IS_EMPTY ( ( SP_SINCE JOIN SP_DURING ) WHERE SINCE ≤ POST ( DURING ) ) ;
Requirement R9: If the database shows supplier Sx as able to supply some part Py on day d, it must also show supplier Sx as being under contract on day d.
This requirement has to do with denseness. In fact, it’s quite similar in structure to Requirement R6, which in turn was somewhat similar to Requirement R3. We therefore skip the detailed analysis here and simply state the constraint:
CONSTRAINT BR9
WITH ( t1 := EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ SNO_SINCE : LAST_DATE ( ) ] ) } , t2 := t1 { SNO , DURING } ,t3 := t2 UNION S_DURING ,
t4 := EXTEND SP_SINCE : { DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] ) } , t5 := t4 { SNO , DURING } , t6 := SP_DURING { SNO , DURING } ,t7 := t5 UNION t6 ) :
USING ( DURING ) : t3 ⊇ t7 ;
Without this constraint, SP_DURING might contain (e.g.) the following tuple—
—while at the same time neither S_SINCE nor S_DURING contains a tuple showing supplier S1 as being under contract on day 4.
To complete this section, Fig. 14.9 shows a revised version of the original design from Fig. 14.7. Observe that we do now make use of the “U_key” shorthand.
Syntactic Shorthands
From the discussions and examples in this chapter so far, it certainly seems that the design with both since and during relvars gives rise to the most complicated constraints. Nevertheless, we still regard that design as our preferred one in general. In this section, therefore, we make good on our promise from the beginning of the chapter (our promise, that is, to try to conceal some of that complication from the user) by looking for ways of making it a little easier to specify all of the constraints that seem to be needed with that particular design.
We begin by observing that—as is well known, of course—the KEY and FOREIGN KEY specifications used in regular (i.e., nontemporal) relvar definitions are essentially just shorthand for constraints that can be expressed, albeit more longwindedly, using the general “constraint language” portion of any relationally complete language such as Tutorial D. However, the shorthands in question are extremely useful ones: Quite apart from the fact that they save us a lot of writing, they also effectively serve to raise the level of abstraction, by allowing us to talk in terms of certain “bundles” of concepts that seem to fit together very naturally. (As a bonus, they also pave the way for more efficient implementation.) And it’s our belief that analogous shorthands can be defined to provide similar benefits in the temporal case, as we now show.
Before we get into details, however, we should make it clear that we’re far more concerned in this book with getting the foundations right than we are with purely syntactic matters. Thus, the remarks in what follows should be seen mainly as “notes toward” the kind of shorthands we believe ought to be feasible in practice. Certainly the concrete syntax needs further work.
Now, it’s indeed the case that we can observe some abstract structure in Fig. 14.9 that looks as if it would be applicable to temporal databases in general. To be specific, we can make the following observations regarding the design of Fig. 14.9:
1. Each of the since (“current”) relvars concerns certain “entities” and specifies certain “properties” of those entities.
2. Within each such since relvar, the entities are identified by a set K of key attributes and the properties are specified by other attributes (as usual).9 Some of those since relvars also have foreign keys that reference other since relvars (again as usual).
3. Within each such since relvar, each property has an associated “since” attribute, and so does the key. Within any given tuple, no “since” attribute has a value less than that of the “since” attribute associated with the key.
4. Given any specific since relvar, each property also has an associated during (“historical”) relvar, and so does the key. Each such during relvar consists of:
a. A set of attributes K corresponding to the key of the pertinent since relvar
b. Attribute(s) corresponding to the pertinent property (except for the during relvar associated with the key of the since relvar, to which this paragraph b. doesn’t apply)
5. Each of those during relvars is subject to the constraint that the combination of K and DURING is a U_key.
6. Each combination of a property (or the key) in a since relvar together with the corresponding during relvar is subject to certain constraints that are implied by Requirements R1-R9 (or by whatever analogs of those requirements apply to the temporal database we happen to be dealing with), and the general form of those constraints is as illustrated in Fig. 14.9.
We therefore propose a set of syntactic extensions along the following lines.
■ First of all, we propose some syntax for specifying that, within a given since relvar, some specified attribute B is the “since” attribute corresponding to some specified set of attributes A. For example (note the highlighted text):
VAR S_SINCE BASE RELATION
{ SNO SNO ,
SNO_SINCE DATE SINCE_FOR { SNO },
STATUS INTEGER ,
STATUS_SINCE DATE SINCE_FOR { STATUS } }
KEY { SNO } ;
VAR SP_SINCE BASE RELATION
{ SNO SNO ,
PNO PNO ,
SINCE DATE SINCE_FOR { SNO , PNO } }
KEY { SNO , PNO }
FOREIGN KEY { SNO } REFERENCES S_SINCE ;
Now the system knows that SNO_SINCE and STATUS_SINCE are the “since” attributes for {SNO} and {STATUS}, respectively, in relvar S_SINCE, and SINCE is the “since” attribute for {SNO,PNO} in relvar SP_SINCE. It also knows for each of those relvars which “since” attribute is associated with the key, and in the case of S_SINCE it knows that the constraint
IS EMPTY ( S_SINCE WHERE STATUS_SINCE < SNO_SINCE )
is required to hold.
■ Next, we propose some syntax for specifying the during relvar corresponding to a given “since” attribute. For example (again note the highlighted text):
VAR S_SINCE BASE RELATION
{ SNO SNO ,
SNO_SINCE DATE SINCE_FOR { SNO } HISTORY_IN ( S_DURING ),
STATUS INTEGER,
STATUS_SINCE DATE SINCE_FOR { STATUS } HISTORY_IN ( S_STATUS_DURING ) }
KEY { SNO } ;
VAR SP_SINCE BASE RELATION
{ SNO SNO ,
PNO PNO,
SINCE DATE SINCE_FOR { SNO , PNO } HISTORY_IN ( SP_DURING ) }
KEY { SNO , PNO }
FOREIGN KEY { SNO } REFERENCES S_SINCE ;
Now the system knows that relvars called S_DURING, S_STATUS_DURING, and SP_DURING must be defined. In fact, those definitions might even be automated (since the system certainly knows what their structure must be), but for explanatory purposes we show them explicitly here:
VAR S_DURING BASE RELATION
{ SNO SNO ,
DURING INTERVAL_DATE }
USING ( DURING ) : KEY { SNO , DURING } ;
VAR S_STATUS_DURING BASE RELATION
{ SNO SNO ,
STATUS INTEGER,
DURING INTERVAL_DATE }
USING ( DURING ) : KEY { SNO , DURING } ;
VAR SP_DURING BASE RELATION
{ SNO SNO ,
PNO PNO,
DURING INTERVAL_DATE }
USING ( DURING ) : KEY { SNO , PNO , DURING } ;
We conjecture that the foregoing specifications taken together should be sufficient for the system to infer Constraints BR12, BR36, BR3X, BR4, BR5, BR78, and BR9 for itself, thereby avoiding any need for those constraints to be stated explicitly.10
Concluding Remarks
We conclude this chapter with a few miscellaneous observations.
■ In conventional databases, relvars are sometimes “dropped” (meaning the relvar in question is deleted entirely, and there’s now no information regarding the relvar in question in the database catalog). In the temporal context, however, it seems unlikely that a during relvar would ever be dropped, since the whole point of the database is to maintain historical records.11 By contrast, a since relvar might perhaps be dropped, but it would probably be necessary to move all of the information it contains to appropriate during relvars first. For obvious reasons, moreover, dropping either kind of relvar is likely to require a lot of revision to existing constraints.
■ Another operation that’s sometimes performed in conventional databases is that of adding a new attribute to an existing relvar. In a temporal database, adding a new attribute to a during relvar seems to make little sense, because—given our recommendations regarding sixth normal form, at any rate—each such relvar represents the history of just one “property,” more or less by definition. By contrast, adding a new attribute to a since relvar might make sense, but (a) the new attribute would probably need an accompanying new “since” attribute, and (b) it would probably need an accompanying new during relvar as well. New constraints would also be required.
■ Third, we note that constraints in general do change over time (though the particular kinds of constraints we’ve been discussing in this chapter are perhaps less susceptible to change than most). As a consequence, a temporal database might contain data that satisfies some constraint that was in effect when the data was entered into the database but fails to satisfy some revised version of that constraint that’s in effect now. One implication is that constraints themselves might need to include temporal components (“valid times,” in fact—see Chapters 4 and 17). A further and possibly more serious implication is that the database catalog might itself need to be treated as a temporal database. However, further discussion of such possibilities is beyond the scope of this book.
Finally, we began this chapter by describing the nine requirements, all of which stated in effect that certain conditions must hold in any temporal database involving suppliers and shipments. Subsequently, however, we saw that it might sometimes be acceptable to violate certain of those requirements after all (see the discussion of Requirement R1 in the section “During Relvars Only”). Thus, where the requirements generally say that some condition must be satisfied, it might perhaps be better if they said that, other things being equal, the condition in question should preferably be satisfied. At any rate they should generally be understood in this light.
Exercises
14.1 What’s a denseness constraint?
14.2 We said in the section “The Nine Requirements” that Requirements R2, R5, and R8 can’t possibly be satisfied, in general, if full vertical decomposition into 6NF hasn’t been done. Why not?
14.3 In the design consisting of during relvars only, S_DURING is such that its value at any given time is equal to the value of the U_projection, using DURING, of S_STATUS_DURING on {SNO,DURING} at that same time. So shouldn’t a constraint be stated to that effect?
14.4 (Repeated from the body of the chapter.) With reference to the section “Both Since and During Relvars,” we said it’s a logical consequence of Requirement R3 that every supplier number appearing in S_DURING must also appear in S_STATUS_DURING (see Constraint BR3X). But we also pointed out that the same was obviously true with the “during relvars only” design; so why didn’t we mention the point when we were discussing this latter design?
14.5 With reference to Fig. 14.9:
a. Why isn’t {SNO,DURING} in SP_DURING defined to be a foreign U_key (using DURING) referencing S_DURING?
b. Among other things, Requirement R3 clearly implies that if t is a tuple in S_SINCE, and if the SNO_SINCE and STATUS_SINCE values in t are d and d’, respectively, then it must be the case that d ≤ d’. Does Constraint BR36 take care of this requirement?
c. (Repeated from the body of the chapter.) We saw it made sense to combine Constraints BR1 and BR2 into a single constraint (BR12), so why didn’t we do something similar with constraints BR4 and BR5?
14.6 State analogs of the nine requirements in a form (natural language only) that applies to the courses-and-students database from Exercise 13.7 in Chapter 13.
14.7 Given your answer to Exercise 14.6:
a. Show the corresponding formal constraints.
b. What SINCE_FOR and HISTORY_IN specifications, if any, would you add to the database definition?
14.8 The database definition from Exercise 13.7 in Chapter 13 really needs to be extended still further to allow us to record, in connection with an enrollment, the particular offering to which that enrollment is assigned. State whatever additional natural language requirements you can think of that might arise in connection with such an extension. Also make the corresponding changes to the Tutorial D definition of the database.
14.9 In the body of the chapter we mentioned a niladic operator called TODAY that returns the date today. Care needs to be taken, however, if such an operator is referenced within an integrity constraint. Why?
Answers
14.1 A denseness constraint is a constraint to the effect that some condition c1 must be satisfied throughout every interval throughout which some other condition c2 is satisfied. For example, c1 might be “supplier Sx is under contract” and c2 “supplier Sx is able to supply some part.”
14.2 Loosely, because a during relvar that’s not in 6NF will display some redundancy (see, e.g., relvar SSSC_DURING in Chapter 12). A more formal answer follows. First, Requirements R2, R5, and R8 are all requirements to the effect that the database mustn’t contain more than one tuple representing a certain proposition. A fortiori, therefore, no relvar must do so, either. Now, a relvar R that’s not in 6NF is, by definition, one whose predicate can be expressed as a conjunction (AND) of two or more predicates; thus, if t is a tuple in such a relvar R, then t must represent some true proposition of the form p1 AND p2, where p1 and p2 are propositions (p1 ≠ p2). Now let the proposition p1 AND p3 (p2 ≠ p3) also be true and be capable of representation by a tuple t’ that could appear in R. By The Closed World Assumption, then, that tuple t’ must in fact appear in R. Thus, proposition p1 is represented by more than one tuple in the database, thereby violating one of the specified requirements.
14.3 Yes, but it is—either in the form of constraint DR36, q.v., or in the form of the two foreign U_keys, one from each of the relvars to the other.
14.4 Because the constraint in question (a hypothetical “Constraint DR3X”) is subsumed by Constraint DR3.
a. Because a supplier number can appear in SP_DURING without appearing in S_DURING (in other words, a supplier with no contract history can nevertheless have a shipment history). A fortiori, therefore, an {SNO,DURING} value can appear in the unpacked form of SP_DURING on DURING without simultaneously appearing in the unpacked form of S_DURING on DURING.
b. Assuming various other constraints in the figure are also enforced, yes, it does.
c. Because Requirement R4 has to do merely with the supplier having some status value on some day, while Requirement R5 has to do with the supplier having the same status value on consecutive days.
14.6 The courses-and-students analog actually consists of 22 requirements, as follows (note the numbering):
1. If the database shows course Cx as being available on day d, it must contain exactly one tuple that shows that fact.
2. If the database shows course Cx as being available on days d and d+1, it must contain exactly one tuple that shows that fact.
3. If the database shows course Cx as being available on day d, it must also show course Cx as having some name on day d.
4. If the database shows course Cx as having some name on day d, it must contain exactly one tuple that shows that fact.
5. If the database shows course Cx as having the same name on days d and d+1, it must contain exactly one tuple that shows that fact.
6. If the database shows course Cx as having some name on day d, it must also show course Cx as being available on day d.
7. If the database shows student STx as being registered on day d, then it must contain exactly one tuple that shows that fact.
8. If the database shows student STx as being registered on days d and d+1, it must contain exactly one tuple that shows that fact.
9. If the database shows student STx as being registered on day d, it must also show student Sx as having some name on day d.
10. If the database shows student STx as having some name on day d, it must contain exactly one tuple that shows that fact.
11. If the database shows student STx as having the same name on days d and d+1, it must contain exactly one tuple that shows that fact.
12. If the database shows student STx as having some name on day d, it must also show student STx as being registered on day d.
13. If the database shows student STx as being enrolled on course Cx on day d, it must contain exactly one tuple that shows that fact.
14. If the database shows student STx as being enrolled on course Cx on days d and d+1, it must contain exactly one tuple that shows that fact.
15. If the database shows student STx as being enrolled on course Cx on day d, it must also show student STx as being registered on day d.
16. If the database shows student STx as being enrolled on course Cx on day d, it must also show course Cx as being available on day d.
17. If the database shows student STx as having achieved grade g on course Cx on day d, it must contain exactly one tuple that shows that fact.
18. If the database shows offering o of course Cx as taking place on day d, it must contain exactly one tuple that shows that fact.
19. If the database shows offering o of course Cx as taking place on days d and d+1, it must contain exactly one tuple that shows that fact.
20. If the database shows offering o of course Cx as having quota q on day d, it must contain exactly one tuple that shows that fact.
21. If the database shows offering o of course Cx as having quota q on days d and d+1, it must contain exactly one tuple that shows that fact.
22. If the database shows offering o of course Cx as taking place on day d, it must also show Cx as being available on day d.
a. The following solutions follow the numbering in the answer to the previous exercise.
1. The key specifications for CURRENT_COURSE and OLD_COURSE partly cater for this requirement, but we also need:
CONSTRAINT X147R1 IS_EMPTY
( ( CURRENT_COURSE { COURSENO, AVAILABLE } JOIN
OLD_COURSE { COURSENO, AVAILABLE_DURING } ) WHERE AVAILABLE < POST ( AVAILABLE_DURING ) ) ;
However, the fact that {COURSENO} is a key for OLD_COURSE strongly suggests that old courses are never revived, in which case the following simplification of Constraint X147R1 would cater for that requirement as well as the current one:
CONSTRAINT X147R1S IS_EMPTY
( CURRENT_COURSE { COURSENO } JOIN OLD_COURSE { COURSENO } ) ;
2. The key specifications for CURRENT_COURSE and OLD_COURSE partly cater for this requirement. If Constraint X147R1S is defined, then no additional constraint is needed. Otherwise, the requirement could be catered for by:
CONSTRAINT X147R2 IS_EMPTY
( ( CURRENT_COURSE { COURSENO, AVAILABLE } JOIN
OLD_COURSE { COURSENO, AVAILABLE_DURING } )
WHERE AVAILABLE = POST ( AVAILABLE_DURING ) ) ;
Constraints X147R1 and X147R2 could be combined as follows:
CONSTRAINT X147R1&2 IS_EMPTY
( ( CURRENT_COURSE { COURSENO, AVAILABLE } JOIN
OLD_COURSE { COURSENO, AVAILABLE_DURING } )
WHERE AVAILABLE ≤ POST ( AVAILABLE_DURING ) ) ;
3. No additional constraint is needed. The requirement is catered for by the headings of CURRENT_COURSE and OLD_COURSE.
4. Constraint X147R1 and the key specifications for CURRENT_COURSE and OLD_COURSE cater for this requirement.
5. Constraint X147R1&2 and the key specifications for CURRENT_COURSE and OLD_COURSE cater for this requirement.
6. No additional constraint is needed. The requirement is catered for by the headings of CURRENT_COURSE and OLD_COURSE.
7. The requirement is partly catered for by the specifications of {STUDENTNO} as a key for CURRENT_STUDENT and USING (REG_DURING) : {STUDENTNO,REG_DURING} as a U_key for STUDENT_HISTORY. But we also need:
CONSTRAINT X147R7 IS_EMPTY
( ( CURRENT_STUDENT { STUDENTNO, REGISTERED } JOIN
STUDENT_HISTORY { STUDENTNO, REG_DURING } ) WHERE REGISTERED < POST ( REG_DURING ) ) ;
8. The requirement is partly catered for by the specifications of {STUDENTNO} as a key for CURRENT_STUDENT, USING (REG_DURING) : {STUDENTNO,REG_DURING} as a U_key for STUDENT_HISTORY, and Constraint X147R7. But we also need:
CONSTRAINT X147R8 IS_EMPTY
( ( CURRENT_STUDENT { STUDENTNO, REGISTERED } JOIN
STUDENT_HISTORY { STUDENTNO, REG_DURING } ) WHERE REGISTERED = POST ( REG_DURING ) ) ;
Constraints X147R7 and X147R8 could be combined as follows:
CONSTRAINT X147R7&8 IS_EMPTY
( ( CURRENT_STUDENT { STUDENTNO, REGISTERED } JOIN
STUDENT_HISTORY { STUDENTNO, REG_DURING } ) WHERE REGISTERED ≤ POST ( REG_DURING ) ) ;
9. No additional constraint is needed. The requirement is catered for by the headings of CURRENT_STUDENT and STUDENT_HISTORY.
10. Constraint X147R7, the key specification for CURRENT_STUDENT, and the U_key specification for STUDENT_HISTORY together cater for this requirement.
11. Constraint X147R7&8, the key specification for CURRENT_STUDENT, and the U_key specification for STUDENT_HISTORY together cater for this requirement.
12. No additional constraint is needed. The requirement is catered for by the headings of CURRENT_STUDENT and STUDENT_HISTORY.
13. The key specifications for ENROLLMENT and COMPLETED_COURSE partly cater for this requirement, but something equivalent to the following is also needed:
CONSTRAINT X147R13 IS_EMPTY
( ( ENROLLMENT { COURSENO, STUDENTNO, ENROLLED } JOIN
COMPLETED_COURSE { COURSENO, STUDENTNO, STUDIED_DURING } ) WHERE ENROLLED < POST ( STUDIED_DURING ) ) ;
14. The key specifications for ENROLLMENT and COMPLETED_COURSE partly cater for this requirement, but we also need:
CONSTRAINT X147R14 IS_EMPTY
( ( ENROLLMENT JOIN COMPLETED_COURSE ) WHERE ENROLLED = POST ( STUDIED_DURING ) ) ;
Constraints X147R13 and X147R14 could be combined as follows:
CONSTRAINT X147R13&14 IS_EMPTY
( ( ENROLLMENT JOIN COMPLETED_COURSE ) WHERE ENROLLED ≤ POST ( STUDIED_DURING ) ) ;
15. The foreign key constraint from ENROLLMENT to CURRENT_STUDENT partly caters for this requirement, but we also need:
CONSTRAINT X147R15 IS_EMPTY
( ( ENROLLMENT JOIN CURRENT_STUDENT ) WHERE REGISTERED > ENROLLED ) AND WITH
( t1 := EXTEND CURRENT_STUDENT : { DURING := INTERVAL_DATE ( [ REGISTERED : LAST_DATE ( ) ] ) } { STUDENTNO, DURING },
t2 := t1 UNION ( STUDENT_HISTORY RENAME { REG_DURING AS DURING } ) { STUDENTNO, DURING } ,
t3 := ( COMPLETED_COURSE RENAME { STUDIED_DURING AS DURING } ) { STUDENTNO , DURING } ) :
USING ( DURING ) : t3 ⊆ t2 ;
16. The foreign key constraint from ENROLLMENT to CURRENT_COURSE partly caters for this requirement, but we also need:
CONSTRAINT X147R16 IS_EMPTY
( ( ENROLLMENT JOIN CURRENT_COURSE ) WHERE AVAILABLE > ENROLLED ) AND WITH
( t1 := EXTEND CURRENT_COURSE : { DURING := INTERVAL_DATE ( [ AVAILABLE : LAST_DATE ( ) ] ) } { COURSENO, DURING } ,
t2 := t1 UNION ( OLD_COURSE RENAME { AVAILABLE_DURING AS DURING } ) { COURSENO, DURING } ) ,
t3 := ( COMPLETED_COURSE RENAME ( STUDIED_DURING AS DURING ) ) { COURSENO, DURING } ) :
USING ( DURING ) : t3 ⊆ t2 ;
17. The key specification for COMPLETED_COURSE caters for this requirement.
18. The key specification for COURSE_OFFERING caters for this requirement.
19. The key specification for COURSE_OFFERING caters for this requirement.
20. The key specification for COURSE_OFFERING caters for this requirement.
21. The key specification for COURSE_OFFERING caters for this requirement.
22. The requirement could be catered for by the following:
CONSTRAINT X147R22 WITH
( t1 := EXTEND CURRENT_COURSE : { DURING := INTERVAL_DATE ( [ AVAILABLE : LAST_DATE ( ) ] ) } { COURSENO, DURING } ,
t2 := T1 UNION ( OLD_COURSE RENAME ( AVAILABLE_DURING AS DURING ) ) { COURSENO, DURING } ) ,
t3 := ( OFFERING RENAME ( OFFERED_DURING AS DURING ) ) { COURSENO, DURING } ) :
USING ( DURING ) : t3 ⊆ t2 ;
b. Add to the specification of AVAILABLE in CURRENT_COURSE:
Add to the specification of REGISTERED in CURRENT_STUDENT:
SINCE_FOR { STUDENTNO , NAME }
HISTORY_IN ( STUDENT_HISTORY )
Add to the specification of ENROLLED in ENROLLMENT:
SINCE_FOR { COURSENO , STUDENTNO }
HISTORY_IN ( COMPLETED_COURSE )
Note: These last additions are perhaps a little suspect—the ENROLLED attribute represents the date on which the student enrolled for some offering of the course, not the date on which that student’s study of that course started.
14.8 The requirement to record on which particular offering a student enrolls on could be addressed by adding an OFFERINGNO attribute to both of the relvars ENROLLMENT and COMPLETED_COURSE. The STUDIED_DURING attribute of COMPLETED_COURSE is probably now redundant, since the start of a student’s study presumably coincides with the start of the offering. However, the end of a student’s study might be considered to be earlier than the end of the offering in the case of dropouts, in which case perhaps STUDIED_DURING should be replaced by a COMPLETED_ON attribute of type DATE. The following new natural language requirement arises:
23. If the database shows student Sx as having enrolled on offering o of course Cy on day d, it must also show offering o of course Cy as starting to take place on or after day d. (Presumably it’s not permitted for a student to enroll on an offering that has already started, and might even have finished.)
Requirement 16 no longer applies.
Requirement 23 could be catered for by this:
( ( ENROLLMENT JOIN COURSE_OFFERING ) WHERE ENROLLED > BEGIN ( OFFERED_DURING ) ) ;
If COMPLETED_ON is added to COMPLETED_COURSE, then the following new natural language requirement arises:
24. If the database shows student Sx as having completed offering o of course Cy on day d, it must also show offering o of course Cy as starting to take place on or after day d and finishing before or on day d.
Requirement 24 could be catered for as follows:
( ( ENROLLMENT JOIN COURSE_OFFERING ) WHERE COMPLETED_ON ∈ OFFERED_DURING ) ;
14.9 Let D be a variable of type DATE, and consider the boolean expression D > TODAY ( ). Observe that this expression could evaluate to TRUE on some given day and FALSE on the next, even if the value of D remains unchanged between the two evaluations. Thus, an integrity constraint that involves such an expression—or the checking implied by such a constraint, rather—could succeed on one day and fail on the next, even if the database hasn’t changed in the interim.