Case Study Scenario:
After completing a course in database management, you have been hired as a summer intern by Mountain Valley Community Hospital. Your first assignment is to work as part of a team of three people to develop a high level entity relationship diagram and develop a conceptual database design for the hospital. You conduct interviews with a number of hospital administrators and staff to identify the key entity types for the hospital. Your team has identified the following entity types:
• Care Center – a treatment center within the hospital. Examples of care centers are maternity, emergency care, or multiple sclerosis center. Each care center has a care center ID (identifier) and a care center name.
• Patient – a person who is either admitted to the hospital or is registered as an outpatient. Each patient has an identifier, the medical record number (MRN) and a name.
• Physician – a member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID (identifier) and a name.
• Bed – a hospital bed that may be assigned to a patient who is admitted to the hospital. Each bed has a bed number (identifier), a room number, and a care center ID.
• Item – any medical or surgical item that may be used in treating a patient. Each item has an item number (identifier), description, and unit cost.
• Employee – any person employed as part of the hospital staff. Each employee has an employee number (identifier) and name.
• Diagnosis – a patient’s medical condition diagnosed by a physician. Each diagnosis has an ICD-9-CM diagnosis code and diagnosis description.
• Treatment – any test or procedure ordered by and/or performed by a physician for a patient. Each treatment has an ICD-9-CM procedure code and procedure description.
• Order – any order issued by a physician for treatment and/or services such as diagnosis tests (radiology, laboratory) and therapeutic procedures (physical therapy, diet orders), or drugs and devices (prescriptions). Each order has an order_ID, an order date, and order time.
The team next recorded the following information concerning 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 any 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 in that care center.
• A given patient may or may not be assigned to a bed (since some patients are outpatients). Occupancy rates are seldom at 100 percent, so a bed may or may not be assigned to a patient.
• A patient may be referred to the hospital by exactly one physician. A physician may refer any number of patients or may not refer any patients.
• A patient must be admitted to the hospital by exactly one physician. A physician may admit any number of patients or may not admit any patients.
• Prior to being seen by a physician, a nurse typically obtains and records relevant information about the patient. This includes the patient’s weight, blood pressure, pulse, and temperature. The nurse who assesses the vital signs also records the date and time. Finally, the reasons for the visit and any symptoms the patient describes are recorded.
• Physicians diagnose any number of conditions affecting a patient, and a diagnosis may apply to many patients. The hospital records the following information: date and time of diagnosis, diagnosis code, and description.
• Physicians may order and perform any number of services/treatments for a patient or may not perform any treatment. A treatment or service may be performed on any number of patients, and a patient may have treatments performed or ordered by any number of physicians. For each treatment or service rendered, the hospital records the following information: physician ordering the treatment, treatment date, treatment time, and results.
• A patient may also 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.)
The assignment consists of the following:
1. Determine all the entities and the attributes (fields of information) you will need to collect for each entity. You can add fields that are not mentioned, i.e., for the patient entity, the patient’s address, city, state, zip, account number, etc.
2. Once you have all the entities and attributes listed, normalize the data to third normal form so you will eliminate redundancies when the database is actually built.
3. List all of the relationship assumptions that make up your business rules. (They are somewhat listed above, but make each one more clear by including on a separate line.) Example for the first bullet of relationship information listed above:
• Each employee works in one or more care centers.
• Each care center has at least one employee, but can also have many employees.
4. Create an entity-relationship diagram of all the entities with their attributes – showing the primary field/s and all other fields of information for each entity. Show the relationships between tables with the lines and the signs that show relationships (i.e., one-to-one, one-to-many, or many-to-many.) Also include the cardinality learned in your videos and your textbook, i.e., the minimum 0 or 1 and maximum of many). I am looking for the zero, line, and clawed feet symbol.
Deliverables:
1. List of all entities from #1 above.
2. Data fields broken down and normalized to third normal form.
3. List of relationship assumptions from #3 above.
4. An entity-relationship diagram from #4 above.
5. Go into Access and set up the tables and relationships.This last part is not required, but will be extra credit for those that want to do it.
Entire assignment is worth 150 points. MS Access as noted in #5 above is worth an additional 25 points. You will have 4 weeks to complete this assignment – due Sunday, December 6th, 2015.