Case Description

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 enti$-$ ties 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 employ$-$ ees only. Volunteer Services records skills and interests of its 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 phy$-$ sicians and nurses have a specialty $($e$.$g$.,$ pediatrics, oncology$).$

In addition to the characteristics already mentioned, the hospital records a number of other characteristics about its pa$-$ tients: emergency contact information $($last and first name, rela$-$ tionship to patient, address, and phone$),$ insurance information $($insurance company name, policy number, group number, and insurance phone number$),$ information about the insurance sub$-$ scriber 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 re$-$

sponsible 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 sur$-$ gery, diagnostic services, or emergency room care. Each outpa$-$ tient is scheduled for zero or more visits. A visit has several at$-$ tributes: 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 hos$-$ pital 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 b volunteering. Volunteers work in many person of the hospital based on their