FINAL
CS320, Fall 2008


Please upload your files using CSNS. The files should include all the source code, documentation (if any), and an HTML file final.html which contains a link to your application on the CS3 server. Note that file uploading will be disabled automatically after 10:00PM, and late submission will not be accepted.

[Overview]

In this exam you are going to create a web application for a clinic that allows patients to make appointments online. The clinic has several doctors, all working from 9AM to 6PM (excluding lunch hour from noon to 1PM) on Monday through Friday. Each appointment takes half an hour, starting on the hour or half-hour, e.g 9:00-9:30, 9:30-10:00 and so on. Two appointments cannot be scheduled at the same time with the same doctor.

You may use servlet and/or JSP to implement this application. However, note that servlets cannot be used to generate HTML content, and no scripting elements are allowed in JSP pages. For simplicity, we will assume that all user input will be in the correct format, so you do not need to validate user input in your code.

[Database]

Your application should store information about doctors, patients, and appointments in a database. The database should consist of at least three tables:

ID is an integer that uniquely identifies a doctor, and SSN is the Social Security Number of a patient. For an appointment, only start time needs to be stored because all appointments are half an hour long.

At the end of the exam, you need to turn in a final.sql file which include the SQL statements to create all the tables in the database, and the statements to populate the tables so that each table has at least two records.

[Main Page]

The main page of the application asks for the patient's name and SSN, and lets the patient to choose to either make a new appointment or check existing ones. For example:

Name:
SSN:
Make a new appointment
List current appointments

[List Current Appointments]

If the patient chooses to see the current appointments, the application displays the appointments of the patient as follows:

Time Doctor Cause of Visit
2008-12-02 09:00 Dr. Amy Johnson Headache
2008-12-10 15:00 Dr. Ted Smith Physical exam

Back to Main Page

Note that the application should only display the patient's appointments in the future, i.e. appointments of other patients or appointments in the past should not be displayed. Also note that the appointments should be display in chronological order.

[Make New Appointment]

If the patient chooses to make a new appointment, the application asks the patient for the information about the appointment as follows:

Time:
Cause of visit:
Doctor:

Back to Main Page

The appointment time will be entered in the format of "yyyy-MM-dd HH:mm", e.g. 2008-12-02 15:00. The names of the doctors in the drop-down list should be retrieved from the database at request time, i.e. the doctor names should not be hard coded in HTML text. 

After the patient clicks the Make Appointment button, the application should check whether the appointment can be made. In particular, an appointment cannot be made under one of the following conditions:

If  the appointment can be made, the application should save the appointment in the database and display the patient's current appointments as described in the [List Current Appointments] section. Note that for a new patient, the patient's name and SSN should be saved in the database as well.

If the appointment cannot be made, the application displays an error message and asks the patient to change the appointment time. For example:

The time slot is not available. Please choose a different time.

Time:
Cause of visit:
Doctor:

Back to Main Page

Note that the application should pre-fill the form with the information the patient entered before, including the doctor selection.

[Grading Criteria]