Case Description After completing a course in database management, you have been hired a...

Modify the ER diagram for Mountain View Community Hospital:

• Add the entities of ROOM and ITEM BILLING to existing ER diagram for Mountain View Community Hospital.

Modified Entity Relationship Diagram for the Mountain View Community Hospital is as given below:

Explanation:

From the above ER diagram:

This system contains the nine entities, such as CARE CENTER, ROOM, EMPLOYEE, BED, PATIENT, PHYSICIAN, DIAGNOSIS, ITEM, and TREATMENT.

CARE CENTER entity type:

• The entity CARE CENTER is identified by the identifier Care Center ID.

• The attributes of the CARE CENTER entity has Care Center Name.

EMPLOYEE entity type:

• The entity EMPLOYEE is identified by the identifier has Employee Number.

• The attributes of the EMPLOYEE entity has Employee Name.

ROOM entity type:

• The weak entity ROOM is identified by the identifier has Room Number.

BED entity type:

• The weak entity BED is identified by the composite identifier has Bed Number.

• The composite attributes of the BED entity has Room Number and Bed Number

PATIENT entity type:

• The entity PATIENT is identified by the identifier has Medical Record Number.

• The attributes of the PATIENT entity has Patient Name.

PHYSICIAN entity type:

• The entity PHYSICIAN is identified by the identifier Physician ID.

• The attributes of the PHYSICIAN entity has Physician Name.

DIAGNOSIS entity type:

• The entity DIAGNOSIS is identified by the identifier Diagnosis Code.

• The attributes of the DIAGNOSIS entity has Diagnosis Name.

ITEM entity type:

• The entity ITEM is identified by the identifier Item Number.

• The attributes of the ITEM entity has Item Description and Item Unit Cost.

TREATMENT entity type:

• The entity TREATMENT is identified by the identifier Treatment Code.

• The attributes of the TREATMENT entity has Treatment Name.

ASSESSMENT associative entity type:

• The associative entity ASSESSMENT between the entity EMPLOYEE and entity PATIENT.

• It contains the attributes of the Visit ID, Visit Date, Visit Time, Visit Comments, Visit Patient Weight, Visit Patient Blood Pressure, Visit Patient Pulse, and Visit Patient Temperature.

o The cardinality relationship between entity EMPLOYEE and associative entity ASSESSMENT is mandatory one to optional many.

o The cardinality relationship between entity PATIENT and associative entity ASSESSMENT is mandatory one to mandatory many.

PHYSICIAN DX associative entity type:

• The associative entity PHYSICIAN DX between the entity PATIENT and entity PHYSICIAN.

• It contains the attributes of the Diagnosis Date and Diagnosis Time.

o The cardinality relationship between entity PATIENT and associative entity PHYSICIAN DX is mandatory one to optional many.

o The cardinality relationship between entity PHYSICIAN and associative entity PHYSICIAN DX is mandatory one to optional many.

o The cardinality relationship between entity DIAGNOSIS and associative entity ITEM CONSUMPTION is mandatory one to optional many.

ITEM CONSUMPTION associative entity type:

• The associative entity ITEM CONSUMPTION between the entity PATIENT and entity ITEM.

• It contains the attributes of the Consume Date, Consume Time, and Consume Quantity.

• It contains the derived attribute of Total Cost.

o The cardinality relationship between entity PATIENT and associative entity ITEM CONSUMPTION is mandatory one to optional many.

o The cardinality relationship between entity ITEM and associative entity ITEM CONSUMPTION is mandatory one to optional many.

ORDER associative entity type:

The associative entity ORDER contains the attributes of the Order ID, Order Date, and Order Time.

• The associative entity ORDER between the entity PATIENT and entity ITEM.

o The cardinality relationship between entity PATIENT and associative entity ORDER is mandatory one to optional many.

o The cardinality relationship between entity ITEM and associative entity ORDER is mandatory one to optional many.

• The associative entity ORDER between the entity PHYSICIAN and entity TREATMENT.

o The cardinality relationship between entity PHYSICIAN and associative entity ORDER is mandatory one to optional many.

o The cardinality relationship between entity TREATMENT and associative entity ORDER is mandatory one to optional many.

Multi-valued relationships in MVC Hospital:

The Mountain View Community Hospital contains the multivalued relationship from the description.

Multi-valued relationship #1:

The multivalued relationship between the entity types of PATIENT and PHYSICIAN are connected with two relationships. There are:

• Refers relationship

• Admits relationship

Refers relationship:

• The relationship between the entity PATIENT and PHYSICIAN is “Refers” and the cardinality relationship is optional many to mandatory one.

o This is because, a patient may refer exactly one physician but physician may or may not refer many patients.

Admits relationship:

• The relationship between the entity PATIENT and PHYSICIAN is “Admits” and the cardinality relationship is optional many to mandatory one.

o This is because, a patient should be admited in hospital by exactly one physician but a physician may or may not admit many patients.

Multi-valued relationship #2:

Another multi-valued relationship in MVC Hospital between the entity types of EMPLOYEE and CARE CENTER are connected with two relationships. They are:

• Has Assigned relationship

• Nurse In Charge relationship

Has Assigned relationship:

The relationship between the entity CARE CENTER and EMPLOYEE is “Has Assigned” and the cardinality relationship is mandatory many to mandatory many.

• This is because, each employee in hospital is assigned to work in more than one care center and each care center has at least one or more than one employee.

Track the record of “Hours Worked” of employee for care center.

• So, the Hours Worked is put into the rectangle with dashed line connected to the “Has Assigned” relationship line.

• The Hours Worked is the property of the relationship Has Assigned.

Nurse In Charge relationship:

The relationship between the entity CARE CENTER and EMPLOYEE is “Nurse In Charge” and the cardinality relationship is optional many to mandatory one.

• This is because; every care center has exactly one employee who is designated as nurse in charge for that care center.

Contains weak relationship:

The weak relationship between the entity ROOM and BED is “Contains” and the cardinality relationship is mandatory one to optional many.

• This is because; each many may or may not contain at least one bed.

Contained In relationship:

The relationship between the entity CARE CENTER and ROOM is “Contained In” and the cardinality relationship is mandatory one to mandatory many.

• This is because; care center may contain one or more rooms but each room may have at least one item billed to patient.

Is Assigned relationship:

The relationship between the entity BED and PATIENT is “Is Assigned” and the cardinality relationship is optional one to optional one.

• This is because; each bed may or may not be assigned to patient.

Consist Of relationship:

The relationship between the entity ORDER and TREATMENT is “Consist Of” and the cardinality relationship is optional many to optional many.

Track the records of the “Treatment Date”, “Treatment Time”, and “Results”.

• So, the Treatment Date, Treatment Time, and Results are put into the rectangle with dashed line connected to the “Consists Of” relationship line.

• The Treatment Date, Treatment Time, and Results are the property of the relationship Consists Of.

Assumptions made to develop the Entity Relationship diagram of Mountain View Community Hospital are:

• EMPLOYEE entity type and its attributes can serve as the Nurse in Charge for one or more care centres.

o It is the Mandatory many cardinality constraints.

• For ASSESSMENT entity type,

o Visit ID is a unique identifier.

• To record the patients Visit Reason and Symptoms in the Visit Comments attributes for ASSESMENT entity type.

• For BED entity, Bed Id is composite identifier and it is represented as partial identifier as Room Number to complete the unique identifier and also the identifier Room Number is included in later modelling.