CSC 352/452 Database Programming Movie Rental Case Solution
Your task is to build a PL/SQL-based application to insert and update records in a video rental store database and generate some reports.
The database consists of only the following essential tables.
MEMBER(MEMBER_ID, PASSWORD, NAME, EMAIL_ADDRESS, PHONE_NUMBER,
REGISTRATION_DATE, EXPIRATION_DATE, LAST_UPDATE_DATE);
VIDEO_TITLE(VIDEO_TITLE_ID, VIDEO_NAME, FORMAT, PUBLISH_DATE);
VIDEO_COPY(VIDEO_COPY_ID, VIDEO_TITLE_ID*, MAXIMUM_CHECKOUT_DAYS,
COPY_STATUS);
VIDEO_RENTAL_RECORD(MEMBER_ID*, VIDEO_COPY_ID*, CHECKOUT_DATE, DUE_DATE,
RETURN_DATE);
The primary keys are underlined and the foreign keys are marked with asterisks.
VIDEO_COPY (COPY_STATUS): A – Available, R – Rented, D – Damaged
Each video in the VIDEO_TITLE table has at least one video copy in the VIDEO_COPY table.
Part 1)
1) Create and populate the following tables.
CREATE TABLE member
( MEMBER_ID NUMBER PRIMARY KEY,
PASSWORD VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
EMAIL_ADDRESS VARCHAR2(50) NOT NULL,
PHONE_NUMBER VARCHAR2(15) NOT NULL,
REGISTRATION_DATE DATE NOT NULL,
EXPIRATION_DATE DATE NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL);
/
INSERT INTO member
VALUES(2001, ‘C1psBTKpN0′,’SMITH’,’smith@yahoo.com’, ‘3123622345’,
’02-FEB-2010′, ’01-FEB-2016′, ’02-FEB-2010′);
INSERT INTO member
VALUES(2002, ‘ZWNWnQJT09’, ‘JONES’, ‘jones@hotmail.com’, ‘6302077890’,
’03-MAR-2010′, ’02-MAR-2016′, ’31-DEC-2010′);
INSERT INTO member
VALUES(2003, ‘gc88Wmvpx0’, ‘MILLER’, ‘miller@abc.com’, ‘6303551234’,
’09-APR-2010′, ’08-APR-2016′, ’09-APR-2010′);
INSERT INTO member
VALUES(2004, ‘KcxweSYg55’, ‘JOHNSON’, ‘jo12@gmail.com’, ‘7732015678’,
’22-APR-2010′, ’21-APR-2016′, ’22-APR-2010′);
INSERT INTO member
VALUES(2005, ‘CDYe44BBXd’, ‘CLARK’, ‘clark12@dpu.edu’, ‘8476391001’,
’18-MAY-2010′, ’17-MAY-2016′, ’18-MAY-2010′);
INSERT INTO member
VALUES(2006, ‘xGqmkw345zr’, ‘LEWIS’, ‘lewis@ual.com’, ‘2246166666’,
’20-MAY-2010′, ’19-MAY-2016′, ’20-MAY-2010′);
INSERT INTO member
VALUES(2007, ‘Y79zAHQwcB’, ‘KING’, ‘king@yahoo.com’, ‘3018551234’,
’30-JUN-2010′, ’29-JUN-2016′, ’30-JUN-2010′);
INSERT INTO member
VALUES(2008, ‘vhSDHMDg66’, ‘SCOTT’, ‘scott@hotmail.com’, ‘7701239876’,
’30-AUG-2010′, ’30-DEC-2011′, ’30-DEC-2011′);
COMMIT;
/
CREATE TABLE video_title
( VIDEO_TITLE_ID NUMBER(4) PRIMARY KEY,
VIDEO_NAME VARCHAR2(50) NOT NULL,
FORMAT VARCHAR2(20) NOT NULL,
PUBLISH_DATE DATE NOT NULL);
/
INSERT INTO video_title
VALUES(1000, ‘PRETTY WOMAN’, ‘VHS TAPE’, ’28-SEP-1990′);
INSERT INTO video_title
VALUES(1010, ‘TOY STORY’, ‘VHS TAPE’, ’30-OCT-1996′);
INSERT INTO video_title
VALUES(1020, ‘TITANIC’, ‘VHS TAPE’, ’01-SEP-1998′);
INSERT INTO video_title
VALUES(1030, ‘THE PLANETS’, ‘VHS TAPE’, ’02-APR-1999′);
INSERT INTO video_title
VALUES(1040, ‘TARZAN’, ‘VHS TAPE’, ’04-JUN-1999′);
INSERT INTO video_title
VALUES(1050, ‘TOY STORY 2’, ‘VHS TAPE’, ’24-NOV-1999′);
INSERT INTO video_title
VALUES(1060, ‘DIE ANOTHER DAY’, ‘VHS TAPE’, ’03-JUN-2003′);
INSERT INTO video_title
VALUES(1070, ‘DOWN WITH LOVE’, ‘VHS TAPE’, ’20-FEB-2003′);
INSERT INTO video_title
VALUES(1080, ‘DIE ANOTHER DAY’, ‘DVD’, ’03-JUN-2003′);
INSERT INTO video_title
VALUES(1090, ‘PRETTY WOMAN’, ‘DVD’, ’30-AUG-2005′);
INSERT INTO video_title
VALUES(1100, ‘DIE ANOTHER DAY’, ‘BLU-RAY’, ’21-OCT-2008′);
INSERT INTO video_title
VALUES(1110, ‘TOY STORY’, ‘DVD’, ’11-MAY-2010′);
INSERT INTO video_title
VALUES(1120, ‘TOY STORY 2’, ‘DVD’, ’11-MAY-2010′);
INSERT INTO video_title
VALUES(1130, ‘TOY STORY 2’, ‘BLU-RAY’, ’23-MAY-2010′);
COMMIT;
/
CREATE TABLE video_copy
( VIDEO_COPY_ID NUMBER(4) PRIMARY KEY,
VIDEO_TITLE_ID NUMBER(4) NOT NULL
REFERENCES VIDEO_TITLE (VIDEO_TITLE_ID),
MAXIMUM_CHECKOUT_DAYS NUMBER(3),
COPY_STATUS CHAR NOT NULL CONSTRAINT ck_item
CHECK (COPY_STATUS in (‘A’, ‘R’, ‘D’)));
/
INSERT INTO video_copy VALUES(6000, 1000, 14, ‘A’);
INSERT INTO video_copy VALUES(6001, 1000, 14, ‘A’);
INSERT INTO video_copy VALUES(6003, 1010, 14, ‘A’);
INSERT INTO video_copy VALUES(6004, 1020, 14, ‘A’);
INSERT INTO video_copy VALUES(6008, 1040, 14, ‘A’);
INSERT INTO video_copy VALUES(6009, 1050, 14, ‘A’);
INSERT INTO video_copy VALUES(6010, 1060, 14, ‘A’);
INSERT INTO video_copy VALUES(6012, 1070, 14, ‘A’);
INSERT INTO video_copy VALUES(6013, 1070, 14, ‘A’);
INSERT INTO video_copy VALUES(6014, 1080, 7, ‘A’);
INSERT INTO video_copy VALUES(6015, 1090, 7, ‘A’);
INSERT INTO video_copy VALUES(6019, 1120, 7, ‘A’);
INSERT INTO video_copy VALUES(6020, 1130, 3, ‘A’);
INSERT INTO video_copy VALUES(6005, 1020, 14, ‘R’);
INSERT INTO video_copy VALUES(6002, 1010, 14, ‘R’);
INSERT INTO video_copy VALUES(6006, 1030, 14, ‘R’);
INSERT INTO video_copy VALUES(6022, 1000, 14, ‘D’);
INSERT INTO video_copy VALUES(6021, 1030, 14, ‘R’);
INSERT INTO video_copy VALUES(6011, 1060, 14, ‘R’);
INSERT INTO video_copy VALUES(6007, 1040, 14, ‘R’);
INSERT INTO video_copy VALUES(6018, 1120, 7, ‘R’);
INSERT INTO video_copy VALUES(6017, 1110, 7, ‘R’);
INSERT INTO video_copy VALUES(6016, 1100, 3, ‘R’);
INSERT INTO video_copy VALUES(6023, 1130, 3, ‘D’);
COMMIT;
/
CREATE TABLE video_rental_record
( MEMBER_ID NUMBER REFERENCES MEMBER (MEMBER_ID),
VIDEO_COPY_ID NUMBER(4) REFERENCES VIDEO_COPY (VIDEO_COPY_ID),
CHECKOUT_DATE DATE NOT NULL,
DUE_DATE DATE NOT NULL,
RETURN_DATE DATE,
CONSTRAINT pk_rental PRIMARY KEY
(MEMBER_ID, VIDEO_COPY_ID, CHECKOUT_DATE));
/
INSERT INTO video_rental_record
VALUES(2001, 6000, ’03-FEB-2014′, ’17-FEB-2014′, ’16-FEB-2014′);
INSERT INTO video_rental_record
VALUES(2002, 6012, ’04-MAR-2014′, ’18-MAR-2014′, ’17-MAR-2014′);
INSERT INTO video_rental_record
VALUES(2002, 6012, ’18-MAR-2014′, ’01-APR-2014′, ’01-APR-2014′);
INSERT INTO video_rental_record
VALUES(2003, 6005, ’12-APR-2014′, ’19-APR-2014′, ’18-APR-2014′);
INSERT INTO video_rental_record
VALUES(2004, 6016, ’01-MAY-2014′, ’04-MAY-2014′, ’02-MAY-2014′);
INSERT INTO video_rental_record
VALUES(2001, 6014, ’02-JUL-2014′, ’09-JUL-2014′, ’05-JUL-2014′);
INSERT INTO video_rental_record
VALUES(2006, 6017, ’21-AUG-2014′, ’28-AUG-2014′, ’23-AUG-2014′);
INSERT INTO video_rental_record
VALUES(2005, 6019, ’22-OCT-2014′, ’29-OCT-2014′, ’25-OCT-2014′);
INSERT INTO video_rental_record
VALUES(2007, 6022, ’05-DEC-2014′, ’19-DEC-2014′, ’06-DEC-2014′);
INSERT INTO video_rental_record
VALUES(2001, 6005, ’08-APR-2015′, ’15-APR-2015′, ”);
INSERT INTO video_rental_record
VALUES(2007, 6002, ’09-APR-2015′, ’23-APR-2015′, ”);
INSERT INTO video_rental_record
VALUES(2007, 6006, ’09-APR-2015′, ’23-APR-2015′, ”);
INSERT INTO video_rental_record
VALUES(2003, 6021, ’20-APR-2015′, ’04-MAY-2015′, ”);
INSERT INTO video_rental_record
VALUES(2003, 6011, ’20-APR-2015′, ’04-MAY-2015′, ”);
INSERT INTO video_rental_record
VALUES(2002, 6007, ’22-APR-2015′, ’06-MAY-2015′, ”);
INSERT INTO video_rental_record
VALUES(2005, 6018, ’28-APR-2015′, ’05-MAY-2015′, ”);
INSERT INTO video_rental_record
VALUES(2002, 6017, ’01-MAY-2015′, ’08-MAY-2015′, ”);
INSERT INTO video_rental_record
VALUES(2002, 6016, ’01-MAY-2015′, ’04-MAY-2015′, ”);
COMMIT;
Part 2) [260 Points]
- You are not allowed to create temporary tables/views/triggers.
- Hard coding is not allowed in your program.
- You can only use the MEMBER, VIDEO_TITLE, VIDEO_COPY, and VIDEO_RENTAL_RECORD tables. You will get a zero point if you use a different table (e.g., different table names, column names, or data types).
- You cannot change the procedure headers. You will get a zero point if you use a different procedure header (e.g., different procedure names, parameter names, data types, or default values).
1) [0 Point] – member_registration()
Create a procedure called member_registration to add a new member to the MEMBER table.
All passwords must be between 10 and 30 characters in length.
(You may use my example in your project.)
The procedure header is
CREATE OR REPLACE PROCEDURE member_registration
(
p_member_id NUMBER,
p_password VARCHAR2,
p_name VARCHAR2,
p_email_address VARCHAR2,
p_phone_number VARCHAR2,
p_registration_date DATE,
p_expiration_date DATE
)
Consider the following special cases:
- The string in p_password is too short/long.
- The p_name is empty.
- The p_email_address is empty.
- The value of p_registration_date is greater than the current date.
- The value of p_registration_date is greater than the value of p_expiration_date.
Example
CREATE OR REPLACE PROCEDURE member_registration
(
p_member_id NUMBER,
p_password VARCHAR2,
p_name VARCHAR2,
p_email_address VARCHAR2,
p_phone_number VARCHAR2,
p_registration_date DATE,
p_expiration_date DATE)
IS
v_count NUMBER;
v_status CHAR;
BEGIN
IF p_member_id <= 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid ID!’);
RETURN;
END IF;
SELECT COUNT(*)
INTO v_count
FROM member
WHERE member_id = p_member_id;
IF v_count != 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid ID!’);
RETURN;
END IF;
IF LENGTH(p_password) < 10 OR LENGTH(p_password) > 30 THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid passsword!’);
RETURN;
END IF;
IF p_name is NULL THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid name!’);
RETURN;
END IF;
IF p_email_address is NULL THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid email address!’);
RETURN;
END IF;
IF p_registration_date IS NULL OR
TO_CHAR(p_registration_date, ‘yyyymmdd’) >
TO_CHAR(sysdate, ‘yyyymmdd’) THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid registration date!’);
RETURN;
END IF;
IF p_expiration_date IS NULL OR
TO_CHAR(p_expiration_date, ‘yyyymmdd’) <
TO_CHAR(p_registration_date, ‘yyyymmdd’) THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid expiration date!’);
RETURN;
END IF;
INSERT INTO member
VALUES(p_member_id, p_password, UPPER(p_name),
p_email_address, p_phone_number, p_registration_date,
p_expiration_date, sysdate);
COMMIT;
DBMS_OUTPUT.PUT_LINE
(INITCAP(p_name) || ‘ has been added into the member table.’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘My exception: ‘ ||
TO_CHAR(SQLCODE) || ‘ ‘ || SQLERRM);
END;
Testing the procedure
- EXEC member_registration(2009, ‘efg12345abcD’, ‘Adams’, ‘adams_1@yahoo.com’, ‘3123621111’, ’02-SEP-2013′, ’01-SEP-2018′)
Dbms Output: Adams has been added into the member table.
- EXEC member_registration(2010, ‘abc’, ‘FORD’, ‘ford1@yahoo.com’, ‘3123622222’, ’02-SEP-2013′, ’01-SEP-2018′)
Dbms Output: Invalid passsword!
- ……
2) [10 points] – member_new_expiration_date()
Create a procedure called member_new_expiration_date to update an existing member’s expiration date.
The procedure header is
CREATE OR REPLACE PROCEDURE member_new_expiration_date
(
p_member_id NUMBER,
p_new_expiration_date DATE
)
You need to consider the following cases:
- The value of p_member_id is not in the MEMBER_ID column of the MEMBER
Dbms Output: Invalid ID!
- The value of p_member_id is in the MEMBER_ID column of the MEMBER
- Update the MEMBER table (the EXPIRATION_DATE and LAST_UPDATE_DATE columns).
- Dbms Output: The expiration date has been updated.
3) [70 points] – video_search()
Create a procedure called video_search to search a video and display the VIDEO_NAME, VIDEO_COPY_ID, FORMAT, and COPY_STATUS of the video’s copies. In addition, the checkout dates (CHECKOUT_DATE) and due dates (DUE_DATE) are also displayed for unreturned copies. The damaged copies (COPY_STATUS = “D”) are excluded in your output. Sort your output by the VIDEO_NAME and then the VIDEO_COPY_ID.
The procedure header is
CREATE OR REPLACE PROCEDURE video_search
(
p_video_name VARCHAR2,
p_video_format VARCHAR2 DEFAULT NULL
Hint: WHERE UPPER(video_name) like ‘%’ || UPPER(p_video_name) || ‘%’;
Testing the procedure
(If your output does not match mine EXACTLY, you will lose some points.)
- EXEC video_search(‘ocean’)
Dbms Output
***** 0 results found for ocean. *****
- EXEC video_search(‘PRETTY WOMAN’, ‘Blu-Ray’)
Dbms Output:
***** 0 results found for PRETTY WOMAN (Blu-Ray). *****
- EXEC video_search(‘Pretty Woman’)
Dbms Output:
***** 3 results found for Pretty Woman. (Available copies: 3) *****
VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS CHECKOUT_DATE DUE_DATE
———————————————————————————————
PRETTY WOMAN 6000 VHS TAPE Available
PRETTY WOMAN 6001 VHS TAPE Available
PRETTY WOMAN 6015 DVD Available
- EXEC video_search(‘Another’)
Dbms Output:
***** 4 results found for Another. (Available copies: 2) *****
VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS CHECKOUT_DATE DUE_DATE
———————————————————————————————
DIE ANOTHER DAY 6010 VHS TAPE Available
DIE ANOTHER DAY 6011 VHS TAPE Rented 20-APR-2015 04-MAY-2015
DIE ANOTHER DAY 6014 DVD Available
DIE ANOTHER DAY 6016 BLU-RAY Rented 01-MAY-2015 04-MAY-2015
- EXEC video_search(‘ANOTHER’, ‘Dvd’)
Dbms Output:
***** 1 result found for ANOTHER (Dvd). (Available copies: 1) *****
VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS CHECKOUT_DATE DUE_DATE
———————————————————————————————
DIE ANOTHER DAY 6014 DVD Available
- EXEC video_search(‘Story’)
Dbms Output:
***** 7 results found for Story. (Available copies: 4) *****
VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS CHECKOUT_DATE DUE_DATE
———————————————————————————————
TOY STORY 6002 VHS TAPE Rented 09-APR-2015 23-APR-2015
TOY STORY 6003 VHS TAPE Available
TOY STORY 6017 DVD Rented 01-MAY-2015 08-MAY-2015
TOY STORY 2 6009 VHS TAPE Available
TOY STORY 2 6018 DVD Rented 28-APR-2015 05-MAY-2015
TOY STORY 2 6019 DVD Available
TOY STORY 2 6020 BLU-RAY Available
4) [70 Points] – video_checkout()
Create a procedure called video_checkout to record a new rental. When the video is successfully checked out, you need to insert a new record into the VIDEO_RENTAL_RECORD table and update the corresponding record in the VIDEO_COPY table. Otherwise, the action is denied.
The procedure header is
CREATE OR REPLACE PROCEDURE video_checkout
(
p_member_id NUMBER,
p_video_copy_id NUMBER,
p_video_checkout_date DATE
)
A member whose expiration date is less than the current date (sysdate) is not able to make a rental.
Consider the following special cases:
- The value of p_member_id is not in the MEMBER_ID column of the MEMBER
- The member’s expiration date is less than the current date.
- The copy is not available (COPY_STATUS = “R” or “D”).
- The value of p_video_checkout_date is greater than the current date.
- How to calculate the due date? Checkout periods are determined by the values in the MAXIMUM_CHECKOUT_DAYS column. Hard coding is not allowed.
- A member may have up to five (5) copies checked out at any one time.
(For example, Tom has five copies checked out; he cannot rent a copy before he returns one of the five copies he checked out.)
- (CSC 452 only) Before a member returns a copy, he/she cannot rent a second copy of the same video title (VIDEO_TITLE_ID).
You need to create/run some test cases.
5) [60 points] – video_return()
Create a procedure called video_return to change the rental status for that returned copy. When the copy is successfully checked in, you need to update both the VIDEO_RENTAL_RECORD table and the VIDEO_COPY table. Otherwise, the action is denied.
The procedure header is
CREATE OR REPLACE PROCEDURE video_return
(
p_video_copy_id NUMBER,
p_video_return_date DATE
)
Consider the following special cases:
- The value of p_video_copy_id does not exist in the corresponding column of the VIDEO_COPY table.
- The status (COPY_STATUS) of that copy is not “R” (rented).
- The value of p_video_return_date is greater than the current date.
You need to create/run some test cases.
6) [25 points] – print_unreturned_video()
Create a procedure called print_unreturned_video to retrieve all the copies that a member hasn’t returned. The output should include the member’s ID, name, expiration date, first checkout date, last checkout date, the number of unreturned copies, video name (VIDEO_NAME), copy ID (VIDEO_COPY_ID), format, checkout date, and due date of the rentals. Sort the data by due date and then the video name. The procedure header is
CREATE OR REPLACE PROCEDURE print_unreturned_video
(
p_member_id NUMBER
)
Testing the procedure
(If your output does not match mine EXACTLY, you will lose some points.)
- EXEC print_unreturned_video(90)
Dbms Output:
The member (id = 90) is not in the member table.
- EXEC print_unreturned_video(2004)
Dbms Output:
—————————————-
Member ID: 2004
Member Name: JOHNSON
Expiration Date: 21-APR-2016
First Checkout Date: 01-MAY-2014
Last Checkout Date: 01-MAY-2014
—————————————-
Number of Unreturned Videos: 0
—————————————-
- EXEC print_unreturned_video(2008)
Dbms Output:
—————————————-
Member ID: 2008
Member Name: SCOTT
Expiration Date: 30-DEC-2011
First Checkout Date: N/A
Last Checkout Date: N/A
—————————————-
Number of Unreturned Videos: 0
—————————————-
- EXEC print_unreturned_video(2002)
Dbms Output:
—————————————-
Member ID: 2002
Member Name: JONES
Expiration Date: 02-MAR-2016
First Checkout Date: 04-MAR-2014
Last Checkout Date: 01-MAY-2015
—————————————-
Number of Unreturned Videos: 3
—————————————-
Video Copy ID: 6016
Video Name: DIE ANOTHER DAY
Format: BLU-RAY
Checkout Date: 01-MAY-2015
Due Date: 04-MAY-2015
—————————————-
Video Copy ID: 6007
Video Name: TARZAN
Format: VHS TAPE
Checkout Date: 22-APR-2015
Due Date: 06-MAY-2015
—————————————-
Video Copy ID: 6017
Video Name: TOY STORY
Format: DVD
Checkout Date: 01-MAY-2015
Due Date: 08-MAY-2015
—————————————-
7) [25 points] – Package video_ pkg
Group all the above subprograms (member_registration, member_new_expiration_date, video_search, video_checkout, video_return, and print_unreturned_video)together in a package (package specification and package body) called video_pkg.
5 points – package specification
20 points – package body
You need to test your package.
- File Format: .sql
- Tested On: Oracle 11i