MC39 Lab 3: Database Usage (Spring 1999)

Due: March 2, 1999

In this lab, you will extend the movie example that we have been using to illustrate SQL and JDBC. All the work for this lab will need to be done on one of the MCS department PCs running Linux, since we only have the PostgreSQL client software installed on those machines.

First you should configure your environment for using PostgreSQL, by giving the following commands to the shell. (This assumes you are using csh or tcsh as your shell.)

set path=(/usr/local/pgsql/bin $path)
setenv MANPATH :/usr/local/pgsql/man
setenv CLASSPATH /usr/local/pgsql/java/lib/postgresql.jar:.

(To have these commands done each time you start a csh or tcsh, you can put them in the .cshrc file.) Now you should be able to use the man command to get information about PostgreSQL commands, for example

man createdb

for information on creating a database. (There is also documentation for PostgreSQL on the web, linked from the MC39 page.) And, you should be able to run PostgreSQL commands and Java programs that make JDBC access to PostgreSQL.

In particular, start by replicating the example I gave. Create a database (named with your username, which is the default) on kilpinen, and connect to it using psql, the interactive SQL interface:

createdb -h kilpinen
psql -h kilpinen

Now use SQL commands to create the movie_info and movie_actors tables, put the data into the tables, and do a couple sample queries to make sure it works. To put the data in, you can use commands like the following:

\copy movie_info from /Net/solen/home/m/a/max/MC39/movie_info

Having reproduced the basic movie database functionality I demonstrated, you are now to extend it so as to track the specific tapes that our movie store chain owns, which are copies of the movies described by movie_info and movie_actors. Those tapes may be owned by either of our two stores (St. Peter and Mankato, with the possibility of more stores later), and may be checked out to any of our customers or checked out to no customer, i.e., in the store. To track this information, add two tables:

One holds the information about stores in our chain. It should have one row per store, and at a minimum two columns: one containing the textual name of the store (St. Peter or Mankato) and one containing a store ID number. By putting this information in a separate table, we can do things like change the name of a store without having to alter each individual tape's record, since the per-tape information will just have the store ID number.
The other table holds the per-tape information. It should have one row per tape our chain owns, and a minimum of three columns: movie ID number (for relating to movie_info and movie_actors), store ID number (for relating to the table of stores), and customer ID number that the tape is currently rented to. This last column would tie in with a table of customers, but since that isn't relevant to our immediate needs, you needn't construct that table. All we'll pay attention to is whether this column in a particular row contains an integer (meaning the tape is checked out) or the special null value (meaning the store should have the tape). Null is used in SQL for missing data, such as here the absence of a customer to whom the tape is rented. You can check in a where clause whether a column "is null" or "is not null".

You should make up some data for these tables. So long as the stores are called St. Peter and Mankato, you can use your creativity regarding how many copies of each movie they are likely to stock and have rented out.

Now, here is the crux of the assignment: formulate a SQL query to find what movies store X has one or more copies of on the shelf that include actor Y. For example, you might want to know what movies with Claude Rains in them are currently available (not all rented out) at St. Peter.

Once you have this working in psql, you can try programming it in Java using JDBC. I am providing a skeletal Java client program, which provides the graphical user interface (primitive, admittedly) but not the JDBC calls to actually get the data. (Instead, it has two fixed sample movies that it always claims are the answer, just to show how the output should be done once you retrieve the real movies.) The code is linked from the web version of this lab handout. You should save it in a file called FindActor.java, because the class is named FindActor. To compile and run it, you would use the commands

javac FindActor.java
java FindActor

Try it first as is, then add the JDBC calls to access your PostgreSQL database for the answers. (You should be able to confine your attention to the FindActorController class. In addition to modifying its getMovies method, you may want to add one or more instance variables and a constructor to initialize them.) Your code should be based on the SQL query you directly used in the prior portion of the lab. Getting it to work directly in psql will get you most of the credit for the lab, but for full credit you need to get it to work from Java as well.

If you are looking for a little extra to do, you can rectify one of the FindActor program's shortcomings. Namely, it has hard-coded into it the names of the two stores (St. Peter and Mankato). This obviously will be a problem as the chain expands - we'll need to upgrade the client software and redistribute it to all the machines it is installed on. Instead, you should have FindActor query the database to find the names of the stores.

Instructor: Max Hailperin