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:
- 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.
- 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.
- 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.