Database Project: Developing a DVD Rental Database with SQL and ERD

This database project focuses on developing a DVD rental system, encompassing enhanced business rules, entity-relationship diagrams (ERD), and SQL queries. The assignment begins with a description of the business rules that govern the system, followed by the creation of a conceptual and a logical ERD. The solution then presents SQL queries designed to retrieve specific information from the database, such as movie titles based on director, and customer names with their current rental plans. Furthermore, a parameterized stored procedure is developed to add movies to a customer's queue. The project demonstrates the application of database concepts to design, implement, and query a relational database for a DVD rental scenario. Desklib offers comprehensive study resources, including past papers and solved assignments, to help students understand and excel in their coursework.

ITERATION 4 ASSIGNMENT
STUDENT
30 SEPTEMBER, 2016

Contents
ENHANCED BUSINESS RULES...............................................................................2
CONCEPTUAL ERD...................................................................................................3
LOGICAL ERD............................................................................................................4
USECASE AND SQL..................................................................................................5
1 | P a g e

ENHANCED BUSINESS RULES
The enhanced business rules after iteration 4 are as follows:
 There will a separate table to store login details
 A customer must sign up for one account.
 Each customer will have a unique customer ID which will be there user ID.
 Each account will have a customer.
 Each customer must select one DVD rental program at a time.
 A DVD rental program is selected by one or more customers
 A customer account has a single movie queue to add one or more movies to it.
 A DVD can be rented by one or more customers
 Each DVD will have unique ID to store its info.
 Each movie will have unique ID to store its info.
 Customers rent one or more DVDs
 A customer can have one or more credit card information.
 A credit card information must belong to a single customer.
 Each DVD rental program will have unique ID to store its info.
 Each movie will have an associated DVD to it and there will be copies of same
DVD.
 Entire address of the customer will be stored in the customer.
 There will be zero or one DVD for a movie available.
2 | P a g e

CONCEPTUAL ERD
3 | P a g e

LOGICAL ERD
Final logical ERD after applying business rules is as follows:
4 | P a g e

USECASE AND SQL
USECASE 1: A customer requests the titles of all movies that are directed by
"George Lucas" or by “Rich Christiano”. Write a single query that retrieves this
information.
ANSWER: For this Usecase we need to create the movies table and then query it.
SQL QUERY: select movie_name as title from movies where director = ‘George
Lucas’ or director = ‘Rich Christiano’;
OUTPUT:
USECASE: Management requests the names of all currently active customers, as
well as the name of the current plan in which each of these customers is enrolled.
Write a single query that retrieves this information.
For this Usecase we need to create customer table, account table and DVD_Rental
program table.
SQL: select c.fname as customerFirstName, c.lname as CustomerLastName,
d.plan_name as Plan from customer c, account a, DVD_rental_program d where
c.cust_id = a.cust_id and d.plan_id = a.dvd_plan_id;
OUTPUT:
5 | P a g e

USECASE: A customer wants to add a movie to their queue so that the newly added
movie will be the next movie they receive. Develop a parameterized stored
procedure that accomplishes this, then invoke the stored procedure for a customer of
your choosing.
ANSWER: for this procedure we need to create the movie_queue table.
SQL:
Create or replace procedure queue_add()
6 | P a g e