Homework 4 due Sunday midnight Apr.28 in directory cs630/hw4. Note that homework 5, more on JDBC and also problems on PL/SQL, will be due the following Sunday.
1. Run JdbcCheckup.java (in the jdbc directory of our home page) using your Oracle account while logged in on pe07 and also from your development system. Specifically, copy the jdbc directory from /data/htdocs/cs630 to your cs630 homework directory:
cp -r /data/htdocs/cs630/jdbc cs630 (assuming your current directory is your login directory on pe07 or users)
That will give you the needed driver jar files as well as JdbcCheckup.java itself. Don't forget you need the driver jar file on the command line when you run the program, as detailed in the header comment in JdbcCheckup.java. Put the script of the pe07 run in hw4/JdbcCheckup_pe07.txt in your cs630/hw4 directory. To make a script, run "script", then run JdbcCheckup, then exit and rename typescript to JdbcCheckup_pe07.script. Optionally, run it using mysql on pe07 as well.
Transfer the whole jdbc directory to your development system. Note that you will need a tunnel to access Oracle from your dev system. See DatabaseSetup. Compile and run JdbcCheckup using the command line. Put the text of your dev system run in JdbcCheckup_devsys.txt and transfer it back to your cs630/hw4 directory. If you have decided to do the program development on pe07 rather than on your own development system, say so in a README file and explain how you will be editing files for use on pe07. In this case, your "pe07" script will be enough. Optionally, run JdbcCheckup to mysql on pe07.
Once you have JdbcCheckup working on the command line, you could try running it an IDE (Netbeans, eclipse, etc.) if that is how you develop Java programs. Note that each IDE has some way to add jar files (i.e. libraries) to a project. If you don't want to use an IDE, that's fine too. In that case use a text editor as described in DevelopmentSetup.
2. Consider the flights-aircraft-employees-certified database of our createdb.sql. The times (using SQL datatype timestamp) are assumed to all be using one timezone, so we don't have to worry about timezones. Read about the timestamp datatype for Oracle on pp. 531-545 of Murach, and, optionally, here for MySQL. FYI: SQL 92 and Oracle (but not mysql) can handle timezones via datatype "timestamp with time zone", so we could handle data that specified various timezones, as shown on pg. 549 of Murach.
Write a small Java program FindFlights that accepts database credentials like JdbcCheckup, and then accepts two cities (Los-Angeles and Chicago for example) and finds the flights from the first to the second in the flights table. For the selected flights, print out the flight number, distance, and duration of the flight, computed by time difference (and displayed in hours:min:sec or days hours:min:sec). Note that one of the time differences is negative: just report it that way. Note: don't use LIKE where equals (=) can be used to match the city name. Equals is much faster in general.
First write the query in query2.sql (output in query2.txt) to display the information for Los-Angeles and Chicago. It's easier to debug a query outside the program. This query should be portable and display the same results on Oracle and mysql, but mysql execution is optional.
A timestamp difference like arrives-departs has SQL type INTERVAL DAY TO SECOND (from SQL92, supported by Oracle, but not mysql), described in Murach, pg. 561. Unfortunately, JDBC will not map a SQL interval type value in the database to a time-related datatype of Java (and in fact Java has no specific time-interval type), but it will map it to String type, so one way to get the needed time difference is to form the difference in SQL in the select list, give it an alias, and use getString on that column of SQL output. You will receive a string in "days hr:min:sec" format. Alternatively, get JDBC to map the individual timestamps to java.sql.Timestamp values and then use Timestamp methods such as getTime() to convert to seconds, and then to minutes, hours and days.
Provide a typescript FindFlights.txt showing FindFlights reports on Los-Angeles to Honolulu, Los-Angeles to Sydney, and Madison to Chicago (3 runs of the program). The same program should work on both Oracle and Mysql, so use either one for output.
3. Recall that Las Vegas is well-represented in the YelpDB data that we have. I have added a table yelp_db.apartments that lists 215 locations in Las Vegas for (supposed) apartments for rent. Each apartment has a listing number, address, neighborhood, etc., and latitude and longitude. Since each business also has longitude/latitude information, we can get the database to calculate distances to various useful points close by to each apartment.
Oracle and Mysql code to compute a distance between two given longitude/latitude points is provided in the jdbc directory in files OracleDistanceExample.sql and MysqlDistanceExample.sql and the results agree well enough for our purposes. You may choose to use either Oracle or mysql for this problem, or support both.
Write a Java program FindRestaurants that accepts database credentials like JdbcCheckup, and then accepts a listing number for one apartment. The program then queries the businesses in Las Vegas, NV and, for each restaurant within 200 meters of the apartment, displays the name, rating, and number of reviews of each such restaurant, but only if there are at least 10 reviews. A restaurant is a business with category 'Restaurants' assigned to it. Be sure to restrict the query to businesses in Las Vegas, NV to get decent performance by avoiding useless distance calculations to businesses outside this city.
First write the query for apartment listing 120 that displays this
information and put it in query3_oracle.sql or query3_mysql.sql, and its
echo-on typescript in query3_oracle.txt or query3_mysql.txt. Provide a
typescript FindRestaurants_oracle.txt or FindRestaurants_mysql.txt
depending on which database you focused on, showing listing 120 again. In
fact, Mysql is easier to use for this, as will be evident by looking at
the *DistanceExample.sql files.
4. Database rows and Java objects. Study the emaillist example (partly from Murach's Java Servlets and JSP book) available in the emaillist subdirectory of the class home. See its README. This example uses a User object to hold information on one user, and holds the information on emaillist users in a database table userdb. The provided program EmailList.java accepts new emaillist users for the database. Write a Java program EditUser.java to allow changes to information on one emaillist user. EditUser should accept database credentials and connect to the database as EmailList does, then additionally request a emailList user email via readEntry, then call UserDB.selectUser to get that user's User object. Then output the firstname, ask for changed firstname or nullstring for no change, and similarly for lastname. Then use UserDB calls to change the database to reflect the edit. Use delete and insert or provide an update method and use that. Provide a typescript EmailList.txt that shows execution of dropuserdb.sql, createuserdb.sql, then a run of EmailList like that in README, followed by a run of EditUser editing information on swong@gmail.com, specifically changing Sue to Susan.
Files to deliver in cs630/hw4: (the
.class and .jar driver files are also expected.)
README: explanations to grader.
1. JdbcCheckup_pe07.txt, JdbcCheckup_devsys.txt
2. query2.sql, query2.txt, FindFlights.java, FindFlights.txt
3. query3_oracle.sql or query3_mysql.sql, query3_oracle.txt or
query3_mysql.txt, FindRestaurants.java, FindRestaurants_oracle.txt or
FindRestaurants_mysql.txt
4. files from emaillist directory except its README, plus EditUser.java,
EmailList.txt