CSC 352 / 452: DATABASE PROGRAMMING PROJECT | Q3 to Q7 Solution

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