[solution]- CSC 352 / 452: DATABASE PROGRAMMING PROJECT (270 POINTS)


CSC 352 / 452: DATABASE PROGRAMMING
PROJECT
(270 POINTS)

Due on Friday, 3/18/2016 at midnight

Late projects will not be accepted or graded.
You’ll receive no points if your programs do not compile.

• 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.
• The project is due by 11:59 pm Friday, March 18, 2016. Do not wait until the last day to submit your assignment file! Submit ahead in case of unforeseen circumstances such as a computer crash or illness. There are no exceptions to the deadline.

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 table. Dbms Output: Invalid ID! • The value of p_customer_id is in the CUSTOMER_ID column of the CUSTOMER table. o Update the CUSTOMER table (the EXPIRATION_DATE and LAST_UPDATE_DATE columns). o 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 table. • 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 You need to test your package. Please submit a text file containing all the source codes to D2L before or on due date.

Have a similar question?

Ask your homework question

 

Enjoy Our Unique Features!

INDIVIDUAL APPROACH:
Chat with every writer who applies to your request, and view their skills and portfolio. Make the choice that’s right for you.
MANAGE YOUR ORDER:
Monitor progress and see any changes made. Have full control over every phase of the process.
COMMUNICATE:
Ask your writer questions and provide your ideas about your paper. Produce the exact result that you want.
ENJOY THE OUTCOME:
Get everything done on time with high quality. Writing papers is much simpler with us.

Submit Your Instructions to Writers for FREE!!

Ask your homework question

 

Recent Posts

© 2017 theacademicessays. All Rights Reserved. Design & Developed by theacademicessays.
Loading...