[SOLVED] SQL database Overview of the Assignment:

Overview of the Assignment:
In this exercise we will continue our review of advanced programming components, specifically looking at stored procedures, triggers, functions and codereuse.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 realworld environment of walking into a working system and adding additional functionality. Code reuse 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
Continuing with the scenario from the Advanced Programming assignments, suppose that the small firm for which you designed the Netflixlike DVD rental database has established an Internet presence and their sales are growing rapidly.You have been asked to make additional programming changes to the database and add new functionality. Your assignment is to implement the following additional requirements.Note that you may want to review your solution to Advanced Programming part 1 and part 2 and reuse the components which you developed there.

Write a function that returns the DVD ID of the next in stock DVD in the customers 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 customers 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. Additionally, the function should perform error handling to check that the customers account balance is not negative.
Write a function that returns the number of additional DVDs that a customer may receive before they reach the limits of their contract. 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.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.
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.
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 customers request list rentalqueue 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 DVDssince more then one DVD could be returnedthe 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.Note that you can implement this as part of this stored procedureor as a separate trigger which is used by this stored procedure.

Extra Credit Questions

Extra credit. Write a stored procedure that checks that all of the data in the database satisfies all general database integrity constraints. This is an example of a stored procedure that may be run daily to verify that there have been no failures or compromise of the general database integrity constraints enforced by triggers and stored procedures. We do not need to check integrity constraints enforced by primary keys, foreign keys, check constraints, and not null constraints.An example might be that the instock quantity of DVDs is accurate based on the rental history.There might be other interesting and relevant scenarios.
Extra credit. 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.