Elijay Community Hospital

You have been asked to develop a database system for the Elijah Community Hospital. ECH is a small, local community hospital with approximately $125$ beds. The basic goal is to provide highquality, cost$-$effective health$-$care services for the surrounding community in North Georgia.

The various business functions that the hospital currently has are:

Patient care administration software$-$record keeping aspects of patient care

Clinic and lab services software$-$provides lab testing and procedures and patient monitoring & screening

Patient care software$-$provide patients with medical care and support services

Financial management software$-$manage the financial resources and operations of the hospital

Administrative services software$-$provide general management and support services not directly related to patient care

The staff on the hospital includes $600$ full$-$time and $200$ part$-$time personnel including doctors, registered nurses, laboratory technicians, counselors, rehabilitation therapists, social services staff, admittance$/$discharge staff, insurance staff, janitorial staff, and security staff.

Patients may get many services from personnel, many tests and many treatments on the hospital.

A database system for the Elijah Community Hospital would have many tables. Draw ER$/$EER diagram for the following entities:

PATIENT: PatientID, Name, DOB, Age, Gender, Address, Email, Phone $($one or two$)$

BED: BedNumber, PatientID

PERSONNEL: PersonnelID, Name, Phone, Email $($one or two$),$ Address, Hire$-$date, Position

DEPARTMENT: DepartmentCode, DepartmentName

TREATMENT: TreatmentCode, TreatmentDescription. TreatmentCost

TEST: TestCode, TestDescription, TestCost

Add new attributes to several of your entities if there is a need.

Note: use Dia tool or Visio to draw the diagram.Normalizing the ER Diagram:

What we did so far is ER diagram $($ER$).$ In order to do ER$,$ our only concern is to capture right entities with right attributes and right relationships between entities. Also we like to figure out the general cardinality of relations.

After we are done with ER$,$ it is time to do some normalization. We can break the process of normalization into two steps. First step is to normalize attributes, and second step is to normalize relationships between entities.

NER Step $1$:

Attribute Normalization is the process of turning compound attributes and multivalued attributes into entities and establish a relation between these new entities and the base entity.

This is the ER of the bookstore that we have:

For the first step of normalization we focus on compound attributes such as publisher. We turn them into entities and establish a relation between them and the base entity. In our example Publisher is a compound attribute:NER Step $2$:

On Step $2$ of normalization we focus on relationships:

o Any many$-$to$-$many $(M-N)$ relationship should be turned into an associative entity.

We have to switch the cardinality of the original relation when we break it into a pair of relationships.

Since associative entity is an entity after all, we have to use a proper name for them. We cannot simply use the Verb that used to be the name of the original relation for associative entity.

The notation for associative entities is a diamond inside a rectangle; in other word it is the mix of relation symbol and entity symbol.

The normalized ER $($NER$)$ diagram: