Dr. Virginia Ilie is the President of a large clinic in Southern California referred to as 'BestCare Care.'Your team has been hired as consultants to help Dr. Ilie build a conceptual data model for an electronic medical record system (EMR) database. Another team has conducted interviews and direct observations in the clinical environment and has determined a set of requirements as presented below.

BestCare has 6 different campuses or treatment centers in various regions in Southern California. For example BestCare has campuses in Westwood, Encino, Thousand Oaks, among other. Some of these campuses offer specialized treatments and imaging.The system must track information about these centers such as their name (e.g. Santa Monica ImagingCenter), full address,contact phone number,email, Website and services offered. As noted previously, each campus may offer various different services. For example, the Santa Monica campus offers mammogram imaging, ultrasounds and biopsy services. System should be able to store data about the services offered at each campus including the type of service and a description. Each campus has a number of treatment rooms available. System needs to track information about the rooms such as room type (exam room vs. surgical room), equipment present in the room and size. Please note that each exam room may have various types of equipment. For example, exam room #20 has a laptop on cart and an ultrasound machine. System should track the unique serial number for each piece of equipment along with type of equipment.

BestCare employs the following staff categories: physicians, nurses and office personnel. For all staff members, the system should track their names, birthdates, addresses and contact information (phone and business email). For physicians, the system should also tracktheir credentials (e.g. MD, MedicalDoctor or DO, Doctor of Osteopathy), medical specialty (e.g. radiologist), their board certification (if any), a DEA number (a physician needs a DEA registration number from the Drug Enforcement Administration to be able to prescribe controlled substances) along with the medical school a physician graduated from and graduation year. For nurses, the system should track their licensure (e.g. nurse or NP, nurse practitioner), any national certification (e.g. RN-BC, Registered Nurse-Board Certified or FNP-BC, Family Nurse Practitioner-Board Certified) and any other award type (e.g. FAAN, Fellow of the American Academy of Nursing). Each BestCare campusemploys various staff members. For example, the Thousand Oaks location will have physicians, nurses and technicians working at that specific location. Please note that each staff member can only work in one BestCarelocation (e.g. Dr. Mary Smithonly practices in the ThousandOaks location).

In addition to the data already mentioned, BestCare needs to track data about their patients. For each patient, the system should record the full patient name, address and contact information (phone, email), one emergency contact person (last and first name, relationship to patient, address, and phone) and primary  insurance information (insurance company name, policy number, group number, and insurance

phone number). Patients can visit any of the BestCare's campuses. When patients visit a specific campus location the system shouldtrack the date of the visit,reason for the visit (e.g. mammogram) and any comments related to the visit. For example, Jane Waas can go to the Santa Monica office for a mammogram and also visit the Westwood location at another point in time. System should track which campus each patient visited (please note that date, reason and comments vary with each patient visiting a particular campus). Physicians see patients. A physician can see many patients and a patient can be seen by various different

physicians (e.g. a patient is seen by a radiologist but also by an oncologist). The system must track this information.

Prior to a patient being seen by a physician, a nurse conducts an assessment of each patient by collecting data such as patient weight, temperature, blood pressure and pulse on a given date. The system must track

 

such data for each nurse-patient interaction. Each nurse can interact with various patients when making such assessment. Each patient can also interact with various nurses as the system will track such data over time. Table 1 shows an example of how data should be stored.

 

Nurse	Patient	Temperature	Blood Pressure	Pulse	Date
Jane Smith	Linda Lang	98.2	121/81	80	8/10/2020
Karen Lang	Wendy Kim	99.5	140/80	70	8/25/2020
Jim Vons	Laura King	98.00	110/64	85	8/31/2020

Table 1: Nurse Assessment of each Patient

Physicians write medication orders (an order is issued by a physician for treatment and/or services such as diagnosis tests - radiology, labs and therapeutic procedures - psychical therapy). The system must store information about these orders including the date and time of the order and any special instructions. Physicians can write any number of orders for a patient while each order is signed by one physician only. Orders include information about diagnosis for a particular patient medical condition. A diagnosis code, name and description must be storedfor each diagnosis. For example, order no. 3466777321 may include a diagnosis about a 'suspicious mass." Each physicianorder may includemultiple diagnoses (e.g. suspicious mass on left breast and area of radial degeneration on the rightbreast). The same diagnosis can be included in many physician orders. In addition, some orders (but not all) may include medications. Data about medications should be stored in the database including medication name, type (e.g. generic vs. brand), manufacturer (company name), unit cost and brief description. Each order may include various medications (it is possiblean order doesn'tinclude any medication) while the same medication can be

present in various orders.Note that for each order of specificmedication, there is a certain quantity and

dosage included which vary based on the orderand the specific medication ordered (e.g. order 9574857845 includes 14 pills of Acyclovir 400 mg. eachwhile order no. 9574583445 includes 7 pills of Acyclovir 250 mg. each). Table 2 below shows a snapshotof how data should be stored.

 

Order	Medication	Quantity	Dosage
9574857845	Acyclovir	14	400 mg
9574583445	Acyclovir	7	250mg
5893042225	Norco	10	500mg.

Table2: Orders & Medications

Each order is sent to a pharmacy for fulfillment. The database must track information about the pharmacies within SoCal geographical area including theirname, full addressand contact info (phone, email,fax). Each order can only be sent to a pharmacythe patient chooses,and any given pharmacy can take variousmedical orders for fulfillment.

 

Problem Requirements:

Your job as data analysts is to assist Dr. Ilie to design  a data model (ERD) to represent the data she needs to keep track of in a relational format. Your ERDs should use M.S. VISIO. You need to include all required entities, attributes and relationships. Be sure to properly indicate primary keys for each entity as well as all minimum and maximum cardinalities together with any attributes on relationships. You must also resolve any composite and multivalued attributes.

 

You will prepare 2 enhanced ERDs: 

(1) ERD#1 which shows all entities, attributes and relationships

(including the M:N relationships), and 

(2) ERD#2 which is the same as ERD#1 but it contains the

resolved M:N relationships into associate entities.

Resolve any composite and multivalued attributes.

List ALL assumptions that you have made (that augment but do not violate given requirements).

  Use M.S. Visio (and the stencil). All ERDs should be professionally prepared (this includes large

enough font, alignment on the page, lines not crossing, and general diagram appearance.