MIS 4372 Midterm Fall 2009 Parks

Name (please print)__________________________________________ PSID______________________

Signature_______________________________________________disc-nt userid _________________

1. (70 points) A table named pc_inv contains values for describing the personal computer inventory for a firm. Some details of the table are shown below:

seq_id int IDENTITY (10,10),
serial_num varchar(30),
pc_manufacturer varchar(30),
pc_model char(8)
property_tag_number varchar(30),
purchase_date varchar(24),

... primary key serial_num

A second table named maint records the maintenance transactions for the computers in the pc_inv table. The table contains:

trans_id int IDENTITY (10,10),
serial_num varchar(30),
property_tag_number varchar(30),
service_action_date varchar(24),
service_action varchar(255),
service_status int [1=pending;2=in-progress;3=waiting on parts;4=completed]

No primary key is specified for the maint table.

Write a maintentance recording program that allows a technician to record a service action on a pc in the inventory. The *.asp program should take the following actions:

  1. On the first pass allow the user to identify the pc being serviced. Provide: (a) two 30-byte textboxes for the serial_num and the property_tag_number; and (b) a submit button. Write a note to the user that only one of the two fields must be supplied. Perform NO edits.
  2. On the second pass: (a) look up the pc in the pc_inv table. Use either the serial_number or property_tag_number -- whichever was provided by the user (if both were provided, use the serial_number for the lookup). If the pc is found the the pc_inv table: (a) display as text: serial_num, pc_manufacturer, pc_model, property_tag_number, and purchase_date; (b) provide a texbox for the service technician to record service_action (allow 255 characters); (c) four radio buttons that record the service_status (labelled "pending"; "in-progress"; "waiting on parts"; and "completed" that have values : 1, 2, 3 and 4 respectively); and (d) a submit button. Be sure to pass the serial_number and property_tag number as hidden variables. If the pc is not found in the pc_inv table, direct the user to click the "BACK" button and try again. Do not edit the user's data.
  3. On the third pass, insert the maintenance transaction data into the maint table using the values from the previous pass. Use the value of now as the data for service_action_date. Report "OK" if the transaction is inserted correctly or otherwise "Action Failed". Use no parameters or transaction control.
Include the "MAIN" and a state maintenance mechanism.

2. (30 points) Create a report using the maint table above that displays all maintenance transactions. Use all the data shown for the maint table above. Sort the data first by serial_number and then by service_status, both is ascending order. Count and report the number of transactions in the table.