SOLUTION AVAILABLE AT: http://libraay.com/downloads/csc-352-452-database-programming-project/
Problem:
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, NAME, FORMAT, PUBLISH_DATE, MAXIMUM_CHECKOUT_DAYS);
- VIDEO_COPY(VIDEO_COPY_ID, VIDEO_ID*, STATUS);
- VIDEO_RENTAL_RECORD(CUSTOMER_ID*, VIDEO_COPY_ID*, CHECKOUT_DATE,DUE_DATE, RETURN_DATE);
The primary keys are marked in red and the foreign keys are marked with asterisks.
VIDEO_COPY(STATUS): A – Available, R – Rented, D – Damaged
Each video in the VIDEO table has at least one video copy in the VIDEO_COPY table.
Part 1)
Create and populate the CUSTOMER, VIDEO, VIDEO_COPY, and VIDEO_RENTAL_RECORD tables by using the following SQL statements. (You have to connect to CDM’s Oracle server to populate the tables.)
CREATE TABLE customer
( CUSTOMER_ID NUMBER PRIMARY KEY,
PASSWORD VARCHAR2(30) NOT NULL,
NAME VARCHAR2(30) 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 SELECT * FROM hchen.customer;
COMMIT;
/
SELECT COUNT(*) FROM customer;
Please make sure that there are 8 rows in your customer table.
CREATE TABLE video
( VIDEO_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100) NOT NULL,
FORMAT VARCHAR2(100) NOT NULL,
PUBLISH_DATE DATE NOT NULL,
MAXIMUM_CHECKOUT_DAYS NUMBER(3) NOT NULL
);
/
INSERT INTO video SELECT * FROM hchen.video;
COMMIT;
/
SELECT COUNT(*) FROM video;
Please make sure that there are 14 rows in your video table.
CREATE TABLE video_copy
( VIDEO_COPY_ID NUMBER PRIMARY KEY,
VIDEO_ID NUMBER NOT NULL REFERENCES VIDEO (VIDEO_ID),
STATUS CHAR NOT NULL CONSTRAINT ck_item
CHECK (STATUS in (‘A’, ‘R’, ‘D’)));
/
INSERT INTO video_copy SELECT * FROM hchen.video_copy;
COMMIT;
/
SELECT COUNT(*) FROM video_copy;
Please make sure that there are 24 rows in your video_copy table.
CREATE TABLE video_rental_record
( CUSTOMER_ID NUMBER REFERENCES CUSTOMER (CUSTOMER_ID),
VIDEO_COPY_ID NUMBER 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 SELECT * FROM hchen.video_rental_record;
COMMIT;
/
SELECT COUNT(*) FROM video_rental_record;
Please make sure that there are 18 rows in your video_copy table.
Part 2) [270 Points]
- You are not allowed to create temporary tables/views/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) [0 Point] – customer_registration()
Create a procedure called customer_registration to add a new customer to the CUSTOMER table. All passwords must be between 8 and 30 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 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 ID!’);
RETURN;
END IF;
IF LENGTH(p_password) < 8 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 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) [15 points] – update_expiration_date()
Create a procedure called update_expiration_date to update an existing customer’s expiration date. The procedure header is
CREATE OR REPLACE PROCEDURE update_expiration_date
(
p_customer_id NUMBER,
p_new_expiration_date DATE
)
You need to consider the following cases:
- The value of p_customer_id is not in the CUSTOMER_ID column of the CUSTOMER
Dbms Output: Invalid ID!
- The value of p_customer_id is in the CUSTOMER_ID column of the CUSTOMER
- Update the CUSTOMER 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 name, copy ID, format, and status of the video’s copies. In addition, the checkout dates and due dates are also displayed for unreturned copies. The damaged copies (STATUS = ‘D’) are excluded in your output. Sort your output by the video name (NAME) and then the copy ID (VIDEO_COPY_ID).
The video name is not case-sensitive (e.g., Ocean = ocean). The format of a video is not case-sensitive (e.g., DVD = dvd). The procedure header is
CREATE OR REPLACE PROCEDURE video_search
(
p_name VARCHAR2,
p_format VARCHAR2 DEFAULT NULL
)
Hint: WHERE UPPER(name) like ‘%’ || UPPER(p_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) *****
NAME VIDEO_COPY_ID FORMAT 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) *****
NAME VIDEO_COPY_ID FORMAT 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) *****
NAME VIDEO_COPY_ID FORMAT 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) *****
NAME VIDEO_COPY_ID FORMAT 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 row (record) into the VIDEO_RENTAL_RECORD table and update the corresponding row (record) in the VIDEO_COPY table. Otherwise, the action is denied.
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
- The customer’s expiration date is less than the current date.
- The copy is not available (STATUS = ‘R’ or ‘D’).
- The value of p_video_checkout_date is greater than the current date.
- Checkout periods are determined by the values in the MAXIMUM_CHECKOUT_DAYS column. The due date is p_video_checkout_date plus the corresponding MAXIMUM_CHECKOUT_DAYS. Hard coding (e.g., 3, 7, or 14) is not allowed.
- A customer 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 customer returns a copy, he/she cannot rent a second copy of the same video (VIDEO_ID).
(For example, Mary has the copy with ID 6000 (VIDEO_COPY_ID = 6000) checked out. She cannot rent the copy with ID 6001 (VIDEO_COPY_ID = 6001) before she returns the copy with ID 6000. The reason is that both copies have the same VIDEO_ID (VIDEO_ID = 1000, ‘PRETTY WOMAN’, ‘VHS TAPE’)).
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 the corresponding rows (records) in the VIDEO_RENTAL_RECORD and VIDEO_COPY tables. 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 of that copy is not “R” (STATUS != ‘R’).
- The value of p_video_return_date is greater than the current date.
You need to create/run some test cases.
6) [30 points] – 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, 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-2014
Last Checkout Date: 01-MAY-2014
—————————————-
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-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 (customer_registration, update_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
SOLUTION AVAILABLE AT: http://libraay.com/downloads/csc-352-452-database-programming-project/