Begin Again Imdb

[Hedy Madrid]

Read this whole document before starting this project. There is a lot ofvaluable information here, including the Final Comments section at the bottom.Congratulations, you are opening your own video rental store!You' signed a contract with a content provider that has videos of all the movies in the IMDB database, and you will resell these videos to your customers.Your store allos customers to search the IMDB movie database, then rent movies (which we assume are delivered by the content provider; we don't do this part in the project).After renting a movie, the customer may 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 what movies.There are two important restrictions:

1. Your contract with the content provider allows you to rent each movie to at most one customer at any time. The movie needs to be first returned before you may rent it again to another customer (or the same customer).
2. Your own business model imposes a second restriction: your store is based on subscriptions (much like Netflix), allowing customers to rent up to a maximum number of movies, depending on their retnal plan. 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 taskis to complete a working prototype of your video store application that connects to the database then allows the customer to use a command-lineinterface to search, 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, but we use a command-line interface to simplify this homework.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:

• 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.
• 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, use eitherthe Java JDK or run javac and java from the command line.You also need to access the IMDB database on SQL Azure from Homework 3. Modify dbconn.properties with your username and password for SQL Azure. This allows your java program to connect to the database on SQL Azur.. You are now ready to try 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:

begin again imdb

DOWNLOAD https://vittuv.com/2yRVtx

That is, you provide the username and password of the video store user. Later, your code will check the name and password of Joe-the-customer, but the starter code ignores both user and password (you can put any strings you like).

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 > plan []> rent > return > fastsearch > quit>The search command works (sort of). Try typing:search NixonAfter a few seconds, you should start getting movie titles containing the word'Nixon', and their directors. (You don't yet get the actors: one ofyour jobs is to list the actors.) An alternative way to run the starter code from Eclipse on Windows (posted by a student on the discussion board): So the instructions seem to emphasize running this application out of a terminal. Since my primary box is Windows, and the terminal is a pain in Windows, I set it up to run entirely in Eclipse so I didn't have to deal with it. Here are the steps you need to do:

This will cause the application to run with those two arguments. It will store them so the next time you hit the run button it will use those same two arguments again. If you wish to change the arguments, you will have to open the Run Configurations... menu again and change the arguments.

Alternatively, you can open Run Configurations..., right click on VideoStore and select Duplicate. This should create one called VidoeStore (1). You can change the arguments in this one without affecting the original VideoStore (or change the name field above the tabs so you know which configuration it is). Then, when you click the arrow next to Run, it should give you an option to choose which configuration to run. This makes it easier to switch users without having to constantly change the arguments.

Your first task is to design and create your customer database in SQL Azure. We created a database for you on SQL Azure called yourloginCustomer where yourlogin is your login: create there all tables for your customer database.Note that your Java application will connect to two databases: IMDB and yourloginCustomer and tehrefore your application needs to establish two JDBC connections.Modify the file dbconn.properties to indicate the name of yourloginCustomer database; use the same username and password as for IMDB. What to turn in: a single text file called setup.sql with CREATE TABLE and INSERT statements for your customer database. Customer informationYour 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.

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 never delete it). Keeping the rental history helps you improve your business by doing data mining (but we don't do this in this class.)

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 youfind 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 m01rrgdwg2.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 willuse, 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 simplecommand-line Java program. A "real" application will have a Webinterface instead, but this is beyond the scope of our class. Your Java application will connect to both the IMDB and the CUSTOMER databases on SQL Azure.

When your application starts, it reads a customer username and passwordfrom the command line. It validates them against the database, thenretains the customer id throughout the session. All actions (rentals/returns etc) areon behalf of this single customer: to change the customer you mustquit 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 thefollowing 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:

1. Complete the provided IMDB movie search function, fixing a security flaw in it along the way.
2. Write a new, faster version of the search function.
3. Implement the remaining functions in Query.java 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 is to return all actors, and also to indicate whether the movie is available for rental.Task 2B: Push the join to the database engine (20 points)Goal: When writing programs that talk to a back-end DBMS it is possible to implement some of the data processing either in the Java code or in the SQL statements. In the first case we issue many SQL queries and perfrom some of the query processing in Java; in the latter case we issue only one (or a small number) of SQL queries and push most of the work to the database engine. We used the former in Task 2A; we will use the latter in 2B. In this task you will reimplement the search function from 2A but instead of dependent joins you will push the joins to the database engine. In principle, this should be faster, however, you may not necessarily notice that: on our small database the speed is affected much more dramatically by whether you are running with a cold cache, or a hot cache, and by the netwrok traffic. Write a function called fastsearch, by using joins (or outer joins? you need to determine that!) computed in the database engine, instead of the dependent joins computed in Java iby the search function. Your fastsearch should return only (1) the movie information (id, title, year), (2) its actors, and (3) its director. It does not need to return the rental status. Notice that search issues O(n) SQL queries, because for each movie it runs a separate SQL query to find all its directors (and its actors). Instead, you will write fastsearch to issue only O(1) SQL queries, say two or three.Hint: One query finds all movies matching the keyword; one query finds all directors of all movies matching the keyword; on query finds all the actors of all the movies matching the keyword. Execute each of these three queries separately. You then need to merge the results of the three queries *in* the Java code. The merge will be easier if your SQL queries sort the answers by the movie id. (There is also a way to write fastsearch with only two, or even only one single SQL query, but don't worry about that because it gets more messy with questionable benefits.)
How much better should you expect fastsearch to be? We tried it on both a local installation of SQL Server and on SQL Azure. In the former case, 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.

59fb9ae87f