Consider the following three tables π ,π and π. In this question, all theβ¦
2015
Consider the following three tablesΒ π ,πΒ andΒ π.Β In this question, all the join operations are natural joinsΒ (β).Β (π)Β is the projection operation of a relation:
\(\begin{array}{|ccc|} \hline & R & \\ \hline A && B \\ \hline 1 && 2 \\ 3 && 2 \\ 5 && 6 \\ 7 & &8 \\ 9 && 8 \\ \hline \end{array} \begin{array}{|ccc|} \hline & S & \\ \hline B && C \\ \hline 6 && 2 \\ 2 && 4 \\ 8 && 1 \\ 8 & &3 \\ 2 && 5 \\ \hline \end{array} \begin{array}{|ccc|} \hline & T & \\ \hline A && C \\ \hline 7 && 1 \\ 1 && 2 \\ 9 && 3 \\ 5 & &4 \\ 3 && 5 \\ \hline \end{array}\)
Possible answer tables for this question are also given as below:
\(\underset{\text{(a)}}{\begin{array}{|ccc|} \hline A & B & C \\ \hline 1 & 2& 4 \\ 1 &2& 5 \\ 3 &2& 4 \\ 3 &2& 5 \\ 5 & 6 &2 \\ 7 & 8 & 1 \\ 7 & 8 & 3 \\ 9 & 8 & 1 \\ 9 & 8 & 3 \\ \hline \end{array}} \underset{\text{(b)}}{\begin{array}{|ccc|} \hline A & B & C \\ \hline 1 & 2& 2 \\ 3 &2& 5 \\ 5 &6& 4 \\ 7 &8& 1 \\ 9 & 8 &3 \\ \hline \end{array}} \underset{\text{(c)}}{\begin{array}{|ccc|} \hline A & B & C \\ \hline 1 & 6& 2 \\ 3 &2& 5 \\ 5 &2& 4 \\ 7 &8& 1 \\ 9 & 8 &3 \\ \hline \end{array}} \underset{\text{(d)}}{\begin{array}{|ccc|} \hline A & B & C \\ \hline 3 & 2& 5 \\ 7 &8& 1 \\ 9 & 8 &3 \\ \hline \end{array}}\)
What is the resulting table ofΒ \(π_{ A,B} (R \bowtie T) \bowtie π_{B,C} (S \bowtie T)\)Β ?
- A.
(a)
- B.
(b)
- C.
(c)
- D.
(d)
Attempted by 81 students.
Show answer & explanation
Correct answer: A
Solution: follow the specified sequence of natural joins and projections.
Step 1: Compute the natural join of R and T on attribute A. Combine matching A values to get (A,B,C) rows: (1,2,2), (3,2,5), (5,6,4), (7,8,1), (9,8,3)
Step 2: Project A,B from that result: Ο_{A,B}(R β T) = (1,2), (3,2), (5,6), (7,8), (9,8)
Step 3: Compute the natural join of S and T on attribute C. Combine matching C values to get (A,B,C) rows: (1,6,2), (5,2,4), (7,8,1), (9,8,3), (3,2,5)
Step 4: Project B,C from that result: Ο_{B,C}(S β T) = (6,2), (2,4), (8,1), (8,3), (2,5) (which is the original S).
Step 5: Now take the natural join of Ο_{A,B}(R β T) and Ο_{B,C}(S β T) on attribute B. This pairs each A,B with every matching B,C to produce the final (A,B,C) rows:
(1,2,4)
(1,2,5)
(3,2,4)
(3,2,5)
(5,6,2)
(7,8,1)
(7,8,3)
(9,8,1)
(9,8,3)
Conclusion: The resulting table is the nine-row table with rows (1,2,4), (1,2,5), (3,2,4), (3,2,5), (5,6,2), (7,8,1), (7,8,3), (9,8,1), (9,8,3).
A video solution is available for this question β log in and enroll to watch it.