A hospital needs to maintain a database to keep track of its patients, doctors, appointments, treatments,

and prescriptions. The following requirements have been identified:

$$ The hospital records each patient$$s name $($Pname$),$ patient ID $($Pid$),$ Social Security number

$($Pssn$),$ current address $($Paddr$),$ phone number $($Pphone$),$ birth date $($Pbdate$),$ sex $($Psex$),$ blood

type $($Pbloodtype$),$ and emergency contact details $($Emergency$\_$contact$).$ Both Pssn and Pid have

unique values for each patient.

$$ Each doctor is identified by a doctor ID $($Did$),$ and the hospital keeps track of the doctor$$s name

$($Dname$),$ department $($Ddept$),$ specialization $($Dspecialization$),$ phone number $($Dphone$),$ and

office number $($Doffice$).$ Each doctor ID $($Did$)$ is unique.

$$ The hospital also tracks appointments. Each appointment is associated with a patient, a doctor,

an appointment date $($Adate$),$ time $($Atime$),$ and purpose $($Apurpose$).$ A patient can have multiple

appointments with different doctors, but an appointment is unique based on the combination of

the patient, doctor, date, and time.

$$ The hospital also records treatment information. Each treatment is described by a treatment ID

$($Tid$),$ treatment name $($Tname$),$ cost $($Tcost$),$ and the treatment$$s duration $($Tduration$).$ Treatment

ID $($Tid$)$ is unique.

$$ Prescriptions are recorded with details of the prescribing doctor $($Did$),$ patient $($Pid$),$ medication

name $($Med$\_$name$),$ dosage $($Dosage$),$ and prescription date $($Presc$\_$date$).$ A combination of Pid,

Did, Med$\_$name, and Presc$\_$date uniquely identifies a prescription.

T$1$:

A$.$ List all the attributes of the Hospital management system for each entity $-$ patient, doctor,

appointment, treatment, and prescription. Identify the primary keys for the relations

$$Patients$,$Doctors$,$ and $$Appointments$$ based on the provided requirements.

$($L: Remember$)$

B$.$ Given the following set of functional dependencies in the Patients relation: $($L: Apply$)$

Pssn $->$ Pname, Paddr, Pphone

Pid $->$ Pname, Pbdate, Pbloodtype

Pname $->$ Paddr, Pphone

Use Armstrong$$s inference rules to derive any new functional dependencies. Then,

find the minimal cover for this set."

C$.$ Is the Prescriptions relation $($Pid$,$ Did, Med$\_$name, Presc$\_$date, Dosage$)$ in $2$NF$?$ If not,

explain the reason and describe the steps to bring it to $2$NF$.($L: Analyze$)$

D$.$ Examine the functional dependencies in the Treatments relation: $($Tid$,$ Tname, Tcost,

Tduration$).$ Is this relation in BCNF$?$ If not, explain the violation and normalize it to

BCNF$.$