Computer Science 4611
Database Management Systems
Spring 2003

Programming Assignment 2
JDBC (35 points)
Due Monday March 3rd, 2003


In this assignment, you will interact with a database through queries using Java Database Connectivity (JDBC) to perform SQL Queries. You will modify a java program ( to establish a connection to the database, query the database, and display the results/errors of the query.

You can learn more about how to interact with a database through JDBC in the JDBC trail of the Java tutorial.

You should read the Readme.txt provided with the OracleTM software for issues specific to JDBC connectivity with Oracle. This can be found at the path $ORACLE_HOME/jdbc/Readme.txt after you log into bulldog server. You need to set up the environment in order to compile and run JDBC programs for Oracle. Add $ORACLE_HOME/jdbc/lib/ to your CLASSPATH and add $ORACLE_HOME/jdbc/lib to your LD_LIBRARY_PATH. This can be done as follows for C shell:

You can add these lines to your initialization script.

To view the GUI (after running the QueryInterface class) you need to be connected to the bulldog server via a X-Client. You can find a X-Client software installed on the machines in MWAH 177 lab. Connect using the X-Client software to bulldog server by providing your login and password and then execute the java program as follows:

java QueryInterface

The Query Interface Program

The Star Wars Query Interface program provides a framework to perform some interesting queries on the Star Wars database we used in the first assignment. The query interface program provides the framework for your program to interface with the Star Wars database. It contains the skeleton to implement 5 different queries on the database. These queries are as follows:

  1. Show all the distinct planets a Star Wars character has visited based on the TIMETABLE table in the Star Wars database.
  2. Record the favorite Star Wars character, from amongst given 4 characters, of a user in a table as a poll. You should also display the summary of the poll results.
  3. Ask the user to classify the characters according to their affiliations and check whether the user has classified correctly or not.
  4. Read in a character and show all the characters who were on the same planet at the same time as the specified character.
  5. Ask the user to select a particular character race and planet type and then show the characters of the selected race and the planets they have visited of the selected planet type.

Working of the QueryInterface Program

The Star Wars Query Interface program is a menu driven application for querying the Star Wars database. There are two menus: Query and Connection. The Query Menu contains links to the various queries provided by the interface, and the connection menu provides functionality to connect to and disconnect from a database.

The panels to query the database do not appear unless you connect to the database. After you connect to the database, you can perform various queries on the database by clicking the queries in the Query menu.

What you have to code in the assignment

The Star Wars Query Interface program provides the code for the GUI and handling various error condtions (but not all). You have to modify the code the add the database interaction to the application using JDBC. A simple way to find where you should modify the code is to search for the word EDIT in the source code.

In all you will need to modify 12 functions, which are described as follows:

  1. getConnection : method to connect to the database, should open a connection to the
  2. disconnect : method to close the current database connection.
  3. getCharacters : method to retreive all of the characters in the CHARACTERS table.
  4. getVisitedPlanets : method to retreive the distinct planets visited by a particular character from the TIMETABLE table.
  5. executeQuery2 : method to record the user's name and his favorite character in a table created by you called VOTES whose script is given below. Allow the user to change his or her vote, but allow only one vote per user.
    create table votes
           username varchar2(20),
           character varchar2(20)
  6. getPollResults : method to retreive the number of votes each character received from the VOTES table.
  7. getRebels : method to get the names of all rebel affiliated characters from the CHARACTERS table.
  8. getEmpire : method to get the names of all empire affiliated characters from the CHARACTERS table.
  9. getPlanetMates : method to get the names of all characters who were on the same planet at the same time as the selected character from the TIMETABLE table.
  10. getPlanetTypes : method to get all distinct planet types from the PLANETS table.
  11. getRaces : method to get all distinct character races from the CHARACTERS table.
  12. getResults : method to find characters belonging to a certain race and the distinct planets of the specified type that they have been to.

Submission Guidelines

Turn in a paper copy of your code.

You will also need to submit an electronic copy of your code (both source and compiled code so we can test). To do so you should put your code in a subdirectory prog02 under a subdirectory corresponding to your login. For example, if your login is rmaclin, you code should be in a directory rmaclin/prog02. The java source files in this directory should end in .java. To submit your code you should tar the file as follows:

tar cf prog02.tar login/prog02

Then use the following link to upload your code.

Extra Credit (5 points)

Add a sixth query to the program that allows the user to specify a time period in a movie and to show all distinct characters who were around in that movie at that time. Movie should be a select box and time should be an input box.