Spring 2013: 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. [6 points] What is the difference between a trigger, a stored procedure and a function? Where is each used?
2. [2 points] Describe the relationship between an application program written in C/C++ and SQL.
3. [2 points] What is a transaction and how is a transaction related to SQL statements?
4. [3 points] Describe the impact of:
   1. START TRANSACTION
   2. COMMIT
   3. ROLLBACK

   You are designing a database for a highlands festival. The festival consists of participants who have assigned roles: Dancers, Pipe Bands, Heavy Athletics, Officials, Vendors, Guests, Staff. All participants must have a role, but some may participate in more than one activity.

5. Participants and Roles:
   1. [2 points] Present an E-R diagram representing the relationship between participants and their roles at the festival.
   2. [3 points] Describe the tables you would use to implement this relationship.
   3. [2 points] Provide a schema for this portion of the database.

   There are a number of clans represented at the festival, and there is a desire to track the clan membership of the participants. A participant may be a member of one clan, but clan membership is not required.

6. Participants and Clans:
   1. [2 points] Present an E-R diagram representing the relationship between participants and their clans at the festival.
   2. [3 points] Describe the tables you would use to implement this relationship.
   3. [2 points] Provide a schema for this portion of the database.
   4. [5 points] Describe how you would enforce the minimal requirements of your design when implementing a database.

   Each heavy athlete must be signed up for at least one event. Events include the Caber toss, Stone put, Scottish hammer throw, Weight throw, Weight over the bar, Sheaf toss and Maide Leisg. You have added an event, Intent To Play, which you will use to enforce minimal cardinality requirements (O-M). Athletes who are not scheduled for any event at the close of registration are deregistered.

7. Heavy Athletics
   1. [2 points] Draw an ER diagram for this portion of the database.
   2. [3 points] Describe the tables you would use to implement this relationship.
   3. [2 points] Provide a schema for this portion of the database.
   4. [6 points] Describe all situations where Intent To Play might be employed. (There should be 6 of them, but some may be trivial)
   5. [6 points] For each situation in part D, describe how you would enforce this in a database implementation.