[ Home ] [ Up ] [ Assignment 1 ] [ Assignment 2 ] [ Assignment 3 ] [ Assignment 4 ] [ Assignment 5 ]

Video Store Database

In this assignment, we'll build a very simple database (using JDBC) for a (very small!) Video Rental store. We'll create a Java application which will allow you to "rent" movies to customers, and to report on current rentals.

Requirements

We'll be creating a "Video Store" database, using JDBC, which contains the following tables:

There is one row in the Tapes table for each distinct movie the video store has in its inventory. For example, if the store has 24 copies of "A Clockwork Orange", there is a single row in the Tapes table for that title, and the sum of QuantityAvailable and QuantityRented for that row will always be 24 (of course, if the store buys more copies, or throws copies away, the total will change.)

There will be one row in the Customers table for each of the video store's customers.

There is one row in the Rentals table for each copy of a tape rented by each customer. For example, if customer "Joseph Mangione" rents a copy of "Gone with the Wind", a copy of "Godzilla" and two copies of "Ghostbusters", there will be four rows in the Rentals table for that transaction.

What To Do

Here are the steps you should follow to complete this assignment.

Obtain a JDBC Driver and Database

Choose a JDBC driver/database to use. Click here for a list of possible JDBC drivers to use, together with links to their sources. Note that it will pay you to choose a driver/database that is not too difficult to learn, so I suggest you do some research to identify the best one for your needs. Another thing to keep in mind is that some drivers come with their associated database, and some do not. For example, the Oracle JDBC driver is freely downloadable from the Oracle web site, but you can only use it if you also have an appropriate Oracle database installed on your system (or available to you via a client/server connection to another system).

Install the driver/database on your system.

Learn How to Use Your Selected Driver/Database

Whatever your choice of driver/database, you will need to learn how to create a database. Each database has its own conventions for creating a database, and you will have to do the appropriate reading in that database's documentation to learn how.

If you decide to use the JDBC/ODBC Bridge Driver (which is the most easily available since it comes with the JDK, but which is not easy to use because you have to learn how to configure ODBC), you will have to learn how to do the following:

1. How to install ODBC, if it isn't already installed on your PC
2. How to install the ODBC Driver you plan to use, if it isn't already installed on your PC
3. How to use the ODBC administration. To access this, click on (on Microsoft Windows systems) Start->Settings->Control Panel, and when the Control Panel comes up, double-click on ODBC Data Sources, which will bring up the ODBC Data Source Administrator. Using this, you can determine what data sources are available, using which ODBC drivers. Once you've chosen an ODBC driver, you can then create a database of that type. Note that there are a number of options, and they are most likely dependent on which ODBC driver/database you choose.

Regardless of which database you choose, you will also have to learn the fully-qualified class name for your selected driver.

Create and Populate Your Video Store Database

Using your chosen JDBC driver/database, create the database. Then create the tables within that database, and populate those tables with appropriate sets of data. There should be:

• at least 30 tapes
• at least 10 customers
• at least 2 rentals

You can, if you wish, use the CreateDatabase program to create and populate the tables. However, you will have to study it to set it up to do what you wish (it isn't necessary to change the CreateDatabase program; you will have to create/change some files that it uses, however)

Verify Your Database Contents

Once your database has been created and populated, you should verify its contents. One convenient way of doing this is to use the ViewTables program (with suitable source modifications) to display the tables and their contents.

Write Your Video Store Application

We'll be writing a Java application to emulate a simple video store's operations.  

Note: Don't try to use an applet for JDBC; it's more trouble than we have time for in this course!

Actually, I've written the application for you;  all you have to do is plug in the proper JDBC code and database queries to finish the job.  You can download the application, minus the real contents of its data access class.  (See later.)

Note: I expect to see at least one (preferably more than one) use of PreparedStatement in your solution to this assignment.

Application Operation

Here's how the application operates:

Renting Movies to a Customer

When you run my VideoStore application, it comes up looking like:

When the program starts up, it makes a connection to the database using JDBC.

The layout consists of a top panel, a middle panel, and a bottom panel:

• The top panel has a main title, and a number of radio buttons -- one button for each supported video store operation.
• The middle panel contains a page for the operation being performed. As you click on the radio buttons in the top panel, an appropriate page is displayed in the middle panel. Then, as you proceed with your operation, you navigate to other pages.
• The bottom panel contains either "Connected", indicating that the connection to the database was successful, or "Not connected to database!" (in red) indicating that the connection failed. In the latter case, all the radio buttons should be disabled, since it's not possible to do anything in the program.

The top panel is already set up to "Rent Movies to Customer", and the middle panel presents a list of customers from which you can select. When the Select Customer page is displayed, the program populates the list with all the video store's customers, from the Customers table.

You select a customer from the list, and then click on the Next button, which brings up the following:

Here, you get to select the movie (or movies) that you rent to that customer. You select one of the movies on the left, and then click on the Add button to add that movie to the right, which is the customer's "shopping bag". Since there are multiple copies of a movie in stock at any given time, a movie is not removed from the left when it is added to the right.

Similarly, when you select one of the movies that have already been added to the right, and click on the Remove button, that movie is removed from the right. 

If you decide not to rent any movies after all, you can click on the Back button, which returns you to the Select Customer page.  From there, you can select another customer if you wish.

Once all the movies the customer wishes to rent have been selected and added to the right, you click the Finish button, which inserts the necessary entries into the Rentals table (one entry for each movie rented to the customer), and then the program returns to the "Select Customer" page:

Adding a New Customer

If you click on the Add New Customer radio button, you will see the following:

Notice that the application generates an ID for you; that field is disabled, so you can't change it.  You can now fill in the other fields, and then click on the Add button, at which point the program inserts a corresponding row into the Customers table.

Note that the all the fields are required, except for the ZIP and Phone. The ID must be an integer, and must not conflict with any existing customer ID. The program checks that the ID is an integer, that all the required fields are present and that the new customer's ID is unique, before the new row is inserted into the Customers table.

Once the customer has been added, the ID field's value is incremented by one, and the other fields are cleared out, and the program remains on the same page so that you can enter another new customer.

Editing or Deleting a Customer

If you select the "Edit or Delete Customer" radio button, the page changes to allow you to select a customer:

You select a customer, and click on the Next key, and the screen changes to look like this:

The program reads the data for the selected customer (based on the customer ID), and fills out the fields in the form. Note that the ID is in a field that is disabled, which means that it cannot be changed (the text in it also looks gray, instead of black)

At this point you have three choices:

1. Click on the Back button to go back and select a different customer
2. Click on the Delete button, which will delete that customer's record from the Customers table
3. Change data in the form, and then click on the Change button, which will update the customer's record in the Customers table. If nothing was changed on the form, an error message box comes up, informing you of that fact, the program does not update the customer record, and the page remains as is, after you dismiss the error message box.

Listing Rentals for a Customer

If you click on the "List Rentals for Customer" radio button, the window looks like:

Select the customer you are going to list rentals for, and click on Next, and you will see:

The program has queried the Rentals and Tapes tables (a join operation) to find all the movies that this customer currently has rented out. Note that it displays the movie ID, its title, when the movie was rented, and when it is due back.

You can use the Back button to return to the Select Customer page, or you can select another operation.

Listing all Movies Currently Rented Out

The final operation is to list all movies that are currently rented out, and by whom. Click on the "List All Rentals" radio button, and you will see:

In this case, the program has performed a 3-way join operation among the Rentals, Customers and Tapes tables.

Returning Movies

Clearly, in any real video store, there also has to be an operation to return movies into inventory when a customer drops them off.

I haven't implemented this, yet -- I got tired!

Note: If I didn't do it, I don't expect you to, either -- there's plenty of other stuff to do in this program already!  Remember, this is an exercise in learning how to use JDBC, not a real application.

Tips and Pointers from my Implementation

Here are some (hopefully helpful) pointers, tips and suggestions that I have learned from writing my version of the above:

JDBC Tips

• I must admit that I fought every JDBC driver I used. I tried the JDBC/ODBC Bridge driver with Microsoft Access, dBase, Text, etc. I also tried InstantDB, and a few odd other drivers. I did most of my work using JDBC/ODBC Bridge with Microsoft Access, but all of the drivers I used had their quirks. If you use the Bridge, however, you'll have to learn how to use the ODBC Data Sources "applet" in the Windows Control Panel, to create a data source to use with your chosen ODBC driver. (Note, I am told that Windows 2000 does not place the ODBC Data Sources applet in the Control Panel, but I'm not sure where.)
• If you use the JDBC/ODBC Bridge, then if you can find Microsoft Query on your system, or download it from somewhere, you may find it useful. I find it extremely non-intuitive, but you can, with a bit of work, test out queries against it.
• If you use the external properties file approach to specifying which JDBC driver you use, and its URL, then it's awfully easy to create yourself another database using a different driver and try again over there.
• I suggest that you work out the queries ahead of time, in some kind of interactive SQL environment (InstantDB has commSQL, for this purpose, in its examples directory). You will probably find it slow to try things out from within Java, where you try a query, and if it doesn't work, you have to change the query in the Java source, recompile, and try again.
• It is particularly important to work out your joins ahead of times (remember, joins are where you are selecting data from more than one table). They can get a little complex -- although none of the queries in this application is terribly complex.
• My version of the video store application has:
  • 7 SELECT statements (5 from a single table, one 2-way join, one 3-way join)
  • 2 INSERT statements
  • 2 DELETE statements
  • 1 UPDATE statement

  So it's not a huge job to come up with the statements to use. 

• When you are trying out a query from a program, you may find that the error messages that arise in SQLExceptions leave a lot to be desired. Sometimes, they had no relationship to what eventually turned out to be the problem. The strongest piece of advice I can give you is to start simple (trivial?) and gradually work towards what you want. Trying the full query in one attempt is often not a good strategy, especially if you are formulating a somewhat complex query.

Download the Application

Here's where you download the application.  The sources for the Video Store application are packaged in a JAR file.  Download it, and "unjar" it into a convenient source directory, maintaining the directory hierarchy. You can use the jar program provided with the Java Standard Edition, or you can use a utility such as WinZIP. 
(Note that the JAR file contains .class files.  You can remove these, because you should create a project in your favorite Java IDE -- NetBeans, or whatever -- and place the source files properly in that project, and then have the IDE build the necessary class files, etc.)

Once you've extracted the sources, and compiled them, the application should run immediately (videostore.VideoStore is the main class), except that it doesn't do any actual database access -- that's your job.

Change one file only -- DataAccess.java -- all other classes should be left alone.

The DataAccess class has a skeleton source, with comments at the appropriate places for where you will have to fill in with real code.

Complete the DataAccess Class

This is where you will have to work out the proper SQL statements to use, and how to call them.  There are a number of private utility methods that I used (see the end of the DataAccess.java file), which you might also find useful to avoid needless repetition of code.

Compile the DataAccess Class and Rebuild the Application

You will doubtless have to do a few iterations on this before you get it working.

When you submit your assignment, show me some screen shots to prove that you got it working (with different data than the above screen shots!).