Please upload your files using the online
turnin server.
Note that file
uploading will be disabled automatically after 7:40PM, so please turn
in your work on time.
[Schema] Download the following scripts:
Run the hr8_main.sql script,
which will create and populate a number of tables as shown in Figure 1.
Figure 1. Human Resource (HR) Schema
You will use these tables to complete the exercises for this lab. If
you
want to remove the tables from your schema, simply run the script hr8_drop.sql.
[JDBC] (5pt) The job of an employee may change due to a number of
reasons. For example, a sales representative may be promoted to sales
manager, or an engineer may be transfered from one department to
another. For this exercise, write a Java program to update the database
when an employee changes to a new job. In particular, the program takes
the following input:
The id of the employee
The information about the employee's new job, which include phone
number, job id, salary, commission percentage, manager id, and
department id.
With the input, your program should perform the following
operations:
Use the JOBS table to check whether the new salary of the
employee is within the salary range of the new job.
Add an new entry to the JOB_HISTORY table to record the
information about the employee's current job.
Update the EMPLOYEES table with the information about the
employee's new job.
All the operations should be performed as part of a transaction, and if
any error occurs during the transaction, all operations should be
properly rolled back, and the program outputs an error message. For
this exercise, you may use EmployeeJDBC-template.java
as a template for your program.
One of the purposes of this exercise is to make sure that you can
compile and run Java programs using Oracle JDBC driver on the classroom
machines. So please complete this exercise and show the instructor that
your program works correctly before you proceed to the next section.
[Stored Procedures and Triggers] Implement the employee job change
using stored procedures and triggers. In particular,
(2pt) Write a trigger to ensure that an employee's salary is
always within the
salary range of the job.
(3pt) Write a trigger to automatically add a new entry to the
JOB_HISTORY table if the JOB_ID field and/or the DEPARTMENT_ID field of
the EMPLOYEES table are updated.
[Embedded SQL] (extra credit 5pt) Implement the employee job change
using SQLJ. Note that like in the JDBC exercise, all the operations
must be implemented on the application side, e.g. you cannot rely on
the stored procedures and triggers you developed in the previous
exercise.
