Name (please print)____________________________ Student Number_________________________

Signature______________________________________disc-nt userid _________________

1. (70 points) A user login table named login contains values for user logins (i.e., user_id and user_pwd).

user_seq_id bigint IDENTITY (10,10),
user_id varchar(30),
user_pwd varchar(30),
user_pwd_date char(8), (NOTE: format is yyyymmdd)
user_first_name varchar(30),
user_last_name varchar(30),
user_pwd_expire_date char(8), (NOTE: format is yyyymmdd)
.
.
.
primary key user_seq_id

Write a maintentance program that performs the following actions:

On the first pass provide: (a) a 30-byte textbox for the user_id; (b) a submit button; and (c) a pair of radio buttons for the administrator to choose one of the following maintenance options:

1. Modify this user_id (first radio label)
   If the administrator chooses this option, on the second pass: (a) show the user_id that was sent in a 30-byte textbox for the administrator to modify (assume the submitted user_id always exists in the table); (b) carry the primary key (user_id_seq) as a hidden field; and (c) provide a submit button. With client side javascript or vbscript, check to be sure that the new value contains more than 9 characters -- if necessary display a message that tells the administrator that the condition has been violated. If this error does not occur, submit the form.

   On the third pass, modify the table value of the user_id with the new value and return the message "Modified userid OK".

2. Modify this user's password (second radio label)
   If the administrator chooses this option, on the second pass: (a) provide two 30-byte textboxes for the administrator to enter the new password (they will enter the new password twice); (b) a submit button; and (c) the primary key (user_id_seq) passed as a hidden variable (assume the user_id always exists in the table). With client side javascript or vbscript, check to be sure that the two values entered for the password are equal -- if necessary display a message that tells the administrator that the condition has been violated. If this error does not occur, submit the form.

   On the third pass, modify the user's password and the user_pwd_date field. Return the message "Modified the user's password OK".

2. (30 points) Create a report using the login table above that shows all users accounts that will expire within the next 30 days. (i.e., between Now and user_pwd_expire_date). Show only the last and first names. Sort the report by last name, then first name. Count the number of occurences.