Time in Database Systems

Data Models and Temporal Databases 5 Booking Meeting Room Time DB Group DC1331 06-Jan-04.10:00 DB Group DC1331 06-Jan-04.10:01 DB Group DC1331 06-Jan-04.10:02 DB Group DC1331 16-Jan-04.11:59 Intro to Databases MC4042 06-Jan-04.10:00 Intro to Databases MC4042 06-Jan-04.11:19 Intro to Databases MC4042 08-Jan-04.10:00 Intro to Databases MC4042 08-Jan-04.11:19 Fig. 19.1 A Fragment of a Timestamp Instance of the Booking relation from Example 19.1. A timestamp temporal database is a first-order structure , where are temporal relations— instances of the temporal extensions . In addition we require that the set be finite for every and . Note that at this point there are no cardinality restrictions imposed on the number of time instants in the instances of abstract temporal relations; we address issues connected with the actual finite representation of these relations in Section 19.6. In the rest of the chapter we use the following example to illustrate various concepts. Example 19.1 Consider a database recording room bookings for meetings in a university. A relational schema booking Meeting Room links meetings to rooms. We assume that rooms are identified by their room numbers and meetings have distinct descriptions (names). Thus our temporal database, assuming the use of the timestamp model, contains a single relational schema with three attributes, Booking Meeting Room Time A tuple in an instance of this relation denotes the fact that a meeting is in a room at time . For simplicity in this chapter we assume that time is measured in minutes. An example instance of this schema is shown in Figure 19.1. To distinguish between non-temporal relations and (the derived) timestamp relations we capitalize the name of the later. The granularity of time in our examples is one minute (more on granularities in Section 19.9). It is important to understand that, e.g., the “DB group” meeting has booked room DC1331 for every time instant between 06-Jan-04.10:00 and 06-Jan-04.11:59. 30th January 2004 16:9 WorldScientific/ws-b9-75x6-50 book-timeai 6 Time in Database Systems This set of tuples, depending on properties of the time domain, can be infinite (e.g., when dense time domain is considered). There are several things to note about the example: an instance of the Booking relation represents complete information about meeting schedule; in particular it contains information about meetings that have already finished (e.g., for accounting and evaluation purposes) as well as about meetings scheduled in the future (e.g., to avoid overbooking of rooms). This is necessary, for example, if we want to schedule another meeting in the future, as we need to make sure no other meeting conflicts with it. For this purpose we need to query the database for empty rooms at the particular future time we desire and such a query is only possible utilizing the closed-world assumption. Second, we assume that distinct meetings have distinct names. Thus the same meeting (e.g., a class) can meet in several different rooms at different times. Moreover the meeting times may not be continuous (as is common, e.g., for classes). If we wish to distinguish between instances of a particular meeting we need to use distinguished names (or an additional attribute). 19.3.1.2 The Snapshot Model The sbstract temporal databases in this model are defined as a mapping of the temporal domain to the class of standard relational databases. This gives a Kripke structure with the temporal domain serving as the accessibility relation. Definition 19.3 (Abstract Snapshot Temporal Database) A snapshot temporal database over , , and is a map , where is the class of finite relational databases over and . It is easy to see that snapshot and timestamp abstract temporal databases are merely different views of the same data and thus can represent the same class of temporal databases. Formally, a snapshot temporal database corresponds to a timestamp temporal database if and only if % ( , for all (and ) in the schema of ( ), where ( , ( ) are the instances of the relations ( ) in ( ), respectively, where ! . This correspondence allows us to move freely between the two models. Example 19.2 A snapshot representation of the instance in Figure 19.1 is shown in Figure 19.2. Note that the relationship between the timestamp and snapshot models is essentially currying and uncurrying [Barendregt, 1984] (the correspondence is exact if the relations are considered to be boolean functions from tuples to the set " # $&% '( *) + % ). 30th January 2004 16:9 WorldScientific/ws-b9-75x6-50 book-timeai Abstract Data Models and Temporal Databases 7Data Models and Temporal Databases 7 Time booking 06-Jan-04.10:00 (DB Group,DC1331), (Intro to Databases,MC4042) 06-Jan-04.10:01 (DB Group,DC1331), (Intro to Databases,MC4042) 06-Jan-04.11:19 (DB Group,DC1331), (Intro to Databases,MC4042) 06-Jan-04.11:20 (DB Group,DC1331) 06-Jan-04.11:59 (DB Group,DC1331) 06-Jan-04.12:00 06-Jan-04.12:00 08-Jan-04.10:00 (Intro to Databases,MC4042) 08-Jan-04.11:19 (Intro to Databases,MC4042) Fig. 19.2 A Fragment of a Snapshot Instance of the Booking relation. Thus, in the rest of the chapter we use the timestamp abstract temporal databases as the common underlying temporal data model. Also, let us reiterate that the abstract data models are used solely at the conceptual level; relations will likely be stored in a different, more space-efficient format, e.g., one that uses time intervals (see Section 19.6). 19.3.1.3 Relational Database Histories Relational databases are updatable and it is natural to consider sequences of database states resulting from the updates. Definition 19.4 (Finite History) A history over a database schema and a data domain is a sequence ( of database instances (called states) such that (1) all the states share the same schema and the same data domain , (2) is the initial instance of the database, (3) results from applying an update to , , There is a clear correspondence between histories over and and snapshot temporal databases over , N (natural numbers), and (see Definitions 19.3 and 19.2). Consequently, any query language for abstract temporal databases can also be used to query database histories. However, there is a difference in the restrictions placed on updates: while there are no a priori limitations placed on snapshot temporal database updates (they can involve any snapshot), histories are append-only (the past cannot be modified). This property is often 30th January 2004 16:9 WorldScientific/ws-b9-75x6-50 book-timeai 8 Time in Database Systems associated with transaction time databases—temporal databases in which time instants correspond to commitment time of transactions; the append-only nature of such databases corresponds to the requirement of durable transactions. Indeed, transaction-time temporal databases can be viewed as finite histories of standard relational databases. 19.3.2 Multiple Temporal Dimensions So far we have considered only single-dimensional temporal databases: temporal relations were allowed only a single temporal attribute. To motivate the introduction of multiple temporal dimensions in the context of temporal databases, consider the following examples: Bitemporal databases: with each tuple in a relation two kinds of time are stored—the valid time (when a particular tuple is true) and the transaction time (when the particular tuple was inserted/deleted in the database) [Jensen et al., 1993]. Spatial databases: multiple dimensions over an interpreted domain can be used for representing spatial data where multiple dimensions serve as coordinates of points in a -dimensional Euclidean space. Most of the data modeling techniques require only fixed-dimensional data. However, the true need for arbitrarily large dimensionality of data models originates in the requirement of having a first-order complete query language (see Theorem 19.5 in Section 19.5). Thus, there are two cases to consider: temporal models with a fixed number of dimensions , and temporal models with a varying number of temporal dimensions without an upper bound. The representation of multiple temporal dimensions in abstract temporal databases is quite straightforward: we simply index relational databases by the elements of an appropriate self-product of the temporal domain (in the case of snapshot temporal databases), or add the appropriate number of temporal attributes (in the case of timestamp temporal databases). 19.3.3 Non 1NF Temporal Models Several temporal data models associate relationships between data values— truth of facts recorded in the database— with sets of time instants (rather than with a single time instant). These models are no longer in first normal form (N1NF) and are often called temporally grouped models [Clifford et al., 1993; Clifford et al., 1994]. 30th January 2004 16:9 WorldScientific/ws-b9-75x6-50 book-timeai Abstract Data Models and Temporal Databases 9Data Models and Temporal Databases 9 Example 19.3 The instance of the Booking relation from Figure 19.1 represented in the N1NF (temporally grouped) model is as follows Booking Meeting Room Time DB group DC1331 06-Jan-04.10:00, 06-Jan-04.10:01, . . . ,06-Jan-04.11:59 Intro to Databases MC4042 06-Jan-04.10:00, . . . ,06-Jan-04.11:19, 08-Jan-04.10:00, . . . ,08-Jan-04.11:19 However, the set-based attributes can be flattened, perhaps by introducing additional surrogate keys, to obtain a 1NF temporal database containing the same information [Clifford et al., 1993; Wijsen, 1999]. Without introducing additional keys, however, this transformation can be lossy. Example 19.4 Consider a fragment of a N1NF temporal relation booking(DB group, DC1331, 06-Jan-04.10:00, . . . ,06-Jan-04.11:59 ) booking(DB group, DC1331, 09-Jan-04.10:00, . . . ,09-Jan-04.11:59 ) The meetings in this design are no longer identified by their names, but rather by their name and the set of all meeting times. The same information, however, can be captured by explicitly identifyin