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.
The purpose of this project is to introduce you to database design and writing of database applications. In most 'real' developments, not all resources are spent on the SQL query writing. The formal design of the database (based on user requirements) and the specification, design and implementation of the user visible interactions with the system also consume a major portion of the development effort. Hence this project attempts to provide exposure to all aspects of developing a major database application.
For this project, you will develop a portable and concurrent application in Java and use JDBC (a Java to RDBMS API) to connect to a relational database for data storage and query execution. The application will use the conceptual model of a hospital database environment which stores various patient records. You will be required to design the database schemes and the necessary graphical user interfaces (GUI). You can consider the following description to be a first cut requirement specification for your application.
Consider that the members of your group are the MIS administrators for a hospital, and have been given the opportunity of developing a prototype for the next generation information system for the hospital. After discussions with the various hospital groups (Legal, Finance, Doctors, Staff and Patients) that will be using this system, you have the following initial requirements.
3.1 Legal Department
The legal department has indicated the following requirements should be achievable:
3.2 Financial Department:
Note that you are not being asked to deal with things like scheduling of facilities (X-rays, operating rooms, therapy, etc). You are only being asked to design the patient record portion of the system.
You must keep track of certain other data to allow this system to work: which patients are assigned to a particular doctor, which doctors are allowed to view patient records, a list of legal prescriptions (a hierarchy would probably be useful, since there are many valid prescriptions), outcomes of diagnosis, which doctor a particular visit was with (since patients can switch doctors), etc.
As an example, consider a GUI for doctors. A doctor might have to enter his/her name and password to access the database. Once the password has been verified, then, depending on your design, the doctor could i) enter a patient's name and examine the records for that patient (provided access is permitted), ii) enter a range of dates and see all patients treated between those dates, iii) enter some other criteria and see portions of the patients records, and/or perhaps other operations. Facilities must exist for entering new patient data as well as updating current patient records, etc. The types and ranges of queries must obviously be limited since the goal is not to produce, for example, a general purpose QBE type of interface. The types of SQL queries (joins, selections, projections, etc.) must be to some extent limited and fixed depending on the GUI active at any given time.
The first stage of your project will be to determine what relational schemes will be necessary. This will involve a formal design of the schemes, identification of appropriate functional dependencies in each scheme, all foreign keys and a good BCNF decomposition of the schemes. As well, the concept of maintaining several views of the database (i.e. for doctors, staff, etc.) should be investigated. A diagram clearly showing the data members of the various tables and the relationships between them (primary keys, foreign keys, etc) will be your first deliverable.
The next stage would be to determine what the user interface to the database should be. For example, what GUIs are needed, what SQL queries must be supported within each GUI, etc. This portion will define the functionality of your Java application using JDBC to access the database and perform the requested queries/updates. Some initial data that could be used in the database will be provided as a starting point. Obviously, the GUIs need not support all possible queries that could be considered.
The final submission will include a demo of your system.
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).
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:
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:
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.
We will experiment with the system for some time, entering some values that don't exist for patient names, update which doctor is treating which patient, change a patient's address, and others. During the demo: 1) Your system should not crash. 2) You should be able to justify why we can't do something if we think we should be able to - based on the above requirements.