Homework 5 due Sunday midnight April 30 in your cs630/hw5 directory.
If you have trouble with your Oracle account being locked up (esp. when using transactions), use "ps" on topcat to see if you have sessions you can kill (kill -9 pid), and/or reboot your development machine to kill off clients from there.
1. For FindPilots1.java, process the flights table in departs order, and find a pilot (employee) for each, and an airplane, such that the pilot is certified for the aircraft, and the aircraft has cruisingrange above the distance of the flight, and neither the pilot (eid) nor the airplane (aid) has yet been assigned to a flight. Find the lowest-salary such pilot by using order by and then using the first row returned, as in homework 4. Again we are assuming any employee and any aircraft can be at the origin airport without delay. If your algorithm finds a pilot, insert a new row in assigned_flights, with flno, eid, and aid. If no pilot is found, insert a row in delayed_flights, with just flno. As in homework 4, do as much computation as possible in the database. Note that you need to modify the query you used in homework 4 to use the assigned_flights table to eliminate pilots and aircraft already assigned. Use the main method to scan the flights table (or use a method for this), and provide a method assignFlight(Connection connection, int flno, int distance) to handle the work for a certain flight.
Create file createairdb.sql to create empty assigned_flights and delayed_flights tables, and dropairdb.sql to drop them. You can test this by executing dropairdb.sql, createairdb.sql, FindPilots1, then displaying tables assigned_flights and delayed_flights. For final runs, use the script developed in problem 3.
2. Once you have FindPilots1.java working, turn each separate flight determination (i.e., execution of assignFlight) into a transaction for FindPilots2.java. Put the commit after the call to assignFlight has returned successfully, so that the commit and rollback are visible in the same function's code. It needs to be clear that every transaction finishes with commit or rollback. Recall that it is important to keep each transaction short, so this is a good plan. Although we would like to use SERIALIZABLE isolation here, our server can't handle it under load, so just use default isolation (READ COMMITTED). Note that some databases might close the ResultSet from the table scan in main at the first COMMIT in assignFlight, but Oracle does not, so we are allowed to next() in main after committing an individual transaction in assignFlight. If interested, try it out on mysql to see if it also allows cursors to work through commits.
Don't save data in varrays or nested tables (these are PL/SQL memory structures described in Murach, Chap. 13). The point here is to use the database as the data manager. Since PL/SQL is Oracle-specific, we are not trying to support mysql here.
3. [4/24: fixed table name assigned_flights] Write a Sqlplus script
report.sql that shows "
select * from
order by flno" and "
select * from delayed_flights"
and then a unified report (using PL/SQL) that lists the flno, distance,
and then aid, eid for flights that have assigned pilots, but only the
string "(delayed)" for delayed flights, all in order of departure
time, like this:
Flight Distance Aid
7789 319 7 574489457
7 7489 (delayed)
Because the display format depends on the case, you need use a PL/SQL
cursor over the query to provide this output.
SQL> set serveroutput on;
SQL> var eid number; -- define bind variable eid
SQL> call assign_pilot(10, 1000, :eid);
hi from SP assign_pilot, param_flno = 10
SQL> print eid; -- display returned value (should be 18 from supplied sample)
5. Turn the code of assignFlight of FindPilotsSP1.java into a transaction, for FindPilotsSP2.java. It will use the same stored procedure as FindPilotsSP1. We could do the transaction in PL/SQL, but the fact that exceptions are being thrown back to Java code means we should do it in the Java caller, so all cases are covered in one code. It is very important to do a rollback if there is an exception, for otherwise transactions can linger in the system and lock up your account.
Files to deliver in cs630/hw5:
README: explanations to grader, if any.
report.sql--from problem 3
FindPilots1.java--prompt for Oracle username and password, host, port, sid as in JdbcCheckup
FindPilots1.txt--typescript of run of dropairdb, createairdb, compile and run FindPilots1, then report.sql
createSP1.sql--create stored procedure for FindPilotsSP1.java and FindPilotsSP2.java
FindPilotsSP1.java--prompt for Oracle username and password, host, port, sid
FindPilots1SP.txt: typescript of a run: execute dropairdb.sql, then createairdb.sql, then createSP1.sql, compile and run FindPilots1SP, then report.sql.
FindPilot2SP.java--prompt for Oracle username and password, host, port, sid.
FindPilot2SP.txt: execute dropairdb.sql, and so on as for FindPilotsSP1.