Emp(Lname, Ssn, Salary, Dno)

CSE 4331/5331Fall 2010

Project 2

In this project, you will implement ORACLE triggers to maintain the consistency of 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 3.6 of the textbook (Sixth Edition – corresponds to Figure 5.6 in the Fifth or Fourth Edition), but with only the attributes shown below:

EMP(LNAME, SSN, SALARY, DNO)

The EMP 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:

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

The table EMP will only keep the current version of each record. A separate table EMP_BT_HIST table will maintain the history of changes by keeping the old versions of employee tuples and their attributes:

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

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

1. Create the tables for EMP and EMP_BT_HIST. 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 use the TIMESTAMP data type.
2. Write and test your triggers (these are described below).
3. Insert the tuples corresponding to the EMPLOYEE table shown in Figure 3.6 of the textbook (6th edition - corresponds to Figure 5.6 in 5th or 4th editions) in the EMP table. All tuples will have the same VST of January 1, 2010 and a VET of NULL (this will represent now). All tuples will have the same TST of “January 1, 2010 at 2pm” and a TET of NULL (this will represent uc (until changed)). At this point, the EMP_BT_HIST table will be empty.
4. Conceptually insert, delete, and update some employee data to demonstrate that your triggers work correctly. Choose Valid Time and Transaction Time values progressively later than January 1, 2010.

(a)When a new record with a new SSN is inserted using an SQL INSERT operation, the first versionis inserted only in the EMP table (the VET and TET should be NULL – you can use NULL as default value for these two attributes). The insert command should provide all the other attribute values of EMP, namely LNAME, SSN, SALARY, DNO, VST, TST. (Note: In a real system, the transaction start time will be the system clock time – for simplicity, you will provide this value as part of the INSERT command).

(b)When an attribute of an existing EMP record is updated, the SQL UPDATE operation must select the record using the SSN value, and it 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 two tuples in the EMP_BT_HIST table: the first tuple is identical to the current tuple in EMP, except for the TET = new_TST. The second tuplewill have the same current (old) attribute values for LNAME, SSN, SALARY, DNO, VST, and will have VET = new_VST, TST = new_TST, and TET = NULL. (We are assuming open intervals).

(c)To simulate the deletion of an employee, you should issue an SQL UPDATE operation that selects a record using the SSN value, and updates only the VET and TET (we will call the new values new_VST and new_TST) of a tuple to values that are later than its current VST and TST. Your triggers should insert two tuples in the EMP_BT_HIST table: the first tuple is identical to the current tuple, with only TET = new_TST. The second tuple will have the same current (old) attribute values for LNAME, SSN, SALARY, DNO, VST, and will have VET = new_VST, TST = new_TST, and TET = NULL. (We are assuming open intervals).

(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).

(e)Extra Credit: How can you simulate a correction operation in this system? Specify precisely how you can simulate a correction of either SALARY or DNO, and implement the appropriate triggers for a correction operation.

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.

Due Date: Wednesday, December 1, 2010 (before midnight).

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. You can use the same teams as for project 1. 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.