Suppose database table \(𝑇1(𝑃,𝑅)\) currently has tuples…
2016
Suppose database table \(𝑇1(𝑃,𝑅)\) currently has tuples \((10,5),(15,8),(25,6)\) and table \(𝑇2(𝐴,𝐶)\) currently has \((10,6),(25,3),(10,5)\). Consider the following three relational algebra queries \(𝑅𝐴1,𝑅𝐴2\) and \(𝑅𝐴3\):
\(𝑅𝐴1\) : \(T1 \Join _{T1.P = T2.A}\ T2\) where \(\Join\) is the join symbol (applied here using the stated condition)
\(𝑅𝐴2\) : \(T1 =\Join _{T1.P = T2.A}\ T2\) where \(=\Join\) is left outer join symbol
\(𝑅𝐴3\) : \(T1 \Join_{T1.P = T2.A}\) and \(_{T1.R = T2.C} T2\)
The number of tuples in the resulting table of \(RA1, RA2\) and \(RA3\) are given by :
- A.
2,4,2 respectively
- B.
2,3,2 respectively
- C.
3,3,1 respectively
- D.
3,4,1 respectively
Attempted by 81 students.
Show answer & explanation
Correct answer: D
Concept:
A join evaluated over a condition produces one output tuple for every pair of rows (one from each relation) that satisfies the condition — if a value on one side matches k rows on the other side, that value contributes k separate tuples, not one.
A LEFT outer join does everything a plain join does, but additionally keeps every row of the left (preserved) relation even when it finds no partner — such a row still appears exactly once in the result, with NULLs filling the missing right-side attributes.
A join restricted by a CONJUNCTION of two equality conditions (both P = A and R = C, say) counts a pair only when every one of those equalities holds for that pair simultaneously — it is not the union of the pairs that satisfy each condition on its own.
Application:
T1 row (P,R) | T2 rows with A = P | Of those, C = R too? |
|---|---|---|
(10,5) | (10,6) and (10,5) — 2 rows | (10,5) only — 1 row |
(15,8) | none — 0 rows | — |
(25,6) | (25,3) — 1 row | none — 0 rows |
RA1 (the plain join on P = A) sums the middle column: 2 + 0 + 1 = 3 tuples.
RA2 (the LEFT outer join on P = A) keeps the same matched tuples as RA1, plus the unmatched row (15,8) contributes one extra row of its own (T2 side NULL): 2 + 0 + 1 + 1 = 4 tuples.
RA3 (join requiring both P = A and R = C) sums the right-hand column: 1 + 0 + 0 = 1 tuple.
Cross-check:
RA2 must equal RA1 plus the number of T1 rows with zero matches — here 3 + 1 = 4, which matches. RA3 must be no larger than RA1 since it applies a strictly tighter condition on the same pairs — 1 ≤ 3, which also holds.
Therefore the resulting tuple counts for RA1, RA2 and RA3 are 3, 4 and 1 respectively.
A video solution is available for this question — log in and enroll to watch it.