Homework 5 due Sun. midnight May 5 in your cs630/hw5 directory. These little programs are just sketches of what can be done with Java and PL/SQL driving Oracle.
If you have trouble with your Oracle account being locked up, use "ps" on pe07 to see if you have sessions you can kill (kill -9 pid), and/or reboot your development machine to kill off clients from there. Run SQL files in SQLPlus with "set echo on".
1. Program to delete an apartment listing (once apartment is rented) from apartments and put the row in rented_apartments. Copy the table yelp_db.apartments to your own schema. This can be done by
create table apartments as (select * from yelp_db.apartments);
This command doesn't create a primary key, so use alter table to add the primary key constraint on your apartments table. Also create a new empty table rented_apartments with the same columns as apartments except the primary key is named simply id. To do this, use the same syntax as above except replace the * with an appropriate select list and add a where clause to avoid copying the rows. Add a PK here too.
Once this works, turn the four commands into a script create_apartmentdb.sql, and the corresponding drop tables in drop_apartmentdb.sql.
Write a small Java program DeleteListing.java that accepts database credentials like JdbcCheckup, and then accepts a listing number, and deletes the row having that listing number in your apartments table and inserts the corresponding row in rented_apartments.
In problem1.txt, show output from running drop_apartmentdb.sql, create_apartmentdb.sql, DeleteListing.java, then SQL queries to count the rows in the two apartments tables.
PL/SQL
Since PL/SQL is Oracle-specific, we are not trying to support mysql here. Mysql does have its own procedural SQL language.
2. Recall the messy expressions for calculating distances on Earth between longitude-latitude points in homework 4. Write an Oracle function geo_distance(long1, lat1, long2, lat2) that uses that messy expression in OracleDistanceExample.sql to calculate and return the distance (in meters, as before, in a binary_double) between longitude/latitude points (long1, lat1) and (long2, lat2), four binary_doubles (IEEE 64-bit floating point). Put the PL/SQL code in sf_distance.sql, along with a SQL query calling it using the coordinates used in OracleDistanceExample.sql. (Here "sf" is for stored function). Note that the code in sf_distance.sql does not have to access any database tables, just call the Oracle-provided function. Don't be confused by scientific notation for floating point numbers: 1.3524E+003 = 1.3522x103 = 1352.2. Output in problem2.txt.
3. Edit your (or the hw4 solution's) FindRestaurants.java to FindRestaurants1.java to use your new geo_distance function and test it as in homework 4. Note that the provided sf_distance_mysql.sql (in the hw5 subdirectory of the class web page directory) sets up a same-named function for mysql, thus allowing FindRestaurants1.java to work on both systems using the same code. However, you can stick to Oracle here if you want. You may need additional privileges to run stored functions on mysql (but not on Oracle).
4. A PL/SQL function using tables. a. Write a PL/SQL function apartment_stats(listing_param, category_param) that accepts a listing number and a category name (such as 'Restaurants') and returns the count of such businesses within 200 meters of the apartment with at least 10 reviews. Use your geo_distance function here. Again, be sure to restrict the query to looking at businesses in Las Vegas, NV to get decent performance. Put the definition in sf_apt_stats.sql, along with SQL queries using apartment_stats to display stats for listing 25, for categories 'Restaurants' and 'Grocery'. Provide its output in problem4.txt.
b. Edit FindRestaurants.java to use this function, in FindRestaurants2.java. It should output a report with the category name and corresponding count of businesses within 200m here, for categories 'Restaurants' and 'Grocery', not all the details of hw4's program. Show the run for listing 25 in FindRestaurants2.txt.
5. Write a stored procedure sp_delete_listing.sql to do the delete and insert of problem1 in PL/SQL procedure delete_listing(listing_param). It has one argument, the listing number to process. Use CALL in Sqlplus to test this, also in sp_delete_listing.sql. Output in problem5.txt.
6. (CS630 only) Modify DeleteListing of problem 1 to DeleteListingSP.java to call sp_delete_listing.sql's delete_listing from Java to do the actions using the stored procedure. See the provided files createFlightSP.sql and ReportFlightSP.java (in the hw5 subdirectory of the class web page directory) for a working example of a PL/SQL procedure called from Java. Test as in problem1, with output in problem6.txt.
Files to deliver in cs630/hw5:
README: explanations to grader, if any.
1. create_apartmentdb.sql, drop_apartmentdb.sql, DeleteListing.java,
problem1.txt
2. sf_distance.sql, problem2.txt
3. FindRestaurants1.java, FindRestaurants1.txt
4. sf_apt_stats.sql, problem4.txt,FindRestaurants2.java,
FindRestaurants2.txt
5. sp_delete_listing.sql, problem5.txt
6. CS630 only: DeleteListingSP.java, problem6.txt