Draft:Decision time
In temporal databases, decision time is the time at which the decision was made about a fact stored in a database. It is used to keep a history of decisions about valid times.
Decision time was coined by Mario Nascimento and Margaret Eich.[1]
In a database table, the start and end times of the decision time interval may be represented by two additional table columns. This interval is closed at its lower bound and open at its upper bound. When a decision has not been superseded, the ending decision time is unknown and it may be considered as "until changed" or infinity (∞).
Comparison to valid times and transaction times
In a tri-temporal database, valid times are a history of valid time ranges for facts stored in the database. Decision times are a history of decisions about valid times, and transaction times are a history of decision times. Hence transaction times are a history of histories of histories.
While ISO/IEC 9075 has included support for "application-time period tables" (valid-time tables) and "system-versioned tables" (transaction-time tables) since SQL:2011, it does not include specific clauses for decision time.
Decision time is unconstrained in relationship to valid time, but it must be earlier than transaction time. e.g. decision time can't be assigned a date in the future.
Example
The following is a list of real-world events that occurred between the United States presidential elections of 1964 and 1976:
Date | Decision Maker | Real world event |
---|---|---|
November 3, 1964 | Electoral College | Election of 1964 |
November 5, 1968 | Electoral College | Election of 1968 |
November 7, 1972 | Electoral College | Election of 1972 |
October 10, 1973 | Spiro Agnew | Agnew resigns |
October 12, 1973 | Richard Nixon | Nixon nominates Ford |
December 6, 1973 | Congress | Congress confirms Ford |
August 9, 1974 | Richard Nixon | Nixon resigns |
August 20, 1974 | Gerald Ford | Ford nominates Rockefeller |
December 19, 1974 | Congress | Congress confirms Rockefeller |
November 2, 1976 | Electoral College | Election of 1976 |
Suppose there is a constant 7-day delay between the decision time and the transaction time committed to the database. Then following the election of 1976 the database contents would be:
President, Vice President, Valid From, Valid Till, Decision From, Decision To, Transaction From, Transaction To
Administration(Lyndon Johnson, Hubert Humphrey, 20-Jan-1965, 20-Jan-1969, 3-Nov-1964, ∞, 10-Nov-1964, ∞) Administration( Richard Nixon, Spiro Agnew, 20-Jan-1969, 20-Jan-1973, 5-Nov-1968, ∞, 12-Nov-1968, ∞) Administration( Richard Nixon, Spiro Agnew, 20-Jan-1973, 20-Jan-1977, 7-Nov-1972, ∞, 14-Nov-1972, 17-Oct-1973) Administration( Richard Nixon, Spiro Agnew, 20-Jan-1973, 20-Jan-1977, 7-Nov-1972, 10-Oct-1973, 17-Oct-1973, ∞) Administration( Richard Nixon, Spiro Agnew, 20-Jan-1973, 10-Oct-1973, 10-Oct-1973, ∞, 17-Oct-1973, ∞) Administration( Richard Nixon, (Vacant), 10-Oct-1973, 20-Jan-1977, 10-Oct-1973, ∞, 17-Oct-1973, 13-Dec-1973) Administration( Richard Nixon, Gerald Ford, ∞, 20-Jan-1977, 12-Oct-1973, ∞, 19-Oct-1973, 13-Dec-1973) Administration( Richard Nixon, (Vacant), 10-Oct-1973, 20-Jan-1977, 10-Oct-1973, 6-Dec-1973, 13-Dec-1973, ∞) Administration( Richard Nixon, (Vacant), 10-Oct-1973, 6-Dec-1973, 6-Dec-1973, ∞, 13-Dec-1973, ∞) Administration( Richard Nixon, Gerald Ford, ∞, 20-Jan-1977, 12-Oct-1973, 6-Dec-1973, 13-Dec-1973, ∞) Administration( Richard Nixon, Gerald Ford, 6-Dec-1973, 20-Jan-1977, 6-Dec-1973, ∞, 13-Dec-1973, 15-Aug-1974) Administration( Richard Nixon, Gerald Ford, 6-Dec-1973, 20-Jan-1977, 6-Dec-1973, 8-Aug-1974, 15-Aug-1974, ∞) Administration( Richard Nixon, Gerald Ford, 6-Dec-1973, 9-Aug-1974, 8-Aug-1974, ∞, 15-Aug-1974, ∞) Administration( Gerald Ford, (Vacant), 9-Aug-1974, 20-Jan-1977, 8-Aug-1974, ∞, 15-Aug-1974, 26-Dec-1974) Administration( Gerald Ford, Nelson Rockefeller, ∞, 20-Jan-1977, 20-Aug-1974, ∞, 27-Aug-1974, 26-Dec-1974) Administration( Gerald Ford, (Vacant), 9-Aug-1974, 20-Jan-1977, 8-Aug-1974, 19-Dec-1974, 26-Dec-1974, ∞) Administration( Gerald Ford, (Vacant), 9-Aug-1974, 19-Dec-1974, 19-Dec-1974, ∞, 26-Dec-1974, ∞) Administration( Gerald Ford, Nelson Rockefeller, ∞, 20-Jan-1977, 20-Aug-1974, 19-Dec-1974, 26-Dec-1974, ∞) Administration( Gerald Ford, Nelson Rockefeller, 19-Dec-1974, 20-Jan-1977, 19-Dec-1974, ∞, 26-Dec-1974, ∞) Administration( Jimmy Carter, Walter Mondale, 20-Jan-1977, 20-Jan-1981, 2-Nov-1976, ∞, 9-Nov-1976, ∞)
Consider the question of who would be President and Vice President for a valid time of 1-Jan-1977:
- Nixon/Agnew when using a decision time and transaction time of 14-Nov-1972
- Nixon/(Vacant) when using a decision time and transaction time of 17-Oct-1973
- Nixon/Ford when using a decision time and transaction time of 8-Aug-1974
- Ford/(Vacant) when using a decision time of 8-Aug-1974 and transaction time of current
- Ford/Rockefeller when using a decision time and transaction time of current
See also
References
- ^ Mario A. Nascimento, Margaret H. Eich, “Decision Time in Temporal Databases”, In Proceedings of the Second International Workshop on Temporal Representation and Reasoning, 1995, pp. 157-162