Spring 2015: Database Final

• The weight for each question is given with the question.
• Use as much paper as you wish, but make your answers are legible.
• Please answer each question thoughtfully and carefully.

1. Consider the following trigger for the SKU_DATA table.

   
   SKU_DATA(SKU, SKU_Description, Department, Buyer)
   
   CREATE TRIGGER Checker BEFORE INSERT
   	ON SKU_DATA FOR EACH ROW
   	BEGIN
   	   IF NEW.SKU < 100000 THEN
   	      SET NEW.SKU_Description = "BAD SKU";
   	   END IF;
   	END;
   	

   1. [1 point] Give an SQL statement would cause this trigger to be called.
   2. [2 points] When will this trigger be called in the execution of the SQL statement?
   3. [2 points] On what data will this trigger operate?
   4. [4 points] Give an example of data which will cause this trigger to perform an action? What will the results of that action be?
2. Consider the following table definition

   EMPLOYEE (EmployeeID, ...)
   
   CREATE TABLE PHONE (
       EmployeeID	INTEGER,
       PhoneNumber VARCHAR(20),
       CONSTRAINT FOREIGN KEY (EmployeeID)
       	REFERENCES EMPLOYEE (EmployeeID )
   	    ON DELETE RESTRICT
   	    ON UPDATE CASCADE
   );
      

   1. [2 points] What happens if a change is made to PHONE.EmployeeID for an existing entry?
   2. [2 points] What happens if EMPLOYEE.EmployeeID is changed for an existing entry?
   3. [2 points] What happens if an existing row in EMPLOYEE is removed?
3. Views
   1. [2 points] What is a view in a database?
   2. [4 points] Give and explain two reasons a view might be employed.
4. [4 points] What is a cursor and how is it used in a database?

   Over Please

    

    

    

5. Consider the following diagram
   
   1. [2 points] Describe the relationship between these two tables.
   2. [3 points] Describe any conditions required for the creation and maintenance, including removal, of data in these tables.
   3. [6 points] Describe the tool(s) and related logic to enforce the conditions described in B.
6. Transactions
   • [2 points] What is an atomic transaction?
   • [2 points] When/why are atomic transactions necessary?
   • [2 points] How is a series of SQL statements turned into an atomic transaction?
   • [2 points] What is the result of the ROLLBACK SQL statement?
7. [6 points] Describe the trade off between implementing logic as a SQL stored procedure and in a programming language using the DMBS supplied API.