CSE 344 Homework 5
- To get experience with database application development and transaction management.
- Assignment tools:
- SQL Server through SQL Azure.
- SQL Server JDBC Jar files, IMDB dataset ascii version, starter code files
- Due date:
- Nov 21, 2011, at 11:59 pm in the dropbox
- What to turn in:
- Customer database schema in , completed version of the 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:
- Because your store is brand-new, your contract with the content provider will only allow you to rent each movie to at most one customer at any one time. The movie needs to be first returned before you may rent it again to another customer (or the same customer).
- Your own business model imposes a second important restriction: your store is based on subscriptions (much like Netflix), allowing customers to rent up to a maximum number of movies for as long as they want. Once they reach that number you will deny them more rentals, until they return a movie. You offer a few different rental plans, each with its own monthly fee and maximum number of movies.
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.
Task 0: Running the starter code (0 points)
Your system will be a Java application. Download the starter code files; you will see the following files:
- : the command-line interface to your video store; calls into to run customer transactions
- : code to run customer transactions against your database, such as renting and returning movies
- : a file containing settings to connect to the customer and IMDB databases
- : the JDBC to SQL Server driver. This tells Java how to connect to a SQL Azure database server, and needs to be in your (see below)
- : 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.
You also need to access the 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 to indicate your username and password. These are the username and password for SQL Azure. If you did not try to use SQL Azure in HW3, use the following instructions for changing your password.
- From SQL Server Management Studio R2, connect to fntxwa8jwq.database.windows.net using SQL Server Authentication.
- Once you are connected, change your password as follows:
- In the Object Explorer on the left, select Databases -> System Databases -> master
- Click on New Query (at the top)
- Execute the following SQL Statement to change your password. Note that you will get an error message if your password is not sufficiently complex. Make sure to use some capital letters and some numbers.
ALTER LOGIN yourlogin WITH PASSWORD='some_new_password' OLD_PASSWORD = 'old_password'
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:
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 and 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=.;sqljdbc.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 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.)
Task 1: Customer database design (20 points)
Your first task is to design and create your 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:
- From SQL Server Management Studio, connect to fntxwa8jwq.database.windows.net using SQL Server Authentication.
- Once you are connected:
- In the Object Explorer on the left, select Databases -> yourloginCustomer
- Click on New Query (at the top)
- Copy and paste the content of setup.sql and press "Execute". We recommend that you execute one statement at a time and examine its effects before moving on to the next statement.
Task 2: Java customer application (80 points)
Your second task is to write the Java application that your customers will use, by completing the starter code. You need to modify only . Do not modify , because we will test your homework using the current version of .
What to turn in: the Java file .
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 and the 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:
- Search for movies by words or strings in the title name.
- View a list of rental subscription plans, and change his/her plan.
- Rent a movie by IMDB ID number.
- Return a rented movie, again by IMDB ID number.
To complete your application, you will do the following:
- Complete the provided IMDB movie search function, fixing a security flaw in it along the way.
- Write a new, faster version of the search function.
- Implement the remaining functions in to read and write customer data from your database, taking care to ensure atomic transaction semantics.
Task 2A: Completing the search function (20 points)
In the search command, the user types in a string, and you return:
- all movies whose title matches the string, case-insensitively
- their director(s)
- their actor(s)
- an indication of whether the movie is available for rental (remember that you can rent the movie to only one customer at a time), or whether the movie is already rented by this customer (some customers forget; be nice to them), or whether it is unavailable (rented by someone else).
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.
Task 2B: A faster version of search (20 points)
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 , 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 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.
Task 2C: Customer database transactions (30 points)
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 and you set her new plan to that plan id. How does the customer know which plan id's are available? They type in 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 , and you will "rent" that movie to the customer.
The "return" transaction. The user types in . 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.
Task 2D: Stop SQL Injection (10 points)
The function in this simple application (and similarly ), 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? 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 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.
Transaction management (for Task 2C)
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 . 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 useBEGIN 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:
We provide you with three helper methods. So before your first statement in the transaction, simply executebeginTransaction();
When you are done with the transaction, then call:commitTransaction();
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.
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.
- We won't use recovery in this homework. 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.
IS361 – Enterprise Database Management Graded Assignment 1 This is your first graded assignment for the course. It includes all the material in Chapters 2, 3 and 4. Please refer to the class’ Moodle site for the due date. Pay attention to what is being asked for. There is a difference between and Entity Relational Diagram (ERD), Entity Relational Model (ERM) and Relational Diagram (RD). Make sure you respond with the design that is asked for. Make sure you use the Crow’s Feet method for your entity diagrams. Use the database shown in Figure P3.24 to answer Problems below. AviaCo is an aircraft charter company that supplies on-demand charter flight services using a fleet of four aircraft. Aircrafts are identified by a unique registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table. FIGURE P3.24 The Ch03_AviaCo Database Tables (Part 1) 1 | P a g e