Question

Activity 2a: Develop an ERD for a Hospital

A database will be made to store information about patients in a hospital. On arrival, each patient’s personal details (name, address, and telephone number) are recorded where possible, and they are given an admission number. They are then assigned to a particular ward (Accident and Emergency, Cardiology, Oncology, etc.). In each ward there are a number of doctors and nurses. A patient will be treated by one doctor and several nurses over the course of their stay, and each doctor and nurse may be involved with several patients at any given time.”

And add a weak entity ti the ERD

Activity 2b: Reduce the hospital ERD to Relational Schema?

These pictures are examples of these questions

Answer 1

Key points:

• Each patient’s personal details (name, address, and telephone number), and they are given an admission number.
• Patiets are then assigned to a particular ward.
• In each ward there are a number of doctors and nurses. (So, Ward: Doctor - 1: N)
• A patient will be treated by one doctor and several nurses. Which means (Doctor:Patient - 1:N) and (Nurse: Patient - N:N)
• Each doctor and nurse may be involved with several patients.(1:N)

So the ER diagram as required:

ER diagram:
ER model is to represent the strucre of data by using entities and attributes and their reklations. ER diagram considers entities in real world and relating with them. It is a graphical view.

Relational diagram:
In Relational database model,the entities and relations are represented by using tables. Each table contains records and attribues.

ERD-Relational Schema

• If we have multi-valued attribute, make that attribute as new entity.
• In 1:1 Relationships, we are not considering relation as seperate entity. We add less priority table primary key as foreign keyb of priority table.
• In 1:M relationships, we are not considering relation as seperate entity. We simply use primary key of one table as forign key of another table.
• In M:M relationship, We need to create a relationship as seperate entity.

Now let us convert ER diagram to Relational tables:

1) Relationship between Ward and Patient: As it contains 1:N relationship,we are adding primary key of Ward as foreign keyb of Patient table.

Ward(Ward_Number,Ward_Name)
Patient(Admission_Number,Name,Number,Address,Ward_Number)

2) Relationship between Doctor and Patient: As it contains 1:N relationship,we are adding primary key of Doctor as foreign keyb of Patient table.

Doctor(Doctor_Id,Doctor_Name,Speciality)
Patient(Admission_Number,Name,Number,Address,Ward_Number,Doctor_Id)

3) Relationship between Nurse and Patient: As it contains N:N relationship,we are adding relationship as another table.

Nurse(Nurse_ID,Nurse_Name)
Assists(Admission_Number,Nurse_ID)
Patient(Admission_Number,Name,Number,Address,Ward_Number,Doctor_Id)

4)Relationship between Ward and Doctor :As it contains 1:N relationship,we are adding primary key of Ward as foreign key of Doctor table.

Ward(Ward_Number,Ward_Name)
Doctor(Doctor_Id,Doctor_Name,Speciality,Ward_Number)

5) Relationship between Ward and Nurse: As it contains 1:N relationship,we are adding primary key of Ward as foreign key of Nurse table.

Ward(Ward_Number,Ward_Name)
Nurse(Nurse_ID,Nurse_Name,Ward_Number)

So the final tables are:

Patient(Admission_Number,Name,Number,Address,Ward_Number,Doctor_Id)
Ward(Ward_Number,Ward_Name)
Nurse(Nurse_ID,Nurse_Name,Ward_Number)
Assists(Admission_Number,Nurse_ID)
Doctor(Doctor_Id,Doctor_Name,Speciality,Ward_Number)