Mountain View Community Hospital Project Case

1
Mountain View Community Hospital Project Case

• Chapter 3, Team 3
• Presented By
• Andrea Bledsoe, Terri Pool, Talvickeo Banks
• September 18, 2003
• MIS 2403 800 TR
• Dr. Richard Segall

2
Project Description

• Hired as summer interns, our first assignment
  was to work as part of a team to develop a
  high-level E-R Diagram for MVCH. After conducting
  interviews with a number of hospital
  administrators and staff we identified the
  following key entity types for the hospital.

3
Key Hospital Entity Types

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

4
Entities Care center (care center ID care
center name) Patient (patient number patient
name) Physician (physician ID physician
name Bed (bed number, room number, care center
ID) Item (item number unit cost) Employee
(employee number name) Treatment (treatment ID
treatment number treatment name)
5
We then recorded the following information
concerning relationships

• Each hospital EMPLOYEE is assigned to work in one
  or more CARE CENTERS. which has at least one
  EMPLOYEE, and may have any number of EMPLOYEES,
  recording 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.

6
Relationships Cont.

• A given PATIENT may or may not be assigned to a
  BED (since some patients are outpatients), which
  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.

7
Relationships Cont.

• PHYSICIANS may perform a 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 TREATMENT performed on
  behalf of a given PATIENT by a particular
  PHYSICIAN, the hospital records the following
  information treatment date, treatment time and
  results.

8

• A PATIENT may optionally consume any number of
  ITEMS. A given ITEM may or may not 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).

9
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 MVCH would use E-R modeling to understand
  its data requirements because this approachs end
  result will be capturing the business rules that
  are required to maintain data integrity. Also
  MVCH might consider modeling their requirements
  using the object-oriented model.

10
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.
• Answer

Bed_No
Ward_No
Bed_No
Room_No
11
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.

12
Project Exercise 5 Answer
Contains
WARD
ROOM
Room_No
Contains
Ward_No
Billed to
Item_No
ITEM
PATIENT
Cost
Patient_No
13
Andreas Question

• What could MVCH do to help reduce the cost of
  their patients treatment?

14

• Assumptions
• the employee number and the treatment ID are
  identifiers (the book does not specify)
• charge nurses cannot be assigned to be in charge
  of more than one care center
• each care center must have more than one bed (it
  would not be cost effective for a care center to
  treat only one in-patient at a time)

15
Project Question 2 Do there appear to be any
weak entities in the description of the MVCH data
requirements? If so, what are they? Patient is
dependent on the reference by the physician, but
it has its own complete identifier, so it is not
weak. Item and treatment depend on the patient,
but they too have their own complete identifiers,
so they are not weak.
16
Project Question 4 Is MVCH itself an entity
type in a data model to represent its data
requirements? If the hospitals data requirements
had mentioned something about payroll or product
costs (cost to the hospital, not to the patient),
then MVCH could have been added to the ERD as an
entity, but since those and any accompanying
relationships were not mentioned, it is obviously
not necessary information to the hospital.
17
Project Exercise 2
18
Project Exercise 2 cont.
19
Project Exercise 2 cont.
20

Project Exercise 2 cont.
21
Project Exercise 2 cont.
22

Project Question 6 Does the answer to PE 2
allow more than one physician to perform a
treatment on a patient at the same time? Yes.
Any number of physicians can treat any number of
patients at the same time.
23
Project Exercise 7 Does your answer to PE 2
allow the same treatment to be performed more
than once on the same patient by the same
physician? Yes, because, according
to the
hospitals data
requirements, the date,

time, and results of each

treatment is required to
be recorded
whenever a
treatment is performed.
24
Terris Question The book mentions that an
instance of an entity may at some point change
entity types. The Ch. 3 case requires that some
instances of an entity (Employee) sometimes also
be another entity (Charge Nurse). What other
entities may also simultaneously be instances of
different entities and how would this be
represented on an entity-relationship diagram?
25
Project Question 3

• Why would Mountain View Community Hospital want
  to use entity-relationships modeling to
  understand its data requirements? What other ways
  might the hospital want to model its information
  requirements?

26
Project 3 Answer

• From what we know at this point about the E-R
  diagram, we simply diagram the relationship
  (called Is_assigned) between PATIENT and BED as
  optional 0-1 relationship. Later in Chapter 4 we
  will learn how to model the subtypes of PATIENT
  (IN PATIENT and OUT PATIENT) and then create a
  mandatory relationship between IN PATIENT and BED.

27
Project Exercise 1

• Study the project descriptions very closely. What
  other questions would you like to ask in order to
  better understand the data requirements at MVCH?
• Answer some of these following questions maybe
  helpful.
• A. Should we model pharmaceutical items
  separately from ITEM, since such items are
  prescribed by a physician for a patient?

28
Project Exercise 1 continued

• 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?
• 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?

29
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?

30
Project Exercise 3 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.

31
Vicks Question

• In MVCH, what would be important in checking the
  data modeling in an entity-relationship diagram?