'
 
MIS 4372 -- Midterm -- Fall 2014 -- Parks

FULL NAME___________________________________________________

Last 4 PSID _________________

Assume, there is a philanthropic organization called "Odd Fellows" whose member contribute their time to charities and other worthy causes. Holding the elective office of "Most Odd", you are in charge of keeping track of the year's events, participants, and the members of each event's crew.

There are three tables:

  1. a table named odd_events that holds the charitable events data;
  2. a table named oddf that hold the Odd Fellow membership data; and
  3. a table named crews which holds the members of the crew that participate in a specific event (in the events table).

The table structures are shown below.

Execute the program for Question #1 (This also links to the program for Question #2)
(Code for question #1 Code for question #2)

odd_events oddf crews
eid int IDENTITY (1,1),
evt_name varchar(25),
evt_yr int,
evt_mon int,
evt_day int
evt_start_time varchar(5),
evt_end_time varchar(5),
evt_loc varchar(50) (PRIMARY KEY (eid)) 		oid int IDENTITY (1,1),
odd_fn varchar(25),
odd_ln varchar(25),
odd_phone varchar(10),
odd_cell varchar(10),
(PRIMARY KEY (oid)) 		crew_evt_id int,
crew_oid_id int

  1. (50 points) Write the two passes for a INSERT program (named "f1472m1.asp") that allows the "Most Odd" to add an "Odd Fellow" to an event's crew. Specifically:
    1. the first pass provides a two select boxes (i.e, drop-down menus)
      1. one select (named "sel1") for "events" that allows the "Most Odd" to pick an event
        The option text should show the event name (i.e., evt_name from the events table)
        The value clause of the option should be the event id (i.e., the eid value from the odd_events table)
        All events should appear as options (i.e., the number of options should be equal to the numbers of rows in odd_events table)
      2. a second select (named "sel2") for "odd fellows" that allows the "Most Odd" to pick an odd Fellow in order to add him to the crew for the event
        The option text should show: the odd fellow's last name; comma; a space; and the odd fellow's first (i.e., odd_ln and odd_fn from the oddf table)
        the value clause for the option is the odd fellow's id (i.e., the oid value from the oddf table)
      3. a hidden variable with name="token" with a value of "2"
      4. a submit button with action="add_oddf.asp" and method="POST"
    2. the second pass receives the data from pass #1 and then:
      1. performs an existence test on the crews table to see if the the odd fellow is already in the crews table for the selected event (use the Request.Form("sel1") amd Request.Form("sel2") for the event number and the oddfellow number, respectively)
      2. if step #1 above shows that the odd fellow is already signed up for the crew for the event, write the message to the "Most Odd" that says: "Already on the Crew. Cannot Add him again."
      3. if step #1 shows that the odd fellow is NOT on the crew:
        1. INSERT the appropriate new row into the crews table
        2. Create a link for the user that points to the program in Question #2 below with a querystring:
              e=[value of Request.Form("sel1")]

  2. (50 points) Write an asp program named "f1472m2.asp" to display the crew for a specified event. Assume that the asp program receives a single query string pair like this:

        e=[integer value of Request.form("sel1") above]

    The report should show for the event:

    1. The event: event name, month, day, year, start time, end time and location
    2. In a table, with a row for each odd fellow on the crew show columns with: last name, first name, and both phone numbers (this is two columns). Show no headings.

Do NOT use parameters or transaction control. Do NOT write the "main" routine.