Project Lab 2: SQL and JDBC
Objective
Your assignment in this lab is to build on the movie example that we have been using to illustrate SQL and JDBC. In the first part, you will extend the database by adding tables stores that rent DVDs and the DVDs they contain, some of which might be currently rented out. The second part of the lab will use JDBC to create an graphical application that allows you to query your database in a particular way, using the query we ask you to write in the first part.
Part 1: SQL
Having reproduced in Check-off lab 3 the basic movie database functionality I demonstrated in class, you should now extend it as follows: imagine that you have a chain of stores that rent out DVDs; currently you only have stores in St. Peter and Mankato, but you are hoping to expand. These stores will typically have multiple copies of DVDs, some of which are rented out at any given time. To keep track of this information, you should 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 DVD's record, since the per-DVD information will just have the store ID number.
- The other table holds the per-DVD information. It should have one row per DVD our chain owns, and a minimum of three columns: movie ID number (referencing the movies table), store ID number (referencing the stores table), and customer ID number that the DVD is currently rented to. This last column would tie in with a table of customers; for our immediate needs, you can just use the same persons table that holds directors and actors. (A real customers table would likely contain other attributes not relevant for directors and actors.) All we'll pay attention to is whether the customer ID column in a particular DVD's row contains an integer (meaning the DVD is checked out) or the special NULL value (meaning the store should have the DVD on the shelves). NULL is used in SQL for missing data, such as here the absence of a customer to whom the DVD is rented. You can check in a where clause whether a column IS NULL or IS NOT NULL. (In UML terms, the CheckedOutTo association between DVD and Person has multiplicity 0..1 on the Person end.)
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, and to whom.
Now, here is the crux of this part 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 the St. Peter store.
Part 2: JDBC
This part should be started after I talk about JDBC in class. To get started on this part, you should create a package called
movies in the Java project Movies you created in part 1. You should then download and uncompress the zip file
movies.zip and copy the files there into the movies package. For your convenience,
I have included the two Java files Decades.java and DecadesPrep.java which are the first two examples I
did in class; they are for your reference only, and are not part of this assignment.
In order to run the programs, you will need the appropriate sql driver jar; you should use the same mysql-connector-java-5.1.7-bin.jar
file you used in check-off lab 3; for your convenience, I am including that jar file here.
You need to add this jar file to your project's build path. I showed how to do this in class, but here is a short explanation:
Right click on the Movies project in the Package Explore and go to Build Path > Configure Build Path. After doing this, a window
titled something like "Properties for Movies" should open, open to the Libraries tab. You should then press the Add External JARS...
button, which will let you add mysql-connector-java-5.1.7-bin.jar to the project's build path.
I am also providing a skeletal Java client program called FindActor.java that provides a graphical user interface
(primitive, admittedly) for your program. You should be able to run this class as a Java application and see how it creates
a new window. FindActor.java relies on another class called MoviesDao.java (DAO stands for Data
Access Object) that should connect to and send queries to your database on mcs-mysql.gac.edu. You should be able to figure out how to do
this using the two example files Decades.java and DecadesPrep.java (the latter is the one you should concentrate on).
Currently the method getMoveNames() in FindActor.java has
two fixed sample movies that it claims are the answer in every circumstance, just so you can see how the output
should be done once you retrieve the real movies. You will need to change this. In addition to modifying the getMoveNames() method,
you may want to add one or more instance variables to MoviesDao and a constructor to initialize them. Your primary
task in this part is to make those modifications so that the correct answers are given for every actor and store. Be sure your code works for Peter O'Toole,
despite the apostrophe in his name. The easy way to do that is by using a PreparedStatement.
If you are looking for a little extra to do, you can fix one of 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.