Consider the following ERD

Where

PatientId: It is the identification number of each patient

PatientName: It is the name of the patient

Patientddr: It is the address of the patient

AdmitDate: It is the date when the patient is admitted to the hospital

AmounOwing: The amount the patient owes based on his/her sickness after being discharged

RoomNo: it is the room where the patient is kept in the hospital

RoomPhone: The phone number in the patients room

HospitalStayDays: Number of days the patient would be in the hospital for treatment.

RoomRate: The rate charged for every day the patient is in the room

In the second table:

PhysId: It is the identification number of each physician

PhyName: It is the name of each physician

PhysDept: It is the department id where physician works

DeptSupervisorId: It is the id of the physician who is in change of managing the PhyDept. For example, suppose physician x works in department y. DeptSupervisorId is the id of the physician (not necessarily physician x) who in managing department y.

TreatId is a number that represents the type of treatment the physician can do

TreatDesc and TreatCost are Treatment description and treatment cost

Each patient is assigned one doctor, but a doctor can have many patients

There may be more than one patient in a room but each patient is kept in one room only

There is only one phone number in each room in the hospital

Each doctor can do only do one treatment, but a treatment can be done by many doctors

The treatment cost is fixed for each treatment

Each doctor works in only one department, but a department can have many doctors

Each department has 1 supervisor. This supervisor is just one of the physicians who works in that department

A Patient is charged based on the treatment cost and number of days in hospital

Note that not all the rooms in the hospital has patient at a particular time but all patient must be is some rooms. Further, only some of the physicians are supervising the departments in the hospital; however, all departments must be managed by some physicians.

You may make any other assumption you think is necessary but you have to be very specific and realistic. You can add other assumptions but you are not allowed to change the above assumptions

Do the following

1a)Change the ERD to tables

1b)Place the tables in 3^rd normal form

1c)Revise the given ERD based on the normalized tables