Modification semantics in now-relative databases

• Published in: Information systems (Oxford) 2004-12, Vol.29 (8), p.653-683
• Main Authors: Torp, Kristian, Jensen, Christian S., Snodgrass, Richard T.
• Format: Article
• Language: English
• Subjects: Computerized information storage and retrieval; Now; Now-relative information; Query languages; Searching; SQL; Temporal data; Temporal query language; Updates
• ISSN: 0306-4379; 1873-6076
• DOI: 10.1016/S0306-4379(03)00047-4

Most real-world databases record time-varying information. In such databases, the notion of “the current time,” or NOW, occurs naturally and prominently. For example, when capturing the past states of a relation using begin and end time columns, tuples that are part of the current state have some past time as their begin time and NOW as their end time. While the semantics of such variable databases has been described in detail and is well understood, the modification of variable databases remains unexplored. This paper defines the semantics of modifications involving the variable NOW. More specifically, the problems with modifications in the presence of NOW are explored, illustrating that the main problems are with modifications of tuples that reach into the future. The paper defines the semantics of modifications—including insertions, deletions, and updates—of databases without NOW, with NOW, and with values of the type NOW+ Δ, where Δ is a non-variable time duration. To accommodate these semantics, three new timestamp values are introduced. Finally, implementation is explored. We show how to represent the variable NOW with columns of standard SQL data types and give a mapping from SQL on NOW-relative data to standard SQL on these columns. The paper thereby completes the semantics, the querying, and the modification of now-relative databases.