CSE 4331/5331                                                              Summer 2009

Project 1

In this project, you will implement triggers on ORACLE or MySQL to maintain the consistency of a temporal history table. You will then expand your project into a bi-temporal table with both valid time and transaction time. Your project will use a simplified EMPLOYEE table based on the database in Figure 5.6 of the textbook, with only the attributes shown below:

EMPLOYEE(LNAME, SSN, SALARY, DNO)

Part 1: Maintain a valid-time history table:The EMPLOYEE table will hold the current record of each EMPLOYEE, and will be expanded with VST (valid start time) and VET (valid end time) attributes as follows:

EMPLOYEE(LNAME, SSN, SALARY, DNO, VST, VET)A separate table EMP_HIST will maintain the history of changes by keeping the old versions of employee tuples and their attributes:

EMP_HIST(LNAME, SSN, SALARY, DNO, VST, VET)

Your triggers should maintain the consistency of the two tables when you insert, delete, and update the EMPLOYEE table. You should do the following:

  1. Create the tables for EMPLOYEE and EMP_HIST. The VST and VET (valid start time and valid end time) attributes will use day granularity (use the DATE data type).
  2. Write and test your triggers (these are described below).
  3. Insert the tuples corresponding to the database shown in Figure 5.6 of the textbook in the EMPLOYEE table. All tuples will have the same VST of January 1, 2005 and a VET of NULL (this will represent now). At this point, the EMP_HIST table will be empty.
  4. Insert, delete, and update some EMPLOYEE records to demonstrate that your triggers work correctly. Choose Valid Time values progressively later than January 1, 2005.

(a)    When a new record with a new SSN is inserted using an SQL INSERT operation, the first version is inserted only in the EMPLOYEE table (the VET should be NULL). The VST value should be later than January 1, 2005.

(b)   When an attribute of an existing EMPLOYEE is updated, the SQL UPDATE operation must change one of the attributes SALARY or DNO, as well as VST (we will call this new_VST, which must be later than the current VST in the tuple; we will call the current VST value in the tuple old_VST). Your triggers should automatically insert a tuple in the EMP_HIST table with the same current (old) attribute values for LNAME, SSN, SALARY, DNO and with VST = old_VST and VET = new_VST. (We are assuming open intervals).

(c)    To simulate the deletion of an employee, you should issue an SQL UPDATE operation that changes only the VET of a tuple, to a date value that is later than its VST. Your triggers should move the updated tuple to the EMP_HIST table (that is, delete it from the EMPLOYEE table, and insert it in the EMP_HIST table).

(d)   Assume that select conditions for SQL UPDATE operations will use the SSN values to select the tuple to be updated (in both (b) and (c) above).

Document your project. External documentation should include your CREATE TABLE and CREATE TRIGGER statements, and clearly state any assumptions you made. Internal documentation in your code should also be included.

Part 1 Due Date: Friday, July 10, 2008.

Important Note: In part (c), you may get an error called ˇ°self referencing triggerˇ±. You can create a separate table DUMMY_EMPLOYEE (which holds the same tuple contents as EMPLOYEE), and apply the operation in (c) to that table, then delete the record from EMPLOYEE table. The same holds for Part 2 (c).

Part 2: Maintain a bi-temporal table:

The EMPLOYEE table will be expanded with VST (valid start time) and VET (valid end time), plus TST (transaction start time) and TET (transaction end time) attributes as follows:

EMPLOYEE(LNAME, SSN, SALARY, DNO, VST, VET, TST, TET)

A separate table EMP_HIST_BT table will maintain the history of changes by keeping the old versions of employee tuples and their attributes:

EMP_HIST_BT(LNAME, SSN, SALARY, DNO, VST, VET, TST, TET)

Your triggers should maintain the consistency of the two tables when you insert, delete, and update the EMPLOYEE table. You should do the following:

  1. Create the tables for EMPLOYEE and EMP_HIST_BT. The VST and VET (valid start time and valid end time) attributes will use day granularity (use the DATE data type) and the TST and TET will also use the DATE data type.
  2. Write and test your triggers (these are described below).
  3. Insert the tuples corresponding to the database shown in Figure 5.6 of the textbook in the EMPLOYEE table. All tuples will have the same VST of January 1, 2005 and a VET of NULL (this will represent now). All tuples will also have the same TST of January 1, 2005 and a TET of NULL (this will represent uc (until changed)). At this point, the EMP_HIST_BT table will be empty.
  4. Insert, delete, and update some EMPLOYEE records to demonstrate that your triggers work correctly. Choose Valid Time and Transaction Time values progressively later than January 1, 2005.

(a)    When a new record with a new SSN is inserted using an SQL INSERT operation, the first version is inserted only in the EMPLOYEE table (the VET and TET should be NULL). You should provide values for VST and TST.

(b)   When an attribute of an existing EMPLOYEE is updated, the SQL UPDATE operation must update one of the attributes SALARY or DNO, as well as VST and TST (we will call these new_VST and new_TST, which must be later than the VST and TST currently in the tuple; we will call the current values in the tuple old_VST and old_TST). Your triggers should automatically insert a tuple in the EMP_HIST_BT table with the same current (old) attribute values for LNAME, SSN, SALARY, DNO and with VST = old_VST and VET = new_VST and TST = old_TST and TET = new_TST. (We are assuming open intervals).

(c)    To simulate the deletion of an employee, you should issue an SQL UPDATE operation that changes only the VET and TET of a tuple to values that are later than its current VST and TST. Your triggers should move the updated tuple to the EMP_HIST table (that is, delete it from the EMPLOYEE table, and insert it in the EMP_HIST table).

(d)   Assume that the conditions for SQL UPDATE operations will use the SSN values to select the tuples to be updated (in both (b) and (c) above).

Document your project. External documentation should include your CREATE TABLE and CREATE TRIGGER statements, and clearly state any assumptions you made. Internal documentation in your code should also be included.

Part 2 Due Date: Wednesday, July 15, 2009.

Instructions on how to turn in your project will be posted on the Web site of the course, and a demo will be required. This project can be done in teams of up to two students. All students in a team will get the same grade. You can also do the project individually. No teams with more than two students will be permitted.