Mountain view Community Hospital
After developing a preliminary E-R model and discussing it with the rest of your team, you realize that you need to delve deeper into the interview notes and documentation you obtained to add more detail to the model and possibly add entities and relationships you had overlooked. Several issues need to be addressed. As a large service organization, Mountain View Community Hospital (MVCH) depends on four major groups of persons for its continued success: employees, physicians, patients, and volunteers. A small number of persons in the hospital community do not belong to any of these four groups. A particular person may belong to two (or more) of these groups at a given time. For example, a volunteer or employee may also be a patient at the hospital at some point in time. The four groups of people listed previously share many common characteristics such as a unique identifier, Name, Address, City/State/Zip, Birth Date, Phone, and E-mail. Then there are characteristics that apply to only one of these groups. For example, a hire date (Date Hired) is recorded for employees only. Volunteer Services records skills and interests of their volunteers in order to place them appropriately. Physicians have a pager number (Pager#) and a DEA number (a physician needs a DEA registration number from the Drug Enforcement Administration to be able to prescribe controlled substances). For patients, the hospital records the date of first contact with the hospital (Contact Date). There are also characteristics that apply to some, but not all of the groups. For example, both physicians and nurses have a specialty (e.g., pediatrics, oncology, etc.). In addition to the characteristics already mentioned, the hospital records a number of other characteristics about its patients: emergency contact information (last and first name, relationship to patient, address, and phone), insurance information (insurance company name, policy number, group number, and insurance phone number), information about the insurance subscriber in case the patient is not the insurance subscriber (last and first name, relationship to patient, address, and phone), and contact information for the patient’s primary care physician or other physician who referred the patient to the hospital. At MVCH, each patient has one (and only one) physician responsible for that patient. A given physician may not be responsible for a patient at a given time or may be responsible for one or more patients. The primary patient segments are resident patients and outpatients. Outpatients may come in for many reasons, including routine examinations at an outpatient care center (e.g., the MS Center), ambulatory/outpatient surgery, diagnostic services, or emergency room care. Each outpatient is scheduled for zero or more visits. A visit has several attributes: a unique identifier (Visit#), date, and time. Notice that an instance of visit cannot exist without an outpatient owner entity. Some patients that are seen as outpatients, for example, in the emergency room, are subsequently admitted to the hospital and become resident patients. Each resident patient has a Date Admitted attribute as well as a Discharge Date. The volunteer application form in MVCH Figure 3-1 shows all the information that Volunteer Services under Mr. Davis requires from persons interested in volunteering. Volunteers work in many areas of the hospital based on their interests and skills. Volunteer Services keeps track of a person’s time of service (begin and end date), work unit where a person works as a volunteer, and the volunteer’s supervisor. Each volunteer is supervised by an employee or physician, but not all employees and physicians supervise volunteers. Volunteer Services also keep track of a volunteer’s number of hours worked and recognizes outstanding volunteers at an annual awards ceremony. Employees fall into three categories: nurses, technicians, and staff. Each nurse has a certificate/degree indicating his or her qualification as an RN or LPN. (LPNs work under the direction of RNs at MVCH.) Each nurse must also have a current Colorado nursing license and may hold certifications in special fields such as dialysis, pediatrics, anesthesia, critical care, pain management, and so on. Most nurses are assigned to one (and only one) care center at a time, although over time, they may be working in more than one care center. Some nurses are floaters who are not assigned to a specific care center but instead work wherever they are needed. As described earlier, one of the nurses assigned to a care center is appointed nurse-in-charge (Nurse In Charge). Only nurses with an RN certificate can be appointed nurse-in-charge. Specific job-related competency skills are recorded for the hospital’s technicians. A cardiovascular technician for example may be skilled in specific equipment, such as setting up and getting readings from a Holter monitor, a portable device that monitors a patient’s EKG for a period of 24 to 48 hours during routine activities. Medical laboratory technicians need to be able to set up, operate, and control equipment, perform a variety of tests, analyze the test data, and summarize test results for physicians who use them to diagnose and treat patients. Emergency room technicians’ skills include the ability to perform CPR, or set up an IV. Dialysis technicians, who may be skilled in different types of dialysis, (e.g., pediatric dialysis, outpatient dialysis) need a variety of skills related to setting up treatment, assessing the patient during dialysis, and assessing and troubleshooting equipment problems during dialysis. Each technician is assigned to a work unit in the hospital (a care center, the central medical laboratory, radiology, etc.). Staff members have a job classification (Job Class), such as secretary, administrative assistant, admitting specialist, collection specialist, and so on. Like the technicians, each staff member is assigned to a work unit in the hospital (a care center, the central medical laboratory, radiology, etc.). Work units such as a care center have a Name (identifier) and Location. The location denotes the facility (e.g., main building) and floor (e.g., 3 West, 2 South). A care center often has one or more beds (up to any number) assigned to it, but there are also care centers without assigned beds. The only attribute of bed is the identifier Bed ID, which consists of two components: Bed# and Room#. Each resident patient must be assigned to a bed. Because MVCH doesn’t always fill all its beds, a bed may or may not have a resident patient assigned to it at a given time.
1. Is the ability to model supertype/subtype relationships important in a hospital environment such as MVCH? Why or why not?
2. Are there any weak entities, multivalued attributes, or multiple relationships in the description of the data requirements in this case segment? If so, what are they?
3. Can you think of any other business rules (other than the one explicitly described in the case) that are likely to be used in a hospital environment? Can these be represented on an EER diagram for MVCH? 4. Are there any universal data models that can be reused as a starting point for modeling MVCH’s data requirements?
4. Would you recommend using such as model for the MVCH project? Why or why not?
1. Draw an EER diagram to represent the requirements described in this case segment carefully following the notation from this chapter.
2. Suppose each care center had two nurses-in-charge, one for the day shift, and another one for the evening shift. How would that change the diagram you developed in Case Exercise 1?
Case Exercise 1
Draw an EER diagram to represent the requirements described in this case segment carefully following the notation from this chapter.
3. Develop definitions for each of the following types of objects in your EER diagram from
Case Exercise 1
Consult with some member of the hospital or health-care community (if one is available); do some research on the Internet, or otherwise make reasonable assumptions based on your own knowledge and experience. a. Entity types
4. Figure 3-17 shows the following entity types in a universal data model: PARTY, PARTY ROLE, PARTY RELATIONSHIP, EVENT, PRIORITY TYPE, STATUS TYPE, EVENT ROLE, and ROLE TYPE. How would these apply to the MVCH case? Give examples of each entity type based on the information provided in the case descriptions up to this point.
5. Derive and clearly state the business rules that are implicit in the volunteer application form shown in MVCH Figure 3-1.
6. Compare the EER diagram that you developed in this chapter with the E-R diagram you developed in Chapter 2. What are the differences between these two diagrams? Why are there differences?