Maintain your data
Embedded Files
Start Inserting, Updating and Deleting your data in the context of Time
Start Inserting, Updating and Deleting your data in the context of Time
EMP-DEPT
EMP-DEPT
We will use the EMP table from the classic EMP-DEPT schema as an example to show how an employee is inserted, updated, corrected and deleted in the context of time.
Note how the time context and period of effect rules are set before the DML.
soe - start of existence
sov - start of valid
eoe - end of existence
Insert
Insert
Create employee Fred, now, as a salesman on salary 100k and commission 20k. The job only lasts for a year, so end his tenure on date t1 (in the future).
set VALID_TIME();
set SPECIFIED_TIME(t1);
set INSERT_UNTIL (specified_time);
insert EMP
( empno
, nino
, ename
, job
, mgr
, sal
, comm
, deptno)
values
( 123
, 'NA135238A'
, 'Fred'
, 'Salesman'
, 33
, 100000
, 20000
, 10);
Update
Update
We arrange for Fred to move to Technical Support on a lower salary and no commission for the last 3 months of his tenure.
set VALID_TIME(t1);
set UPDATE_UNTIL(eoe);
update EMP
set
job = 'Technical Support'
, sal = 150000
, comm = 0
, deptno = 20
where
empno = 123;
Update
Update
Time moves on. We realise that Fred's start date is wrong. Let's correct that.
set VALID_TIME();
update EMP
set
soe = t1
where
empno = 123;
Update
Update
Time moves on. Fred is doing well. Let's extend his tenure.
set VALID_TIME();
update EMP
set
eoe = t1
where
empno = 123;
Update
Update
We realise that Fred was a 'Sales Intern' for the first 3 months. Let's correct that.
set VALID_TIME(t1);
set SPECIFIED_TIME(t2);
set UPDATE_UNTIL (specified_time);
update EMP
set
job = 'Sales Intern'
, sal = 30000
, comm = 500
where
empno = 123;
Update
Update
Actually, he was a 'Sales Intern for the first 2 months. Let's correct that.
set VALID_TIME(t1);
set SPECIFIED_TIME(t2);
set UPDATE_UNTIL (specified_time);
update EMP
set job = 'Salesman'
, sal = 100000
, comm = 20000
where
empno = 123;
Update
Update
Time moves on. We realise that Fred's start date is still wrong. Let's correct that again.
set VALID_TIME();
update EMP
set
SOE = t1
where
empno = 123;
Update
Update
Time moves on. We realise that Fred did so well that he stayed in Sales, as the Director, for an extra month before moving to Technical Support. Let's correct that.
set VALID_TIME(t1);
set SPECIFIED_TIME(t2);
set UPDATE_UNTIL(specified_time);
update EMP
set
job = 'Sales Director'
, mgr = 12
, sal = 130000
, comm = 0
, deptno = 10
where
empno = 123;
Insulate from
Insulate from
Complexity
Complexity
Notice how the developer is completely insulated from the underlying complexities of inserts, updates and deletes such as ensuring that there are no gaps or overlaps in the dates, and that departments exist when the employee row refers to them or when the employees existence is extended, and keeping a perfect audit of all corrected histories of exactly what has happened.
The code doing that, normally written by developers, was 100% generated by the system.
Developers just set a time context and perform DML.
Elevate to
Elevate to
Current View
Current View
Working with the current view means that the developer is at a level where the infrastructure takes care of the time dimensions for all DML. Quite simply, before all DML operations the time is set to either the present time or to a specified time. The infrastructure then takes care to ensure the DML is not compromising any referential or data integrity rules before inserting the data values. If referential rules are going to be breached or gaps in time or time overlaps are potentially going to be created the DML is not permitted. All policed by the infrastructure not the developers code.
Page updated
Google Sites
Report abuse