CSC352 452: DATABASE PROGRAMMING PROJECT Q3 to Q7 Solution

Question: #3919

CSC 352 / 452: DATABASE PROGRAMMING PROJECT

Please, remember that this is NOT a group project. So do not consult with anybody in class or outside class.
·Please submit a text file containing all your programs to D2L before or on due date.
·Please review your file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct file. If you submit a blank/wrong file, you will simply receive a grade of zero.

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.

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 table.
•   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: &n