Computer Science 4611
Database Management Systems
Fall 2004

Programming Assignment 2
JDBC (35 points)
Due Wednesday, October 20, 2004

Introduction

In this assignment, you will interact with a database through queries using Java Database Connectivity (JDBC) to perform SQL Queries. You can use a sample java program (QueryUI.java) to learn how 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/classes12.zip to your CLASSPATH and add $ORACLE_HOME/jdbc/lib to your LD_LIBRARY_PATH. This can be done as follows for C shell:

set CLASSPATH=($ORACLE_HOME/jdbc/lib/classes12.zip:. $CLASSPATH)
set LD_LIBRARY_PATH=($LD_LIBRARY_PATH $ORACLE_HOME/jdbc/lib)
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 an 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 QueryUI

The Query UI Program

The Personal Address Book Query UI program provides a framework to perform some interesting queries on the Personal Address Book database we used in the first assignment. The query interface program provides the framework for your program to interface with the Personal Address Book database. It contains the skeleton to implement different queries on the database. You have to use this skeleton to execute and show results of 5 queries. These queries are as follows:

  1. Show all the appiontments (name of the person, address and time of appiontment) that are scheduled in the month of November 2004.
  2. Record 5 different suggested times of appointment from the user in a new table. Show which of them are suitable and which are not.
  3. Ask the user to classify the contacts in the "Friends", "Family" or "Business" category and check whether the classification is correct. Show the results.
  4. Read in a name of contact and show all possible information related to him/her (contact numbers, addresses and appointments scheduled with the contact if any).
  5. Ask the user to select a particular category and then show the persons belonging to that category and their phone numbers (if any).

Working of the QueryInterface Program

The Personal Address Book Query UI program is a menu driven application for querying the Personal Address Book 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 and executing them.

What you have to code in the assignment

The Personal Address Book Query UI program provides the skeleton code for the GUI and handling various error condtions (but not all). You have to modify the code to add the database interaction to the application using JDBC.

DETAILS TO BE ADDED.

Submission Guidelines

Turn in a hard 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. https://webapps.d.umn.edu/service/webdrop/rmaclin/cs4611-1-f2004/upload.cgi

Extra Credit (5 points)

Add a sixth query to the program that allows the user to specify the number of days for which he wants the upcoming appointments listed and displays the details as in query 1.