Mt. Sinai Hospital Medical Center Each time a patient receives treatment from a physician at the hospital, an entry is made into the patient's automated hospital record. This information is stored to the patient note table in the note_comment column. This table allows storage of an individual note comment that is up to 4000 characters in size. The table allows for storage of an unlimited number of comments for a patient. The relationship between patient and patient_note is one-to-many. When patients are admitted to the hospital, they are assigned to a specific bed. The hospital assigns each bed a unique identifier known simply as a bed_number. A room may have zero, one or more beds (some rooms do not contain beds, e.g., a radiology laboratory). The information system has three additional tables, bed, room, and bed_type. The bed_type table is used as a validation table. The hospital provides patients with various medical services. Services are categorized for insurance reporting purposes because insurance companies expect hospitals to use standard reporting categories and service codes. The hospital maintains data about services in a service table. Additionally, a service_cat (category) table stores validation data about service code categories. The relationship between a service_cat and service is one-to-many because each service falls into a single category. The hospital keeps records of the services provided by a doctor for each patient. The rendering of a service is termed a treatment and is implemented by three relationships named Gets Treatment, Treatment-Provided, and Gives-Treatment linking treatment to the patient, service, and physician table. Each treatment has a unique identifier, the TreatmentID. The hospital maintains information about treatments including the patient, physician, and service as well as the date/time

2 of the treatment and the charge. Each table related to treatment is linked by storing FOREIGN KEY columns (pat_id, service_id, and physician_id) in the treatment table. Additional comments regarding each treatment are also recorded. These comments may include diagnosis information. Physicians prescribe medicines to be administered to patients. The hospital stores medicines in its internal pharmacy department. Information about medicines that are stocked is stored in the medicine table. The prescription of medicine is an associative table that relates the patient, and medicine tables, and is implemented as the table named prescription with three one to-many relationships named Orders-Prescription, Gets-Prescription, and Medicine-Provided.

3 Requirements: You have been asked to develop a logical data model for Mt. Sinai Medical Center based on the information given to you by the new hospital administrator and their staff. Through analysis of the nouns and verbs in the case study above, you have accumulated the following entity, attribute, and relationship information shown in the table below. The attribute list may not be complete. If you determine that additional attributes are needed to better define an entity, then you should add them. Entities Attributes and Relationships for Mt. Sinai Medical Center: Entity Attributes Relationships Physician PhysicianID, Name, Address, Phone A physician provides the Treatment for each patient. ServiceCategory ServiceCATID, ServiceCATDescription Services are categorized for insurance reporting purposes because insurance companies expect hospitals to use standard reporting categories and service codes. It stores validation data about service code categories. Service ServiceID, ServiceDescription, ServiceCharge, ServiceCatID, ServiceComments The hospital keeps records of the services provided by a doctor to each patient. This table is basically a list of all the services the hospital provides. Treatment TreatmentID, TreatmentDate, PatientID, PhysicianID, ServiceID, TreatmentCharge, Comments Treatment entity records information about the specific treatments for a patient. It brings together through foreign keys information from other entities such as service, patient, and physician. Medicine MedicineID, MedicinceName, QuantityInStock, NormalDosage Identifies the medication the hospital pharmacy dispenses. One medication can be dispensed per prescription. The quantity on hand allows Pharmacists to determine the inventory levels. Prescription PrescriptionID, PrescriptionDate, MedicineID, PatientID, PhysicianID, DosagePrescribed, DosageDirections. A Prescription is an associate table. It connects information from the medicine, patient, and physician tables. Patient PatientID, Name, Address, City, State, Zip, Phone, DateOfBirth, BedID A patient can be associated with multiple prescriptions, but any one prescription is to one and only one patient.

4 Patient Note Patient Number, NoteDate, Note Comment Each time a patient receives treatment from a physician at the hospital, an entry is made into the patient's automated hospital record. Room RoomID, RoomDescription This table contains a list of all the rooms in the hospital. A room can have several patients in it. BedType BedTypeID, BedDescription This table is a list of all the types of beds that are available. Bed BedID, RoomID, BedTypeID, BedAvailability A bed is associated with one patient or it can be empty. Using an appropriate drawing/data modelling tool, develop an ERD that meets the following guidelines. Draw the entities with their attributes. Indicate the relationships between the entities using Crow's Foot notation. You will need to determine the cardinality and optionality for each direction of the relationships. Some of the Foreign Key relationships are identified in the graph above but not all. Be sure you identify and account for all Foreign Key relationships. Add a name (in both directions) to the relationships. Remember, if you can verbalize the relationship in both directions, then you probably have a valid relationship. Deliverables The deliverable for this lab will be your completed ERD as a single MS Word document using copy/paste or imported as an image from your drawing/modelling application, cropped and sized appropriately (it should fit on a single page), and named lab2_solutions_yourname. LAB STEPS STEP 1: Drawing Entities and Attributes Be sure to include all of the entities that have been defined. You need to include at least the primary and foreign key attributes where applicable in your diagram. STEP 2: Add Relationships Be sure that you link all entities based on PK to FK relationships. There may be a case where you need to identify a combination PK and if so make sure that all of the relationships involved are defined. Be sure that you have set your drawing/modelling tool set to show Crow's Foot notation. Also, be sure that you are defining the correct cardinality for the relationships. STEP 3: Naming of relationships If your drawing/modelling tool creates a default name for relationships, do not simply accept this default without due consideration. There may be some relationships where the default is applicable, but in most cases you will want to explicitly name the relationship. Be sure that you have provided a verb phrase for both directions in the relationship. This is the end of lab #2