Chapter 12 - Temporal Tables Create Functions
“I became a policeman because I wanted to be in a business where the customer is always wrong.”
Three types of Temporal Tables
| The Three types of Temporal Tables are: | |
 |
Valid Time Temporal Tables |
 |
Transaction Time Temporal Tables |
 |
Bi-Temporal Tables containing both Valid Time and Transaction Time. |
Temporal Tables use a Valid Time or Transaction Time or combine both Valid Time and Transaction Time to form Bi-Temporal tables.
CREATING a Bi-Temporal Table
CREATE MULTISET TABLE Property_Owners
| ( Cust_No | INTEGER |
| ,Prop_No | INTEGER |
| ,Prop_Val_Time | PERIOD (DATE) NOT NULL as VALIDTIME |
| ,Prop_Tran_Time | PERIOD (TIMESTAMP(6) with TIME ZONE) |
| NOT NULL as TRANSACTIONTIME | |
| ) PRIMARY INDEX(Prop_No) ; | |
This is a Bi-Temporal Table because one column is aliased VALIDTIME and another column is aliased TRANSACTIONTIME. This makes this table a Bi-Temporal Table.
PERIOD Data Types
CREATE MULTISET TABLE Property_Owners
| ( Cust_No | INTEGER |
| ,Prop_No | INTEGER |
| ,Prop_Val_Time | PERIOD(DATE) NOT NULL as VALIDTIME |
| ,Prop_Tran_Time | PERIOD(TIMESTAMP(6) with TIME ZONE) |
| NOT NULL as TRANSACTIONTIME | |
| ) PRIMARY INDEX(Prop_No) ; | |
A new data type PERIOD has been introduced. This means two dates (begin and end date) or it could be two Timestamps (begin and ending Timestamp).
Bi-Temporal Data Type Standards
CREATE MULTISET TABLE Property_Owners
(
| Cust_No | INTEGER |
| ,Prop_No | INTEGER |
| ,Prop_Val_Time | PERIOD (DATE) NOT NULL as VALIDTIME |
| ,Prop_Tran_Time | PERIOD (TIMESTAMP(6) with TIME ZONE) |
| NOT NULL as TRANSACTIONTIME | |
| ) | |
| PRIMARY INDEX(Prop_No) ; | |
What PERIOD Data Types do ValidTime and TransactionTime require?
- ValidTime can be either a date or a Timestamp
- TransactionTime must be a Timestamp written exactly as above!
The example above is perfect for your PERIOD Data type for TRANSACTIONTIME. You have options for the VALIDTIME, as it can be either a Date or Timestamp.
Bi-Temporal Example – Tera-Tom buys!
CREATE MULTISET TABLE Property_Owners
(
| Cust_No | INTEGER | |
| ,Prop_No | INTEGER | |
| ,Prop_Val_Time | PERIOD (DATE) NOT NULL as VALIDTIME | |
| ,Prop_Tran_Time | PERIOD (TIMESTAMP(6) with TIME ZONE) | |
| NOT NULL as TRANSACTIONTIME | ||
| ) | ||
| PRIMARY INDEX(Prop_No) ; | ||
INSERT INTO PROPERTY_OWNERS
(Cust_No, Prop_No)
VALUES (1, 100) ;
On January 1, 2011, Tera-Tom buys property 100 which is beach front property. Tera-Tom is Cust_No 1 in your table and number 1 in your heart.
A Look at the Temporal Results
INSERT INTO PROPERTY_OWNERS
(Cust_No, Prop_No)
VALUES (1, 100) ;
Below, is what the table looks like internally.
On January 1, 2011, Tera-Tom buys property 100 and this is what the Bi-Temporal table looks like. Notice the 9999-12-31 dates. That means this is an OPEN Date.
Bi-Temporal Example – Tera-Tom Sells!
UPDATE Property_Owners
SET Cust_No = 2
WHERE Prop_No = 100 ;
How will the table below change after the UPDATE?
On January 1, 2011, Tera-Tom buys property 100, and then Tera-Tom sells to Socrates (Cust_No 2) on February 14th, 2011.
Bi-Temporal Example – How the data looks!
Property_Owners Before Update
Property_Owners After Update
Here is how the new table looks like with three rows. In the bottom table example, there is only 1-row that is still open. Do you know which one? The last one!
Normal SQL for Bi-Temporal Tables
Property_Owners Table
It is special SQL that allows Bi-Temporal tables to work so effectively. You will see a wide variety of SQL Keywords before the real SQL starts. The first is normal SQL.
NONSEQUENCED SQL for Temporal Tables
Property_Owners Table
It is special SQL that allows Bi-Temporal tables to work so effectively. Here is a look at the keyword NONSEQUENCED. This brings back all customers.
AS OF SQL for Temporal Tables
Property_Owners Table
It is special SQL that allows Bi-Temporal tables to work so effectively. The VALIDTIME AS OF DATE ‘2011-01-30’ keywords report the state of Property_Owners on that exact date.
NONSEQUENCED for Both
NONSEQUENCED VALIDTIME
AND NONSEQUENCED TRANSACTIONTIME
SELECT * FROM Property_Owners ;
It is special SQL that allows Bi-Temporal tables to work so effectively. Above is NONSEQUENCED VALIDTIME and NONSEQUENCED TRANSACTIONTIME
Creating Views for Temporal Tables
CREATE VIEW SQL01.Prop_As_Is
AS
Locking row for access
CURRENT VALIDTIME
SELECT Cust_No
,Prop_No
BEGIN(Prop_Val_Time) AS Beg_Val_Time,
END(Prop_Val_Time) AS End_Val_Time,
FROM Property_Owners;
SELECT * FROM SQL01.Prop_As_Is ;
CREATE VIEW SQL01.Prop_As_Was
AS
Locking row for access
NONSEQUENCED VALIDTIME
SELECT Cust_No
,Prop_No
BEGIN(Prop_Val_Time) AS Beg_Val_Time,
END(Prop_Val_Time) AS End_Val_Time,
FROM Property_Owners;
SELECT * FROM SQL01.Prop_As_Was ;
You can create views that will allow users to see the way things are or the way things were. Above are two excellent examples.
Bi-Temporal Example – Socrates is DELETED!
DELETE FROM Property_Owners
WHERE Prop_No = 100 ;
How will the table change below after the DELETE?
Property_Owners Before DELETE
On April Fool's day, April 1, 2011, Socrates sells the property, but through another Mortgage company, so since the mortgage company no longer owns the property, Socrates is DELETED. How will the table look after the Delete?
Bi-Temporal Results – Socrates is DELETED
Property_Owners Before DELETE on April 1st
Property_Owners AFTER DELETE on April 1st
Here is the table and it has no Open Rows. The bold red shows why the row is closed.