Assignment: Database Programming
1)
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 emp_dept_change that keeps track of such history.
EMP_DEPT_CHANGE(EMPLOYEE_ID, EMPLOYEE_NAME, OLD_DEPARTMENT_NAME,
NEW_DEPARTMENT_NAME, EFFECTIVE_DATE);Write a trigger emp_dept_change_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 emp_dept_change table in any situations.
o The OLD_DEPARTMENT_NAMEis always'[New Hire]'.
o Find the new department name from the departmenttable based on the new department_id.
If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.
• When an employee changes his/herdepartment (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change 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 emp_dept_change table.)
o Find the old department name from the departmenttable based on the old department_id.
If the olddepartment_id is NULL, the OLD_DEPARTMENT_NAMEwill be '[-----]'.
o Find the new department name from the departmenttable based on the new department_id.
If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.
• The SYSDATEcan be usedin theEFFECTIVE_DATEcolumn.
• 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 trigger.
• You can only use the department, employee, andemp_dept_changetables 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) Create the emp_dept_changetable,
CREATE TABLE emp_dept_change
(
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_dept_change_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.
Q & A)
Q: I keep getting the following prompt when I try to use :OLD and :NEW when doing this week's homework. Does this have to do with my system settings or do I need to enter something on this screen?
A: You should always click the 2nd button or F5 to run your PL/SQL program. You can find that from lecture note 1, page 8.
2)
Create a trigger called fin_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 fin_job_minimum_salarytable in any situations.(For example, you can find that the minimum salary for a programmer is 800 from the fin_job_minimum_salarytable. Your trigger ensures that the salary for a programmer in the employee table is greater than or equal to 800 in any situations.)
Step 1) Create a table fin_job_minimum_salary as follows.
CREATE TABLE fin_job_minimum_salary
(
JOB VARCHAR2(50) PRIMARY KEY,
MINIMUM_SALARY NUMBER(7, 2) NOT NULL
);
Step 2) Populate the fin_job_minimum_salary table as follows.
INSERT INTO fin_job_minimum_salary VALUES ('ANALYST', 2000);
INSERT INTO fin_job_minimum_salary VALUES ('DATABASE ADMINISTRATOR', 2500);
INSERT INTO fin_job_minimum_salary VALUES ('PRESIDENT', 4800);
INSERT INTO fin_job_minimum_salary VALUES ('PROGRAMMER', 800);
INSERT INTO fin_job_minimum_salary VALUES ('PUBLIC ACCOUNTANT', 2400);
INSERT INTO fin_job_minimum_salary VALUES ('SALESMAN', 1800);
INSERT INTO fin_job_minimum_salary VALUES ('VICE PRESIDENT', 3800);
INSERT INTO fin_job_minimum_salary VALUES ('OTHERS', 1800);
COMMIT;
Step 3) Create the trigger fin_job_min_sal_trg.
• The fin_job_minimum_salarytable is read-only. Your trigger cannot modify any rows in the fin_job_minimum_salarytable.
• You must get the minimum salaries from the fin_job_minimum_salarytable in your trigger.
• Thejob is not case sensitive (e.g., SALESMAN = Salesman).
• Hard coding, except the string'OTHERS', is not allowed in your trigger (e.g., IF job = 'SALESMAN' THEN v_min_sal = 1800 ...).
• If the job cannot be found from the fin_job_minimum_salary table(e.g., the job "Program Facilitator" is not in the fin_job_minimum_salary table), the job isconsidered as "OTHERS". (You need to check whetherthe salary is equal to or greater than the minimum salary for job = 'OTHERS'.)
• If the salary is equal to or greater than the minimum salary of the corresponding job, your trigger does not change anything.
• If the salary is less than the minimum salary of the corresponding job, your trigger increases the salary to the minimum salary of the corresponding job.
• No temporary table/view/procedure/function is allowed in your trigger.
• To avoid a mutating table error, please take a look examples on page 8, class handout9. (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 table (e.g., different table names, column names, or data types) 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, pleasedelete and re-populate it.
Step 4) Test your trigger.
You need to create/runsome test casesto check whether the values of the salary columnin the employeetable meet the criteria in the fin_job_minimum_salary table in any situations.You do not need to submit your test cases.
Please submit a text file containing all the source codes to D2L before or on due date.