ITC 4150, Database Design and Implementation
Unit IV - Westlake Research Hospital
Sample Answers to Scenario Questions
1. Review all the requirements and business rules.
2. Define your entities and attributes and the relations that exist between them.
3. Create a logical model using crows feet notation in Visio or hand draw it on graph paper,
if you prefer.
4. Add all the entities and their attributes. You dont need to worry about data types for
now.
5. Identify the key fields for each entity and the foreign keys.
6. Analyze the diagram. Identify which role (i.e., domain, linking, lookup, weak) each entity
plays in your database.
7. Review your diagram for the following:
a. Are all the major components of the Westwood Hospitals business model
represented by domain entities?
b. Does each entity contain the appropriate attributes to fully describe it and meet
the business rules you have gathered so far?
c. Does every entity have an appropriate primary key defined?
d. Are all many-to-many relationships resolved into one-to-many relationships by
linking tables?
e. Are the relationships valid (no cross-relationships)? Is the appropriate entity
defined as the one side of a one-to-many relationship? Do the tables have
appropriate foreign keys? Also check for other such issues.
f. Are lookup tables used for attributes that have a set list of values?
The diagram below meets the requirement of all the above instructions for the assignment in
this unit and represents one possible ERD. The drug group could be handled by adding a linking
entity between Drug Group and Patient. The Medical History and the Patient entities could be
joined into one entity in some diagrams. In the Medical History entity, Medicines, Allergies and
Symptoms are multivalued.
Copyright 2012 Pearson Education, Inc. publishing as Prentice Hall
Patient
PK
PatientKey
FK1
PatientLastName
PatientFirstName
PatientPhone
PatientEmail
PatientAddress
PatientCity
PatientState
PatientZip
PatientBirthDate
DoctorKey
PatientMedicalHistory
PK,FK1
PK
PatientKey
AggreementNumber
FK2
medicines
lungDisorder
HighBloodPressure
HeartTrouble
NervousDisorder
DigestiveTrack
KidneyDisorder
Diabetes
Hepititatis
Malaria
BloodDisorder
PhysicalDeformity
LifeThreateningCondition
Contagous
DepressionLevel
DepressionBeginDate
Symptoms
DepressionDescription
FamilyHistory
Notes
DrugGroupKey
Doctor
PK
DoctorKey
DoctorLastName
DoctorFirstName
DoctorPhone
DrugGroup
PK
DrugGroupKey
Appointment
PK
AppointmentKey
FK1
FK2
AppointmentDate
DoctorKey
PatientKey
AppointmentDetails
PK
AppointmentDetailKey
FK1
AppointmentKey
AppointmentDetailBloodPressure
AppointmentDetailWeight
AppointmentDetailPulse
AppointmentDetailDepressionStatus
AppointmentDetailSymptoms
AppointmentDetailsSideEffects
AppointmentDetailDrNotes
AppointmentDetailRecommendation
AppointmentDetailRecommendationNotes
General Guidelines for this Assignment
The diagram above represents only a suggested diagram. You may add entities and attributes
that arise in your analysis. The suggested diagrams assign a key field to each entity. You may
be much more specific about the nature and content of the keys and may develop composite
keys for some entities. As long as the key is guaranteed unique then it will be accepted.
Your naming conventions may also vary from the ones used in the diagram. That is fine as long
as there is some evidence that you have used a naming convention of some sort.
In addition to the guidelines you use to review your diagram, the following rubric should also
guide you on the level of detail and the content expected. Contact your instructor if you have
questions.
Suggested Rubric:
Poor
Entities and relationships
mistakenly and poorly defined.
Diagram confused and
incomplete
OK
Diagram contains all the
domain entities and most or
all attributes. Relationships
mostly correct, proceeding
from primary key to foreign
key, most many-to-many
relationships resolved
Good
Each question listed in the
review criteria (part 7 of the
question) can be answered
with a clear yes
