Explore BrainMass
Share

Movie Rental Database: Stored Procedure and Stored Functions

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

In this lab assignment, you will create a stored procedure and a function. Make sure that you retain scripts with the CREATE statements for this assignment-you will need them in the following lab assignments when you will put them all in a package.

While you test your program units, you will probably change some data in your More Movie Rentals database. This okay and no cause for concern. Below is the list of things that you need to do for this assignment:

1. Create two stored PL/SQL procedures that process movie rental and movie return, based on the movie id and member id. More specifically:

? Procedure MOVIE_RENT_SP takes in three parameters: the movie id and the member id, and a payment method. It adds a new record to the MM_RENTAL table. It should also update the movie inventory, which is the MOVIE_QTY column of the MM_MOVIE table.
? Procedure MOVIE_RETURN_SP takes in two parameters: the movie id and the member id. Based on these two values, it identifies the rental record in the MM_RENTAL table and records the current date in the CHECKIN_DATE column. It also needs to update the movie inventory in the MM_MOVIE table.
? Make sure you validate the parameters: The movie id and member id must indicate an existing movie and member, respectively, and the payment method must be one of the valid payment method codes.

Run tests that include various cases of calling these procedures with valid and invalid parameter values to prove that your code identifies the case and reacts properly.

2. Write a function that retrieves the movie stock information and formats it in a friendly message to display for user requests. The display should resemble the following: "Star Wars is available: 11 on the shelf".

More specifically, create a function named MOVIE_STOCK_SF that takes in a movie id as a parameter. It should retrieve from the MM_MOVIE table the movie title and quantity information, build the output string and return it.

Again, make sure that your function behaves properly when given a non-existing movie id. Also, make sure that if a movie exists, but there are no copies available, you display a message like: "Star Wars is currently not available" rather than "Star Wars is available: 0 on the shelf".

Run tests with function calls that cover all possible scenarios including existing and non-existing movie ids, and available and unavailable movies. You probably will need to change some data in your tables to create a particular scenario.

© BrainMass Inc. brainmass.com October 16, 2018, 11:17 pm ad1c9bdddf
https://brainmass.com/business/accounting/movie-rental-database-stored-procedure-and-stored-functions-253196

Attachments

Solution Summary

This solution deals with Movie rental database. It has a two stored procedures and a stored function.

$2.19
Similar Posting

PL/SQL package

Combine the work you've completed so far for the More Movie Rentals database into one program. Create a simple PL/SQL package in order to go through a menu process. In this PL/SQL package, all users would enter in a movie by executing a procedure. Be sure the validation works correctly. Include the proper exception handling and have the PL/SQL code throw any exceptions that are deemed necessary. Be sure to use a good naming scheme for the procedures and functions within this package.

attached is all the work completed for movie rentals

View Full Posting Details