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

Question

<p>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)<br /><br />A&#160;&#160; B&#160;&#160; C<br />a1 b1 c1<br />a1 b2 c2<br />&#160; r(R)<br /><br />B&#160;&#160;&#160;C&#160;&#160; D<br />b1 c1 d1<br />b1 c3 d3<br />&#160;&#160; s(S)<br /><br />a. please give the result of the Cartesian product of r and s, r ? s.<br />b. please give the result of r join s, r ? r.C= s.C s.<br />c. please give the result of r natural join s, r * s.<br />d. please give the result of r semi-join s, r ? s.<br />e. please give the result of s semi-join r, s ? r.<br />f. please give the result of r left outer join s.<br />g. please give the result of r right outer join s.<br />h. please give the result of s left outer join r.<br />i. please give the result of s right outer join r.<br />j. please give the result of r full outer join s.</p>

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 #
• Subjects

---

---

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