The following tables are used to answer the question
MEMBER (memberID (PK), profession, name, address, telNo, areaCode (FK)
VOLUNTEER (volunteerID (PK), typeVolunteer, description)
MEMBER_VOLUNTEER (memberID (PK), volunteerID (PK), dateJoin, dateDisjoin, allowance)
This query is to select the names, address, and telephone number of member, type and description of volunteer, dateJoin, dateDisjoin, and allowance of the volunteer.
SELECT m.name, m.address, m.telNo, v.typeVolunteer, v.description, mv.dateJoin, mv.dateDisjoin, mv, allowance
FROM MEMBER m, MEMBER_VOLUNTEER mv, VOLUNTEER v
WHERE m.memberID=mv.memberID AND v.volunteerID=mv.volunteerID AND mv.dateJoin > “1/1/2010” AND mv.dateDisjoin < “31/12/2020”="" and="" mv.allowance="NULL" and="" m.profession="">
|
The Question
Draw a relational algebra tree (RAT) for the queries and use the heuristic rules to
transform the queries into a more efficient form. State each step and any transformation rules used in the process. Assume the member was joined the volunteer more than one time, and different type of volunteer organization.