Name (please print)____________________________ last 4 digits of Student Number______________

Signature______________________________________disc-nt userid _________________

1. (50 points) An architectural firm maintains a database of architectural project data. A project table contains the descriptive data for each project. Assume the project table has:

project_id int not null,
project_name varchar(30) not null,
primary key (project_id)

Further, there is a table of documents called doc_table that contains the information related to where the documents for a project are stored. Specifically, doc_table has:

project_id int not null,
doc_num int IDENTITY (1,1),
doc_type char(1) not null,   ["D"=drawing, "R"=report, "C"=correspondence, "M"=memo]
doc_desc varchar(30) not null,
doc_file_name varchar(60),
doc_date varchar(30),
primary key (project_id,doc_num)

Write an insert program for adding new documents to a project. This will be a three step (pass) process:

1. On the first pass, provide a list of all available projects from the project table. Display each project_id and project_name on a line, ordered by ascending project_id. Allow the user to click on a project_id and pass the project_id and project_name to the next step via a quesrystring.
2. Return a page with the project_id and project_name displayed at the top AND stored as hidden variables. Provide fields for collecting: doc_type, doc_desc, doc_file_name. Submit the data via a querystring. DO NOT EDIT THE DATA.
3. Using the data received from step 2:
   1. check to make sure the doc_file_name has not been used before. If it already exists, return an error message to the user.
   2. Insert the data received from the second step into the doc_table. Use the now function to define the date. Report the outcome of the insert attempt to the user.

2. (50 points) Using the data in the doc_table above, write an asp program that shows the entire contents of the table ordered: first by doc_type in ascending order, then by project_id in ascending order, then by doc_num in ascending order. DO NOT INCLUDE project_name from the project table. The data should be in a 1 pixel bordered table with 6 columns: document type, project id, document number, document description, document filename, document date.