CS122B: Project 1

Due: 1/18/2017, Wednesday, 11:45 pm. Submit on EEE.
Notice that we use 1 day after the official deadline as the submission cut-off time on EEE to allow you to use the 24-hour grace period if you chose so. After that, EEE will no longer accept submissions.

Tasks

1. Create a MySQL database with provided movie info (on your development machine);
2. Write a Java program to connect to the database using JDBC (on your development machine);
3. Setup Apache Tomcat (on your development machine);
4. Setup an Amazon AWS instance and repeat tasks 1-3 on the instance.

---

Task 1: Create a MySQL Database

• Download a MySQL database for your development machine from ​http://dev.mysql.com/downloads/mysql/;
• Create a database called "moviedb" with the following tables:
  

Table Name	Attributes	Notes
movies	id:integer (primary key) title:varchar(100) year:integer director:varchar(100) banner_url:varchar(200) trailer_url:varchar(200)	required, AUTO_INCREMENT required required required URL of movie's "poster"; not required URL of trailer; not required
stars	id:integer (primary key) first_name:varchar(50) last_name:varchar(50) dob:date photo_url:varchar(200)	required, AUTO_INCREMENT required required not required not required
stars_in_movies	star_id:integer, referencing stars.id movie_id:integer, referencing movies.id	all attributes required
genres	id:integer (primary key) name:varchar(32)	all attributes required; "id" should be "AUTO_INCREMENT"
genres_in_movies	genre_id:integer, referencing genres.id movie_id:integer, referencing movies.id	all attributes required
customers	id:integer (primary key) first_name:varchar(50) last_name:varchar(50) cc_id:varchar(20), referencing creditcards.id address:varchar(200) email:varchar(50) password:varchar(20)	all attributes required; "id" should be "AUTO_INCREMENT"
sales	id:integer (primary key) customer_id:integer, referencing customers.id movie_id:integer, referencing movies.id sale_date:date	all attributes required; "id" should be "AUTO_INCREMENT"
creditcards	id:varchar(20), (primary key) first_name:varchar(50) last_name:varchar(50) expiration:date	all attributes required

The table-creation SQL statements should be written in a createtable.sql file. All varchar() fields for which there is no data (i.e., the fields contents are missing or unknown) are the empty string (''); other non-required fields which have no data are null. Required fields have the constraint that they are not null.

• Use the provided data.sql​ file to populate the tables.

Task 2: Write a JDBC Program

Download a MySQL JDBC driver file from ​https://dev.mysql.com/downloads/connector/j/.

Write a Java program that that uses JDBC to interact with MySQL. It provides for the following functionality using a text-based console.

• Print out (to the screen) the movies featuring a given star. All movie attributes should appear, labeled and neatly arranged; the star can be queried via first name and/or last name or by ID. First name and/or last name means that a star should be queried by both a) first name AND last name b) first name or last name.
• Insert a new star into the database. If the star has a single name, add it as his last_name and assign an empty string ("") to first_name.
• Insert a customer into the database. Do not allow insertion of a customer if his credit card does not exist in the credit card table. The credit card table simulates the bank records. If the customer has a single name, add it as his last_name and assign an empty string ("") to first_name.
• Delete a customer from the database.
• Provide the metadata of the database; in particular, print out the name of each table and, for each table, each attribute and its type.
• Enter a valid SELECT/UPDATE/INSERT/DELETE SQL command. The system should take the corresponding action, and return and display the valid results. For a SELECT query, display the answers. For the other types of queries, give enough information about the status of the execution of the query. For instance, for an UPDATE query, show the user how many records have been successfully changed.
• Exit the menu (and return to the get-the-database/user/password state)
• Exit the program.

When this program is running, the user is asked for the the user name and the user password (the database user login info not the password in the above schema) . If all is well, the employee is granted access (and a message to that effect appears on the screen). If access is not allowed, it says why (e.g., the database is not present, the password is wrong). Allow a way for the employee to exit easily. In case the requested tasks cannot be accomplished, print out a clear, crisp error message–do not just pass along some Java exception!

Here are some sample JDBC programs. The how_to_run.txt will give you the instructions on how to create the required database/tables and how to execute the sample code.

• Instructions to run above code.​
• Sample JDBC program - obtaining metadata and table contents​
• Sample JDBC program - deleting a record​
• Sample JDBC program - updating a record​

Task 3: Setup Apache Tomcat

Download and install the latest Apache Tomcat 8.5.9 from ​https://tomcat.apache.org/download-80.cgi for your development machine. Make sure to successfully setup the "Tomcat Web Application Manager" interface shown as below:

Task 3.5: Apache Tomcat Eclipse Integration (Optional but Recommended)

Apache Tomcat makes hosting your applications easy. The Eclipse IDE makes development easy. Important: You may use Eclipse version 3.1 or higher or may opt to use Eclipse EE (EE stands for Enterprise Edition and has built-in support for application and web servers including Tomcat)

Eclipse Downloads:

• ​Eclipse Standard Edition
• ​Eclipse EE Edition

The following tutorial is a quick start guide and will help you set up your development environment in eclipse. Use Tomcat version 8.5.9 instead of the shown version in the tutorial. Note: If you opt for Eclipse EE edition, you can skip the installation of Eclipse Web Tools Platform when following the quick start guide.

• ​Apache Tomcat Eclipse Integration Quickstart Guide

Task 4: Setup an AWS Instance to Repeat Tasks 1-3

You need to launch an Amazon ​AWS instance to repeat the tasks above. Make sure to use the free-tier 64-bit Ubuntu instances so that we can make our future instructions consistent. You are welcome to participate in the ​​AWS Educate program, which can provide $100 AWS credits per student. (I believe UCI is a member institution.)

Generally, similar to many other tasks in this quarter, we expect you to figure out how to do many tasks by reading online materials. ​This link is a good place to start from.

Launch a free AWS instance

1. Go to ​AWS Console to sign up. You will need to enter a valid credit card. Don't worry; as long as you choose a free-tier instance and remove it after the end of the quarter, you will not be charged.
2. When you are done, login to the AWS console.
3. Launch a new Ubuntu 14.04 free-tier t2.micro instance. Notice that you need to generate and download a key to ssh to the machine, and it may take a few minutes for the instance to be initialized.
4. After the instance is running, you will see a public IP address assigned to it. Keep this IP: you are required to give us this IP to demo project 1.
5. When viewing the list of instances, you can click on the "connect" button, on the top to get instructions on how to use SSH to connect to the instance. By default, only the SSH port, 22, is open. In order to get other services (e.g., HTTP, HTTPS, and Tomcat) to be available to other machines, you will need to open the corresponding ports. To do so, when the instance is checked, select the security group, go to the "inbound" tab, and add more rules.

Setup MySQL and Apache/Tomcat on the AWS instance

This part assumes you have SSH access to the Ubuntu 14.04 AWS instance.

1. ​Install MySQL.
2. ​Install Apache Web server. Open port 80.
3. ​Install Java
   
4. Install Tomcat on AWS. You may skip the part of installing JDK. Setup its application app manager page and test it using the URL ​http://YOUR_PUBLIC_IP:8080/manager/html. Open port 8080. This part is a little tricky, so read the instructions carefully.

Once you finish these steps, repeat tasks 1 - 3 on this instance.

Here are two cheat sheets of Linux/Ubuntu commands:

• ​http://cli.learncodethehardway.org/bash_cheat_sheet.pdf
• ​http://www.cheat-sheets.org/saved-copy/ubunturef.pdf

Project Submission

Pack all the required files in a single zip file to include the following:

• All source code (*.java, *.sql, and so on, excluding binary files such as .class files and .exe files), this includes the SQL commands you issued to create the database and populate it.

Each group will be assigned a unique group ID, which should be used to name your files and submission. In particular, your submission zip file should be named "project1_[2-digt-groupid].zip" For example, the file for group 9 should be "project1_09.zip". The submission should include at least one .sql file: createtable_[2-digt-groupid].sql (such as "createtable_09.sql" for group 09) to include all the "CREATE TABLE" SQL statements.

Each group MUST follow this naming requirement.

Those script files should be directly executable by being fed as input to the mysql command.

mysql -u root -p -D moviedb < createtable.sql

• The ssh private key file (.pem) of your AWS instance.
  
• Makefiles and ant build files if you use make or ant to build you project.
  
• A readme file specifying how to compile and run your program
• Note: Checking constraints is not a requirement of the project.

If your project does not compile or if your SQL scripts generate errors such a project will get a very low score if any.

Login onto EEE using your UCInetID. Under our course COMPSCI 122B, click Dropbox. Under Folder View, click CS122B Proj 1/Assignment Submission. Upload your zip file there. Please notice that after the deadline you will not be able to upload your files to the system any more.

Demonstration

You are required to demonstrate this project. Create a running system from your ZIP file–quickly. Show the correctness, completeness, functionality and performance of your database during your demonstration by using the employee interface developed here. Your task is to convince us (in 15 minutes) that your work is correctly and completely carried out and the course project is ready to move to phase 2. Your demo will include the following:

a) Setup your application using the files you submitted on EEE. The TA/grader will upload these files on your PC. You must have already installed MySQL.

b) Demonstrate that your creatable.sql file creates a valid database. Since execution of the data.sql can take too long, you should have already setup a database and populate it. This database will be the one accessed by your demonstrated JDBC program. Thus, creatable.sql will be executed for a database under a different name and get populated by executing data.sql, while you are demonstrating other parts of the project.

c) Demonstrate the correctness, completeness, and performance of all the required features.

d) Last but not least, entering YOUR_PUBLIC_IP:8080 on your AWS instance should show us the Tomcat application manager, which confirms that you are running Tomcat on the AWS instance. Additionally, the TA/Reader may want to use his username (with the password that you provide) to access the instance through SSH.

Please note that you will have no more than 15 min to demonstrate. It is completely up to you to plan your demo so that all the required features are displayed and the grader can be convinced of the quality of your work in the given time frame. If a group's demonstration appears to be unstructured, the demo could be aborted and rescheduled for another time with a substantial grade penalty.

Demonstration schedule will be posted later.