Question

Question 1. Prepare an Entity-Relationship diagram for the following requirements of a medical clinic

The clinic has a set of patients and a set of specialist medical doctors.
A doctor sees many patients and a patient may have many doctors
Patients take various tests.
Many specialist doctors may consult on a test and recommend it. A doctor can recommend many tests

However, a test is only for one patient. So I get blood pressure tested but it cannot be for everyone – just for me.
Patients have a name and insurance policy number.
Both patients and doctors have unique identifiers.

Tests likewise have a name and unique identifier, a date and time the test was taken, and the results of the test.
.

Answer 1

Please find below the steps to create the ERD for given Doctor, Patient clinic system:

Step 1: List the entities:

1. Patient: To store the patient details

2. Doctor: To store the doctor details

3. Test: To store the test details

Step 2: List attributes of the entities:

1. Patient(patient_id,name, insurance_policy_no)

2. Doctor(doctor_id, name)

3. Test(test_id, name, date_time, result)

Step 3: List relationship and cardinality:

1. Patient consults many doctors and a doctor sees many patients, thus it is a many to many relationship.

2. Patient has many tests, but a test is made on a single patient only. It is one to many relationship.

3. Test is recommended by One doctor, whereas a doctor can be recommend many tests. It is many to one relationship.

Step 4: Create ERD:

doctor_id name patient_id (1,M) Doctor name seen by (1,1) Insurance_policy no Patient (1,N) (1,1) recommend takes (0,M) (1,M) Test test_id resu name date_time