Temporális táblák

Olvasmányok

• Snodgrass, Richard T.: Developing Time-Oriented Database Applications in SQL. Morgan Kaufmann, San Francisco, 1999.¹
• Jungwirth, Paul A.: Temporal Databases Annotated Bibliography. illuminatedcomputing.com, 2017-12-05.
• Fowler, Martin: Bitemporal History. martinfowler.com, 2021-04-07.
• Vos, Roelant: A gentle introduction to bitemporal data challenges. roelantvos.com, 2023-02-21.
• Vos, Roelant: A not-so-gentle follow-up on bitemporal data challenges. roelantvos.com, 2023-03-23.
• Vos, Roelant: Visualising the merger of time-variant and bitemporal data. roelantvos.com, 2023-03-26.

Developing Time-Oriented Database Applications in SQL

There are three fundamental temporal data types:

• Instant: something happened at an instant of time.
• Interval: a length of time (e.g., three months).
• Period: an anchored duration of time (e.g., the fall semester, August 24 through December 18, 1998). (1.1)

There are three fundamental kinds of time:

• User-defined time: an uninterpreted time value.
• Valid time: when a fact was true in the modeled reality.
• Transaction time: when a fact was stored in the database. (1.1)

There are three basic kinds of time-oriented statements:

• Current: now.
• Sequenced: at each instant of time.
• Nonsequenced: ignoring time. (1.1)

Valid time, capturing the history of a changing reality, and transaction time, capturing the sequence of states of a changing table, are orthogonal, and can thus be separately utilized or applied in concert. A table supporting both is termed a “bitemporal table”. (2.3)

Other terms have been applied to the valid-time, transaction-time, and bitemporal tables introduced in this chapter. They have been called temporal tables. The term time-varying has been used, but this is a misnomer, as all tables in practice vary over time, as rows are added, removed, and changed. The term time-oriented tables is also not quite precise; just what does it mean to be “time-oriented”? (For that reason, the title of this book is unfortunate. To be honest, I originally preferred Developing Temporal Database Applications in SQL, but felt that title might confuse people who did not know the technical definition of “temporal”, which no longer characterizes you, gentle reader.) Such tables have also been called historical tables, but this implies that they record information only about the past. Valid-time tables often store information about the future, for example, in planning or scheduling applications. The accepted terminology then is to refer to such tables as temporal tables, or more specifically as, say, a valid-time table. (2.5)

The presence of a DATE column will not a priori render the database a temporal database; rather, the database must record the time-varying nature of the information managed by the enterprise. (2.5)