need in 6 hours

1

Problem 1

a) Suppose there are two relations, R1 and R2. Relation R1(A,B) has the tuples:

A B

0 1

2 3

4 5

and the relation R2(B,C,D) has tuples:

B C D

1 3 5

3 5 7

3 6 8

Compute the natural join of R1 and R2.

2

b ) Suppose there are two relations, R1 and R2. Relation R1(A,B) has the tuples:

A B

0 1

2 3

4 5

and the relation R2(B,C,D) has tuples:

B C D

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)

3

c ) Suppose there are two relations, R1 and R2. Relation R1(A,B,C) has the

tuples:

A B C

2 3 4

5 3 4

5 6 7

3 6 4

2 3 7

and relation R2(A,B,C) has the tuples:

A B C

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) =?

4

d ) Suppose there are two relations, R1 and R2. Relation R1(A,B,C) has the tuples:

A B C

1 2 3

1 2 3

4 5 6

2 5 3

1 2 6

and relation R2(A,B,C) has the tuples:

A B C

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:

A B C

0 1 2

0 1 3

4 5 6

4 6 3

Compute the generalized projection πB,A+C,B(S)

5

f ) Suppose relation S(A,B,C) has the tuples:

A B C

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:

A B C

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)

6

h ) Suppose there are two relations, R1 and R2. Relation R1(A,B) has the tuples:

A B

0 1

2 3

4 5

and the relation R2(B,C,D) has tuples:

B C D

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‘)

7

Problem#3

Translate the following SQL statements into Relational Algebra Tree.

SELECT EName

FROM Job, Employee, Group

WHERE Group.ENO=Employee.ENO

AND Group.JNO=Job.JNO

AND EName<>“C. Wu”

AND Job.Name=“Database”

AND DUR=12 ;