Question

**System Analysis** I will appreciate your clear answer without handwriting to understand the solution.

9.46 Consider the list of individual 3NF relations below. These relations were developed from several separate normalization activities.

PATIENT(Patient_ID,Room_Number,Admit_Date, Address)

ROOM(Room_Number,Phone,Daily_Rate)

PATIENT(Patient_Number,Treatment_Description, Address)

TREATMENT(Treatment_ID,Description,Cost)

PHYSICIAN(Physician_ID,Name,Department)

PHYSICIAN(Physician_ID,Name,Supervisor_ID)

A. Merge these relations into a consolidated set of 3NF relations. State whatever assumptions you consider necessary (including but not limited to foreign keys) to resolve any potential problems you identify in the merging process.

B. Draw an E-R diagram for your answer to Problem and Exercise 9-46a

Answer 1

(A) The list of individual 3NF relations are merged into a consolidated set of 3NF relations as follows:

Let us consider first the two PATIENT relations in 3NF which can be merged:

PATIENT(Patient_ID, Room_Number, Admit_Date, Address)

PATIENT(Patient_Number, Treatment_Description, Address)

This shall be now written as PATIENT (Patient_ID, Room_Number, Admit_Date, Treatment_Description, Address)

Similarly, there are two PHYSICIAN relations in 3NF which can be merged:

PHYSICIAN(Physician_ID, Name, Department)

PHYSICIAN(Physician_ID, Name, Supervisor_ID)

This shall be now written as PHYSICIAN (Physician_ID, Name, Department, Supervisor_ID)

The remaining two relations can be written as it is:

ROOM(Room_Number, Phone, Daily_Rate)

TREATMENT(Treatment_ID,Description,Cost)

In the above 3NF relations, We could see that PATIENT relation has the Treatment_Description field which shall be taken from TREATMENT relation - Description field.

Assumptions

However, we can make use of Treatment_ID field which is the PRIMARY KEY in TREATMENT relation as the FOREIGN KEY in PATIENT relation to refer the Treatment_Description and to finetune it for achieving Referential Integrity.

Now, the PATIENT relation becomes PATIENT (Patient_ID, Room_Number, Admit_Date, Treatment_ID, Address)

Next, in PHYSICIAN relation, we are having one Supervisor_ID field which is hanging out separately. So, we have to consider one more relation - SUPERVISOR for merging into this consolidated 3NF.

SUPERVISOR (Supervisor_ID, Department)

Further, we could also assume two other fields - Patient_Name which can be added into PATIENT relation and Supervisor_Name that can be added into SUPERVISOR relation for better understanding and results.

​​​​​​​