COMP9315 22T1 Final Exam

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: Rel₁ Join[Condition] Rel₂
Set Union: Rel₁ U Rel₂
Set Intersection: Rel₁ ^ Rel₂
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