INLS 723 Database Systems II, Fall 2006
Individual Database Project
IntroductionRecently, there has been growing interest in personal health records (PHRs) and management of healthcare data. In this assignment, you will design and implement a database to manage health records for a doctor's office. The goals of this assignment are to give you experience designing a complex schema based on real-world situations and then to implement it in Oracle. This is an individual assignment, meaning that you will work on your own to complete the assignment.
This assignment is intended to make you think about how to design for complex situations. There are many design choices to make in this assignment. Carefully think about your design and justify the choices you make. It is suggested (but not required) that you submit/discuss your ER diagram to the instructor prior to implementation.
This assignment was inspired by and is loosely based on Exercise 2.7 in Ramakrishnan and Gehrke, 3rd edition. You may find it helpful to look at that exercise.
Mini-World DescriptionDoctors work in a practice with other several other doctors. A practice has a name, address, and a unique tax identification code. Each doctor has a name, specialty, and a unique prescription ID code. A doctor only works in one practice.
There are other employees in a practice in addition to the doctors. There may be nurses, assistants, and office personnel. All employees have a unique employee ID number, name, address, age, and start date with the practice. Doctors, nurses, and assistants may have professional post-nominal initials that are recorded (i.e. MD, APRN, LVN, LPN, RN, PA, RPA, etc.). Doctors, nurses, and assistants may have multiple post nominal initials.
Patients are identified by a unique patient ID number, name, address, and age.
Doctors see patients at visits. Visits are on a particular date with a particular doctor.
Doctors diagnose conditions for patients during visits. A doctor could make one or more diagnosis for a patient. A patient could receive diagnoses from several doctors in the practice. However, only one doctor will make diagnoses for a patient during a particular visit. Each condition has an associated medical name.
Doctors recommend treatments for conditions. The treatments are specific to particular patient-condition combinations. In other words, treatments are not made based solely on conditions, but are made for a particular patient with a particular condition. Treatments consist of written instructions (such as "Bed rest for 3 days. No spicy foods for 1 week.") and possibly prescriptions for medication.
Prescriptions are part of a treatment. Prescriptions include the drug name, dosage, instructions, and warnings. In addition, there is an option for the doctor to allow the prescription to be filled using a generic counterpart instead of the name brand drug.
What to turn in and howYou will "turn-in" your assignment at a demo session scheduled with the instructor. All demos will be held in the SILS computer lab. Email the instructor to schedule a 20 minute demo time for the afternoon (1:00-6:00pm) of Monday, October 9 or Tuesday, October 10. If you have a schedule conflict with those times, contact the instructor to work out a different time.
At the demo, you should have: