CSC 352 / 452: Database Programming Project


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

 

Leave a Reply

Your email address will not be published. Required fields are marked *



  • File Format: .sql