Different forms of temporal databases

1. A historical database stores data with respect to valid time.2. A rollback database stores data with respect to transaction time. 3. A bitemporal database stores data with respect to both valid and transaction time – they store the history of data with respect to valid time and transaction time.

1

2

Mathematic for Temporal DataBase Management Systems US logic and derived Modal operators - gives an insight into new temporal operators that are specifically designed to manipulate temporal databases. These operators have been shown to be mathematically sound which makes them useful to extract temporal data. The publications discusses procedures to take in implementing these operators on relational databases systems using the logic associated with relational algebra. Temporal and Modal Logic – describes the mechanics of temporal and modal logic. They discuss the different tenses (Past and Future) and give the underlying semantics of the logic.

Temporal Database – gives an insight on how to build a complete temporal database system and how to form TSQL, which is an extension of SQL to handle temporal components denoted in records. The publications also describe how to optimise queries and gives material covering the subject in broad.

TimeChain Technology – provide a TDBMS that eases the difficult task of managing historical databases. The technology does not consider new temporal specific operators that are provided by US logic or the derived modal operators.

3

Application domains of temporal data Examples of application domains dealing with temporal data are:

1) Financial applications – e.g. history of stock markets, share prices;

2) Reservation systems – e.g. when was a flight booked;

3) Medical systems – e.g. patient records;

4) Computer applications – e.g. history of file back ups;

5) Archive management systems – e.g. sporting events, publications

and journals.

4

Non - temporal, Uni - temporal and Bi - temporal data

ID From To Name Price1 20/01/2006 13/06/2006 Eggs $1.201 13/06/2006 31/12/9999 Eggs $1.252 20/01/2006 01/01/2007 Milk $0.453 20/01/2006 31/12/9999 Bread $0.30

5

Temporal database history

The basic issue is this: if you have a primary key and some attributes in the table, and you add a date to the primary key to track historical changes, you can suddenly give out the original key over and over again. Deletes get different meaning. And so forth. In 1992, this issue was recognized but standard database theory was not yet up to resolving this issue, and neither was the then newly formalized SQL-92 standard.

Richard Snodgrass proposed in 1992 that temporal extensions to SQL be developed by the temporal database community. In response to this proposal, a virtual committee was formed to design extensions to the 1992 edition of the SQL standard (ANSI X3.135.-1992 and ISO/IEC 9075:1992).Those extensions, known as TSQL2, were developed during 1993 by this committee meeting only via email. In late 1993, Snodgrass first presented this work to the group responsible for the American National Standard for Database Language SQL, ANSI Technical Committee X3H2 (now known as NCITS H2). The preliminary language specification appeared in the March 1994 ACM SIGMOD Record. Based on responses to that specification, changes were made to the language, and the definitive version of the TSQL2 Language Specification was published in September, 1994.

An attempt was made to incorporate parts of TSQL2 into the new SQL standard SQL:1999, called SQL3. Parts of TSQL2 were included in a new substandard of SQL3, ISO/IEC 9075-7, called SQL/Temporal. However, the ISO project responsible for temporal support was canceled near the end of 2001.

The ideas and concepts described in the TSQL2 specification, such as Valid Time, Transaction Time and Bitemporal tables, have all found their way into the general literature on temporal databases since then. In 2002 Chris Date, Hugh Darwen and Nikos Lorentzo presented in their book Temporal Data & the Relational Model a treatment of the topic that includes many of the terms introduced by TSQL2 but also introduces the Sixth normal form to solve some of the issues.

6

Temporal extensions in SQL:2011 standard

SQL:2011 or ISO/IEC 9075:2011 (under the general title "Information technology – Database languages – SQL") is the seventh revision of the ISO (1987) and ANSI (1986) standard for the SQL database query lan-guage. It was formally adopted in December 2011.

Temporal supportOne of the main new features is improved support for temporal databases. Language enhancements for temporal data definition and manipulation in-clude:1) time period definitions use two standard table columns as the start and end of a named time period, with closed - open semantics; 2) definition of application time period tables (elsewhere called valid time tables), using the PERIOD FOR annotation;3) update and deletion of application time rows with automatic time period splitting;4) temporal primary keys incorporating application time periods with op-tional non-overlapping constraints via the WITHOUT OVERLAPS clause;5) temporal referential integrity constraints for application time tables;6) application time tables are queried using regular query syntax or using new temporal predicates for time periods including CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, and IMMEDIATELY SUCCEEDS (which are modified versions of Allen’s interval relations);7) definition of system-versioned tables (elsewhere called transaction time tables), using the PERIOD FOR SYSTEM_TIME annotation and WITH SYSTEM VERSIONING modifier. System time periods are main-tained automatically. Constraints for system-versioned tables are not re-quired to be temporal and are only enforced on current rows8) syntax for time-sliced and sequenced queries on system time tables via the AS OF SYSTEM TIME and VERSIONS BETWEEN SYSTEM TIME ... AND ... clauses;9) application time and system versioning can be used together to provide bitemporal tables.

7

Newspaper subscription calculationstime

Subscription

Appearance

Price

Discount

Temporālie dati un to saistība (piemērs)

Darbinieki(UZV, AMATS, LAIKS)

1s 2s 3s 4s

a1 a1 a1 a2 a2 a2 a2 a330 30 30 35 35 40 40 30 35 35

0 0 10 10 0 0 15 15 15 10

Subscription Start

End

1s2s3s4s

Appearance Start Endo1o2o3

Price Start End3035403035

Discount Start End01001510

8

Koks palīgs mākslinieks[2002.06.25, 2005.12.12) [2005.12.12, now)

Sakne otrais asistents asistents režisors[1996.12.03, 1999.10.23) [1999.10.23, 2000.07.12) [1999.07.12, now)

Egle asistents vadītājs[1995.10.23, 2000.07.12) [2000.07.12, now)

Atalgojums(AMATS, ALGA, A_LAIKS)palīgs 350 500

[2002.04.01, 2003.07.01) [2003.07.01, now)

mākslinieks 1000 1200[2000.04.02, 2004.07.04) [2004.07.04, now)

otrais asistents 500 700[1995.07.04, 2000.12.14) [2000.12.14, now)

asistents 600 750 1000[1993.06.02, 1995.07.04) [1995.07.04, 1998.12.14) [1998.12.14, now)

režisors 2300 2500[1998.11.11, 2001.03.24) [2001.03.24, now)

vadītājs 1000 1300 2000[1999.10.22, 2001.09.30) [2001.09.30, 2003.01.02) [2003.01.02, now)

9

Time element, time series and periodA time series is a series of data points indexed (or listed or graphed) in time order. Most commonly, a time series is a sequence taken at successive equally spaced points in time. Thus it is a sequence of discrete-time data.

Laika elements

Laika sērija

Time periods t

d1 d2 d3 d4 d5 d6 d7

[start, end] [d1, d2] closed periods

[d1, d2) (d1, d2] – partially closed periods

(d1, d2) - open periods

s1-1, s1, s1+1, s1+2, ... , e1, e1+1 elementary periods

Atribūta vērtība Sākuma laiks Beigu laiks

Atribūta vērtība Sākuma laiks Beigu laiksAtribūta vērtība Sākuma laiks Beigu laiksAtribūta vērtība Sākuma laiks Beigu laiksAtribūta vērtība Sākuma laiks Beigu laiks

10

DBMS Oracle time data tips

11

Datetime Field  Valid Values for Datetime Valid Values for INTERVAL 

YEAR  -4712 to 9999 (excluding year 0)  Any positive or negative integer  

MONTH  01 to 12  0 to 11  

DAY  01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the current NLS calendar) 

Any positive or negative integer 

HOUR  00 to 23  0 to 23  

MINUTE  00 to 59  0 to 59  

SECOND  00 to 59.9(n), where "9(n)" is the precision of time fractional seconds 

0 to 59.9(n), where "9(n)" is the precision of interval fractional seconds 

TIMEZONE_HOUR  -12 to 13 (This range accommodates daylight savings time changes.) 

Not applicable 

TIMEZONE_MINUTE  00 to 59  Not applicable 

Examples of intervals

Form of Interval Literal InterpretationINTERVAL '4 5:12:10.222' DAY TO SECOND(3)

4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.

INTERVAL '4 5:12' DAY TO MINUTE

4 days, 5 hours and 12 minutes.

INTERVAL '400 5' DAY(3) TO HOUR

400 days 5 hours.

INTERVAL '400' DAY(3) 400 days.INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)

11 hours, 12 minutes, and 10.2222222 seconds.

INTERVAL '11:20' HOUR TO MINUTE

11 hours and 20 minutes.

INTERVAL '10' HOUR 10 hours.

12

Form of Interval Literal InterpretationINTERVAL '10:22' MINUTE TO SECOND

10 minutes 22 seconds.

INTERVAL '10' MINUTE 10 minutes.INTERVAL '4' DAY 4 days.INTERVAL '25' HOUR 25 hours.INTERVAL '40' MINUTE 40 minutes.INTERVAL '120' HOUR(3) 120 hoursINTERVAL '30.12345' SECOND(2,4)

30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4.

13

Set of date format

TO_DATE('98-DEC-25:17:30','YY-MON-DD:HH24:MI')

The default date format for an Oracle date literal is specified by the initialization parameter NLS_DATE_FORMAT:

ALTER SYSTEM SET NLS_DATE_FORMAT = 'format';

Date-time functions

ADD_MONTHS

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

EXTRACT (datetime)

FROM_TZ

LAST_DAY

LOCALTIMESTAMP 

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NUMTODSINTERVAL

NUMTOYMINTERVAL

ROUND (date)

SESSIONTIMEZONE

SYS_EXTRACT_UTC 

SYSTIMESTAMP

SYSDATE

TO_DSINTERVAL

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TRUNC (date)

TZ_OFFSET 

14

Bitemporal database

15

tDBSDesign of temporal database

1. Extension of SQL language OVER() and ...

2. Use of temporal database management systems with temporal SQL.

3.Temporal extension design and development:

- commercials extensions;

- not commercial extensions.

16

Commerial temporal extensions of universal databases

1. Time DB (Time Consult).

2. Informix TimeSeries Datablade (IBM)

3. Immortal DB (MS rResearch group)

4. Oracle Flashback

5. Oracle Workspace Manager6. TimeIT (Department of Computer Science, University of Arizona).

7. ORES (Department of Computer Science, University of Athens;

IBM Ingres).

8. TOOBIS (Department of Computer Science, University of Athens).

9. Tiger (Department of Computer Science, University of Aalborg;

Oracle).

10. TimeChain Developer (TimeChain).

17

Implementations of bitemporal database

Oracle Workspace Manager a feature of Oracle Database, enables application developers and DBAs to manage current, proposed and historical versions of data in the same database.

TimeDB is a free temporal relational DBMS by TimeConsult. It runs as a frontend to Oracle that accepts TSQL2 statements and generates SQL92 statements.

PostgreSQL has an open-source contributed package that can be installed in the database to manage temporal data. The function reference is here.

Teradata version 13.10 and Teradata version 14 has temporal features built into the database.

Anchor Modeling emulates temporal features and automates the implementation in databases that lack support.The SQL:2011 standard added (belated) support for temporal features in synchronicity with an implementation in IBM DB2 version 10 as the so-called "time travel query".

18