Superb Professors

CPSC 551 – Advanced Database Design Fall 2014 Project A – Temporal Trigger Objectives: 1. Gain experience with SQL application 2. Understanding Database PL/SQL Due: Monday, Nov 3, 2014. 11:59:59 PM (EST) In this project, you will implement ORACLE triggers. The triggers will be used to maintain the consistency of a bi-temporal history table. Your project 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: Notice: The primary key are different in three tables. ? Table1: EMPLOYEE (LNAME, SSN, SALARY, DNO, VST, VET, TST, TET) LNAME SSN SALARY DNO VST VET TST TET Xyz 123456789 900.00 123 01-JAN-13 31-DEC-99 01-JAN-11 01:00:00.000000PM 31-DEC-99 11:59:59.000000PM Abc 000000000 800.00 101 01-JAN-13 31-DEC-99 01-JAN-11 01:00:00.000000PM 31-DEC-99 11:59:59.000000PM A separate table EMP_HIST will maintain the history of changes by keeping the old versions of tuples and their attributes: ? Table2: EMP_HIST (LNAME, SSN, SALARY, DNO, VST, VET, TST, TET) Because circular (recursive) triggers are not allowed in ORACLE, we will two other tables: ? Table3: 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: suggestion: VST and VET use “DATE” data type TST and TET use “TIMESTAMP” data type For example, if two tuples are inserted into EMP_HIST table, and the time is in a same second, it will cause insertion problem, because the primary key is same. However if you use “TIMESTAMP”, it is like 11:59:59.000000PM, the primary key cannot be same….

PLACE YOUR ORDER TO GET STARTEDThe post CPSC 551 – Advanced Database Design Fall 2014 Project A – Temporal Trigger Objectives: 1. Gain exper appeared first on Essay Gem.

"Order a Custom Paper on Similar Assignment! No Plagiarism! Enjoy 20% Discount"