Purchase Solution

Movie Rental Database: Stored Procedure and Stored Functions

Not what you're looking for?

Ask Custom Question

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.

Attachments
Purchase this Solution
Solution Summary

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

Purchase this Solution
Free BrainMass Quizzes
Transformational Leadership

This quiz covers the topic of transformational leadership. Specifically, this quiz covers the theories proposed by James MacGregor Burns and Bernard Bass. Students familiar with transformational leadership should easily be able to answer the questions detailed below.

Six Sigma for Process Improvement

A high level understanding of Six Sigma and what it is all about. This just gives you a glimpse of Six Sigma which entails more in-depth knowledge of processes and techniques.

MS Word 2010-Tricky Features

These questions are based on features of the previous word versions that were easy to figure out, but now seem more hidden to me.

Employee Orientation

Test your knowledge of employee orientation with this fun and informative quiz. This quiz is meant for beginner and advanced students as well as professionals already working in the HR field.

Situational Leadership

This quiz will help you better understand Situational Leadership and its theories.

View More Free Quizzes
Related BrainMass Solutions

  • Stored Procedures in SQL Server

    This address information is likely stored in a database and likely not in a format where it can be directly placed on the letter as it appears in the database.

  • Creating a Database Design Using Common Sense

    Charles Brinston wants to open a movie rental business. He needs you to help with database design activities.

  • Subquery and Join

    130877 SQL 2005 stored procedures, sub query and join general question. 1. Stored procedures in SQL 2005 can be written in a variety of languages. Why is this important in the development of database systems?

  • Structured Query Language (SQL) in Practice

    Discuss stored procedures. A stored procedure is a program that is stored within the database and is compiled when used. They can receive input parameters and they can return results.

  • Triggers & Operational Data

    A common task is designed, coded, and tested in the stored procedure. The stored procedure issues a SELECT statement that returns results based on activity and then issues a statement based on the parameters of the activity.

  • Stored Procedures.

    - Provide an example of a stored procedure that a database designer might write to demonstrate why this capability is important. Hi there, Please find enclosed the solution document. Best, Word document containing the solution is attached.

  • Database Management Software

    In doing so, management would build a database with the relevant information, which could include sales information, customer data, employee data, trend data, and any other information that needs to be stored and subsequently retrieved from a database

  • Hierarchy of Data

    Database: A database is the collection of all these files a single and relational form so that we can gather the information about the movie by using the data files.

  • RENT-A-MOVIE DATABASE PROJECT

    120254 DataBase design Using VB.NET and Ms Access RENT-A-MOVIE DATABASE PROJECT I want to design a relational database to store information about my store (video rental store) customers and videotapes.

View More Related Solutions