CODEWITHSUNDEEP
relational-algebra
tike
tike

Reputation: 548

SQL to Relational Algebra

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

medicine

patient

Upvotes: 2

Views: 1064

Answers (2)

Chimmy
Chimmy

Reputation: 112

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.

alt text http://img532.imageshack.us/img532/8589/diagram1b.jpg

Upvotes: 3

Lombo
Lombo

Reputation: 12235

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.

Upvotes: 3

Related Questions