Project 1

In this project you will be provided with a description of an application (below) to create an entity-relationship diagram (ERD) and design accompanying table layout using sound relational modeling concepts and practices. The relationships between the entities and the attributes for the entities will be identified and described. This database will provide the foundation for the follow-on project. The following paragraphs provide the background and summary of the business requirements.

You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.

You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent; therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.

The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors, and at different wholesale prices.

Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these catalogs must be included in the database.

Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the academy awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.

There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.

Using this information, you should:

Step 1) Determine and list your entities. Then create relationship sentence pairs between those entities that are related. You should not have any many-to-many relationships.

Entities:
Distributor
Shipment
Shipment_Line
DVD_video
Movie
Actor
Cast_member
Movie_Catalog_Entry
Movie_Director
Movie_Award
Rental_line
Rental
Customer
Award
Movie_Catalog
Actor_Award
Director
Director_Award

Relationships between entities:

DISTRIBUTOR Sends SHIPMENT to video store
SHIPMENT is sent via SHIPMENT_LINE.
An MOVIE_CATALOG_ENTRY of SHIPMENT_LINE is made in MOVIE_CATALOG
SHIPMENT_LINE has information of movies
DVD_VIDEO has MOVIE
MOVIE has ACTOR and information is stored in CAST_MEMBER
MOVIE is directed by DIRECTOR and information is stored in MOVIE_DIRECTOR
DVD_VIDEO is rented and information is stored in RENTAL and RENTAL_LINE
DVD_VIDEO is rented to CUSTOMER
MOVIE can get AWARD and information is stored in MOVIE_AWARD
ACTOR can get AWARD and information is stored in ACTOR_AWARD
DIRECTOR can get AWARD and information is stored in DIRECTOR_AWARD

Step 2) Create an entity/relationship diagram (ERD) showing all your entities, attributes, and relationships. Sketch your ERD by hand or use a drawing program. Your diagram must beon a single page. All entities should be relatated to at least one other entity. Your ERD should have all one-to-many relationships and not have any many-to-many relationships.

Project 2

In this project you will perform the physical design and implementation using SQL Data Definition Language (DDL) and proceed with populating the Mom and Pop Johnson Video Store database via Data Manipulation Language (DML) SQL commands.

Each of the steps below requires a SPOOL file to be submitted. Be sure your SPOOL file contains your SQL statements along with the Oracle responses and/or displayed results. Do NOT submit your SQL script files. Only submit your output SPOOL files. If you are using iSQL*Plus you must screen snapshots as necessary of your SQL and the results.

Project 2 Details:

1. Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed in Project 1. Make sure that entity and referential integrity are enforced by declaring a primary key for each table (these may be composite keys) and declaring all appropriate foreign keys. Your CREATE TABLE statements must show integrity constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, and CHECK constraints. Be sure to save your script used to create these tables as yournameproject2step1.sql. You should test your script to make sure it runs without error. Submit your SPOOL file showing that all SQL in your SQL script file worked properly.

2. Provide two examples of SQL DML (i.e., "INSERT") commands that fail different table integrity constraints you set up in one of your table. Explain why the statements fail. Be sure to save your script used to as yournameproject2step2.sql. You can include comments in the SQL script describing why the insert statements failed. Submit your SPOOL file showing that all SQL in your SQL script file worked properly.

3. Populate each of your tables with at least five valid rows of data each and show the SQL you used. Populate other tables in your database, as necessary, to satisfy referential integrity. Be sure to save your script used to create these records as yournameproject2step3.sql. You should test your script to make sure it runs without error. Submit your SPOOL file showing that all SQL in your SQL script file worked properly.

4. Write SQL to perform the following queries and updates. Be sure to save your script used to create these records as yournameproject2step4.sql. You should test your script to make sure it runs without error:

o Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number.
o Retrieve all of the videos rented in the last 30 days and sort in chronological rental date order.
o Produce a list of your distributors and all their information sorted in order by company name.
o Update a customer name to change their maiden names to married names. You can choose which row to update. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.
o Delete customers from the database. You can choose which row to delete. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.

Submit your SPOOL file(s) showing that all SQL in your SQL script file worked properly. Show the actual SQL statements executed and the results the SQL produced below the code. Do NOT submit your SQL script files. Also, submit all of your .sql files.

Before attempting this project, be sure you have completed all of the reading assignments, hands-on labs, discussions, and assignments to date.

Design a class named Clock. You should use your IDE for this exercise. The class contains private data fields for startTime and stopTime, a no argument constructor that initializes the startTime to the current time, a method named start() that resets the startTime to the given time, a stop() method that sets the endTime to the given time and a getElapsedTime() method that returns the elapsed time in seconds. Create a TestClock class to construct a Clock instance and return the elapsed time. Command line arguments should be used to send the start and end times. You should use the java.time classes.