MCS-270 Lab 3: Database Usage (Spring 2008)

Individual lab, due on Monday, March 10, at the beginning of class

Introduction

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 MySQL client software installed on those machines. You can, however, ssh into one of these computers and do your work, provided you have ssh capabilities on your home computer.

Resources

• Books on MySQL in monitor room:
  • MySQL Tutorial, published by MySQL Press
  • MySQL Language Reference, published by MySQL Press
• Web resources for MySQL:
  • MySQL tutorial
  • MySQL documentation

Part 1: MySQL

In Part 1, you will first replicate the example I gave in class, and then extend it as indicated below. Following are your Tasks for Part 1:

1. To replicate the SQL example I gave in class, you will first connect to the MySQL server on mcs-jsp.gac.edu using the username and password I will give you; create the movie database; select that database with the use command; create the tables; and finally, load the tables from the files I give you.

   Following are the commands to do this. The first command connects to the mysql server. (Here, you'll be prompted for your mysql password. ) The second and third commands are used to create a database and begin using it. Once it's created, the database should remain in place between connections to the server until you (or root) drops the database.

     mysql -h mcs-mysql.gac.edu -u dummy -p
   
     create database dummy_movies;
     use dummy_movies;
   

   Naturally, you'll want to replace the occurences of 'dummy' above with your username, and you have permissions to create any database starting with your username and an underscore. While you could ssh over to mcs-jsp to run mysql (without the -h mcs-jsp argument), I'd rather you run mysql from a lab machine.

   Now use SQL commands to create the movies, persons, and acts_in 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:

     load data local infile '/Net/gac/home/k/a/karl/public/270/movies/persons.data'  into table persons;
     load data local infile '/Net/gac/home/k/a/karl/public/270/movies/movies.data'  into table movies;
     load data local infile '/Net/gac/home/k/a/karl/public/270/movies/acts_in.data' into table acts_in;
   

2. Having reproduced the basic movie database functionality I demonstrated in class, 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 listed in the movies table. 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 (referencing the movies table), store ID number (referencing the stores table), and customer ID number that the tape 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 tape's 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. (In UML terms, the CheckedOutTo association between Tape 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.

3. 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.

Part 2: JDBC

This part should be started after I talk about JDBC in class. You may want to look the two JDBC examples I gave (Introductory JDBC Example and JDBC Prepared Statement Example). Following are your Tasks for Part 2:

1. For your convenience, I have created a folder containg various files and folders of use to you. Open a terminal on one of the departmental Linux machines, go to the directory where you want to copy it, and issue the following command:

       cp -r ~karl/public/270/lab3 .

   Having done that, explore the files there. The sub-directory sql contains useful sql files for you which should allow you to easily reset your database. The java sub-directory contains the files you will need for this part of the lab.

2. You can now try programming in Java using JDBC. First you should configure your environment for loading the necessary JDBC classes to access mysql, by giving the following commands to the shell (this assumes you are using csh or tcsh as your shell):

     setenv KARL ~karl
     setenv CLASSPATH $KARL/public/270/mysql.jar:.
   

   If you are running bash, use the following command:

   export CLASSPATH=/Net/solen/home/w/o/karl/public/270/mysql.jar:.
   

   (To have these commands done each time you start a csh or tcsh, you can put them in the file .cshrc or, respecitively, .bashrc) Now you should be able to run the Java programs that make JDBC access to MySQL.

3. Go to the java directory and recreate the examples I did in class. This will require you to change the database, username, and password in the appropriate places to your accounts; do so, and verify that it works correctly. You might test it by deleting a movie in mysql and seeing that it is reflected in the calls to Decades and DecadesPrep.

4. I also provided a skeletal Java client program called FindActor.java in the java directory that 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.) 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 MySQL database for the answers. (You should be able to confine your attention to the DatabaseController 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 the query to work directly in mysql 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. 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.

5. 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.

Submitting your code

1. In a shell, go to the parent directory of the directory containing your Lab3 directory. Warning: You will probably have problems if this directory name has spaces in it, since Linux doesn't look kindly on so-named files/directories.
2. Assuming that the directory containing your solution is named lab3, now type:

        ~karl/public/270/submit lab3