CSC 459- Homework #2 (Due February 20, 2006)

We are modeling a medical records database for a clinic. The following story describes the entities and their relationships in our clinic:

• For each patient, there is an ID, name, age. We also need to maintain the patient's medical history before they joined the clinic with information on their previous illnesses, as well as, their vaccination record.
• For each physician, there is an ID, name, and specialty.
• We also need to keep track of each visit: Who the patient was, who the attending doctor was, and the date of the visit.
• For each visit, we also need to know:
  1. the medications prescribed and their dosage,
  2. tests ordered and the results of those tests,
  3. The doctor's notes about the visit.
• A Patient may have one Primary Physician. The Primary Physician must be one of the doctors that the patient visits at the clinic.

Draw the ERD for the database model that you come up with for this clinic showing:

• Entities and their Attributes, Primary Keys and Foreign keys and entity types (Independent, Dependent) must be clear.
• Category, as well as, Connection relationships.
• Put big-dots where they belong in the connection relationships as well as the verb that describes the relationship.
• Resolve all m-n relationships into 1-m relationships. Your ERD should be void of any m-n relationships.
• If you can identify category relationships, depict them correctly so it is clear whether they are Total or not.

What Referential Integrity Constraints exist in this database? Write your constraints in the context of adding, deleting, and updating instances for each entity.

I used Together6.0 to build my class examples; ERDsample2.pdf is the example with Connection relationships and ERDsample.pdf was suppose to be the example for the student categorical relationship; but I couldn't find an icon to depict categories. So, it probably will be updated later.