CSC 352 / 452: DATABASE PROGRAMMING ASSIGNMENT #6 complete solutions correct answers key

CSC 352 / 452: Database Programming

assignment #6 (60 Points)

complete solutions correct answers key 

Unless prior arrangements are made, homework turned in late but within 24 hours of the due time will be graded at 75% credit, homework turned in between 24 and 48 hours will be graded at 50% credit, and homework turned in later than 48 hours will not be accepted.

1) (30 points)

Employees may move to different departments. We want to keep track of the departments where each employee has been. To do so we create a new table employee_history that keeps track of such history.

EMPLOYEE_HISTORY(EMPLOYEE_ID, EMPLOYEE_NAME, OLD_DEPARTMENT_NAME,

               NEW_DEPARTMENT_NAME, EFFECTIVE_DATE);

Write a trigger emp_hist_trg that monitors the employee table as follows.

·         When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the employee_history table in any situations.

o   The OLD_DEPARTMENT_NAME is always '(New Hire)'.

o   Find the new department name from the department table based on the new department_id.

If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be 'TBA'.

·         When an employee changes his/her department (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the employee_history table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the employee_history table.)

o   Find the old department name from the department table based on the old department_id.

If the old department_id is NULL, the OLD_DEPARTMENT_NAME will be 'TBA'.

o   Find the new department name from the department table based on the new department_id.

If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be 'TBA'.

·         The SYSDATE can be used in the EFFECTIVE_DATE column.

·         You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.

·         No temporary table/view/procedure/function is allowed in your program.

• You can only use the department, employee, and employee_history tables in your trigger. You will get a zero point if you use a different table (e.g., different table names, column names, or data types) in your trigger.

Step 1) (0 point) Create the employee_history table,

CREATE TABLE employee_history

(

      EMPLOYEE_ID             NUMBER(4)         NOT NULL,

EMPLOYEE_NAME                 VARCHAR2(50)      NOT NULL,

OLD_DEPARTMENT_NAME           VARCHAR2(100)     NOT NULL,

NEW_DEPARTMENT_NAME           VARCHAR2(100)     NOT NULL,

EFFECTIVE_DATE                DATE         NOT NULL

);

Step 2) Create the trigger emp_hist_trg.

You will get a zero point if you use a different trigger name.

Step 3) Test your trigger.

You need to create/run some test cases to check your trigger. You do not need to submit your test cases.

2) (30 points)

Create a trigger called job_min_sal_trg on the employee table. When an INSERT or UPDATE statement is issued against the employee table, the trigger is fired to ensure that the value of the SALARY column meets the criteria in the job_minimum_salary table. (For example, you can find that the minimum salary for a programmer is 800 from the job_minimum_salary table. Your trigger ensures that the salary for a programmer in the employee table is greater than or equal to 800.)

Step 1) (0 point) Create a table job_minimum_salary as follows.

CREATE TABLE job_minimum_salary

(

      JOB         VARCHAR2(50) PRIMARY KEY,

      SALARY      NUMBER(7, 2) NOT NULL

);

Step 2) (0 point) Populate the job_minimum_salary table as follows.

INSERT INTO job_minimum_salary VALUES ('ANALYST', 2000);

INSERT INTO job_minimum_salary VALUES ('DATABASE ADMINISTRATOR', 2500);

INSERT INTO job_minimum_salary VALUES ('PRESIDENT', 4800);

INSERT INTO job_minimum_salary VALUES ('PROGRAMMER', 800);

INSERT INTO job_minimum_salary VALUES ('PUBLIC ACCOUNTANT', 2400);

INSERT INTO job_minimum_salary VALUES ('SALESMAN', 1800);

INSERT INTO job_minimum_salary VALUES ('VICE PRESIDENT', 3800);

INSERT INTO job_minimum_salary VALUES ('TBA', 1800);

COMMIT;

Step 3) Create the trigger job_min_sal_trg.

·         The job_minimum_salary table is read-only. Your trigger cannot modify any rows in the job_minimum_salary table.

·         You must get the minimum salaries from the job_minimum_salary table in your program.

·         Hard coding, except the string'TBA', is not allowed in your program (e.g., IF job = 'SALESMAN' THEN v_min_sal = 1800 …).

·         If the job cannot be found from the job_minimum_salary table (e.g., Program Facilitator), the job is considered as “TBA”. (You need to check whether the salary is equal to or greater than the minimum salary for job = 'TBA'.)

·         If the salary is equal to or greater than the minimum salary of the corresponding job, the trigger does not change anything.

·         If the salary is less than the minimum salary of the corresponding job, the trigger increases the salary to the minimum salary of the corresponding job.

·         No temporary table/view/procedure/function is allowed in your program.

·         To avoid a mutating table error, please take a look examples on page 6, class handout 8.  (Hint: you cannot use some INSERT/UPDATE statements to modify the employee table in your trigger.)

·         You will get a zero point if you use a different trigger name.

·         If you modified the employee table created in Assignment #1, please delete and re-populate it.

Step 4) Test your trigger.

You need to create/run some test cases to check whether the values of the salary column in the employee table meet the criteria in the job_minimum_salary table in any situations. You do not need to submit your test cases.