Temporal database
A temporal database refers to database systems which support at least some aspects of the storage of time-related data that go beyond the support of time-related data types and which are therefore particularly suitable for temporal data storage .
In addition to the temporal databases, there are also the spatio-temporal databases , which map a spatial dimension in addition to the temporal dimension, but the focus of these databases is usually on the spatial dimension.
Apart from Teradata 13.10, there is currently no commercial database system that comprehensively maps the requirements of temporal data storage. However, there are some drafts that are essentially extensions of relational databases . In particular, the future SQL / Temporal standard that is currently being worked on , which was originally intended as part of the SQL3 or SQL: 1999 SQL standard , is intended to combine the approaches of the currently existing drafts and offer comprehensive support for the storage of temporal data. Standardization stalled because of differences of opinion, so that SQL / Temporal was not included in the SQL: 2008 standard, but the SQL: 2011 standard deals with temporal databases.
criteria
The following criteria are helpful for classifying temporal databases:
- Time dimension
A distinction is made between validity time (period in which an object has the described state in the modeled reality) and transaction time (point in time at which a data change in the database takes place). If both the validity and the transaction time are relevant, one speaks of bitemporal . - Data type used for time stamping
Here a distinction must be made between elementary time information, intervals or so-called temporal elements (a combination of any number of intervals). - Explicit or implicit time stamping
A distinction is made here as to whether the time stamp is implicitly supplemented by the database system or whether it must be specified explicitly. -
Tuple or attribute time stamping
A distinction must be made between time stamping for each attribute individually or for an entire data line (tuple). - Automatic temporal normalization (coalescing)
Some database systems automatically summarize periods of time with matching attribute values after appropriate update operations.
Temporal Database Concepts
Essential current concepts of temporal databases are briefly presented below. However, this list does not claim to be complete.
IXSQL
| Time dimension | Validity period |
| Data type for time stamping | interval |
| Time stamping explicit / implicit | explicit |
| Tuple or attribute time stamping | Tuple |
| Automatic coalescing | No |
The Interval Extended Relational Model (IXRM) and the IXSQL language based on it is an extension of SQL to include comprehensive support for interval data that does not necessarily have to relate exclusively to temporal intervals. This differs fundamentally from the other concepts of temporal databases presented here. IXSQL is upwardly compatible with SQL92.
In addition to the interval data type, the most important extensions compared to SQL92 are the operations FOLD (" fold up ") and UNFOLD ("unfold"). Thereby effects UNFOLD a decomposition of an interval into a set of individual pixel values. FOLD is the opposite operation - a lot of individual point data is combined again into intervals - and thus causes a temporal normalization for the attributes concerned.
TSQL2
| Time dimension | bitemporal |
| Data type for time stamping | Temporal element |
| Time stamping explicit / implicit | implicitly |
| Tuple or attribute time stamping | Tuple |
| Automatic coalescing | Yes |
The TSQL2 language was developed by a larger group of researchers and is based on the Bitemporal Conceptual Data Model (BCDM). TSQL2 is also upwardly compatible with SQL92.
The BCDM uses bit-temporal elements for time stamping , i.e. That is, all tuples with the same attribute values can always be combined into a single line, since the bit-temporal time stamp can record the union of all relevant time intervals. In addition, the time stamping is done implicitly, i. In other words, the time stamps are not "normal" attributes that can be directly influenced. Another special feature of the BCDM is that this combination of tuples with similar attribute values (coalescing) takes place automatically.
In order to be able to map time-anchored intervals , the data type PERIOD exists in TSQL2 . In connection with this type, additional special comparison operators are also introduced ( MEETS , PRECEDES , OVERLAPS and CONTAINS ).
ATSQL2
| Time dimension | bitemporal |
| Data type for time stamping | interval |
| Time stamping explicit / implicit | explicit |
| Tuple or attribute time stamping | Tuple |
| Automatic coalescing | No |
In contrast to the other concepts presented here, ATSQL2 is not based on a special database system, but functions as the front end of a conventional relational database system . For example, Oracle and Sybase are supported.
The language syntax used by ATSQL2 is based on TSQL2. Each database statement is converted into one or more SQL92 statements.
Another essential difference between ATSQL2 and the other concepts presented is that with TimeDB there is already a working prototype (see web links ).
Products with support for temporal data
- Oracle has implemented support for temporal data from version 10 and the temporal function of SQL2011 from version 12
- From Version 10, DB2 from IBM offers support for temporal data based on the SQL2011 standard
- PostgreSQL offers many of the temporal features from version 9.2
- Teradata offers temporal features from version 13.10 and TSQL2 features from version 14
- Microsoft SQL Server offers temporal tables from version 2016
- MariaDB offers temporal functions from version 10.3
See also
Individual evidence
- ^ Consensus Glossary of Temporal Database Concepts
- ↑ Snodgrass, Böhlen, Jensen, Steiner: Transitioning Temporal Support in TSQL2 to SQL3 , 1998 (PDF; 171 kB)
- ↑ Oracle v12c temporal features. Retrieved July 1, 2016 .
- ↑ Temporal features in DB2 v10. Retrieved July 1, 2016 .
- ↑ SQL2011 Features in PostgreSQL. Retrieved July 1, 2016 .
- ↑ Temporal tables in MSSQL 2016. Retrieved July 1, 2016 .
- ^ MariaDB to Update its Server with Temporal Data Processing. dbta.com, February 28, 2018, accessed on January 13, 2019 .
literature
- Thomas Myrach: Temporal Databases in Business Information Systems ; Teubner Verlag; Wiesbaden 2005; ISBN 3-519-00442-9