Temporal data modelling and integrity constraints in relational databases*

Figures & data

Figure 1. Attribute timestamping. (a) Time points, (b) Intervals, (c) Temporal element, (d) Temporal set.

Figure 2. Tuple timestamping. (a) Two time columns, (b) Interval data type.

Figure 3. The EMP relation in attribute time stamping.

Figure 4. The EMP relation in tuple time stamping. (a) EMP_N Relation, (b) EMP_D Relation, (c) EMP_S Relation.

Table 1. Operations for PK and TGI.

Operation	Primary key (PK)	Temporal grouping identifier (TGI)
Insertion	A new tuple with a PK value that matches an existing PK value may not be inserted	A new tuple with a TGI value that matches an existing TGI value may not be inserted
Modification of a key value	Deletion followed by insertion	Deletion followed by insertion
Modification of a non-key attribute	A new tuple with a PK value that matches an existing PK value replaces the old tuple	A new temporal value is added: to an existing tuple identified by a TGI in attribute time stamping by inserting a new tuple to a group of tuples identified by a TGI in tuple time stamping
Deletion of a key value	Remove the matching tuple from the relation	Remove the Matching tuple from the relation

Figure 5. EP Relation in tuple time stamping.

Figure 6. EP Relation in attribute time stamping.

Figure 7. EP Relation embedded in EMP relation.

Figure 8. EP Relation embedded in PROJECT relation.

Figure 9. Nested temporal relation that stores integrity constraints.

Table 2. Temporal existence integrity constraint.

TEI1. At any time instant, the TGI may not have null values.

TEI2. The TGI values are constant, i.e. the same for all the time instants in an object’s lifespan.

TEI3. Time reference of the TGI in a tuple may not be null.

TEI4. In a tuple t of a temporal relation, any of its temporal attributes may not assume values outside of its lifespan.

TEI5. In a temporal group, the times of attribute values should be maximal intervals (temporal elements or temporal sets).

TEI6. In a temporal group, an attribute has one value at a database state unless it is a multi-valued attribute.

Figure 10. EMP_LS in tuple time stamping.

Table 3. Enforcing time component of temporal existence integrity.

1. Get the time reference of an attribute, i.e. salary.	Consider the lifespan relation, i.e. EMP_LS in Figure 7. Consider a temporal relation, i.e. EMP_S in Figure 4. Join the relations of steps 1 and 2 on the equality of the TGI, i.e. E#.
2. Test to verify that attribute’s time reference is a subset of the time reference of the TGI.	Group by (partition) the result of step 3 by the TGI, i.e. E#. Combine the time reference of the TGI and the time reference of the time dependent attribute for each partition. Then, verify that the latter is a subset of the former.
3. Repeat steps 1 and 2 for the remaining time varying attributes.	Repeat steps 1–5 for the remaining temporal relations.
(a) Attribute time stamping.	(b) Tuple time stamping.

Table

Verify_Time_TEI_TTS

{Repeat for i = 1, … .,n

{J = R_LS joinR_LS.TGI = R_Ai.TGI R_Ai

S = σR_LS.T ${}_{\mathrm{\neg }}$⊇ R_Ai.T(J)

If S = Ø no violation

else temporal existential integrity violation}

Table

Verify_Time_TEI_ATS

{Repeat for i = 1, … .,n

{U = Unnest Ai

S = σTGI.T ${}_{\mathrm{\neg }}$⊇ Ai.T (U)

If S = Ø no violation

else temporal existential integrity violation}

End repeat}

Table 4. Temporal referential integrity constraint.

TRI1. At any time instant, a foreign TGI (foreign key) value in a tuple (referencing table) should be null or appears among the values of the TGI in the referenced table.

TRI2. Time reference of a foreign TGI value in a tuple should be a subset of the time reference of the TGI in the referenced table.

Table

Verify_TRI_TTS

{J = R_LS joinR_LS.TGI = S_A.A S_A

X = σR_LS.T ${}_{\mathrm{\neg }}$⊇ R_A.T(J) // $\mathrm{\neg }$(R_LS.Start >=  S_A.Start and

// R_LS.End <=  S_A.End)

If X = Ø no violation

else temporal referential integrity violation}

Table

Verify_TEI_ATS

{U = Unnest Bi of the relation S

J = R joinR.TGI.v = S_Bi.v U //”.v” notation refers to the // value part of the temporal atom.

X = σRTGI.T ${}_{\mathrm{\neg }}$⊇ Ai.T (J) // Similar to the second step of// Verify_TRI_TTS

If X = Ø no violation

else temporal referential integrity violation}

Figure 11. Department relations in tuple time stamping. (a). DEPARTMENT_LS Relation, (b). DEPARTMENT_B Relation.

Table 5. Enforcing temporal referential integrity.

1. Consider a foreign TGI attribute, i.e. DNAME in EMP	1. Consider a temporal relation that has a foreign TGI attribute, i.e. EMP_D relation
2. Unnest the foreign TGI attribute, i.e. DNAME	2. Consider the lifespan relation where foreign TGI attribute appears as the TGI, i.e. DEPARTMENT_LS
3. Verify that time reference of foreign TGI attribute (i.e. DNAME) is a subset of the time reference of the TGI in the referenced relation, i.e. DNAME in the DEPARTMENT relation	3. Verify that time EMP_D is a subset of the time of DEPARTMENT_LS
(a) Attribute time stamping	(b) Tuple time stamping

Figure 12. Bitempral atoms. (a) Attribute time stamping, (b) Tuple time stamping.

Figure 13. Bitemporal integrity constraints.