SQL to Relational Algebra

Question

How do I go about writing the relational algebra for this SQL query?

Select patient.name, 
       patient.ward, 
       medicine.name, 
       prescription.quantity, 
       prescription.frequency
From patient, medicine, prescription
Where prescription.frequency = "3perday" 
  AND prescription.end-date="08-06-2010" 
  AND canceled = "Y"

Relations...

prescription

• prescription-ref
• patient-ref
• medicine-ref
• quantity
• frequency
• end-date
• cancelled (Y/N))

medicine

• medicine-ref
• name

patient

• Patient-ref
• name
• ward

Answer 1

I will just point you out the operators you should use

Projection (π)

π(a1,...,an): The result is defined as the set that is obtained when all tuples in R are restricted to the set {a1,...,an}.

For example π(name) on your patient table would be the same as SELECT name FROM patient

Selection (σ)

σ(condition): Selects all those tuples in R for which condition holds.

For example σ(frequency = "1perweek") on your prescription table would be the same as SELECT * FROM prescription WHERE frequency = "1perweek"

Cross product(X)

R X S: The result is the cross product between R and S.

For example patient X prescription would be SELECT * FROM patient,prescription

You can combine these operands to solve your exercise. Try posting your attempt if you have any issues.

Note: I did not include the natural join as there are no joins. The cross product should be enough for this exercise.

Answer 2

An example would be something like the following. This is only if you accidentally left out the joins between patient, medicine, and prescription. If not, you will be looking for cross product (which seems like a bad idea in this case...) as mentioned by Lombo. I gave example joins that may fit your tables marked as "???". If you could include the layout of your tables that would be helpful.

I also assume that canceled comes from prescription since it is not prefixed.

Edit: If you need it in standard RA form, it's pretty easy to get from a diagram.