Extending the Kernel of a Relational DBMS with Comprehensive Support for Sequenced Temporal Queries

Abstract

Many databases contain temporal, or time-referenced, data and use intervals to capture the temporal aspect. While SQL-based database management systems (DBMSs) are capable of supporting the management of interval data, the support they offer can be improved considerably. A range of proposed temporal data models and query languages offer ample evidence to this effect. Natural queries that are very difficult to formulate in SQL are easy to formulate in these temporal query languages. The increased focus on analytics over historical data where queries are generally more complex exacerbates the difficulties and thus the potential benefits of a temporal query language. Commercial DBMSs have recently started to offer limited temporal functionality in a step-by-step manner, focusing on the representation of intervals and neglecting the implementation of the query evaluation engine. This article demonstrates how it is possible to extend the relational database engine to achieve a full-fledged, industrial-strength implementation of sequenced temporal queries, which intuitively are queries that are evaluated at each time point. Our approach reduces temporal queries to nontemporal queries over data with adjusted intervals, and it leaves the processing of nontemporal queries unaffected. Specifically, the approach hinges on three concepts: interval adjustment, timestamp propagation, and attribute scaling. Interval adjustment is enabled by introducing two new relational operators, a temporal normalizer and a temporal aligner, and the latter two concepts are enabled by the replication of timestamp attributes and the use of so-called scaling functions. By providing a set of reduction rules, we can transform any temporal query, expressed in terms of temporal relational operators, to a query expressed in terms of relational operators and the two new operators. We prove that the size of a transformed query is linear in the number of temporal operators in the original query. An integration of the new operators and the transformation rules, along with query optimization rules, into the kernel of PostgreSQL is reported. Empirical studies with the resulting temporal DBMS are covered that offer insights into pertinent design properties of the article's proposal. Many databases contain temporal, or time-referenced, data and use intervals to capture the temporal aspect. While SQL-based database management systems (DBMSs) are capable of supporting the management of interval data, the support they offer can be improved considerably. A range of proposed temporal data models and query languages offer ample evidence to this effect. Natural queries that are very difficult to formulate in SQL are easy to formulate in these temporal query languages. The increased focus on analytics over historical data where queries are generally more complex exacerbates the difficulties and thus the potential benefits of a temporal query language. Commercial DBMSs have recently started to offer limited temporal functionality in a step-by-step manner, ...

Supporting Set Operations in Temporal-Probabilistic Databases

“…1a. According to the lineage expression of tuple ('milk', [2,4), c 1 ∧ ¬a 1 , 0.42), at t = 2, the fact 'milk' has been derived from the input tuples a 1 and c 1 , i.e., the only input tuples of the probabilistic snapshot at t = 2 ( Fig. 5 that include the fact 'milk'.…”

“…Intuitively, change preservation ensures that only consecutive time points of tuples with equivalent lineage expressions are grouped into intervals. For example, the output tuples ('milk', [1,2), c 1 , 0.6) and ('milk', [2,4), c 1 ∧ ¬a 1 , 0.42) are not merged into the interval [1,4), since they do not have equivalent lineages. Change preservation guarantees that a fact is valid over the same possible worlds with maximal intervals.…”

“…It indicates that, over interval [1,2), fact 'milk' is included in tuple c 1 of relation c (w 1 .λ r = c 1 ) but in no tuple of relation a (w 1 .λ s = null). The window w 1 only spans the maximal interval [1,2), since at time point t = 2, tuple a 1 starts being valid and thus, there is a change in the tuples of the two relations that are valid at t = 2 and include fact 'milk'. Assuming that there is no such window, i.e., assuming that one of the above mentioned conditions is not satisfied, we conclude that there are no valid tuples including u.F or the interval u.T is not maximal.…”

“…For example, the tuple ('milk', a 1 , [2,10), 0.3) captures that, at each day from the 2 nd to the 10 th of the month, "milk is bought" with probability 0.3. There is a single prediction for each fact at each time point and thus, there is no other tuple in a that predicts the probability of buying 'milk' over an interval overlapping with [2,10). In order to have an overview of its supply and demand, the supermarket wants to determine, at each time point, the probability that a product is in stock but no client wants to order or buy this product.…”

“…Note that, although in a relational setting intersection is a dependent operation which can be expressed in terms of two difference operations, we show that considering intersection as a separate operator has significant performance advantages in a TP setting. [5,9) 0.6 'chips' b 2 [3,6) 0.9 c (productsInStock) Product λ T p 'milk' c 1 [1,4) 0.6 'milk' c 2 [6,8) 0.7 'chips' c 3 [4,5) 0.7 'chips' c 4 [7,9) [7,9) 0.8…”