Note: This document contains 4 pages.

CIS3600 Systems Analysis and Design Assignment #4 Conceptual E-R Modeling and Database Design(Due Date/Time: Word file due by 11:59PM/midnight of Sunday, 11/04/2018)

I. Objective

This assignment is to get you familiarized with the development of an Entity-Relationship Diagram (ERD) by using one of the data modeling tool in Microsoft Visio Craws Foot Database Notation. Once you have finished this assignment, you shall be able to deliver an ERD that will support the design and implementation of a relational database for a business application.

Knowledge source: You shall apply skills learned from In-Lab Practice #4 and knowledge covered in Chapters 8 (E-R diagramming) of the textbook to complete this assignment.

II. A Narrative of a Simplified Local Hospital

Bronborg Hospital (BB hereinafter) is a not-for-profit acute care regional hospital located in K Town, Michigan. In brief, BB mostly admits emergency patients from the greater Kalamazoo area. Currently, the hospital has about 30 beds for in-patient care and five clinical rooms for emergent care (i.e., acute care) that are often provided for inpatient care. At present, the hospital hires about 50 employees (i.e., Physicians, Physician Assistants, Nurses, and Administrative Staff).

Now you are hired as a system analyst to develop a database model that can be used to support the hospital day-to-day operations, in particular, the operations that are related to 1) patient appointment and 2) treatment scheduling. Your database design shall address the following processing needs:

1) Set up an appointment for an inpatient (i.e., patient hereinafter) who needs to meet aphysician for inpatient care (i.e., stay at hospital). Before an appointment can be scheduled, the following information must be collected about a patient, i.e., patients name, gender, date of birth, emergency contact (a persons name and phone), and home address. For easy access, a unique Medical Registration Number (MRN) will be assigned to each patient and used as the primary key. An appointment is about a particular time, date, and description that a patient will meet with a physician for an inpatient care. For easy record management, a unique appointment ID (AID) is used to identify an appointment record as the primary key. For easy appointment tracking, the record also keep the identifier of the staff who completes the appointment.

2) Manage employees who are working at the hospital. As mentioned above, there are about 50 employees currently working at the BB. Each employee may belong to one of three possible categories, i.e., Physician, Clinical Assistant, and Staff. (Note: physician assistant and nurses are grouped together as Clinical Assistants). All employees share the same facts as Employee ID (EID), First Name, Last Name, Date-of-Birth (DoB), and an Employee Type (i.e., T1 for physician, T2 for clinical assistant, and T3 for staff). For now, our data model will consider the following three types of employees:

T1: specialty, rank, certificate#.

T2: base_pay, license#, exp_date.

T3: hourly_rate, hired_date.

Of course, one of the above subtypes will be related to each employee working at BB. In addition, each physician may will have at least one or several clinical assistants, but

1

***Late submission will NOT be accepted.*** ***Extension request without supporting document will be DENIED AUTOMATICALLY with no further discussion. Please respect the basic class rules.***

Note: This document contains 4 pages.

each clinical assistant only works for one and only one physician. There is no specific relationship between physicians and staff, and between clinical assistants and any staff.

3) Assign an inpatient to a ward. Once an inpatient finish meeting with a physician, he/she will be admitted to the hospital, and will be assigned to a certain bed within a certain ward. To assure a proper logistic management, the following facts are used to describe each Ward: Ward ID (i.e., WID, a unique identifier), # of beds, type (e.g., rehab, recovery, etc.), and its division (e.g., OBGYN, ER, etc.). An inpatient will be assigned to a specific bed in ward, along with a specific start date and expected end date. Thus, the Ward Assignment will include MRN, WID, bed#, start_date, andend_date. Note that a patient may visit the hospital more than one time and each time will be assigned to a bed within a particular ward. Nevertheless, a bed in a ward may never be used by any patient, and so is a ward.

4) Maintain the diagnosis of a patient. A patient will be examined by the physician scheduled on his/her appointment. Each patients appointment may receive one or moreDiagnoses, depending on the needs of illness and the length of a patients stay at the hospital. Each Diagnosis will record the following facts: patients vital signs (i.e., temperature, blood pressure, and heart rate), symptoms, and comment. Each diagnosis shall be related to a specific appointment.

5) Order a treatment plan. In our case, a treatment plan involves a clinical procedure ordered by a physician after his/her diagnosis of a patient, which may or may not be needed, depending on a patients health condition. If a treatment plan is ordered, it is always arranged by a particular clinical assistant with a particular physicians approval, along with the following facts: time, date, duration, procedure #, and comment.

III. Modeling Requirements

Remember that there are more operations than what has been described above. For easy modeling, you are confined to the description in Section II. Your job includes the following:

First, conceptualize all necessary entities that are needed in your data model.

Secondly, identify the relationships between every two relevant entities. Remember: your E-R diagram should NOT include any many-to-many relationships. Your database software does not allow this type of relationship.

Thirdly, you need to characterize each entity by including all necessary attributes, i.e., theprimary key for each entity and all its necessary characteristics (i.e., attributes).

IV. Sample ERD and Transformed Entities Demo

For your convenience, extra hints are given below: 1) An incomplete ER diagram for this assignment; and 2) a sample presentation of two transformed entities.

The requirements for this assignment are (a) a complete ER diagram and (b) all transformed entities.

2

***Late submission will NOT be accepted.*** ***Extension request without supporting document will be DENIED AUTOMATICALLY with no further discussion. Please respect the basic class rules.***

Note: This document contains 4 pages.

1) A Partial ER Diagram:

Note that the above ER diagram does not contain all entities. For demo purposes, this sample only contains two entities (i.e., EMPLOYEE and STAFF) by following the correct naming rules. Also note EID is a unique identifier for the EMPLOYEE entity as well as the primary key for each employee subtype (e.g., PHYSICIAN, CLINICAL_ASSISTANT, and STAFF) but with a different name (e.g., PhID for PHYSICIAN and CID for CLINICAL_ASSISTANT). In your ERD, you need to include all entities addressed in Section II and all necessary relationships between them.

3

***Late submission will NOT be accepted.*** ***Extension request without supporting document will be DENIED AUTOMATICALLY with no further discussion. Please respect the basic class rules.***

Note: This document contains 4 pages.

2) A Sample Output for Two Transformed Entities:

Once you have completed the ER diagram, each entity in the diagram shall be transformed into a normalized format (see below).

EMPLOYEE (EID, FName, LName, DoB, Type) Primary Key: EID Foreign Key: None.

STAFF (SID, Hourly Rate, Hired_Date) Primary Key: SID Foreign Key: SID (since this field refers to an existing EID in EMPLOYEE entity)

V. Final Deliverable for this Assignment.

For this assignment, you will need to complete both a conceptual data model (i.e., an E-R Diagram) and the database schema (i.e., a set of transformed entities). Your Word submission will consists of the following two parts.

Part I: An E-R diagram that contains all entities and relationships addressed in Section II of this assignment. For Part I, once you completed the whole E-R diagram in Microsoft Visio, select all symbols (press Ctrl + A) and then save them as a JPEG file (image). Then, insert this image file into your Word document as Part I.

Part II: In addition to the E-R diagram, you need to follow the examples given in Section IV 2and transform all entities in your E-R diagram into a set of flat entities. In specific, you need to write down all attributes in an entity along with its primary key and foreign key(s). (Part II is the so-called database schema for a database administrator).

Name format for your Word file: Assignment #4 - Yourlastname, Yourfirstname Where to submit? Submit your renamed Word file to the associated dropbox folder on

eLearning. Due: 11:59PM/midnight of Sunday, 11/04/2018

***Late submission will NOT be accepted.*** ***Extension request without supporting document will be DENIED AUTOMATICALLY with no further discussion. Please respect the basic class rules.***

4

***Late submission will NOT be accepted.*** ***Extension request without supporting document will be DENIED AUTOMATICALLY with no further discussion. Please respect the basic class rules.***