MIS 4372 -- Midterm -- Fall 2011 -- Parks

Name ________________________________________________________________ Last 4 digits of PSID_______________

Suppose a student organization wished to "record attendance" at their events by the membership. A single workstation with a wireless internet connection will be used at all of the organization's events for this purpose. Assume there is a membership database table named membership that already has data loaded for each member. The structure of the membership table is shown below. Secondly, there is an events table named events that stores the event description and date as shown in the second column below. Third, there is an attendence table named event_attendance that is used to record a member's attendance at a specific event. The table structures for these tables are shown below:

membership	events	event_attendance
member_id bigint identity (1,1), psid bigint, [this is a 7 digit Peoplesoft ID] first_name varchar(30), last_name varchar(30), email varchar(60), joined_year int, joined_month int, joined_day int, address1 varchar(40), address2 varchar(40), event_count int, city varchar(25), state varchar(2), zip varchar(5), phone varchar(10) PRIMARY KEY (member_id)	event_id bigint identity (1,1), event_description varchar(30), event_year int, event_month int, event_day int PRIMARY KEY (event_id)	attendance_id bigint identity (1,1), event_id int, member_psid int, PRIMARY KEY (attendance_id)

Create a two pass *.asp program named take_attendance.asp to record a member's attendance at an event (i.e., insert a row into the event_attendance table) and update their event_count in the membership table. The program should:

(50 points) In Pass 1, create a form has:
1. A textbox for the member to enter their psid number (i.e., their psid of 7 digits)
2. A select box with the names of ALL event descriptions currently in the events table. The event_description values are to be shown as the options of the select box AND the value clause of the option tag should contain the event_id (Note: this will require rolling through the events table to produce the option tags). The events should be ordered so that they are in descending date order (i.e., descending event_year, descending event_month, and descending event_day order). An example of an option tag might be something like this:
  <option value='17'>Habitat for Humanity for Fall 2011
  where "17" would be the event_id and "Habitat for Humanity Fall 2011" would be the event_description from a row in the events table. Make the first (i.e., the most recent date) option tag "selected" (i.e., the first option tag has the "selected" attribute).
3. A hidden value for the pass token
4. A submit button
(50 points) In Pass 2,
1. First, be sure not to allow a member to enter their psid for a specific event_id more than once (i.e., use a SELECT to check the event_attendance table before the INSERT is performed to be sure that the values do not already exist in the events table. If the data already exists, response to the user with the message: "You have already recorded your attendance at this event.". If this occurs, do not perform the next three steps.
2. Check to be sure the member's psid exists in the membership table (i.e., another Select), If it fails, respond "Member psid not found" else response "Found psid OK". If the psid is not found do not perform the next two steps.
3. Insert the data from Pass 1 (the event_id and the psid) into a new row in the event_attendance table.
  Respond with "Insert Failed" if the task fails, else respond "Recorded your attendance data -- OK".
4. Update the membership table by adding one to the event_count for the member.
  Respond with "Update Failed" if the task fails, else respond "Counted your the attendance -- OK".
Show the code for: (a) Pass 1; (b) Pass2; and (c) the main routine. Perform NO data edits in Pass 1.