Temporal Support in Relational Databases

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. To copy otherwise, to republish, to post on servers or to redistribute to lists, requires prior specific permission. © 2012 Higher Education AcademyThis paper examines the current state of temporal support in relational databases and the type of situations where we need that support. There has been much research in this area and there were attempts in the American National Standards Institute (ANSI) and the International Organisation for Standardisation (ISO) standards committees in the late 1990s to add an extension called TSQL2 to the existing SQL standard. However no agreement could be reached as it was felt that some of the suggested extensions did not fit well with the relational model, as well as being difficult to implement. TSQL2 was abandoned and since then vendors have added their own data types, and if we are lucky, operators too in an attempt to provide support. However, to novice students and database designers it is often not apparent why some temporal concepts are difficult to deal with in a relational database. In teaching these concepts to students we use a Case Study (based on a real example) which illustrates the problems of providing temporal support by using examples of the data types which could be useful to solve temporal problems and the operators which are necessary to provide this

A Principled Framework for Constructing Natural Language Interfaces To Temporal Databases

Most existing natural language interfaces to databases (NLIDBs) were designed to be used with ``snapshot'' database systems, that provide very limited facilities for manipulating time-dependent data. Consequently, most NLIDBs also provide very limited support for the notion of time. The database community is becoming increasingly interested in _temporal_ database systems. These are intended to store and manipulate in a principled manner information not only about the present, but also about the past and future. This thesis develops a principled framework for constructing English NLIDBs for _temporal_ databases (NLITDBs), drawing on research in tense and aspect theories, temporal logics, and temporal databases. I first explore temporal linguistic phenomena that are likely to appear in English questions to NLITDBs. Drawing on existing linguistic theories of time, I formulate an account for a large number of these phenomena that is simple enough to be embodied in practical NLITDBs. Exploiting ideas from temporal logics, I then define a temporal meaning representation language, TOP, and I show how the HPSG grammar theory can be modified to incorporate the tense and aspect account of this thesis, and to map a wide range of English questions involving time to appropriate TOP expressions. Finally, I present and prove the correctness of a method to translate from TOP to TSQL2, TSQL2 being a temporal extension of the SQL-92 database language. This way, I establish a sound route from English questions involving time to a general-purpose temporal database language, that can act as a principled framework for building NLITDBs. To demonstrate that this framework is workable, I employ it to develop a prototype NLITDB, implemented using ALE and Prolog.Comment: PhD thesis; 405 pages; LaTeX2e, uses the packages/macros: amstex, xspace, avm, examples, dvips, varioref, makeidx, epic, eepic, ecltree; postscript figures include

On Data Representation and Use In A Temporal Relational DBMS

Numerous proposals for extending the relational data model to incorporate the temporal dimension of data have appeared over the past decade. It has long been known that these proposals have adopted one of two basic approaches to the incorporation of time into the extended relational model. Recent work formally contrasted the expressive power of these two approaches, termed temporally ungrouped and temporally grouped, and demonstrated that the temporally grouped models are more expressive. IN the temporally ungrouped models, the temporal dimension is added through the addition of some number of distinguished attributes to the schema of each relation, and each tuple is "stamped" with temporal values for these attributes. By contrast, in temporally grouped models the temporal dimension is added to the types of values that serve as the domain of each ordinary attribute, and the application's schema is left intact. The recent appearance of TSQL2, a temporal extension to the SQL-92 standard based upon the temporally ungrouped paradigm, means that it is likely that commercial DBMS's will be extended to support time in this weaker way. Thus the distinction between these two approaches - and its impact on the day-to-day user of a DBMS - is of increasing relevance to the database practitioner and the database user community. In this paper we address this issue from the practical perspective of such a user. Through a series of example queries and updates, we illustrate the differences between these two approaches and demonstrate that the temporally grouped approach more adequately captures the semantics of historical data.Information Systems Working Papers Serie

ON PERIODICITY IN TEMPORAL DATABASES

The issue of periodicity is generally understood to be a desirable property of temporal data that should be supported by temporal database models and their query languages. Nevertheless, there has so far not been any systematic examination of how to incorporate this concept into a temporal DBMS. In this paper we describe two concepts of periodicity, which we call strong periodicity and near periodicity, and discuss how they capture formally two of the intuitive meanings of this term. We formally compare the expressive power of these two concepts, relate them to existing temporal query languages, and show how they can be incorporated into temporal relational database query languages, such as the proposed temporal extension to SQL, in a clean and straightforward manner.Information Systems Working Papers Serie

Snapshot Semantics for Temporal Multiset Relations (Extended Version)

Snapshot semantics is widely used for evaluating queries over temporal data: temporal relations are seen as sequences of snapshot relations, and queries are evaluated at each snapshot. In this work, we demonstrate that current approaches for snapshot semantics over interval-timestamped multiset relations are subject to two bugs regarding snapshot aggregation and bag difference. We introduce a novel temporal data model based on K-relations that overcomes these bugs and prove it to correctly encode snapshot semantics. Furthermore, we present an efficient implementation of our model as a database middleware and demonstrate experimentally that our approach is competitive with native implementations and significantly outperforms such implementations on queries that involve aggregation.Comment: extended version of PVLDB pape

Schema Vacuuming in Temporal Databases

Temporal databases facilitate the support of historical information by providing functions for indicating the intervals during which a tuple was applicable (along one or more temporal dimensions). Because data are never deleted, only superceded, temporal databases are inherently append-only resulting, over time, in a large historical sequence of database states. Data vacuuming in temporal databases allows for this sequence to be shortened by strategically, and irrevocably, deleting obsolete data. Schema versioning allows users to maintain a history of database schemata without compromising the semantics of the data or the ability to view data through historical schemata. While the techniques required for data vacuuming in temporal databases have been relatively well covered, the associated area of vacuuming schemata has received less attention. This paper discusses this issue and proposes a mechanism that fits well with existing methods for data vacuuming and schema versioning

Irregular Indeterminate Repeated Facts in Temporal Relational Databases

Time is pervasive of reality, and many relational database approaches have been developed to cope with it. In practical applications, facts can repeat several times, and only the overall period of time containing all the repetitions may be known (consider, e.g., On January, John attended five meetings of the Bioinformatics project). While some temporal relational databases have faced facts repeated at (known) periodic time, or single facts occurred at temporally indeterminate time, the conjunction of non-periodic repetitions and temporal indeterminacy has not been faced yet. Coping with this problem requires an in-depth extension of current techniques. In this paper, we have introduced a new data model, and new definitions of relational algebraic operators coping with the above issues. We have studied the properties of the new model and algebra (with emphasis on the reducibility property), and how it can be integrated with other models in the literature