ESTIMATED TIME: 12 hours.
STARTER CODE: project2.tar.gz
Dowload and untar into your local directory. You will need to modify only Query.java.
POSTGRES: In addition to accessing the IMDB database on IISQLSRV, you will create a database and run it locally with Postgres. Here are instructions on setting up Postgres. If you're using your own computer, you need to install Postgres and/or the Java SDK onto your machine first.
IISQLSRV FROM HOME: If you are working on your own computer, read the SQL Server tunneling instructions, to learn how to change the SQL Server connection URL in dbconn.config.
JDBC DRIVERS: In order to connect to a database from Java, you need two database-specific JDBC drivers, one for Postgres and one for SQL Server. You must add these to your Java classpath (list of locations to search for libraries and program class files).
If you are working in the labs, you can simply use the following Windows shell script that sets the classpath for you: 444shell.cmd.
If you are working on your own computer, you can find the JDBC drivers in project2.tar.gz . Make sure to add them to the classpath.
RUNNING THE STARTER CODE: To run the starter code: set the classpath as described above. Then, change dbconn.config to include your username and password for SQL Server and Postgres. Then start up the Postgres server. Then:
createdb CUSTOMER ; this creates the CUSTOMER database on postgres; you do it only once javac Project2.java javac Query.java java Project2 user password
(The starter code does not authenticate the user, so
password are ignored. One of your jobs is to modify the starter
code to do the authentication.)
At this point you should see this:
*** Please enter one of the following commands *** > search <movie title> > plan [<plan id>] > rent <movie id> > return <movie id> > fastsearch <movie title> > quit >
The command 'search' works (sort of). Try typing:
After a few seconds, you should start getting movie titles containing 'Nixon', and their directors. (You don't yet get the actors: one of your jobs is to list the actors.)
The goal of this project is to develop a simple video-on-demand application. This is a typical application using a database: the only thing we are missing is a Web interface. You will connect to two databases, one on Postgres, the other on SQL Server.
You sell video streams to customers. You have a contract with a content provider that has videos of all the movies in the IMDB database, and you resell this content to your customers. Your first task is to create a database of your customers. Next, your application allows the user to search the IMDB movie database (the starter code already does that), and to rent movies (which we assume are delivered by the content provider; we don't do this part in the project). Once a customer rents a movie, she can watch it as many times as she wants, until she decides to "return" it to your store. You need to keep track of which customers are currently renting which movies.
There are two important restrictions:
What to turn in: a single text file called "setup.sql" with CREATE TABLE and INSERT statements for this database. Test this file by loading it into PostgreSQL while connected to an empty CUSTOMER database. Your Java program should then be ready to run.
The database has the following entity sets:
E1. Customer: a customer has an id (integer), a login, a password, a first name and a last name.
E2. Plan. Each plan has a plan id (integer), a name (say: "Basic", "Rental Plus", "Super Access" -- you can invent your own), the maximum number of rentals allowed (e.g. "basic" allows one movie; "rental plus" allows three; "super access" allows five; again, these are your choices), and the monthly fee. For this project, you are asked to insert four different rental plans.
E3. Rental: a "rental" entity represents the fact that a movie was rented by a customer with a customer id. The movie is identified by a movie id (from the IMDB database). The rental has a status that can be open, or closed. When a customer first rents a movie, then you create an open entry in Rentals; when he returns it you update it to closed (you don't delete it). Keeping the rental history helps you improve your business by doing data mining (but we don't do this in this class.)
In addition there are the following relationships:
R1. Each customer has exactly one rental plan.
R2. Each rental refers to exactly one customer. (It also refers to a single movie, but that's in a different database, so we don't model that as a relationship.)Create a text file called setup.sql with CREATE TABLE statements and INSERT statements that populate each table with a few tuples (say 2-8 tuples): you will turn in this file. This file should load into Postgres with the psql "\i" command, so put anything that is not runnable SQL in comments. Write a separate script file with DROP TABLE statements; it's useful to run it whenever you find a bug in your schema or data (don't turn in this file).
What to turn in: the Java file Query.java.
The application is a simple command-line Java program. A "real" application will have a Web interface instead, but that requires a lot of programming that is unrelated to the database content of this course. Your Java application needs to connect to two databases: the IMDB database on IISQLSRV, and your own CUSTOMER database on your local machine running Postgres.
When your application starts, it reads a customer name and password from the command line. It validates them against the database, then retains the customer id throughout the session. All rentals/returns are on behalf of this single customer: to change the customer you will quit the application and restart it with another customer. Much of this logic is already provided in the starter code. For the most part, all you need to do is uncomment the code that performs the authentication and modify it to match your CUSTOMER schema.
Once the application is started, the user can select one of the following transactions. (We call each action a transaction. You will need to write some of them as SQL transactions. Others are interactions with the database that do not require transactions.)
The "search" transaction: the user types in a string, and you return:
In the starter code, the directors are obtained using a "dependent join" (sometimes called "nested loop join"). You should use the same technique for retrieving the actors: there is a more efficient way, but we'll implement that later for "fastsearch".
WHAT YOU WILL LEARN in 1: (a) how to run SQL queries from Java, (b) what a dependent join is, and how easy it is to embed it in an application, (c) how slow the dependent joins are.
The "plan" transaction. Here, the customer types in a plan id and you set her new plan to this plan id. How does the customer know what plan id's are available? They type in "plan" without any plan id, and then you will list all available plans, their names, and their terms (maximum number of movies available for rental and monthly fees).
WHAT YOU WILL LEARN in 2: simple database updates from Java.
The "rent" transaction. The user types in a movie id, and you will "rent" that movie to the customer.
The "return" transaction. The user types in the movie id to be returned. You update your records to mark the return.
WHAT YOU WILL LEARN in 3 and 4, and also in 2: the typical interaction between the application and the database; basic data integration (you integrate CUSTOMER with IMDB); the need for transactions and rollback.
The "fastsearch" function. Here, you will fix the performance problem in the "search" function: "fastsearch" has exactly the same functionality as "search" (except that it doesn't have to return the availability status), but where all the dependent joins are replaced with regular joins, executed in the database engine.
HINT: Add ORDER BY to each of the three queries so you can merge-join the three streams efficiently.
NOTE: This task requires a little bit more creativity (and more work) than the others, but shouldn't be too difficult!
NOTE 2: Depending on the load on IISQLSRV, "fastsearch" may actually run slower than regular "search": if that happens for the right reason, then you are OK. In "fastsearch", you are running upfront three SQL queries, which are more complex than the single SQL query executed in "search": this initial step will take longer. Subsequently however, "fastsearch" no longer issues queries, and iterates over all movies, actors, and directors exactly once, while search needs to issue two new SQL queries for each movie (not counting the computation of the availability status). Thus, the time it takes to the first answer may be longer in "fastsearch", but subsequent answers should be returned "fast".
WHAT YOU WILL LEARN in 5: the database system is much better at performing the join than the Java application.
In addition you have to provide a minimal amount of user-friendliness: at each iteration of the main loop you will print the current customer's name, and tell them how many additional movies they can rent (given their current plan and the number of movies that they have already rented). WHAT YOU WILL LEARN: give the user as much data as they need, but not more.
The starter code is designed to give you a gentle introduction to embedding SQL into Java. Start by running the starter code, examine it and make sure you understand the part that works. Then create your customer database, insert 1-2 customers, and uncomment the few lines of code in the starter code that do the user authentication and that greet the user with her/his name and plan details. This should all work flawlessly (if not, talk to the instructor or TA). Then, complete the first (the "search") transaction, i.e. return the actors. Then continue with the other functions.
The completed project has about 20 quite simple SQL queries embedded in the Java code. Some queries are parameterized: a parameter is a constant that is known only at runtime, and therefore appears as a '?' in the SQL code in Java: you already have examples in the started code.
You need to enforce the following constraints:
C1. at any time a movie can be rented to at most one customer.
C2. at any time a customer can have at most as many movies rented as his/her plan allows.
What these constraints imply is (a) when a customer requests to rent a movie, you may need to deny this request and (b) when a customer selects a "lower" plan (with fewer allowed movies), you may also need to deny this request (why?). Thus, you need to worry about C in ACID.
HINT: Transactions and ROLLBACK are your friends.
You also need to worry about concurrency. A user may try to cheat and coerce your application to violate the constraint C2 above by running two instances of your application in parallel, with the same user id: depending on how you write your application and on race conditions, the malicious user may succeed in renting more movies than he/she is allowed. You need to ensure that each instance of your application runs in isolation, i.e. the I in ACID.
HINT: Transactions are your friends again.
As a student concerned about your grade in 444, you also need to worry about the A in ACID: what if you lose your CUSTOMER database, or it becomes inconsistent as a result of a systems crash. You won't use recovery, however. Instead you will rely on the script file that you need to prepare for Task 1. This file contains all the CREATE TABLE and INSERT statements that are needed to start your project.
Never include a user interaction inside a transaction! That is, don't begin a transaction then wait for the user to decide what she wants to do (why?). Your transactions should not include any user interactions.
You need transactions only on the CUSTOMER database: you don't need transactions for IMDB, since this database is never updated.
Don't try to start a transaction inside another transaction! Postgres doesn't support nesting transactions, and will ignore attempts to run START TRANSACTION while already in a transaction. It will not ignore COMMIT or ROLLBACK, however, which means that if you (accidentally or purposefully) nest transactions, you could end up committing or aborting a transaction earlier than you intended.
Connection _db; _db.setAutoCommit(false); [... execute updates and queries.] _db.commit(); OR _db.rollback();
_begin_transaction_read_write_statement.executeUpdate(); [... execute updates and queries.] _commit_transaction_statement.executeUpdate(); OR _rollback_transaction_statement.executeUpdate();