Consider the following relation schemes:

Patients = {p-id, p-name, street, city, state, zip}

Doctors = {d-name, d-phone, specialty}

Visits = {d-name, p-id, time, day, month, year, fee}

Accounts = {p-id, date-in, date-out, amount}

Write the following queries in relational algebra:

Q1 (10%) List id and names of patients who live in High Point.

Q2 (10%) List the details of all visits for which a fee of $400 or more was charged.

Q3 (10%) List the account details for patient with id ``123456''. Can the total (i.e. sum of amounts) be calculated in relational algebra?

Yes, you can use relational algebra by extending it to include aggregate operations such as sum.
The operation sum will take a collection of numbers and return a single value.

Q4 (10%) List all patients (id and name) visited by Dr. Smith in July and August 1998.

Q5 (10%) List doctors who have not visited ``Tom Holiday''.

 


Q6 (10%) List pairs of doctors (names) with the same specialty.

Q7 (10%) List patients (id) who have been visited by all doctors.

Q8 (10%) List patients admitted on or before 08/15/98 who were visited by Dr. Smith during their hospitalization. (Assume comparison is possible on dates.)

Q9 (10%) List doctors who have been themselves hospitalized. Are you making any assumptions in formulating your query?

I am assuming that the p-name and d-name fields uniquely identify doctor or patient.

Q10 (*) (10%) List the doctor who has charged the highest fee (ever).

(*) Indicates difficult problem.