setup.sql,
completed version of the Query.java starter code file Read this whole document before starting this project. There is a lot of valuable information here, including the Final Comments section at the bottom.
Congratulations, you've decided to start your very own video rental store!
You've just signed a contract with a content provider that has videos of all the movies in the IMDB database, and you will resell this content to your customers. Once you open for business, your customers will access your service online to search the IMDB movie database for movies they are interested in, and then 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:
In this homework, you have two main tasks. The first is to design a database of your customers.
The second task is to complete a working prototype of your video store application. This system consists of a command-line interface and a back end system. The back end connects to your database to update the customer database to rent and return movies. We have already provided code for a complete UI and partial back end; you will implement the rest of the back end.
In real-life, you would develop a Web-based interface instead of a command-line interface. We use a command-line interface to simplify the assignment.
Your system will be a Java application. Download the starter code files; you will see the following files:
VideoStore.java: the command-line interface to your video store;
calls into Query.java to run customer transactions
Query.java: code to run customer transactions against
your database, such as renting and returning movies
dbconn.properties: a file containing settings to connect
to the customer and IMDB databases. You need to edit it before running the starter code. UPDATED
sqljdbc4.jar: the JDBC to SQL Server driver.
This tells Java how to connect to a SQL Azure database server, and needs to be
in your CLASSPATH (see below)
sqljdbc.jar: the JDBC to SQL Server driver for older versions of Java. Use this driver only if the other one does not work for you.
To run the starter code, you will need the the Java JDK, if you don't already have it, or run javac and java from the command line. UPDATED
You also need to access the IMDB database as in HW3. In this assignment, however, we will only use the IMDB database on SQL Azure. The reason for this is that IISQLSRV is behind a firewall and you cannot connect to it directly from home. Our instance on SQL Azure is also behind a firewall but it has no such restrictions (we enabled connections form everywhere). For your java application to connect to IMDB on SQL Azure , you first need to modify dbconn.properties to indicate your username and password. These are the username and password for SQL Azure.
You are now ready to try and run the starter code. Please follow the instructions for your platform as shown in the table below. The last command launches the starter code. Normally, you run it like this:
java VideoStore Joe jopsswd
That is, you provide the username and password
of the video store user. Your project should check that Joe is a valid user in the database
and that his password is correct; the starter code does not authenticate the user,
so user and password are ignored. You can put any strings you like.
| Windows | Linux or Mac |
|---|---|
cd \where\you\unzipped\the\starter\code [replace the directory below with your JDK's bin\ directory] path C:\Program Files\Java\jdk1.6.0_25\bin;%path% set CLASSPATH=.;sqljdbc4.jar javac -g VideoStore.java Query.java java VideoStore user password |
cd /where/you/unzipped/the/starter/code export CLASSPATH=.:sqljdbc4.jar javac -g VideoStore.java Query.java java VideoStore user password |
If you got an error message about the JDBC driver when running the above, try to use the older driver sqljdbc.jar instead of sqljdbc4.jar.
Now you should see the command-line prompt for your video store:
*** Please enter one of the following commands *** > search <movie title> > plan [<plan id>] > rent <movie id> > return <movie id> > fastsearch <movie title> > quit >
The search command works (sort of). Try typing:
search Nixon
After a few seconds, you should start getting movie titles containing the word 'Nixon', and their directors. (You don't yet get the actors: one of your jobs is to list the actors.)
Your first task is to design and create your customer database in SQL Azure. We already created a database for you on SQL Azure. What you need to do is design, create, and populate tables.
The name of your customer database on SQL Azure is yourloginCustomer where yourlogin is your login.
Note that in SQL Azure, the IMDB database and all our Customer databases are hosted on different physical machines. For this reason, your application needs to establish two JDBC connections: one to IMDB and one to your Customer database.
If you have not already done so above, modify the file dbconn.properties to indicate the name of your Customer database. You will use the same username and password to connect to that database as for IMDB.
What to turn in: a single text file called setup.sql with CREATE TABLE and INSERT statements for your customer database.
Your customer information database shoudl have 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, and the date and time the movie was checked out, to distinguish multiple rentals of the same movie by the same customer. 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 (minimum 8 tuples): you will turn in this file. This file should be runnable on SQL Azure through SQL Server Management Studio. 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). IMPORTANT NOTE: SQL Azure requires that you create a clustered index on each table before you can insert values, see this link for details and an example. You will get an error message if you try to insert into a table without a clustered index!
To create your tables and insert your data do the following:
Your second task is to write the Java application that your customers will
use, by completing the starter code.
You need to modify only Query.java. Do not modify VideoStore.java,
because we will test your homework using the current version of VideoStore.java.
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 such an interface is not the topic of this class. Your Java application
needs to connect to both the imdb and the customer databases on SQL Azure.
When your application starts, it reads a customer username 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 must quit the application and restart it with another customer. The authentication logic is not yet wired up in the starter code; as mentioned above, one of your tasks will be to make it work.
Once the application is started, the customer can select one of the following commands:
To complete your application, you will do the following:
Query.java to
read and write customer data from your database, taking care to
ensure atomic transaction semantics.
In the search command, the user types in a string, and you return:
The starter code already returns the movies and directors. Your task to return all actors, and also to indicate whether the movie is available for rental.
The search function in task 2A uses dependent joins, and can be slow sometimes.
(Note: the speed depends dramatically on whether you are running with
a cold cache, or a hot cache.) Your task here is to write a faster version of
search, called fastsearch, by using joins (or outer joins?
you need to determine that!) instead of dependent joins. Your search should
return only (1) the movie information (id, title, year) and (2) its actors.
For extra credit you can also return (3) directors and availability information.
Instead of executing many queries as in the original search method, fastsearch should only perform two or three queries in total.
How much better should you expect fastsearch to be?
With a hot cache, fastsearch typically increases the speed very little:
perhaps from 2-3 seconds to 1 second or so; with a cold cache the performance
increase may be larger, from minutes, to several seconds.
Interestingly, unlike with a local server, with SQL Azure, the performance bottleneck is typically the data transfer from the server to the client, hence you may not see any visible performance improvement. For some queries, fastsearch may actually be slower than search! For example, we found that "search nowhere" took 1min12sec while "fastsearch nowhere" took only 34sec. In contrast, "search Nixon" took 21sec while "fastsearch Nixon" took 30sec.
Now, complete the application by implementing each 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 "login" transaction, which is run implicitly when you start your command line program, authenticating the user by his/her username and password. Much of the authentication 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. However, you must also establish a connection to your Customer database. You need to add code to do this. Look at how the starter code establishes a connection to the IMDB database.
The "print customer info" transaction: To provide a minimum amount of user-friendliness, at each iteration of the program's main loop, you need to 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).
The "plan" transaction. Here, the customer types the command
plan PLAN_ID and you set her new plan to that plan id.
How does the customer know which 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).
The "rent" transaction. The user types in rent MOVIE_ID,
and you will "rent" that movie to the customer.
The "return" transaction. The user types in return MOVIE_ID.
You update your records to mark the return of that movie.
Be sure to use SQL transactions when appropriate to implement these "transactions". See more on this below.
The search function in this simple application (and similarly
fastsearch), provides only search by title keyword.
Now type this at the prompt:
search ' and year=1899 --
You get all movies in 1899! Perhaps try this?
search '; drop movie; drop casts; drop actor; --
Actually, don't try it, you get the idea...
This is SQL injection: hackers like to do it on Website interfaces
to databases. Your task here is to fix the search and fastsearch
function to prevent SQL injection attacks. Fix the security issue by changing
the code in Query.java (only).
Hint: when fixing the issue,
look at other parts of the starter code that execute SQL to see what
they do differently from the broken search code.
You must use SQL transactions
in order to guarantee ACID properties: you must define begin- and end-transaction statements,
and insert them in appropriate places in Query.java. In particular, you must
ensure that the following two constraints are always satisfied, even if multiple instances of your
application talk to the database.
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.
Concretely: (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?). You can implement denying in many ways, but we strongly recommend the SQL ROLLBACK statement.
You must use transactions correctly such that users cannot cheat, nor can race conditions introduced by concurrent execution lead to an inconsistent state of the database. For example, 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. Your properly designed transactions should prevent that.
Design transactions correctly. Avoid including user interaction inside a SQL transaction: that is, don't begin a transaction then wait for the user to decide what she wants to do (why?). The rule of thumb is that transactions need to be as short as possible, but not shorter.
When one uses a DBMS, by default each statement executes in its own transaction. To group multiple statements into a transaction, we use
BEGIN TRANSACTION
....
COMMIT or ROLLBACK
This is the same when executing transactions from Java, by default each SQL statement will be executed as its own transaction. To group multiple statements into one transaction in java, you can do one of three things:
Approach 1:
We provide you with three helper methods. So before your first statement in the transaction, simply execute
beginTransaction();
When you are done with the transaction, then call:
commitTransaction();
OR
rollbackTransaction();
Approach 2:
You can execute the SQL code for START TRANSACTION and friends directly, using the SQL we have provided in the starter code (also check out SQL Azure's transactions documentation):
// When you start the database up Connection conn = [...] conn.setAutoCommit(true); // This is the default setting, actually conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // In each operation that is to be a multi-statement SQL transaction: conn.setAutoCommit(false); // You MUST do this in order to tell JDBC that you are starting a multi-statement transaction beginTransactionStatement.executeUpdate(); [... execute updates and queries.] commitTransactionStatement.executeUpdate(); [OR] rollbackTransactionStatement.executeUpdate(); conn.setAutoCommit(true); // To make sure that future statements execute as their own transactions.
Approach 3:
// When you start the database up Connection conn = [...] conn.setAutoCommit(true); // This is the default setting, actually conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // In each operation that is to be a multi-statement SQL transaction: conn.setAutoCommit(false); [... execute updates and queries.] conn.commit(); [OR] conn.rollback(); conn.setAutoCommit(true);
When auto comit is set to true, each statement executes in its own transaction. With auto-commit set to false, you can execute many statements within a single transaction. By default, on any new connection to a DB auto-commit is set to true.
To test that your transactions work correctly, we recommend the following (and this is how we will test your homework). Place a break in the middle of your transaction, by reading and throwing away a line of the user's input. Run two (or more?) instances of VideoStore.java, say A and B. Let both reach the point when they read from the standard input; then you decide which one you allow to proceed, and thus control the order in which the transactions are interleaved.
FINAL COMMENTS:
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. You will need to create (and connect to) your new customer database before uncommenting and running the query statements. Then you should 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.