Hospital System Exercise

A hospital is organized into WARDs. Each ward has a name, number, and a head nurse who manages

the ward. We keep track of the start date of the head nurse.

Each ward is responsible for a number of PATIENTs. Each patient has a name, ID$,$ and is assigned to a

specific room within the ward.

We store each DOCTOR's license number, office location, specialization, gender, and birthdate. Each

doctor is associated with one or more wards but may provide consultation across different wards. We

keep track of the number of hours per week that a doctor currently spends on patient care. We also

keep track of the primary nurse assigned to each doctor.

Each patient may have a number of MEDICAL RECORDs. For each medical record, we keep track of the

diagnosis, treatment plan, and medications prescribed.

Additionally, we track the hospital's INVENTORY. Each item in the inventory has a name, code,

quantity, and location within the hospital. Items could include medical supplies, equipment, and

pharmaceuticals.

This miniworld scenario reflects the structure of a hospital system, where departments are wards,

projects are patient care, employees are doctors and nurses, and dependencies are medical records

and inventory items.

Entities:

$$ Hospital:

o Attributes: HospitalID $($Primary Key$),$ Name

$$ Ward:

o Attributes: WardNumber $($Primary Key$),$ Name, StartDate

$$ Nurse:

o Attributes: NurseID $($Primary Key$),$ Name, Gender, Birthdate

$$ Doctor:

o Attributes: DoctorLicense $($Primary Key$),$ Name, Specialization, Gender,

Birthdate

$$ Patient:

o Attributes: PatientID $($Primary Key$),$ Name, RoomNumber

$$ MedicalRecord:

o Attributes: RecordID $($Primary Key$),$ Diagnosis, TreatmentPlan, Medications

$$ InventoryItem:

o Attributes: ItemCode $($Primary Key$),$ Name, Quantity, Location

Relationships:

$$ Manages $(1$:N$)$: Connects Ward and Nurse. Each ward is managed by one head nurse,

but a head nurse can manage multiple wards.

$$ PrimaryNurse $(1$:N$)$: Connects Nurse and Doctor. Each doctor has one primary nurse,

but a nurse can be primary for multiple doctors.

$$ PrimaryDoctor $($N:$1)$: Connects Doctor with Nurse. Each doctor has one primary nurse.

Each nurse can be the primary nurse for one or more doctors.

$$ Affiliated $($N:M$)$: Connects Doctor and Ward. A doctor can be affiliated with multiple

wards, and each ward can have multiple affiliated doctors.

$$ Consults $($N:M$)$: Connects Doctor and Patient. A doctor can consult with multiple

patients, and a patient can be consulted by multiple doctors.

$$ AssignedTo $($N:$1)$: Connects Patient and Ward. Each patient is assigned to one ward,

but a ward can have multiple patients.

$$ ReceivesCare $($M:N$)$: Connects Doctor and Patient. A patient can receive care from

multiple doctors, and a doctor can provide care to multiple patients.

$$ BelongsTo $($N:$1)$: Connects MedicalRecord and Patient. Each medical record belongs

to one patient, but a patient can have multiple medical records. c