PL SQL Video Rental

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.

CUSTOMER(CUSTOMER_ID, PASSWORD, NAME, EMAIL_ADDRESS, PHONE_NUMBER,
REGISTRATION_DATE, EXPIRATION_DATE, LAST_UPDATE_DATE);
VIDEO(VIDEO_ID, VIDEO_NAME, FORMAT, PUBLISH_DATE);
VIDEO_COPY(VIDEO_COPY_ID, VIDEO_ID*, MAXIMUM_CHECKOUT_DAYS, COPY_STATUS);
VIDEO_RENTAL_RECORD(CUSTOMER_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

A video may have one or many video copies.

Part 1)

1) Create and populate the following tables.

CREATE TABLE customer
( CUSTOMER_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 customer
VALUES(2001, 'CpsBTKpN','SMITH','smith@yahoo.com', '3123622345',
'02-FEB-2010', '01-FEB-2016', '02-FEB-2010');
INSERT INTO customer
VALUES(2002, 'ZWNWnQJT9', 'JONES', 'jones@hotmail.com', '6302077890',
'03-MAR-2010', '02-MAR-2016', '31-DEC-2010');
INSERT INTO customer
VALUES(2003, 'gc88Wmvpx', 'MILLER', 'miller@abc.com', '6303551234',
'09-APR-2010', '08-APR-2016', '09-APR-2010');
INSERT INTO customer
VALUES(2004, 'KcxweSYg555', 'JOHNSON', 'jo12@gmail.com', '7732015678',
'22-APR-2010', '21-APR-2016', '22-APR-2010');
INSERT INTO customer
VALUES(2005, 'CDYe44BBXd', 'CLARK', 'clark12@dpu.edu', '8476391001',
'18-MAY-2010', '17-MAY-2016', '18-MAY-2010');
INSERT INTO customer
VALUES(2006, 'xGqmkw345zr', 'LEWIS', 'lewis@ual.com', '2246166666',
'20-MAY-2010', '19-MAY-2016', '20-MAY-2010');
INSERT INTO customer
VALUES(2007, 'Y79zAHQwcB', 'KING', 'king@yahoo.com', '3018551234',
'30-JUN-2010', '29-JUN-2016', '30-JUN-2010');
INSERT INTO customer
VALUES(2008, 'vhSDHMDg66', 'SCOTT', 'scott@hotmail.com', '7701239876',
'30-AUG-2010', '30-DEC-2011', '30-DEC-2011');
COMMIT;

CREATE TABLE video
( VIDEO_ID NUMBER(4) PRIMARY KEY,
VIDEO_NAME VARCHAR2(50) NOT NULL,
FORMAT VARCHAR2(20) NOT NULL,
PUBLISH_DATE DATE NOT NULL);

INSERT INTO video
VALUES(1000, 'PRETTY WOMAN', 'VHS TAPE', '28-SEP-1990');
INSERT INTO video
VALUES(1010, 'TOY STORY', 'VHS TAPE', '30-OCT-1996');
INSERT INTO video
VALUES(1020, 'TITANIC', 'VHS TAPE', '01-SEP-1998');
INSERT INTO video
VALUES(1030, 'THE PLANETS', 'VHS TAPE', '02-APR-1999');
INSERT INTO video
VALUES(1040, 'TARZAN', 'VHS TAPE', '04-JUN-1999');
INSERT INTO video
VALUES(1050, 'TOY STORY 2', 'VHS TAPE', '24-NOV-1999');
INSERT INTO video
VALUES(1060, 'DIE ANOTHER DAY', 'VHS TAPE', '03-JUN-2003');
INSERT INTO video
VALUES(1070, 'DOWN WITH LOVE', 'VHS TAPE', '20-FEB-2003');
INSERT INTO video
VALUES(1080, 'DIE ANOTHER DAY', 'DVD', '03-JUN-2003');
INSERT INTO video
VALUES(1090, 'PRETTY WOMAN', 'DVD', '30-AUG-2005');
INSERT INTO video
VALUES(1100, 'DIE ANOTHER DAY', 'BLU-RAY', '21-OCT-2008');
INSERT INTO video
VALUES(1110, 'TOY STORY', 'DVD', '11-MAY-2010');
INSERT INTO video
VALUES(1120, 'TOY STORY 2', 'DVD', '11-MAY-2010');
INSERT INTO video
VALUES(1130, 'TOY STORY 2', 'BLU-RAY', '23-MAY-2010');
COMMIT;

CREATE TABLE video_copy
( VIDEO_COPY_ID NUMBER(4) PRIMARY KEY,
VIDEO_ID NUMBER(4) NOT NULL
REFERENCES VIDEO (VIDEO_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
( CUSTOMER_ID NUMBER REFERENCES CUSTOMER (CUSTOMER_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
(CUSTOMER_ID, VIDEO_COPY_ID, CHECKOUT_DATE));

INSERT INTO video_rental_record
VALUES(2001, 6000, '03-FEB-2013', '17-FEB-2013', '16-FEB-2013');
INSERT INTO video_rental_record
VALUES(2002, 6012, '04-MAR-2013', '18-MAR-2013', '17-MAR-2013');
INSERT INTO video_rental_record
VALUES(2002, 6012, '18-MAR-2013', '01-APR-2013', '01-APR-2013');
INSERT INTO video_rental_record
VALUES(2003, 6005, '12-APR-2013', '19-APR-2013', '18-APR-2013');
INSERT INTO video_rental_record
VALUES(2004, 6016, '01-MAY-2013', '04-MAY-2013', '02-MAY-2013');
INSERT INTO video_rental_record
VALUES(2001, 6014, '02-JUL-2013', '09-JUL-2013', '05-JUL-2013');
INSERT INTO video_rental_record
VALUES(2006, 6017, '21-AUG-2013', '28-AUG-2013', '23-AUG-2013');
INSERT INTO video_rental_record
VALUES(2005, 6019, '22-OCT-2013', '29-OCT-2013', '25-OCT-2013');
INSERT INTO video_rental_record
VALUES(2007, 6022, '05-DEC-2013', '19-DEC-2013', '06-DEC-2013');
INSERT INTO video_rental_record
VALUES(2001, 6005, '08-APR-2014', '15-APR-2014', '');
INSERT INTO video_rental_record
VALUES(2007, 6002, '09-APR-2014', '23-APR-2014', '');
INSERT INTO video_rental_record
VALUES(2007, 6006, '09-APR-2014', '23-APR-2014', '');
INSERT INTO video_rental_record
VALUES(2003, 6021, '20-APR-2014', '04-MAY-2014', '');
INSERT INTO video_rental_record
VALUES(2003, 6011, '20-APR-2014', '04-MAY-2014', '');
INSERT INTO video_rental_record
VALUES(2002, 6007, '22-APR-2014', '06-MAY-2014', '');
INSERT INTO video_rental_record
VALUES(2005, 6018, '28-APR-2014', '05-MAY-2014', '');
INSERT INTO video_rental_record
VALUES(2002, 6017, '01-MAY-2014', '08-MAY-2014', '');
INSERT INTO video_rental_record
VALUES(2002, 6016, '01-MAY-2014', '04-MAY-2014', '');
COMMIT;

Part 2)

• You are not allowed to create temporary tables, views, and triggers.
• Hard coding is not allowed in your program.
• You can only use the CUSTOMER, VIDEO, 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) - customer_registration()

Create a procedure called customer_registration to add a new customer to the CUSTOMER table.

All passwords must be between 8 and 20 characters in length.

(You may use my example in your project.)

The procedure header is

CREATE OR REPLACE PROCEDURE customer_registration
(
p_customer_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 customer_registration
(
p_customer_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_customer_id <= 0 THEN
DBMS_OUTPUT.PUT_LINE('Invalid customer ID!');
RETURN;
END IF;

SELECT COUNT(*)
INTO v_count
FROM customer
WHERE customer_id = p_customer_id;

IF v_count != 0 THEN
DBMS_OUTPUT.PUT_LINE('Invalid customer ID!');
RETURN;
END IF;

IF LENGTH(p_password) < 8 OR LENGTH(p_password) > 20 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 customer
VALUES(p_customer_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 customer table.');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('My exception: ' ||
TO_CHAR(SQLCODE) || ' ' || SQLERRM);
END;

Testing the procedure

• EXEC customer_registration(2009, 'efg12345abcd', 'Adams', 'adams_1@yahoo.com', '3123621111', '02-SEP-2013', '01-SEP-2018') Dbms Output: Adams has been added into the customer table.
• EXEC customer_registration(2010, 'abc', 'FORD', 'ford1@yahoo.com', '3123622222', '02-SEP-2013', '01-SEP-2018') Dbms Output:Invalid passsword!
• ...

2) customer_renewal()

Create a procedure called customer_renewal to update an existing customers expiration date.

The procedure header is

CREATE OR REPLACE PROCEDURE customer_renewal
(
p_customer_id NUMBER,
p_new_expiration_date DATE
)

You may need to consider the following cases:

• The value of p_customer_id is not in the CUSTOMER_ID column of the CUSTOMER table. Your statement: DBMS_OUTPUT.PUT_LINE ('The customer_id (' || p_customer_id || ') is not in the customer table.');
• The value of p_customer_id is in the CUSTOMER_ID column of the CUSTOMER table. Update the CUSTOMER table (the EXPIRATION_DATE and LAST_UPDATE_DATE columns). Your statement: DBMS_OUTPUT.PUT_LINE('The expiration_date for ' || p_customer_id || ' has been updated.');

3) search_video()

Create a procedure called search_video to search a video and display the VIDEO_NAME, VIDEO_COPY_ID, FORMAT, and COPY_STATUS of the videos copies. In addition, the 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.

Assume that each video in the VIDEO table has at least one copy in the VIDEO_COPY table.

The procedure header is

CREATE OR REPLACE PROCEDURE search_video
(
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 search_video('ocean')
Dbms Output:

***** 0 results found for ocean. *****

• EXEC search_video('PRETTY WOMAN', 'Blu-Ray')
Dbms Output:

***** 0 results found for PRETTY WOMAN (Blu-Ray). *****

• EXEC search_video('Pretty Woman')
Dbms Output:

***** 3 results found for Pretty Woman. (Available copies: 3) *****

VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS DUE_DATE
-------------------------------------------------------------------------
PRETTY WOMAN 6000 VHS TAPE Available
PRETTY WOMAN 6001 VHS TAPE Available
PRETTY WOMAN 6015 DVD Available

• EXEC search_video('Another')
Dbms Output:

***** 4 results found for Another. (Available copies: 2) *****

VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS DUE_DATE
-------------------------------------------------------------------------
DIE ANOTHER DAY 6010 VHS TAPE Available
DIE ANOTHER DAY 6011 VHS TAPE Rented 04-MAY-2014
DIE ANOTHER DAY 6014 DVD Available
DIE ANOTHER DAY 6016 BLU-RAY Rented 04-MAY-2014


• EXEC search_video('ANOTHER', 'Dvd')
Dbms Output:

***** 1 result found for ANOTHER (Dvd). (Available copies: 1) *****

VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS DUE_DATE
-------------------------------------------------------------------------
DIE ANOTHER DAY 6014 DVD Available

• EXEC search_video('Story')
Dbms Output:

***** 7 results found for Story. (Available copies: 4) *****

VIDEO_NAME VIDEO_COPY_ID FORMAT COPY_STATUS DUE_DATE
-------------------------------------------------------------------------
TOY STORY 6002 VHS TAPE Rented 23-APR-2014
TOY STORY 6003 VHS TAPE Available
TOY STORY 6017 DVD Rented 08-MAY-2014
TOY STORY 2 6009 VHS TAPE Available
TOY STORY 2 6018 DVD Rented 05-MAY-2014
TOY STORY 2 6019 DVD Available
TOY STORY 2 6020 BLU-RAY Available

4) 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.

The procedure header is

CREATE OR REPLACE PROCEDURE video_checkout
(
p_customer_id NUMBER,
p_video_copy_id NUMBER,
p_video_checkout_date DATE
)

A customer 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_customer_id is not in the CUSTOMER_ID column of the CUSTOMER table.
• The customers 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 customer may have up to seven (7) copies checked out at any one time. (For example, Tom has seven copies checked out; he cannot rent a copy before he returns one of the seven copies he checked out.)

You need to create/run some test cases.

5) 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 attribute 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) print_unreturned_video()

Create a procedure called print_unreturned_video to retrieve all the copies that a customer hasn't returned. The output should include the customer'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_customer_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 customer (id = 90) is not in the customer table.

• EXEC print_unreturned_video(2004)

Dbms Output:
----------------------------------------
Customer ID: 2004
Customer Name: JOHNSON
Expiration Date: 21-APR-2016
First Checkout Date: 01-MAY-2013
Last Checkout Date: 01-MAY-2013
----------------------------------------
Number of Unreturned Videos: 0
----------------------------------------

• EXEC print_unreturned_video(2008)

Dbms Output:
----------------------------------------
Customer ID: 2008
Customer 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:
----------------------------------------
Customer ID: 2002
Customer Name: JONES
Expiration Date: 02-MAR-2016
First Checkout Date: 04-MAR-2013
Last Checkout Date: 01-MAY-2014
----------------------------------------
Number of Unreturned Videos: 3
----------------------------------------
Video Copy ID: 6016
Video Name: DIE ANOTHER DAY
Format: BLU-RAY
Checkout Date: 01-MAY-2014
Due Date: 04-MAY-2014
----------------------------------------
Video Copy ID: 6007
Video Name: TARZAN
Format: VHS TAPE
Checkout Date: 22-APR-2014
Due Date: 06-MAY-2014
----------------------------------------
Video Copy ID: 6017
Video Name: TOY STORY
Format: DVD
Checkout Date: 01-MAY-2014
Due Date: 08-MAY-2014
----------------------------------------

7) Package video_ pkg

Group all the above subprograms
(
customer_registration,
customer_renewal,
search_video,
video_checkout,
video_return, and
print_unreturned_video
)

together in a package (package specification and package body) called video_ pkg.