B.Given the relational schemas R(ABC) and S(BCD), let r(R) and s(S) be the relat

Question

B.Given the relational schemas R(ABC) and S(BCD), let r(R) and s(S) be the relations corresponding to R and S respectively as the following: (55 points)

A B C
a1 b1 c1
a1 b2 c2
r(R)

B C D
b1 c1 d1
b1 c3 d3
s(S)

a. please give the result of the Cartesian product of r and s, r ? s.
b. please give the result of r join s, r ? r.C= s.C s.
c. please give the result of r natural join s, r * s.
d. please give the result of r semi-join s, r ? s.
e. please give the result of s semi-join r, s ? r.
f. please give the result of r left outer join s.
g. please give the result of r right outer join s.
h. please give the result of s left outer join r.
i. please give the result of s right outer join r.
j. please give the result of r full outer join s.

Explanation / Answer

a. Cartesian product of r and s, r ??s.
SQL> run 1a.sql
1 select *
2* from r, s

A B C C D E
-- -- -- -- -- --
a2 b2 c2 c1 d1 e1
a3 b3 c3 c1 d1 e1
a2 b2 c2 c2 d2 e2
a3 b3 c3 c2 d2 e2

b. r join s, r |X| r.C= s.C s.
SQL> run 1b.sql
1 select *
2 from r, s
3* where r.C = s.C

A B C C D E
-- -- -- -- -- --
a2 b2 c2 c2 d2 e2


c. r natural join s, r * s.
SQL> run 1c.sql
1 select r.A, r.B, r.C, s.D, s.E
2 from r, s
3* where r.C = s.C

A B C D E
-- -- -- -- --
a2 b2 c2 d2 e2

d. r semi-join s, r |X s.
SQL> run 1d.sql
1 select *
2 from r
3 where exists
4 (select *
5 from s
6* where r.C = s.C)

A B C
-- -- --
a2 b2 c2

e. s semi-join r, s |X r.
SQL> run 1e.sql
1 select *
2 from s
3 where exists
4 (select *
5 from r
6* where s.C = r.C)

C D E
-- -- --
c2 d2 e2

f. r left outer join s.
SQL> run 1f.sql
1 select *
2 from r, s
3* where r.C = s.C(+)

A B C C D E
-- -- -- -- -- --
a2 b2 c2 c2 d2 e2
a3 b3 c3


g. r right outer join s.
SQL> run 1g.sql
1 select *
2 from r, s
3* where r.C(+) = s.C

A B C C D E
-- -- -- -- -- --
c1 d1 e1
a2 b2 c2 c2 d2 e2

h. s left outer join r.
SQL> run 1h.sql
1 select *
2 from r, s
3* where s.C = r.C(+)

A B C C D E
-- -- -- -- -- --
c1 d1 e1
a2 b2 c2 c2 d2 e2

i. s right outer join r.
SQL> run 1i.sql
1 select *
2 from r, s
3* where s.C(+) = r.C

A B C C D E
-- -- -- -- -- --
a2 b2 c2 c2 d2 e2
a3 b3 c3

j. r full outer join s.
SQL> run 1j.sql
1 select *
2 from r, s
3 where r.C = s.C(+)
4 union
5 select *
6 from r, s
7* where r.C(+) = s.C

A B C C D E
-- -- -- -- -- --
a2 b2 c2 c2 d2 e2
a3 b3 c3
c1 d1 e1

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.