SEN 972 Java EE Programming

SEN 972 Java EE Programming

JDBC: Programming Project 1

Project 1 Overview

In this project you willcreate a database for an online movie website using any relational database management system. You will populate the database and connect to it using client Java programs. The database will contain information about movies, so that we can sell this valuable information to customers. Here are the steps we need to accomplish for this project:

·  Step 1: Create the database called, “moviedb”

·  Step 2: Create the relations of theschema provided below. Use the provided data.sql file to populate the tables.

·  Step 3: Write a JDBC program that provides a text-based user interface to support the following queries using JDBC to talk to the server and return results:

In an actual development effort, the functional and look-and-feel requirements for these functions would have been specified in every particular. We've purposely left these requirements high-level because we want you to use your skills, experience and reasoned judgment to refine the requirements. You can also enhance these specifications, for example, you can use the GUI rather than a console interface.

Schema

Create your movie database! The following chart gives the database schema; for each table, its name, attributes, and any notes about the attributes are provided. 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.

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
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
for a person with one name,
put it in last_name
& make first name 'none'
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
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
for a person with one name,
put it in last_name
& make first name 'non
enforce uniqueness on both email and password.
sales / id:integer (primary key)
customer_id:integer, referencing customers.id
movie_id:integer, referencing movies.id
time:timestamp / all attributes required
creditcards / id:varchar(20), (primary key)
first_name:varchar(50)
last_name:varchar(50)
expiration:date / all attributes required
for a person with one name,
put it in last_name
& make first name 'non

Write a JDBC program

·  Write a Java program that provides for the following functionality. It can be console based (or GUI, as discussed above).

When this program is run, the user is asked for 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.

Provide a menu that allows the employee to

Print to the screen the movies featuring of 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 first name AND last name or first name or last name.

·  Print to the screen a nice, neat list of the genres of the movies in which a particular star appeared; the star can be queried via first nameand/or last name or by ID.

·  Insert a new star into the database.

·  Associate an existing starwitha particular movie. Allow searching for star and the movie by name, so that the user does not have to scan the whole database for the appropriate record IDs.Possible approaches are: a) enter the movie title and the program returns a list of movies that match the query, from which the user picks one, and do the same for the stars using star first name and/or last name; b) If you have a GUI, create drop down menus on which the entries are alphabetically sorted, and can be selected by typing the first few letters of the star name or movie title.You should also allow associating by starID or movie ID, in case the user has this information at hand.

·  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.

·  Insert a movie into the database.

·  Delete a star from the database. Be aware of foreign key violations. SQL will throw an error if you delete a star which is referenced from the "star_in_movies" table, unless you explicitly address this issuein your SQL (preferably) or Java code. You should allow for searching the star you want to delete by first name and/or last name, and your program should print the matching entries from which you select the one to be deleted. Deletions in SQL should always be done by the primary key, but the user should be presented with the names not the hard to remember IDs. You should also allow deleting by ID, in case the user has this information at hand.

·  Delete a customer from the database. The same as above.

·  Delete a movie from the database.The same as above.

·  In cases where the requested tasks cannot be accomplished, print out a clear, crisp error message–do not just pass along some Java exception!

·  Exit the program.

Deliverables

Please, pack all the required files in one zip or tar file, here is the list of what needs to be sent:

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

·  Makefiles and ant build files if you use make or ant to build your project.