Question 7 (10 marks)
Translate the SQL queries below into an efficient sequence of relational algebra operations. Use the notation for relational algebra operations from the Course Notes i.e.
Selection: Sel[Condition]Relation
Projection: Proj[AttrList]Relation
Join: Rel1 Join[Condition] Rel2
Set Union: Rel1 U Rel2
Set Intersection: Rel1 ^ Rel2
Set Membership: (Attr in RelWithOneAttr) (is used as a boolean expression)
When showing complex relational algebra, write it as a sequence of "assignment" statements, with a single relational algebra operation on the right hand side, and a temporary relation to hold the result on the left hand side. The temporary relation can be used in subsequent "assignment" statements.
Example: select x,y from R where z = 5; is rendered as:
Tmp1 = Sel[z=5] R
Res = Proj[x,y] Tmp1
Write relational algebra operations for each of these operations on the tables R(a,b,c) and S(c,d) and T(d,e,f,g)
select * from S where c = 5 and d = 8
select a,d from R join S on R.c = S.c
select *
from R
where c in (select c from S where d = 3)
select a,f,g
from R join S on (R.c = S.c) join T on (S.d = T.d)
where R.b = 2 and S.c = 5 and T.e = 10
You do not need to worry about the sizes of tables, intermediate result, projected tuples, number of memory buffers, etc. in answering this question. Treat this as the initial relational algebra transformation stage after parsing the SQL and before query optimisation, and apply the common heuristics used in this phase.
State all assumptions. Show all working.
Instructions:
- Type your answer to this question into the file called q7.txt
- Submit via: give cs9315 exam_q7 q7.txt
or via: Webcms3 > exams > Final Exam > Q7 submission > Make Submission
End of Question
Translate the SQL queries below into an efficient sequence of relational algebra operations. Use the notation for relational algebra operations from the Course Notes i.e.
Selection: Sel[Condition]Relation
Projection: Proj[AttrList]Relation
Join: Rel1 Join[Condition] Rel2
Set Union: Rel1 U Rel2
Set Intersection: Rel1 ^ Rel2
Set Membership: (Attr in RelWithOneAttr) (is used as a boolean expression)
When showing complex relational algebra, write it as a sequence of "assignment" statements, with a single relational algebra operation on the right hand side, and a temporary relation to hold the result on the left hand side. The temporary relation can be used in subsequent "assignment" statements.
Example: select x,y from R where z = 5; is rendered as:
Tmp1 = Sel[z=5] R Res = Proj[x,y] Tmp1
Write relational algebra operations for each of these operations on the tables R(a,b,c) and S(c,d) and T(d,e,f,g)
select * from S where c = 5 and d = 8
select a,d from R join S on R.c = S.c
select * from R where c in (select c from S where d = 3)
select a,f,g from R join S on (R.c = S.c) join T on (S.d = T.d) where R.b = 2 and S.c = 5 and T.e = 10
You do not need to worry about the sizes of tables, intermediate result, projected tuples, number of memory buffers, etc. in answering this question. Treat this as the initial relational algebra transformation stage after parsing the SQL and before query optimisation, and apply the common heuristics used in this phase.
State all assumptions. Show all working.
Instructions:
- Type your answer to this question into the file called q7.txt
- Submit via: give cs9315 exam_q7 q7.txt
or via: Webcms3 > exams > Final Exam > Q7 submission > Make Submission
