Mountain View Community Hospital

1
Mountain ViewCommunity Hospital
Team 3

• Kevin FloryBrian HatchRichard Johnson

2
Mountain ViewCommunity Hospital

• Introductory Premise

The goal of this project is operation as an
intern in a three person team to produce a
high-level E-R diagram for hospital operations.
The diagram is to be created following interviews
with administrators and staff to identify primary
entities that exist within the hospital.
3
Mountain ViewCommunity Hospital

• Primary Entity Types

Staff interviews revealed the following entities
and related data within MVCH.
4
Mountain ViewCommunity Hospital

• Primary Entity Types

Staff interviews revealed the following entities
and related data within MVCH.
5
Mountain ViewCommunity Hospital

• Entity Relationships

• Hospital employees are assigned to work in one or
  more care centers.
• Each care center has one or more employees.
• The Hospital records the number of hours per week
  per employee in each care center.
• Each care center has only one employee acting as
  designated nurse in charge for that care center.
• Some patients are outpatients therefore some may
  not be assigned to a bed. Conversely, a bed may
  or may not be assigned to a patient.

6
Mountain ViewCommunity Hospital

• Entity Relationships

• A patient must be referred to the hospital by
  only one physician.
• A physician may refer multiple patients to the
  hospital.
• Physicians may perform multiple treatments for
  multiple patients. Conversely, physicians may
  perform no treatments for no patients.
• Patients may have treatments performed by any
  number of physicians.
• For each treatment performed on a patient by a
  physician the hospital records treatment date,
  treatment time, and any results.

7
Mountain ViewCommunity Hospital

• Entity Relationships

• Patients may consume any number of items.
• An item may be consumed by multiple patients.
• For each item consumed, the hospital records
  date, time quantity, and total cost as a function
  of unit cost and quantity.

8
Mountain ViewCommunity Hospital

• Project Question 1

Q Why would MVCH want to use entity-relationship
modeling to understand its data requirements?
What other ways might the hospital want to model
its information requirements?
A By using E-R modeling, MVCH will be able to
capture the business rules required to maintain
data integrity. By doing so, MVCH will be able
to understand more fully its data
requirements. In addition to E-R modeling, MVCH
might consider object-oriented modeling.
9
Mountain ViewCommunity Hospital

• Project Question 2

Q Do there appear to be any weak entities in
the description of the MVCH data requirements?
If so, what are they?
A There are no obvious weak entities in MVCHs
data requirements.
10
Mountain ViewCommunity Hospital

• Project Question 3

Q What is the significance in developing an E-R
diagram for MVCH to the third entry in the
preceding list, which states that some patients
are assigned to a bed, but outpatients are not
assigned to a bed?
A The relationship IS_ASSIGNED between PATIENT
and BED is an optional 0-1 relationship. Further
instruction in the next chapter will reveal how
to model subtypes of PATIENT (INPATIENT,OUTPATIENT
) and create mandatory relationships between
INPATIENT and BED.
H
11
Mountain ViewCommunity Hospital

• Project Question 4

Q Is Mountain Valley Community Hospital itself
an entity type in a data model to represent its
data requirements?
A No, MVCH is an instance of the entity type
HOSPITAL. Because there is only one instance,
there is no need to model the HOSPITAL entity
type as it is understood.
12
Mountain ViewCommunity Hospital

• Project Exercise 1

Study the project questions very closely. What
other questions would you like to ask in order to
better understand the data requirements at
Mountain View Community Hospital?

• A) Should we model pharmaceutical items
  separately from ITEM, since such items are
  prescribed by a physician for a patient?
• B) Is there a need to maintain a historical
  record of a patients relationship with the
  hospital? If so, how can this be modeled in the
  E-R diagram?

13
Mountain ViewCommunity Hospital

• Project Exercise 1

Study the project questions very closely. What
other questions would you like to ask in order to
better understand the data requirements at
Mountain View Community Hospital?

• C) Need we model the various subtypes of EMPLOYEE
  (nurses, staff, physicians, etc.?
• D) Is there a need to model the relationship with
  other persons such as volunteers and donors?

14
Mountain ViewCommunity Hospital

• Project Exercise 1

Study the project questions very closely. What
other questions would you like to ask in order to
better understand the data requirements at
Mountain View Community Hospital?
What are some other questions MVCH might want to
consider?
15
Mountain ViewCommunity Hospital

• Project Exercise 2

Develop an E-R diagram for Mountain View
Community Hospital. State any assumptions you had
to make in developing the diagram.
Section One
16
Mountain ViewCommunity Hospital

• Project Exercise 2

Develop an E-R diagram for Mountain View
Community Hospital. State any assumptions you had
to make in developing the diagram.
Section Two
17
Mountain ViewCommunity Hospital

• Project Exercise 2

Develop an E-R diagram for Mountain View
Community Hospital. State any assumptions you had
to make in developing the diagram.
Section Three
18
Mountain ViewCommunity Hospital

• Project Exercise 3

The project description describes an entity type
called Item. Given your answer to Project
Exercise 2, will this entity type also be able to
represent in-room TVs as a billable item to
Patients? Why or why not?
Because the ITEM entity type has only a Unit_Cost
attribute with no provision for representing unit
cost per day, the ITEM entity is unable to
represent TVs as a billable item.
19
Mountain ViewCommunity Hospital

• Project Exercise 4

Suppose the attribute bed number were a composite
attribute, composed of care center ID, room
number, and individual bed number. Redraw any
parts of your answer to Project Exercise 2 that
would have to change to handle this composite
attribute
20
Mountain ViewCommunity Hospital

• Project Exercise 4

Bed as a composite attribute composed of ward
number, bed number, and room number
21
Mountain ViewCommunity Hospital

• Project Exercise 5

Consider your new E-R diagram for Mountain View
Community Hospital after changing your answer to
Project Exercise 2 to handle the situation in
Project Exercise 4. Now, additionally assume
that a care center contains many rooms, and each
room may contain items that are billed to
patients assigned to that room. Redraw your E-R
diagram to accommodate this new assumption.
22
Mountain ViewCommunity Hospital

• Project Exercise 5

23
Mountain ViewCommunity Hospital

• Project Exercise 6

Does your answer to Project Exercise 2 allow
more than one physician to perform a treatment on
a patient at the same time? If not, redraw your
answer to Project Exercise 2 to accommodate this
situation. Make any additional assumptions you
consider necessary in order to represent the
situation.
24
Mountain ViewCommunity Hospital

• Project Exercise 6

A The E-R diagram designed in Exercise 2 allows
any combination of patient and treatment with
multiple physicians performing that
treatment. The Patient/Physician/Treatment
section is shown below
25
Mountain ViewCommunity Hospital

• Project Exercise 7

Does your answer to Project Exercise 2 allow the
same treatment to be performed more than once on
the same patient by the same physician? If not,
redraw your answer to Project Exercise 2 to
accommodate this situation. Make any additional
assumptions you consider necessary in order to
represent this situation.
26
Mountain ViewCommunity Hospital

• Project Exercise 7

A The model records date, time, and results for
each treatment occurrence performed by a
physician on behalf of a patient. This allows
multiple occurrences of the same treatment by the
same physician.
27
Mountain ViewCommunity Hospital

• Team Exercises

Richards Question
Using the Employee, Employee Number, Employee
Name, First Name, Middle Name, Last Name, create
an E-R diagram showing a composite key attribute
for MVCH employees
28
Mountain ViewCommunity Hospital

• Team Exercises

Kevins Question
In Mountain View Community Hospitals E-R
diagram, what are the attributes of the entity
type PHYSICIAN? What is its identifier? What
other attributes might MVCH want to record for
PHYSICIAN entities.
29
Mountain ViewCommunity Hospital

• Team Exercises

Brians Question
Examine the Total_Cost attribute of the Consume
relationship between PATIENT and ITEM entity
types. What is the significance of the dashed
ellipse that designates the Total_Cost attribute.