2023 Document Preview Objectives 1 Gain experience with SQL application 2 Understanding Database PL SQL Due Fri | Assignment Collections

Document Preview:

Objectives: 1. Gain experience with SQL application 2. Understanding Database PL/SQL Due: Fri day, June 2 0 , 2014. 11:59:59 PM (EST) In this homework , you will implement ORACLE triggers. The triggers will be used to maintain the consistency of a bi-temporal history table. Your homework will use a simplified EMPLOYEE table based on the database in Figure 3.6 of the textbook (Figure 5.6 in the fifth edition of the book), with only the attributes shown below: EMPLOYEE(LNAME, SSN, SALARY, DNO, VST, VET, TST, TET) A separate table EMP_HIST will maintain the history of changes by keeping the old versions of tuples and their attributes: EMP_HIST(LNAME, SSN, SALARY, DNO, VST, VET, TST, TET) Because circular (recursive) triggers are not allowed in ORACLE, we will two other tables: DUMMY_EMP(LNAME, SSN, SALARY, DNO, VST, VET, TST, TET). All the operations listed below will be applied to the table DUMMY_EMP, but the actual data will be stored in the other two tables. You will write appropriate triggers to keep the correct data in the two tables EMPLOYEE and EMP_HISTORY. Your triggers should maintain the consistency of the two tables when you logically (conceptually) insert, delete, and modify records in the DUMMY_EMP table. You should do the following: 1. Create the three tables listed above. The VST and VET (valid start time and valid end time) attributes will use day granularity (use the DATE data type for these attributes, or use CHAR(10) data type in the format “YYYY-MM-DD”). The TST and TET (transaction start time and transaction end time) will use the TIMESTAMP data type, or you can use CHAR(19) data type in the format “YYYY-MM-DD,HH:MM:SS”). 2. All the tuples in the EMPLOYEE table and the DUMMY_EMP will have the TET = “uc” (until changed) and the VET = “now”. You can use the timestamp (or string) “9999-12- 31,23:59:59” to represent “until changed”. 3. For the tuples that have VET = “now”, you can use the date (or string) “9999-12-31” to …