Part$1$: $($E$)$ER design $(25$ point$)$

Hospital Database

The goal of this project is to develop a general database for a hospital that meets the following requirements:

The hospital employs physicians and nurses who provide services to patients.

The hospital comprises several rooms, each designated for patient accommodation.

Each room has a unique number, capacity $($number of patients it can accommodate$),$ and a nightly fee.

Each patient has a name, a unique ID$,$ address, and phone number.

Patients may have a health record that includes a unique ID for each patient, details of diseases, dates of diagnosis, status $($e$.$g$.,$ ongoing, resolved$),$ and a detailed description of their health condition.

Each patient is assigned to a specific room for a certain number of nights during hospitalization. We record the check$-$in and check$-$out dates of a room for each patient.

Each physician has a unique ID$,$ name, certification number, field of expertise, address, and phone number.

Physicians monitor assigned patients for specific durations.

Each nurse has a unique ID$,$ name, certification number, address, and phone number.

Nurses execute physician orders with each execution recorded and its status noted.

Nurses administer medications for patients. We keep the date of adminster and the type and amount of given medications.

Physicians order specific instructions for patient care, each with a unique code, description, and associated fee.

All care provided to patients, including room accommodation, medication, and executed instructions, are recorded as payables

The hospital issues invoices with unique account number and issue date for each patient. Invoice has start date and end date and includes all the payables in this duration. Payable items such as room charges, medication, and specific instructions provided by physicians. Each payable has a amount, date, description and a unique id$.$ The hospital may issue multiple invoices for an account number with different issue dates.

Patients pay payments to the hospital, and each payment is recorded with the date and amount paid. Hospital track the patient balance by substracting payments from total invoices' amount for each patient.