Assume an insurance customer has a basic homeowner’s policy begin on February 3, 2007, which is valid and renewed automatically unless the policy holder requests a change. The full information, including the period of validity for the customer’s policy (in the Policy_Term column) is shown by the following query:
NONSEQUENCED VALIDTIME SELECT * FROM Policy WHERE Policy_ID=540944;
Result:
Policy_ID Customer_ID Policy_Type Policy_Details Policy_Term
---------- ----------- ----------- -------------------- ------------------------
540944 123344567 HM STD-PL-332-YXY-01 ('07/02/03', '99/12/31')
Now assume that the customer requests after a year that the policy be upgraded to a premium homeowner’s policy. The update statement specifies a period of applicability for the change, which would be from the day of the change, February 3. 2008, indefinitely again, until the customer requests further changes or cancels the policy. The following statement would make the required update to the customer policy:
SEQUENCED VALIDTIME PERIOD '(2008-02-03, UNTIL_CHANGED)' UPDATE POLICY SET Policy_Type='HP' WHERE Policy_ID=540944;
The result will be two rows in the table for this Policy_ID:
- a history row showing the initial policy with the Policy_Term column (the valid-time column) showing an ending date corresponding to when the change was made
- a row reflecting the current policy, with valid time starting on the date the change was made
NONSEQUENCED VALIDTIME SELECT * FROM Policy where Policy_ID=540944;
Result:
Policy_ID Customer_ID Policy_Type Policy_Details Policy_Term ----------- ----------- ----------- ------------------- ------------------------ 540944 123344567 HP STD-PL-332-YXY-01 ('08/02/03', '99/12/31') 540944 123344567 HM STD-PL-332-YXY-01 ('07/02/03', '08/02/03')
For a nontemporal table, using nontemporal semantics, the Policy_Type value in the row would have simply been replaced, with no history row left in the table to show how the policy had existed prior to the change.