Time in Databases by g4509244

VIEWS: 0 PAGES: 12

									                 Time in Databases

                  CSCI 242 FEBRUARY 24, 2009




With thanks to Richard Snodgrass, 1985 ACM 0-89791-160-1/85/005/0236, although I don‟t agree with him
                     Snodgrass
                           2

 His work “taxonomy of time in databases” is cited as
  authoritative
 He defines three types of time, transaction time,
  valid time and user-defined time
 I disagree strongly with his definitions, believe that
  they do not reflect real experience with data
 Here we will talk about two types of time, transaction
  time and valid time
            Types of Time in Databases
                              3

 Transaction Time
   The time information is recorded in the database
   Relational databases work in transaction time
   History of transaction times is not history of the real
    information
 Valid Time
   The time that the stored information models reality
   Has start and end time
   Based on semantics of the problem, not mechanics of data
    entry
      Example: Retroactive Pay Increase
                             4

 Chen is given a salary increase
 The salary increase is approved at date t1
 The increase is effective at date t2.
 However, the increase does not get entered into the
  database until some other time, t3>t2 (called a
  retroactive salary increase).
 In this case, t1 is an attribute of the approval process,
  t2 is the start of valid time and t3 is the transaction
  time.
                     Databases and Time
                                      5

 Static
     Typical relational database
     Models information at only a single time
     Change in a stored value changes database to model data at another
      time
     To see past states, database must be rolled back to earlier states
 Historical
     Models the continuing evolution of information
     Can show past states of information without changing the database
     The entire history of the database is available at all times to all
      applications and all users
           Why Historical Databases?
                            6

 Modeling all past states of a database takes more
  disk space, and it continues to grow indefinitely
 In the past, disk space was so expensive that we
  could afford to model only the present state of the
  data
 Today‟s continuing growth in disk capacity for the
  same cost enables us to consider the use of historical
  databases for all data
 Historical databases are also called “point in time”
  databases
             Static Rollback Databases
                              7

 Today‟s databases are typically static rollback
  databases
   Database models information structures at one given
    time
   Updates move the database to model another time
   Database can be rolled back to an earlier time

 Rollback
   Changes stored values
   Requires suspension of normal processing
   Current data must be restored after rollback
                  What It Models
                           8

 Such a database can be called a static rollback
  database
 Through rollback it can model the data as of any
  particular time
 In case of failure, recovery processes can restore a
  database to the point of the last completed
  transaction
 Transactions rather than the real world are modeled;
  no way to enter a retroactive raise, for example
              Historical Databases
                           9

 Historical databases record the actual state of the
  data rather than transaction time
 Historical databases can present the state of the
  data at any time
                   How It Works
                           10

 Each row includes a start and stop time for its
  validity
 When a row is changed the old one is kept and its
  end date is set
 New row is given a start time at time of update
 Also called Point in Time database
               An Example: EMP
                           11

Schema Definition:
 EMP(EMPNO,ENAME,JOB,SAL,DEPTNO)
Now we add START and STOP for validity dates:
 EMP(EMPNO,ENAME,JOB,SAL,DEPTNO,START,STOP)
To find the status of EMP on 1/1/2008
 SELECT *
     FROM EMP
     WHERE START <= to_date( „01-JAN-2008','dd-mmm-
     yyyy') “1/1/2008” AND (STOP >= to_date( '01-JAN-
     2008','dd-mmm-yyyy') OR STOP ISNULL);
DELETE statements will now be UPDATEs instead.
                    Capabilities
                           12

 These techniques allow you to implement a database
  that can show its state as of any time in the past
 This can be done while the database is actively being
  used and even updated by multiple processes
 You can record retroactive changes
 You can easily implement applications to process
  retroactive changes

								
To top