CS 430/630 Homework 4 Views, Intro to JDBC

Homework 4 due Tuesday midnight Apr. 18

1. Views

Consider a database schema with three relations:
emp (eid:integer, ename:string, age:integer, salary:decimal(10,2))
works (eid:integer, did:integer, pct_time:integer)
dept(did:integer, dname:string, budget:
decimal(10,2), managerid:integer)
The keys are underlined in each relation. Relation emp stores employee information such as unique identifier eid, employee name ename, age and salary. Relation dept stores the department unique identifier did, unique department name dname, the department budget and managerid which is the eid of the employee who is managing the department. The managerid value must always be found in the eid field of a record of the emp relation. The works relation tracks which employee works in which department, and what percentage of the time s/he allocates to that department. Each eid and each did in works appears in emp or dept respectively. Note that, an employee can work in several departments. Note that this setup is almost exactly what we have specified in createdb.sql, where "string" is translated to char or varchar.

a. Explain how to change the create tables in createdb.sql to match these specifications. Put this answer in the paper submission.
b. Create a view ManagerSummary that lists for every department the department name, manager ID and manager name, manager salary and the number of employees in that department. The view will have five columns with headings: DeptName, MgrID, MgrName, MgrSalary and EmpCount. Put this SQL in createview.sql and drop-view in dropview.sql.
c. Query the view above to retrieve the set of distinct salaries of managers who manage a department called “Sales”. Put this SQL in queryview1.sql.
d. Query the view above to find the name of the manager who manages most employees. If the same employee works in several departments, that employee is counted once in each of the departments. The manager is included in the count the same as all other employees, i.e., based on his or her records in the Works table. Put this SQL in queryview2.sql.

Make a script problem1.sql for dropping and then creating the view, then doing the two queries. Supply the output in problem1.txt.

2. Run JdbcCheckup.java (in the jdbc directory of our home page) using your Oracle account while logged in on topcat 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 topcat)

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 topcat run in hw4/JdbcCheckup_topcat.txt in your cs630/hw4 directory. To make a script, run "script", then run JdbcCheckup, then exit and rename typescript to JdbcCheckup_topcat.script.

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 topcat rather than on your own development system, say so in a README file and explain how you will be editing files for use on topcat. In this case, your topcat script will be enough.

Once you have JdbcCheckup working on the command line, you could try running it an IDE (DrJava, 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.

3. Consider the flights-aircraft-employees-certified database of our createdb.sql. Let's assume these employees are pilots. 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: Oracle can handle timezones, 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. Note that the database values for origin and destination have single quotes in their string values (we could avoid this by changing the flights.ctl file), so use LIKE '%namestring%'. For the selected flights, print out the flight number, distance, and duration of the flight, computed by time difference (in hours:min). Note that one of the time differences is negative: just report it that way.

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).

4. For FindPilot.java, which also accepts origin and destination cities, use the first flight found as in problem 3., and find a pilot (employee) for it, and an airplane, such that the pilot is certified for the aircraft, and the aircraft has cruisingrange above the distance of the flight. Find the lowest-salary such pilot by using order by and then using the first row returned. We are assuming any employee and any aircraft can be at the origin airport without delay. Print out the origin, destination, and distance of the flight, the pilot's name and salary and the aircraft's model and cruisingrange. Use only one additional query in this program over the one in FindFlights. Sample output:

Please enter information for connection to the database
Using Oracle (o) or MySql (m)? o
Oracle username: xxxx
Oracle password: xxxx
host: localhost
port (often 1521): 1521
sid (site id): dbs3
origin city: Madison
destination city: Chicago
using connection string: jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...using connection string: jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...connected.

Flight: from Madison to Chicago, distance 84
Pilot chosen: Milo Brooks, salary 20
Aircraft chosen: Piper Archer III, cruisingrange 520

Note that this salary value is probably a data error! Luckily Milo is not certified for larger airplanes or he would be chosen in every case.

Note: If all this code is in main, the code "needs procedurization".  To procedurize, set up one method (static method is fine) for finding the flight and another for finding the pilot. It turns out that all that is needed from the first method is the distance, so return that as the method return value and then provide it to the second method. Both need the origin and destination, so establish those in main.

Provide a typescript FindPilot.txt showing FindPilot reports on Los-Angeles to Honolulu, Los-Angeles to Sydney, and Madison to Chicago (3 runs of the program).

5. Database rows and Java objects. Study the emaillist example 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 EmalList.java accepts new emaillist users for the database. Write a Java program ShowUser.java to print out all information on one emaillist user. ShowUser should accept database credentials and connect to the database as EmalList does, then additionally request a emailList user email via readEntry, then call UserDB.selectUser to get that user's User object, and print out the user's firstname, lastname and email. 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 ShowUser displaying information on swong@gmail.com.

Files to deliver in cs630/hw4: (the .class and .jar driver files are also expected)

README: explanations to grader.

1. queryview1.sql, queryview2.sql, problem1.sql, problem1.txt
2. JdbcCheckup_topcat.txt, JdbcCheckup_devsys.txt
3. FindFlights.java, FindFlights.txt
4. FindPilot.java, FindPilot.txt
5. files from emaillist directory except its README, plus ShowUser.java, EmailList.txt