Spring 2014: 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. Two points each. Answer each with respect to databases in general and MySQL when applicable.
   1. What is a stored procedure?
   2. What are the advantages of stored procedures?
   3. What are the costs of stored procedures?
   4. What is a user defined function?
   5. How is a user defined function different from a stored procedure?
   6. What is a vie?
   7. What why would a database program employ views?
   8. What is a trigger?
   9. Why would a trigger be employed in a database?
   10. What is an index?
   11. What is a cascading delete?
   12. Under what circumstances would a cascading delete be employed?
   13. What is a cursor?
   14. What is the impact of the SQL command START TRANSACTION
2. A pet store has a database where an pet owner (name, address) may be associated with zero or more pets (ID number, name, species). Each pet has only one owner.
   1. [2 points] Represent this relation with an E-R diagram.
   2. [4 points] Describe the tables you would produce to implement this relationship. Include how you would enforce the cardinality requirements.
   3. [4 points] What should happen when
      1. A new pet is received in the store (for sale)
      2. A pet is sold.
      3. An owner is removed from the database.
3. In the same pet store, a product (UPC, name) may be supplied by multiple companies (name, address). Some companies, however, may not supply any products, they simply provide services.
   1. [2 points] Represent this relation with an E-R diagram.
   2. [4 points] Describe the tables you would produce to implement this relationship. Include how you would enforce the cardinality requirements.
4. A take home kit at this pet store may be represented as follows:
   1. 
   2. [2 points] Describe this relationship in English.
   3. [4 points] Describe the problems this relationship represents to a database implementer.