Question

a)

Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors.

Associate with each patient a log of the various tests and examinations conducted.

b)

Construct appropriate relation schemas for the E-R diagram. (ex. customer(name, birth, ID...))

Answer 1

Solution:

(a) The final E-R diagram will consist of entity sets of Patient, Doctor, Hospital, Test. They will have their own set of attributes. For example: a Hospital will have Name, ID, Address, Speciality; a Patient will have Name, ID, Date Admitted, Date checked-out and a doctor will have Name, ID, Specialization, Registration number. Similarly Test will have attributes like disgnosis, name, date conducted, result.

A hospital will have many patients (one-to-many relation). Also, a hospital will have many doctors (one-to-many relation).

Each test will have at least one test log and will be performed by a doctor.

(b) Converting the E-R diagram into relation schema/tables:

As per E-R diagram, the tables will be needed for Patient, Hospital, Doctor, Test and Performed_By. Here is the explanation for each:

1. Patient: Since, it has cardinality ratio of N:1 with the Hospital entity set, hence it will consume the relationship and will have the primary key of Hospital as a foreign key in addition to its own key and other attributes. So the Patient table will look like:

Patient(Hospital.id, id, Name, Date_admitted, Date_checked_out)

2. Hospital: This will have id as they the key along with other attributes. It will look like:

Hospital(id, Name, Address, Speciality)

3. Doctor: Since, it has cardinality ratio of N:1 with the Hospital entity set, hence it will consume the relationship and will have the primary key of Hospital as a foreign key in addition to its own key and other attributes. So the Doctor table will look like:

Doctor(Hospital.id, id, Name, Specialization, Registration_no)

4. Test: Since it has complete participation constraint with Patient entity set and hence it will also have cardinality ratio of N:1 with the Patient entity set. Therefore, it will consume the test_log relationship and will have primary key of Patient as a foreign key in addition to its own key and other attributes. So the Test table will look like:

Test(Test_Name, Date, Diagnosis, Result, Patient.id)

5. Performed_By: This is a relationship set which will have its own table and have primary key of Test table and primary key of Doctor table as its candidate keys. This table will look like:

Performed_By(Test.Test_Name, Doctor.id)

Answer: The relation schema will be as follows:

Patient(Hospital.id, id, Name, Date_admitted, Date_checked_out)

Hospital(id, Name, Address, Speciality)

Doctor(Hospital.id, id, Name, Specialization, Registration_no)

Test(Test_Name, Date, Diagnosis, Result, Patient.id)

Performed_By(Test.Test_Name, Doctor.id)