Advanced Programming Part 3 Questions
Continuing with the scenario from the Advanced Programming assignments, suppose that the small firm for which you designed the Netflix®-like 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 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. Additionally, the function should perform error handling to check that the customer’s 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 customer’s 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 DVDs – since more then 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. Note that you can implement this as part of this stored procedure – or 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 in-stock 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.