Question $1($Relational Algebra $-$ Queries $($I$))$

$[25$ points$]$

Consider the following relation schemas. Primary keys are underlined. All attributes are of type string

if not indicated otherwise. Use multi$-$step and multi$-$line queries to ease the formulation of queries. Use

the rename operator $$ to give intermediate query results a name $($some textbooks use the equivalent

notation$).$ Aggregate functions as you can find them defined in some textbooks are not allowed, neither

in homework assignments nor in exams, since they are problematic extensions of the classical Relational

Algebra.

Patients$($PatientID$,$ FirstName, LastName, BirthDate, Gender, PrimaryPhysicianID$)$

Physicians$($PhysicianID$,$ FirstName, LastName, Specialty, DepartmentID, HireDate$)$

Departments$($DepartmentID$,$ Name, Location, StaffNumber$)$

Nurses$($NurseID$,$ FirstName, LastName, Shift, DepartmentID, SupervisorID$)$

Treatments$($TreatmentID$,$ Name, Description, Cost$)$

PatientTreatments$($PatientID$,$ TreatmentID, Date, PhysicianID, NurseID$)$

Appointments$($AppointmentID$,$ PatientID, PhysicianID, Date, Time, Purpose$)$

MedicalRecords$($RecordID$,$ PatientID, Date, Summary, Diagnosis, TreatmentPlan$)$

$($a$)[5$ points$]$ Find the names of patients who have received treatment in more than one department, and

list these departments along with the names of the patients.

$($b$)[5$ points$]$ Find the names of physicians who have treated patients whose first names are PatientA or

PatientB on Nov $11.$

$($c$)[5$ points$]$ Find the names of physicians in the general surgery department who have treated patients

assisted by NurseA $($unique first name$).$

$($d$)[5$ points$]$ Find the names of patients who have had appointments with physicianA $($unique first name$)$

but have not received any treatment $($no show$).$

$($e$)[5$ points$]$ Identify the patients who have incurred the highest treatment costs. List their names along

with the respective costs.