METCS779 Project 3 Solution

In this exercise we will continue our review of advanced programming components, specifically looking at stored procedures, triggers, functions and code-re-use. This exercise is based on the Advanced Programming Netflix database.

Preparing for the Assignment
Before the exercises in this assignment can be completed, you should complete Advanced SQL Programming Part 2.

It is strongly recommended that you review the Netflix schema and the questions before beginning the assignment. This includes inspecting the ERD schema provided with the Advanced Programming Assignments. The assignment simulates a real-world environment of walking into a working system and adding additional functionality. Code re-use as part of your solution is strongly encouraged, if you are able to solve the question in less steps feel free to do so.

Please use the submission template to submit your assignment.



Advanced Programming Part 3 Questions

1. Write a function that returns the DVD ID of the next in stock DVD in the customer’s movie list (rental queue).
• The function should take as an IN parameter the customer ID and should return the DVD ID as the function value.
• The function should return the first title (the DVD ID) in the customer’s movie list that is in stock. If the movie list is empty or none of the titles are in stock, then the function should return NULL.
• The function should take the customer ID as an IN parameter and should return the number of DVDs through its integer function value which the customer can rent based on their current rental activity and their plan.
• There are two limits for each plan (1) how many can be rented at a time, and (2) how many can be rented per month
3. Write a stored procedure that implements the processing when a DVD is returned in the mail from a customer and the next DVD is sent out. This processing should include recording that the DVD has been returned and should also determine the number of additional DVDs that should be mailed to the customer. Use the functions and stored procedures which you have already created to complete this transaction. At minimum your stored procedure must account for the following tasks.
• Customer returns a DVD or notes the DVD is lost in which case they are charged against their account.
• Initiate function from question 2 to return the number of additional dvds which can be rented.
• Initiate the function from question 1 to get a movie from the customer’s request list (rental queue) which is in stock.
• Initiate stored procedure from Advanced Programming part 2 which removes this DVD from the queue.
• Perform the rental of the above DVD
i. [Extra credit 1 point] since more than one DVD could be returned – the stored procedure needs to be able to handle dynamically the rental process of multiple DVDs returned with function #2
• Update all the DVD quantities accordingly both on rental and return.



Extra Credit Questions

1. [Extra credit for 1 point] Implement a trigger that prevents a customer from being shipped a DVD if they have reached their monthly limit for DVD rentals as per their membership contract using the function in from question 2 above.

2. [Extra credit for 1 point] Define and implement additional functionality using one or more stored procedures, functions, or triggers. Make sure to pick a relevant business scenario that has advanced level of complexity. As an example:
a. Write a stored procedure that checks that all of the data in the database satisfies all general database integrity constraints. An example might be that the in-stock quantity of DVDs is accurate based on the rental table. There might be other interesting and relevant scenarios.