DISC 4372 Midterm Fall 2007 Parks

Name (please print)____________________________ last 4 digits of SSN_________________________

Signature______________________________________disc-nt userid _________________

1. (50 points) A major software company offers free versions of its software to students. The instructor is required to submit the last name of students and their student id numbers so that downloads can be authorized (institution code, institution name, course id and course name are also established by the company). The relevant portion of the table called student_authorize is shown below:

institution_code integer;
institution_name varchar(30);
course_id integer;
course_name varchar(30);
instructor_last_name varchar(30);
student_id integer;
student_last_name varchar(30);
date_of_download varchar(8);
   (format is yyyymmdd when the student performs the download, initially the field is empty)

Once a course has been approved, a table row is created for each student. When the student goes to the download site and downloads the software, the date_of_download field is filled in. Initially this date field contains an empty string.

Write a report sorted by course_name (ascending) that counts:

  1. the number of students who are authorized to download software in the course
    (i.e., the number of table rows for the specific course)
  2. The number of students who have actually performed the download
    (i.e., the number of non-empty date_of_download fields for the course).
The report should contain one row per course_name and have NO subtotals and NO grand totals. Show: the course_name and the two counts specified above on each report row. Write the one-pass asp program to display this report.

2. (50 points) Using the table above, write a query program for the table above that allows a user to examine the contents of a table row by specifying the student_id. The query should:

  1. Provide text box fields for:
    1. institution_code (required integer -- maxlength 12)
    2. course_id (required integer maxlength 12)
    3. student_id (required integer maxlength 12)
  2. On the client side, edit the user data to assure that only integers are entered in the three data fields. Display an error message and stop the submission if non-digits are found in any field.
  3. Return to the user all rows where that match the three input fields above
    (display the values of ALL the data fields shown in the table definition in part 1 above)

Write the two-pass asp program to perform this query operation.