Explore BrainMass
Share

Movie Rental Database: Oracle Part-2

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

Submit all of your codes, Oracle responses, and listing of your testing session in a single text or Microsoft Word document.

please see attached file

Now that business is becoming strong and the movie stock is growing for More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise the value should be NULL. Add the needed column and create an anonymous block that contains a CURSOR FOR loop to accomplish the task. The company will run this program monthly to update the STK_FLAG column before the inventory evaluations.

Make sure that you display the table structure of MM_MOVIE using DESC, and SELECT all data from this table before and after you do this exercise to show the effect of your block.

Submit all code and Oracle responses. Before submitting, edit your document to remove erroneous attempts.

Part 2

Here is a block that retrieves the movie title and rental count based on a movie id provided via a host variable.

SET SERVEROUTPUT ON

VARIABLE g_movie_id NUMBER
BEGIN
:g_movie_id := 4;
END;
/

DECLARE
v_count NUMBER;
v_title mm_movie.movie_title%TYPE;
BEGIN
SELECT m.movie_title, COUNT(r.rental_id)
INTO v_title, v_count
FROM mm_movie m, mm_rental r
WHERE m.movie_id = r.movie_id
AND m.movie_id = :g_movie_id
GROUP BY m.movie_title;

DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
END;
/

Add exception handlers for errors that you can and cannot anticipate. Test the block by running it with various values of the host variable.

© BrainMass Inc. brainmass.com October 16, 2018, 11:14 pm ad1c9bdddf
https://brainmass.com/computer-science/sql/movie-rental-database-oracle-part-2-251217

Attachments

Solution Summary

This is the second part of the Movie Rental Database set of questions answered using Oracle. The set of queries primarily deals with triggers and exception handling.

$2.19
Similar Posting

Movie Rental Database: Stored Procedure and Function

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.

View Full Posting Details