Assignment: Enterprise Database Management Systems
Database Constraints, Triggers, Views and Stored Procedures.
1. Add to your CREATE TABLE statements from Phase 3 foreign keys and other constraints where necessary.
2. Write five data modification commands (INSERT, DELETE and UPDATE) on your database application. These commands should be ''interesting'', in the sense that they involve some complex feature, such as inserting the result of a query, updating several tuples at once, or deleting a set of tuples that is more than one but less than all the tuples in a relation. Include in your submission the results displayed by the corresponding EXPLAIN statements to show your modification commands running in a convincing fashion.
3. Create two views on top of your database schema. Show your CREATE VIEW statements and the response of the system. Also, show a query involving each view and the system response. Finally, show what happens when you try to update your view, say by inserting a new tuple into it. Are any of your views updateable? Why or why not? (Essentially, a view is updateable if it is a selection on one base table.)
4. Write two stored functions or procedures. Each stored procedure/function should involve more than one SQL statement. Each should use one or more parameters in a significant way. Include listings of your code in your submission. Also, include corresponding EXPLAIN statements showing these stored procedures or functions called at least once each. Similarly, include the results of queries that demonstrate that the functions have had their intended effect.
5. Write two SQL Triggers or SQL Assertions. It would be ''interesting'' if at least one trigger's action calls a stored procedure or function and at least one is an ''instead of'' trigger.
Include your trigger declarations (CREATE TRIGGER statements) along with your submission. Also, show, for each trigger, the effect of two database modifications. One modification should trigger the trigger (making the trigger's condition true), and the other not (making the trigger's condition false). Include in your submission queries that demonstrate that the trigger has an effect in the first case and not in the second.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.