Unformatted Attachment Preview
Problem 1
a) Suppose there are two relations, R1 and R2. Relation R1(A,B) has the tuples:
AB
0 1
2 3
4 5
and the relation R2(B,C,D) has tuples:
BCD
1 3 5
3 5 7
3 6 8
Compute the natural join of R1 and R2.
1
b )
Suppose there are two relations, R1 and R2. Relation R1(A,B) has the tuples:
AB
0 1
2 3
4 5
and the relation R2(B,C,D) has tuples:
BCD
1 3 5
3 5 7
3 6 8
Compute the theta-join R1
⋈R1.A < R2.C AND R1.B < R2.D R2.
(must show the steps of your work)
2
c )
Suppose there are two relations, R1 and R2. Relation R1(A,B,C) has the
tuples:
ABC
2 3 4
5 3 4
5 6 7
3 6 4
2 3 7
and relation R2(A,B,C) has the tuples:
ABC
3 6 4
3 6 5
5 6 7
2 3 4
Compute (R1 – R2)∪(R2 – R1), often called the "symmetric difference" of R1
and R2.
(1) Show (R1 – R2) =?
(2) Show (R2 – R1) =?
(3) Show (R1- R2)∪(R2 – R1) =?
3
d )
Suppose there are two relations, R1 and R2. Relation R1(A,B,C) has the tuples:
ABC
1 2 3
1 2 3
4 5 6
2 5 3
1 2 6
and relation R2(A,B,C) has the tuples:
ABC
2 5 3
2 5 4
4 5 6
1 2 3
Compute the bag union, R1∪R2.
e) Suppose relation S(A,B,C) has the tuples:
ABC
0 1 2
0 1 3
4 5 6
4 6 3
Compute the generalized projection πB,A+C,B(S)
4
f )
Suppose relation S(A,B,C) has the tuples:
ABC
1 2 3
1 2 3
2 3 1
3 1 2
2 2 3
2 3 3
Using bag projection and intersection, compute πA,B(S)
∩ ρR(A,B)(πB,C(S)).
(must show the steps of your work )
g) Suppose relation S(A,B,C) has the tuples:
ABC
1 2 3
1 2 3
2 3 1
3 1 2
2 2 3
2 3 3
Using bag projection and difference, compute πA,B(S)
– ρR(A,B)(πB,C(S)).
(must show the steps of your work)
5
h )
Suppose there are two relations, R1 and R2. Relation R1(A,B) has the tuples:
AB
0 1
2 3
4 5
and the relation R2(B,C,D) has tuples:
BCD
1 3 5
3 5 7
3 6 8
Compute the outerjoin of R1 and R2, where the condition is: R1.A>R2.B AND
R1.B=R2.C. (Which tuples of R1 or R2 are dangling (and therefore needs to be padded in
the outerjoin)?)
Problem #2
(a). Query Statement: List the model for PCs that are faster than 2Ghz? Translate the
Query Statement into Relational Algebra.
(b). Using Relational Algebra to express the following SQL statements:
SELECT empno
FROM employee
WHERE depno IN (select depno from employee
where name = ‘Jim Smith‘)
6
Problem#3
Translate the following SQL statements into Relational Algebra Tree.
SELECT
FROM
WHERE
AND
AND
AND
EName
Job, Employee, Group
Group.ENO=Employee.ENO
Group.JNO=Job.JNO
EName“C. Wu”
Job.Name=“Database”
AND
DUR=12 ;
7