MVCH Project Case Study Chapter 3 Team 3 Presented By: Lisa Kaulfurst, Lori Hamilton, Carey Stalling

1
MVCH Project Case StudyChapter 3Team
3Presented ByLisa Kaulfurst, Lori Hamilton,
Carey Stallings, Clay Mosley
2

• Project Objectives
• Develop a high level E-R Diagram for the hospital
• Conduct interviews with a number of hospital
  administrators and
  staff to identify the key entity types for the
  hospital

3
Key Entity Types are Identified as Follows

• Care Center
• Patient
• Physician
• Bed
• Item
• Employee
• Treatment

4
The following information concerns entity
relationships

• Each hospital EMPLOYEE is assigned to work in one
  or more CARE CENTERS. Each CARE CENTER has at
  least one EMPLOYEE, and may have an number of
  EMPLOYEES. The hospital records the number of
  hours per week that a given employee works in a
  particular CARE CENTER.
• Each CARE CENTER has exactly one employee who is
  designated nurse in charge for that CARE CENTER.

5

• A given PATIENT may or may not be assigned to a
  BED (since some PATIENTS are outpatients). A BED
  may or may not be assigned to a PATIENT.
• A PATIENT must be referred to the hospital by
  exactly one PHYSICIAN. A PHYSICIAN may refer any
  number of PATIENTS, or may not refer any
  PATIENTS.
• PHYSICIANS may perform any number of TREATMENTS
  on behalf of any number of PATIENTS, or may not
  perform any TREATMENTS. A PATIENT may have
  TREATMENTS performed by any number of PHYSICIANS.
  For each TREATMENTS performed on behalf of a
  given PATIENT by a particular PHYSICIAN, the
  hospital records the following information
  treatment date, treatment time, and results.

6

• A PATIENT may optionally consume any number of
  items. A given item may be consumed by one or
  more PATIENTS, or may not be consumed. For each
  item consumed by a PATIENT, the hospital records
  the following date, time, quantity, and total
  cost (which can be computed by multiplying
  quantity times unit cost.)

7
Project Question 1

• Why would Mountain View Community Hospital want
  to use entity-relationship modeling to understand
  its data requirements? What other ways might the
  hospital want to model its information
  requirements?
• Answer Since this approach will result in
  capturing the business rules that are required to
  maintain data integrity, the Mountain View
  Community Hospital would want to use E-R modeling
  to understand its data requirements. They might
  also want to use the object-oriented model to
  model their requirements.

8
Project Question 2
Do there appear to be any weak entities in the
description of the Mountain View community
Hospital data requirements? If so, what are
they? Answer No, there are no obvious weak
entities. TREATMENT is the most likely candidate
for a weak entity however, TREATMENT has its own
identifier. So we assume that this entity has an
independent existence.
9
Project Question 3
What is the significance in developing an E-R
diagram for Mountain View Community Hospital to
the third entry in the preceding list, which
states that some patients are assigned to a
bed? Answer In the handout the relationship
IS_ASSIGNED is between PATIENT and BED as an
optional 0-1 relationship. In the next chapter
we will learn how to model the subtypes of
PATIENT (INPATIENT and OUTPATIENT) and then
create a mandatory relationship between INPATIENT
and BED.
10
Project Question 4
Is Mountain View Community Hospital itself an
entity type in a data model to represent its data
requirements? Answer No, because an entity type
is defined as a collection of entities that share
common properties and characteristics. Since it
is understood that the entity type HOSPITAL is
only referring to Mountain View Community
Hospital then it is known as entity instance
which is defined as only a single occurrence.
11
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.
• Answer
• Is there a need to model the various patient
  accounts paid, past due, etc?
• Should an attribute regarding age of patients be
  added to the E-R diagram?

12

• Project Exercise 1 continued
• Should an attribute regarding allergies be added
  under patient entity type?

13
Project Exercise 2
Develop an E-R diagram for Mountain View
Community Hospital. State any assumptions you
had to make in developing the diagram. Due to
size of E-R diagram, please refer to handout on
page 6
14
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 to patients.
Why or why not? Answer No. The entity type
ITEM has a Unit-Cost attribute, but has no
provision to represent a unit cost per day which
would be required for items such as in-room TVs.
15
Project Exercise 4
Suppose the attribute bed number were a composite
attribute, supposed 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 computer
attribute. Answer
Bed_ No
Ward_No
Bed_No
Room_No
16
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
accommodate this new assumption.
17
Contains
WARD
ROOM
Room_No
Contains
Ward_No
ITEM
PATIENT
Billed _to
Item No
Patient No
Cost
18
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 this
situation. Answer Yes, any combination of
patient and treatment has multiple physicians who
perform that treatment.
19
Project Exercise 6 continued
PATIENT
PHYSICIAN
Performs
TREATMENT
20
Project Exercise 7
Does your answer to Project Exercise 2 allow the
same treatment to perform 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. Answer Yes. The
model records the data,time, and results for each
treatment occurrence performed by physicians on
the patients.
21
Project Exercise 7 continued
results
PATIENT
PHYSICIAN
Performs
Treatment_time
Treatment_date
TREATMENT
22
Careys Question

• What is the importance of an identifier attribute
  in the E-R diagram in Problem Exercise 2?

23
Loris Question

• Chapter 3 discusses modeling the rules of
  organizations and data modeling. How does this
  information relate to the Mountain View Community
  Hospital case project?

24
Lisas Question
Use the following to create an E-R diagram
showing a composite key attribute for Mountain
View Community Hospitals employees Employee,
Employee No., Employee Name, First Name, Middle
Name, Last Name.
25
Clays Question
What is an example of a time stamp in Mountain
View Community Hospital Case Study and why is it
important? Answer