Department of Electrical and Computer Engineering
University of Waterloo 

The project involves design and implementation of a Management Information System (MIS) using Java and SQL database technologies. There is significant leeway in selecting other technologies. Although, our computing environment provides basic Java development environment, YARD-SQL database engine, and Java based GUI tools, students are permitted to use other suitable tools for building their project.

• require that all records of every visit, length of visit, every procedure, etc. be available.
• editing of existing records are allowable, but only to correct typos (time, date, but not name, etc.).
• adding information to an existing record should create a new record which contains this additional information, rather than modifying the original.
• financial officers should be able see a summary of a particular doctor, but should not be able to look up patient's names from id numbers.
• doctors should only be able to access visitation records of their own patients.
• doctors can grant/revoke permission to another doctor to look at one of their patient's visitation records.
• staff (with permission granted) should be able to access the visitation records for the doctor(s) they work for only.
• patients should only be able to look at their own records.
• patient information records should contain at least the name, address, phone number, Health Card (assume all patients from Ontario), social insurance number, number of visits, default doctor, indication of current health (e.g. healthy, diagnosis in progress, prescription currently being taken, receiving treatment, scheduled for surgery, transferred to another hospital...) - ONE per patient.
• patient visitation records record the details about a particular visit (diagnosis, prescriptions, scheduling of treatment, etc) - probably one per visit.

• ability to monitor any doctor to determine how many patients he/she saw in a given time period, how many times a given patient was seen, what the diagnosis/result was, any drugs prescribed, etc.

• need the capability to look up patient names from "list of current patients" or all patients. Searchable by several criteria (name, patient #, last visit date, ...).
• past records of a patient available when looking at current record (i.e. no need to search to find past records of patient currently onscreen).
• find past records by date, patient name, diagnosis, keyword in freeform comments, prescriptions given, surgery performed, etc.
• enter a patient visitation record, including prescription(s), diagnosis, etc.
• want to record "freeform" comments (not visible to patients).

• enter appointments for a patient for a given doctor, check for scheduling conflicts with that doctor (manually or automatically).
• delete appointments or reschedule them.
• enter new patients.
• update patient information.
• assign patients to a doctor.
• review patient records for a doctor that is served by this staff person.

• view my past appointments, prescriptions, diagnoses, etc.
• the records should be confidential (need password of some kind).
• update my address, phone number, etc.

You may use the resources available on sunee or any computing resource you have available (home PC, etc.). Most of the implementation can be developed and debugged on a PC running Windows95 with the JDK Java.(1.1.3 or later) The JDBC interface will most likely require a UNIX or LINUX platform to support YARD-SQL. Periodically consult the course web page (co-located with the instructors home page: http://etude.uwaterloo.ca/~Asingh) for links to additional project information concerning JDBC, YARD-SQL and Java.

Java-JDBC Project first deliverable: on or before February 16, 2004 (5 pm).

Common Demo Date: on or before March 22, 2004 (5 pm).

6. Details of Deliverables

A software system without proper documentation is not very useful. Since the project description basically provides only a software requirements overview, you will need to provide the necessary specifications and design documents. The project require two deliverables as discussed below. These deliverables will be followed by a demo of your system.

This is to be a short 5-6 page document providing the following information for your relational database design:

1. What are the Entity-Relationship (E-R) diagrams for the application? What was the initial design of the database schemas (based on the E-R diagrams)? What constraints (i.e. security, flexibility, ease of use, etc.) were considered? What were the superkeys, functional dependencies and perhaps multivalued dependencies identified? What decomposition techniques were employed (BCNF, 3NF, etc.). It would be difficult to arrive at some good schema design without being aware of these points.
2. What is the final database design - what are the schema definitions and tables in your design. For this part to minimize your work you can provide the schema definitions using the SQL create table clause. Here, you identify all attributes, the domain for each attribute, the primary key, any foreign keys and any constraints on the values for any of the attributes. Justify any foreign keys.
3. A brief overview of the types of SQL queries that you are prepared to handle. Obviously you do not want to provide the user with complete flexibility to specify any type of query.
4. What development environment will you be using to implement the project? We supply only the Sun JDK + YARD-SQL. If you plan to use some other environment (e.g. Microsoft J++, ...) or some other SQL server, then how are you planning to demo your final project? Short paragraph.

You are of course not bound to this design for the duration of the project. Depending on your implementation, you may find that you need minor schema redefinition as you progress. This is fine as long as you document any deviations from this document.

You do not need to provide hard copies of your Java source files. All source files, Make files and any required executables will be copied at the time of the demo. Your written documentation must be submitted with 2 days of the completed demo and should include the following:

1. Outline (briefly) any changes to your first deliverable document - if any.
2. Briefly describe the GUI for your system using a user-interface flow chart.
3. Briefly describe your system's software qualities with respect to portability and concurrency.
4. How did you test your system - any tools used, test drivers, etc.? Short one paragraph.
5. Highlight any part of your overall project that you deem to be significant and perhaps novel.

The above requirements should only serve as a guide as to what is required. You do not need to present them in the order stated - as long as your document contains equivalent information. The first three points would typically comprise the majority of the document. We do not require a long document. Much of the information can be obtained directly from your source files (i.e. class definitions etc.). It is difficult to provide a page count as a goal, but the above information typically should not exceed 15-20 pages - hopefully less. Ideally, it should be written in an incremental fashion as you develop the project to avoid attempting to produce the document in 2 days.

System Demo