Temporālās (laika) datu bāzes lietošana



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.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.Application domains of temporal data Examples of application domains dealing with temporal data are: Financial applications – e.g. history of stock markets, share prices;Reservation systems – e.g. when was a flight booked;Medical systems – e.g. patient records;Computer applications – e.g. history of file back ups;Archive management systems – e.g. sporting events, publications and journals. Non - temporal, Uni - temporal and Bi - temporal data038100161290IDFromToNamePrice120/01/200613/06/2006Eggs$1.20113/06/200631/12/9999Eggs$1.25220/01/200601/01/2007Milk$0.45320/01/200631/12/9999Bread$0.3006985Temporal database historyThe 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.Temporal extensions in SQL:2011 standardSQL: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 language. 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 include: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 optional 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 maintained automatically. Constraints for system-versioned tables are not required 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.Newspaper subscription calculationstime1s2s3s4sa1a1a1a2a2a2a2a3303030353540403035350010100015151510SubscriptionAppearancePriceDiscountAppearanceStartEndo1o2o3SubscriptionStartEnd1s2s3s4sDiscountStartEnd01001510PriceStartEnd3035403035Temporālie dati un to saistība (piemērs)Darbinieki(UZV, AMATS, LAIKS)Kokspalīgsmākslinieks[2002.06.25, 2005.12.12)[2005.12.12, now)Sakneotrais asistentsasistentsre?isors[1996.12.03, 1999.10.23)[1999.10.23, 2000.07.12)[1999.07.12, now)Egleasistentsvadītājs[1995.10.23, 2000.07.12)[2000.07.12, now)Atalgojums(AMATS, ALGA, A_LAIKS)palīgs350500[2002.04.01, 2003.07.01)[2003.07.01, now)mākslinieks10001200[2000.04.02, 2004.07.04)[2004.07.04, now)otrais asistents500700[1995.07.04, 2000.12.14)[2000.12.14, now)asistents6007501000[1993.06.02, 1995.07.04)[1995.07.04, 1998.12.14)[1998.12.14, now)re?isors23002500[1998.11.11, 2001.03.24)[2001.03.24, now)vadītājs100013002000[1999.10.22, 2001.09.30)[2001.09.30, 2003.01.02)[2003.01.02, now)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.Atribūta vērtībaSākuma laiksBeigu laiksLaika elementsAtribūta vērtībaSākuma laiksBeigu laiksAtribūta vērtībaSākuma laiksBeigu laiksAtribūta vērtībaSākuma laiksBeigu laiksAtribūta vērtībaSākuma laiksBeigu laiksLaika sērijaTime periods td1 d2 d3 d4 d5 d6 d7[start, end] [d1, d2] closed periods[d1, d2) (d1, d2] – partially closed periods(d1, d2) - open periodss1-1, s1, s1+1, s1+2, ... , e1, e1+1 elementary periodsDBMS Oracle time data tipsDatetime 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 intervalsForm of Interval LiteralInterpretationINTERVAL '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 MINUTE4 days, 5 hours and 12 minutes.INTERVAL '400 5' DAY(3) TO HOUR400 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 MINUTE11 hours and 20 minutes.INTERVAL '10' HOUR10 hours.INTERVAL '10:22' MINUTE TO SECOND10 minutes 22 seconds.INTERVAL '10' MINUTE10 minutes.INTERVAL '4' DAY4 days.INTERVAL '25' HOUR25 hours.INTERVAL '40' MINUTE40 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.Set of date formatTO_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 HYPERLINK ":\\Ora9_doc\\server.901\\a90125\\functions5.htm" \l "76719" ADD_MONTHS CURRENT_DATE CURRENT_TIMESTAMP DBTIMEZONE EXTRACT (datetime) FROM_TZ LAST_DAY LOCALTIMESTAMP? HYPERLINK ":\\Ora9_doc\\server.901\\a90125\\functions71.htm" \l "78041" MONTHS_BETWEEN NEW_TIME NEXT_DAY NUMTODSINTERVAL NUMTOYMINTERVAL ROUND (date) SESSIONTIMEZONE SYS_EXTRACT_UTC? HYPERLINK ":\\Ora9_doc\\server.901\\a90125\\functions127.htm" \l "999454" SYSTIMESTAMP SYSDATE TO_DSINTERVAL TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL TRUNC (date) TZ_OFFSET?2937510492760-243840434975Bitemporal database29737053426460-205105171450tDBSDesign of temporal database1. 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 merial temporal extensions of universal databases1. Time DB (Time Consult).2. Informix TimeSeries Datablade (IBM)3. Immortal DB (MS rResearch group)4. Oracle Flashback5. 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).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". ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download