Relational Algebra MCQs (for GATE DBMS) —
Each question includes 4 options (A–D) and a complete solution.
RELATIONAL ALGEBRA
(Concepts covered: Selection, Projection, Join, Union, Difference, Rename, Cartesian Product, Division, etc.)
Q1.
Let relation Employee(EID, Name, Salary, DeptID).
Which relational algebra query retrieves names of employees earning more than ₹60,000?
A. σ(Salary > 60000)(Employee)
B. π(Name)(σ(Salary > 60000)(Employee))
C. π(Salary)(σ(Salary > 60000)(Employee))
D. σ(Name > 60000)(Employee)
✅ Answer: B
Solution:
- σ filters rows, π projects columns.
- We first select tuples with Salary > 60000, then project Name → π(Name)(σ(Salary > 60000)(Employee)).
Q2.
Which operator removes duplicate tuples in relational algebra?
A. Selection
B. Projection
C. Union
D. None (duplicates not allowed inherently)
✅ Answer: D
Solution: Relational algebra works on sets, and sets inherently do not allow duplicates.
Q3.
Given R(A,B) and S(B,C), which query finds all pairs (A,C) where tuples of R and S share same B?
A. R × S
B. R ⨝ S
C. R ⋈ (R.B = S.B) S
D. π(A,C)(σ(R.B = S.B)(R × S))
✅ Answer: D
Solution:
Join is implemented as σ + × → σ(R.B = S.B)(R × S).
Then project A and C → π(A,C)(…).
Q4.
If R = {(1,2),(2,3)} and S = {(2,3),(3,4)}, what is R ∪ S?
A. {(1,2),(2,3),(3,4)}
B. {(1,2),(3,4)}
C. {(2,3)}
D. ∅
✅ Answer: A
Solution: Union adds all unique tuples from both → {(1,2),(2,3),(3,4)}.
Q5.
If R(A,B) = {(1,2),(2,3)}, what is π(A)(R)?
A. {(1),(2)}
B. {(2)}
C. {(1,2)}
D. {(3)}
✅ Answer: A
Solution: Project A → keeps only first column → {1,2}.
Q6.
Let Course(CID, CName, Credits).
To find all courses with more than 3 credits:
A. σ(Credits ≥ 3)(Course)
B. σ(Credits > 3)(Course)
C. π(CName)(σ(Credits > 3)(Course))
D. π(Credits)(σ(Credits > 3)(Course))
✅ Answer: C
Solution: Need course names only → select first, then project CName.
Q7.
For R(A,B) and S(A,B), R − S means:
A. Tuples in both
B. Tuples only in S
C. Tuples in R but not in S
D. Cartesian product
✅ Answer: C
Solution: Set difference → returns tuples in R but not in S.
Q8.
Which of the following relational algebra expressions is commutative?
A. R − S
B. R × S
C. R ∪ S
D. R ÷ S
✅ Answer: C
Solution: Union and intersection are commutative. Difference, Cartesian product, division are not.
Q9.
Which operator corresponds to the WHERE clause in SQL?
A. π (Projection)
B. σ (Selection)
C. × (Product)
D. ∪ (Union)
✅ Answer: B
Solution: Selection (σ) filters tuples — analogous to WHERE in SQL.
Q10.
If R(A,B) = {(1,2),(3,4)} and S(B,C) = {(2,5),(4,6)}, result of R ⨝ S?
A. {(1,2,5),(3,4,6)}
B. {(1,5),(3,6)}
C. {(1,2),(3,4)}
D. None
✅ Answer: A
Solution: Join on B: matches (1,2) with (2,5), (3,4) with (4,6) ⇒ {(1,2,5),(3,4,6)}.
Q11.
In relational algebra, π and σ operations can be reordered when:
A. They involve same attributes
B. σ condition uses only attributes in projection
C. They can never be reordered
D. They depend on join condition
✅ Answer: B
Solution: Safe if selection condition uses only attributes present in projection — otherwise data lost.
Q12.
Let R(A,B,C). To find tuples where A > 10 and B = 5:
A. σ(A>10)(σ(B=5)(R))
B. σ(A>10 ∧ B=5)(R)
C. Both A and B
D. None
✅ Answer: C
Solution: Both expressions are equivalent by the cascade of selection rule.
Q13.
Let R(A,B) and S(B,C). Which gives all combinations of tuples?
A. R × S
B. R ⋈ S
C. R − S
D. R ÷ S
✅ Answer: A
Solution: Cartesian product pairs each R tuple with each S tuple.
Q14.
If R(A,B) has 5 tuples and S(C,D) has 4 tuples, then R × S has how many tuples?
A. 9
B. 20
C. 10
D. 1
✅ Answer: B
Solution: |R×S| = |R| × |S| = 5×4 = 20.
Q15.
Which of these operations may reduce the number of attributes?
A. σ (Selection)
B. π (Projection)
C. ∪ (Union)
D. × (Product)
✅ Answer: B
Solution: Projection keeps only selected columns → fewer attributes.
Q16.
If R(A,B) = {(1,2),(2,3),(3,4)}, find π(B)(σ(A>1)(R)).
A. {2,3,4}
B. {3,4}
C. {1,2}
D. {4}
✅ Answer: B
Solution: σ(A>1): {(2,3),(3,4)} → π(B): {3,4}.
Q17.
The result of σ(A=B)(R × S) is called:
A. Natural Join
B. Cartesian Join
C. Equi-Join
D. Semi-Join
✅ Answer: C
Solution: Equality condition on attributes from two relations → equi-join.
Q18.
The natural join is a special case of:
A. Equi-Join with duplicate columns removed
B. Cartesian Product
C. Intersection
D. None
✅ Answer: A
Solution: Natural join = equi-join + remove duplicate join attributes.
Q19.
If R(A,B) = {(1,2),(2,3)} and S(B,C) = {(2,4),(3,5)}, π(A,C)(R ⋈ S) = ?
A. {(1,4),(2,5)}
B. {(1,5),(2,4)}
C. {(1,2),(3,4)}
D. {(1,3)}
✅ Answer: A
Solution: Join on B:
- (1,2) matches (2,4) → (1,4)
- (2,3) matches (3,5) → (2,5).
Q20.
Let Student(SID, Name, DeptID), Department(DeptID, DName).
Find student-department pairs using relational algebra:
A. Student × Department
B. Student ⋈ Department
C. π(Name,DName)(Student × Department)
D. σ(DeptID=DeptID)(Student × Department)
✅ Answer: B
Solution: Join Student and Department on DeptID → Student ⋈ Department.
Q21.
Which operator combines union-compatible relations?
A. ∪
B. ⋈
C. ×
D. ÷
✅ Answer: A
Solution: Union requires same degree and compatible domains.
Q22.
Which of these is not a basic relational algebra operator?
A. Selection
B. Projection
C. Natural Join
D. Cartesian Product
✅ Answer: C
Solution: Natural join is a derived operator (built using σ and ×).
Q23.
Division (÷) in relational algebra is used to:
A. Match tuples with all combinations in another relation
B. Compute difference
C. Remove duplicates
D. Perform cross join
✅ Answer: A
Solution: Division finds tuples related to all values in the divisor relation.
Q24.
If R(A,B) = {(1,2),(1,3),(2,3)} and S(B) = {(2),(3)}, then R ÷ S = ?
A. {1,2}
B. {1}
C. {2}
D. ∅
✅ Answer: B
Solution: A=1 appears with both B=2 and 3 → only 1 qualifies ⇒ {1}.
Q25.
Which algebraic expression is equivalent to the INTERSECT operation in SQL?
A. R − (R − S)
B. (R × S)
C. π(R)(S)
D. σ(R=S)(R×S)
✅ Answer: A
Solution: Intersection can be expressed as R − (R − S).
Perfect ✅
Here’s Section 2 (Q26–Q50) — next 25 fully solved, plagiarism-free GATE-level MCQs on Relational Algebra, focusing on joins, division, renaming, nested operations, query optimization, and set properties.
SECTION 2 — Relational Algebra (Q26–Q50)
Q26.
If relation R(A,B,C) has 8 tuples and S(B,C,D) has 10 tuples, and there are no matching values for join attributes B and C, then |R ⋈ S| = ?
A. 80
B. 0
C. 8
D. 10
✅ Answer: B
Solution:
Join returns only matching tuples. Since there are no matches → result = ∅ ⇒ 0 tuples.
Q27.
Which of the following can increase the number of tuples?
A. Selection
B. Projection
C. Cartesian Product
D. Intersection
✅ Answer: C
Solution:
Cartesian Product pairs every tuple of R with every tuple of S, multiplying the count.
Q28.
If R(A,B) and S(A,B) are identical, what is R − S?
A. R
B. S
C. ∅
D. R ∪ S
✅ Answer: C
Solution:
Since both have identical tuples, nothing remains after subtraction.
Q29.
Which operation corresponds to the EXCEPT clause in SQL?
A. Union
B. Intersection
C. Difference
D. Product
✅ Answer: C
Solution:
SQL’s EXCEPT ≡ Relational Algebra’s “−” (Difference).
Q30.
Let R(A,B,C) and S(C,D).
The result of π(A,D)(R ⋈ S) contains how many attributes?
A. 2
B. 3
C. 4
D. 5
✅ Answer: A
Solution:
Projection keeps only A and D ⇒ 2 attributes.
Q31.
Which operation may decrease the number of tuples?
A. Selection
B. Cartesian Product
C. Union
D. Rename
✅ Answer: A
Solution:
Selection applies a condition that can filter out tuples → reducing count.
Q32.
R(A,B,C) = {(1,2,3),(2,2,4)}
What is π(B)(R)?
A. {2,2}
B. {2}
C. {2,3,4}
D. ∅
✅ Answer: B
Solution:
Projection removes duplicates → unique {2}.
Q33.
The θ-join generalizes:
A. Equi-Join
B. Natural Join
C. Cross Join
D. Outer Join
✅ Answer: A
Solution:
θ-join allows any comparison operator (>,<,=,≠,≤,≥).
Equi-join is θ-join with condition “=”.
Q34.
In relational algebra, ρ is used for:
A. Rename
B. Remove duplicates
C. Join
D. Division
✅ Answer: A
Solution:
ρ (rho) renames relations or attributes. Example: ρ(NewName)(R).
Q35.
For relations R(A,B) and S(B,C), which expression gives the natural join?
A. σ(R.B=S.B)(R × S)
B. π(A,B,C)(σ(R.B=S.B)(R × S))
C. R ∪ S
D. R − S
✅ Answer: B
Solution:
Natural join = equi-join + projection removing duplicate join attributes.
Q36.
If |R|=10 and |S|=5, and each tuple of R matches exactly 2 tuples in S on join condition, what is |R ⋈ S|?
A. 15
B. 5
C. 20
D. 10
✅ Answer: C
Solution:
Each of 10 tuples joins with 2 ⇒ 10×2=20 tuples.
Q37.
Which of the following cannot be expressed using basic relational algebra?
A. Selection
B. Transitive closure
C. Projection
D. Cartesian product
✅ Answer: B
Solution:
Relational algebra is not recursive — transitive closure (e.g., reachability) not directly expressible.
Q38.
Given relations:
R(A,B) = {(1,2),(2,3)}
S(B) = {(2)}
What is R ÷ S?
A. {(1)}
B. {(2)}
C. {(1,2)}
D. ∅
✅ Answer: A
Solution:
A=1 satisfies B=2 → result {1}.
Q39.
Which operation can be used to rename an attribute B to C in relation R(A,B)?
A. π(A,C)(R)
B. ρ(A,C)(R)
C. ρ(R(A,C))(R)
D. σ(A=C)(R)
✅ Answer: C
Solution:
ρ allows renaming schema → ρ(R(A,C))(R) means attribute B renamed to C.
Q40.
If R and S are relations, then
σ(condition)(R ⋈ S) ≡ ?
A. (σ(condition)(R)) ⋈ S
B. R ⋈ (σ(condition)(S))
C. Depends on attributes in condition
D. Always true
✅ Answer: C
Solution:
Condition must involve attributes of only one relation for pushdown to be valid.
Q41.
Which operation is idempotent in relational algebra?
A. Union
B. Intersection
C. Projection
D. Selection
✅ Answer: D
Solution:
σ(condition)(σ(condition)(R)) = σ(condition)(R) → idempotent.
Q42.
If R(A,B) and S(A,B) are relations, then (R ∩ S) = ?
A. R − S
B. R − (R − S)
C. (R ∪ S) − (R − S)
D. (R × S)
✅ Answer: B
Solution:
Intersection = R − (R − S).
Q43.
If R(A,B) = {(1,2),(2,3),(3,4)}
Find σ(B>2)(R).
A. {(2,3),(3,4)}
B. {(1,2)}
C. {(3,4)}
D. {(1,2),(2,3)}
✅ Answer: A
Solution:
Select tuples where B>2 ⇒ {(2,3),(3,4)}.
Q44.
What does π(A)(σ(B=10)(R)) do?
A. Select tuples where B=10, then project A
B. Project A, then filter B=10
C. Same as R − S
D. None
✅ Answer: A
Solution:
Selection first filters → then projection selects column A.
Q45.
Which algebraic expression finds employees working in all departments?
A. Employee ÷ Department
B. Department ÷ Employee
C. Employee − Department
D. Employee ∪ Department
✅ Answer: A
Solution:
Division used for “for all” queries → employees related to all departments.
Q46.
If R(A,B) has attributes renamed as R1(X,Y) using ρ, which of the following is correct?
A. ρ(X,Y)(R)
B. ρR1(X,Y)(R)
C. π(X,Y)(R)
D. σ(X=Y)(R)
✅ Answer: B
Solution:
ρR1(X,Y)(R) renames relation and its attributes.
Q47.
What does the expression σ(A=10)(R × S) represent?
A. Selection on join
B. Filtering R × S using attribute A
C. Division
D. Union
✅ Answer: B
Solution:
σ applies condition A=10 to the Cartesian product result.
Q48.
If R(A,B) and S(C,D), which operation is not possible?
A. R × S
B. R ∪ S
C. R − S
D. R ÷ S
✅ Answer: B
Solution:
Union requires same schema. Here R and S have different attributes.
Q49.
If R(A,B,C) has 4 tuples, what is the maximum number of tuples in π(A,B)(R)?
A. 4
B. 8
C. 16
D. Unlimited
✅ Answer: A
Solution:
Projection can only reduce or retain number of tuples (never increase).
Q50.
Which of the following operations is associative?
A. Union
B. Intersection
C. Join
D. All of the above
✅ Answer: D
Solution:
Union, intersection, and join are associative:
(R ⋈ S) ⋈ T = R ⋈ (S ⋈ T).
SECTION 3 — Relational Algebra (Q51–Q75)
Q51.
Let R(A,B,C) and S(B,C,D).
Which relational algebra expression finds tuples of R that have a matching (B,C) in S?
A. R ⋈ S
B. R × S
C. R − (R − π(A,B,C)(R ⋈ S))
D. π(A,B,C)(R ⋈ S)
✅ Answer: D
Solution:
Join on common attributes (B,C) and project A,B,C ⇒ only those with matches remain.
Q52.
If R(A,B) and S(B,C), which query finds all A values that are paired with some C = 10 in S?
A. π(A)(R ⋈ σ(C=10)(S))
B. σ(C=10)(R ⋈ S)
C. π(A)(R × S)
D. π(A)(R − S)
✅ Answer: A
Solution:
Select tuples from S where C=10, join with R on B, and project A.
Q53.
Which of these expressions is equivalent to R ⋈ S?
A. σ(R.B=S.B)(R × S)
B. σ(R=A)(R × S)
C. σ(R.B=S.C)(S × R)
D. None
✅ Answer: A
Solution:
Join = selection on equality after Cartesian product.
Q54.
If R(A,B) = {(1,2),(2,3)} and S(A,B) = {(2,3),(3,4)}, what is R ∩ S?
A. {(2,3)}
B. {(1,2)}
C. {(3,4)}
D. ∅
✅ Answer: A
Solution:
Only (2,3) is common in both → intersection.
Q55.
Let R(A,B) and S(A,B). Which expression retrieves tuples only in R but not in S?
A. R ∪ S
B. R − S
C. R ⋈ S
D. π(A)(R)
✅ Answer: B
Solution:
Set difference keeps tuples unique to R.
Q56.
If |R| = 15, |S| = 5, and each tuple in S matches three tuples in R on join attribute, what is |R ⋈ S|?
A. 15
B. 5
C. 45
D. 75
✅ Answer: C
Solution:
Each of 5 tuples in S matches 3 in R ⇒ 5×3 = 15 matches.
Or equivalently, 3 matches × 15 R tuples = 45.
Q57.
Which relational algebra law allows pushing selection below join?
A. Commutativity
B. Associativity
C. Selection Pushdown
D. Projection Pushdown
✅ Answer: C
Solution:
σ(condition)(R ⋈ S) = (σ(condition)(R)) ⋈ S
— if condition involves only R’s attributes.
Q58.
Which operator’s order does not affect result?
A. Join
B. Cartesian Product
C. Union
D. Difference
✅ Answer: C
Solution:
Union is commutative: R ∪ S = S ∪ R.
Q59.
Given R(A,B) and S(C,D), which operation yields a 4-attribute result?
A. R × S
B. R ⋈ S
C. R − S
D. π(A)(R)
✅ Answer: A
Solution:
Cartesian product combines all attributes (A,B,C,D).
Q60.
For R(A,B,C) and S(C,D), find an expression equivalent to SQL:SELECT A,D FROM R,S WHERE R.C=S.C;
A. π(A,D)(R × S)
B. π(A,D)(σ(R.C=S.C)(R × S))
C. π(A,D)(R − S)
D. π(A,D)(R ⋈ S)
✅ Answer: B
Solution:
Condition R.C=S.C defines a join after product, then projection.
Q61.
What is the output schema of π(A,B)(σ(C>10)(R(A,B,C)))?
A. (A,B,C)
B. (A,B)
C. (C)
D. None
✅ Answer: B
Solution:
Projection keeps only A and B attributes.
Q62.
If R(A,B) = {(1,2),(1,3)} and S(B) = {(2)}, then R ÷ S = ?
A. {1}
B. {2}
C. ∅
D. {(1,2)}
✅ Answer: A
Solution:
A=1 satisfies all B in S ⇒ {1}.
Q63.
Which of these relational operations is non-monotonic (result may decrease if more tuples are added)?
A. Union
B. Projection
C. Difference
D. Cartesian Product
✅ Answer: C
Solution:
Adding tuples can reduce result size in difference.
Q64.
R(A,B) = {(1,2),(2,3),(2,4)}
What is π(A)(σ(B>2)(R))?
A. {2}
B. {1,2}
C. {1}
D. ∅
✅ Answer: A
Solution:
σ(B>2): {(2,3),(2,4)} → π(A): {2}.
Q65.
Let R(A,B) = {(1,2),(1,3),(2,4)}.
How many tuples in π(A)(R)?
A. 3
B. 2
C. 4
D. 1
✅ Answer: B
Solution:
Projection removes duplicates → A values {1,2} ⇒ 2 tuples.
Q66.
The number of tuples in π(A)(R) can be at most:
A. Equal to |R|
B. Greater than |R|
C. Always 1
D. 2×|R|
✅ Answer: A
Solution:
Projection ≤ original number of tuples (cannot add new rows).
Q67.
If R(A,B) and S(B,C) are joined using R ⋈ S, which attributes appear once in the result?
A. A,B,C
B. A,B,B,C
C. A,B
D. B,C
✅ Answer: A
Solution:
Join merges common attributes (B) once only → A,B,C.
Q68.
For any relation R, what is R ∪ ∅ ?
A. R
B. ∅
C. Undefined
D. R × ∅
✅ Answer: A
Solution:
Union with empty set yields R.
Q69.
What is the result of R ∩ ∅ ?
A. ∅
B. R
C. R − ∅
D. Undefined
✅ Answer: A
Solution:
No tuple common with empty relation.
Q70.
For R(A,B), which query returns all unique values of A?
A. π(A)(R)
B. σ(A)(R)
C. ρ(A)(R)
D. π(B)(R)
✅ Answer: A
Solution:
Projection over A yields distinct values of A.
Q71.
Let R(A,B), S(B,C), T(C,D).
Find all (A,D) pairs connected through B and C:
A. π(A,D)(R ⋈ S ⋈ T)
B. R × S × T
C. R ÷ S
D. π(A,D)(σ(R.B=S.B ∧ S.C=T.C)(R × S × T))
✅ Answer: D
Solution:
Join all three relations via conditions on B and C, then project A,D.
Q72.
If relation R has 3 attributes, how many distinct non-empty projections exist?
A. 3
B. 7
C. 6
D. 8
✅ Answer: B
Solution:
For n attributes → 2ⁿ − 1 possible non-empty subsets = 2³−1=7.
Q73.
Which operator is used to eliminate duplicate attributes after a join?
A. π
B. σ
C. ρ
D. None (automatic)
✅ Answer: A
Solution:
Projection used post-join to remove redundant join attributes.
Q74.
If R(A,B) = {(1,2),(2,3)} and S(A,B) = {(2,3),(3,4)}, find R ∪ S.
A. {(1,2),(2,3),(3,4)}
B. {(2,3)}
C. {(1,2),(3,4)}
D. ∅
✅ Answer: A
Solution:
Union merges all distinct tuples from both.
Q75.
Which expression represents join followed by projection?
A. π(A,C)(R ⋈ S)
B. σ(A=C)(R × S)
C. R ÷ S
D. R ∪ S
✅ Answer: A
Solution:
Join first combines matching tuples, projection extracts required columns (A,C).
Great — here is Section 4 (Questions 76–100): the final 25 advanced, plagiarism-free Relational Algebra MCQs tailored for GATE (DBMS). Each question has four options (A–D), the correct answer, and a concise but complete solution.
SECTION 4 — Relational Algebra (Q76–Q100)
Q76.
Relations: R(A,B) = {(1,2),(1,3),(2,3)}, S(B) = {(2),(3)}.
What is R ÷ S?
A. {1}
B. {1,2}
C. {2}
D. ∅
✅ Answer: B
Solution:
We need A values that pair with all B in S = {2,3}.
A=1 pairs with B=2 and B=3 → qualifies.
A=2 pairs only with B=3 → does not qualify.
So result = {1}.
(Wait — check R: (1,2),(1,3),(2,3) ⇒ only 1 qualifies.)
Final Answer: A — {1}.
Q77.
Let R(A,B) and S(B,C). Which expression computes the semijoin of R with S (tuples of R that have matches in S)?
A. R ⋈ S
B. π_R.attributes(σ(R.B=S.B)(R × S))
C. π(A,B)(R ⋈ S)
D. R − (R ⋉̸ S) (antijoin)
✅ Answer: C
Solution:
Semijoin R ⋉ S returns R tuples with matches in S. Implementation: perform join and project R attributes → π(A,B)(R ⋈ S).
Q78.
Which relational algebra expression is equivalent to SQL:SELECT DISTINCT A FROM R WHERE NOT EXISTS (SELECT * FROM S WHERE S.B = R.B)?
A. π(A)(R − (R ⋈ S))
B. π(A)(R − π(R.attributes)(R ⋈ S))
C. π(A)(R − (π(A,B)(R) ⋈ S))
D. π(A)(R − π(A,B)(R ⋈ S))
✅ Answer: D
Solution:
We need R tuples whose B has no match in S. Compute join R⋈S, project A,B to remove extra attrs, then difference from R and project A. Correct form: π(A)(R − π(A,B)(R ⋈ S)).
Q79.
Given R(A,B) = {(1,2),(2,3)} and S(A,B) = {(2,3),(3,4)}, what is π(A)(R ∩ S)?
A. {1}
B. {2}
C. {1,2}
D. ∅
✅ Answer: B
Solution:
R ∩ S = {(2,3)} → π(A) = {2}.
Q80.
Which of the following is NOT a valid algebraic equivalence?
A. σ(c)(R ⋈ S) = σ(c)(R) ⋈ S if c references only R attrs
B. π(attrs)(R ⋈ S) = π(attrs)(R) ⋈ S always
C. σ(c1 ∧ c2)(R) = σ(c1)(σ(c2)(R))
D. (R ∪ S) − T = (R − T) ∪ (S − T)
✅ Answer: B
Solution:
Projection cannot be pushed into join arbitrarily; π(attrs)(R ⋈ S) equals π(attrs)(π_R(attrs_R)(R) ⋈ π_S(attrs_S)(S)) only if attrs split appropriately. So B is not always valid.
Q81.
Let Students(Sid, Sname) and Enroll(Sid, Cid). Which RA expression lists students who enrolled in no course?
A. Students − (Students ⋈ Enroll)
B. Students − π(Sid,Sname)(Students ⋈ Enroll)
C. π(Sname)(Students − π(Sid)(Enroll))
D. π(Sname)(Students ⋉̸ Enroll) (antijoin projection)
✅ Answer: D
Solution:
Antijoin Students ⋉̸ Enroll returns students with no matching Sid in Enroll; projecting Sname yields required list. Equivalent expression: π(Sname)(Students − π(Sid)(Enroll) ⋈ Students) but D is direct.
Q82.
If |R| = 100, |S| = 50, and join condition yields selectivity 0.1 (i.e., each R tuple matches 0.1 S tuples on average), estimate |R ⋈ S|.
A. 5
B. 10
C. 500
D. 50
✅ Answer: C
Solution:
If each R tuple matches 0.1 S tuples on average, total matches = 100 × 0.1 = 10. But selectivity often defined as fraction of pairs: if selectivity = 0.1 of all possible pairs 100×50=5000 ⇒ 0.1×5000 = 500 matches. Common interpretation here: answer C = 500.
Q83.
Which relational algebra operator is most appropriate to express: “Find items bought by every customer”?
A. Natural Join
B. Division
C. Intersection
D. Antijoin
✅ Answer: B
Solution:
Division expresses “for all” queries: Items ÷ CustomersBought produces items bought by every customer.
Q84.
Given relation R(A,B,C) and expression π(A)(σ(B=5)(R)), which optimization is safe?
A. σ(B=5)(π(A)(R))
B. π(A)(σ(B=5)(R)) cannot be changed
C. π(A,B)(σ(B=5)(R))
D. π(A)(R) first then σ(B=5) is safe
✅ Answer: A (but check)
Solution:
You cannot push projection before selection if selection uses attribute B and projection removes B. Here projection keeps A only — removing B — so pushing π before σ would lose information. So the safe form is the original; pushing projection is not safe.
Final Answer: B — the original expression must be kept; (A) is not safe.
Q85.
Let R(A,B), S(B,C), T(C,D). Which expression yields pairs (A,D) where there exists a path A→B→C→D?
A. π(A,D)(R ⋈ S ⋈ T)
B. π(A,D)(R × S × T)
C. R ÷ (S ⋈ T)
D. π(A,D)(σ(R.B=S.B ∧ S.C=T.C)(R × S × T))
✅ Answer: D (and A is equivalent if natural join used)
Solution:
Both A and D represent same idea. If joins are natural and attributes named to match, A is succinct. D is explicit via product+selection. We’ll accept A (join form) as correct in exam context.
Final Answer: A.
Q86.
Which of these is the antijoin (R ⋉̸ S) result?
A. Tuples in R that match S
B. Tuples in R that do not match S
C. Tuples in S that do not match R
D. All tuples from R and S
✅ Answer: B
Solution:
Antijoin returns R tuples with no matching S tuples — used to implement NOT EXISTS.
Q87.
If R(A,B) has 200 tuples and S(B) has 10 distinct B values, with R’s B values uniformly distributed among these 10 values, expected size of R ⋈ S?
A. 200
B. 20
C. 1000
D. 20 × 10 = 200
✅ Answer: A
Solution:
All R tuples have B values that exist in S (since S has the domain). If every B in R is among the 10 S values, all 200 R tuples will join with S — but each R tuple joins with exactly 1 S tuple (unique B) ⇒ result size = 200.
Q88.
Which of the following sequences is a valid query rewriting preserving semantics?
A. Push selection below join when condition references only one side
B. Push projection arbitrarily below join
C. Replace division by cartesian product always
D. Replace antijoin by join always
✅ Answer: A
Solution:
Selection pushdown is valid when the predicate refers to attributes of a single relation; projections need careful preservation of attributes. Other rewrites are unsafe in general.
Q89.
Given R(A,B,C), S(B). Which RA expression returns A values that pair with B values not present in S?
A. π(A)(R ⋉̸ S)
B. π(A)(R ⋈ S)
C. π(A)(R − S)
D. π(A)(R ÷ S)
✅ Answer: A
Solution:
Antijoin R ⋉̸ S yields R tuples with B not in S; projecting A returns desired values.
Q90.
If π(A)(R) = {1,2} and π(A)(S) = {2,3}, what is π(A)(R) − π(A)(S)?
A. {1}
B. {3}
C. {2}
D. {1,3}
✅ Answer: A
Solution:
Difference of sets {1,2} − {2,3} = {1}.
Q91.
Consider R(A,B), S(B,C). Which expression yields the same as π(A)(R ⋈ (S ⋈ T)) (associativity)?
A. π(A)( (R ⋈ S) ⋈ T )
B. π(A)( R × S × T )
C. π(A)( R ⋈ S ) ⋈ T
D. None
✅ Answer: A
Solution:
Join is associative: (R ⋈ S) ⋈ T = R ⋈ (S ⋈ T). Projecting A after is equivalent.
Q92.
R(A,B) = {(1,2),(1,3),(2,2)}; S(B) = {(2)}. Compute R ⋉̸ S (antijoin).
A. {(1,3)}
B. {(1,2),(2,2)}
C. {(1,3),(2,2)}
D. ∅
✅ Answer: A
Solution:
Antijoin returns R tuples with B not in S. S has B=2, so rows with B=2 are excluded → only (1,3) remains.
Q93.
Which RA expression implements SQL LEFT OUTER JOIN (extended operator)?
A. R ⋈ S ∪ (R ⋉̸ S) ⋈ NULL_PAD(S)
B. R × S
C. R ÷ S
D. R ∩ S
✅ Answer: A
Solution:
Left outer join = inner join plus R tuples with no match padded with NULLs. Option A describes that behavior.
Q94.
Let R(A,B) and S(B). Which of these is an efficient way to compute π(A)(R ⋈ S) when S is small and indexed on B?
A. Scan R and probe S index for each R.B (index-nested-loop)
B. Compute R × S then select then project
C. Sort-merge without using S index
D. Compute R − S then project
✅ Answer: A
Solution:
Index-nested-loop join is efficient when one relation is small and indexed.
Q95.
If R has attributes (A,B,C) and S has (C,D), which projection after join cannot be pushed below both relations simultaneously?
A. π(A,D)
B. π(A,C)
C. π(B)
D. π(C)
✅ Answer: A
Solution:
Projection π(A,D) needs attributes from both relations; pushing π(A,D) below each relation individually would remove needed join attribute C. So cannot be pushed below both; must be after join (or push partial projections preserving C).
Q96.
Given Employees(Eid,Name,MgrId) representing manager hierarchy, is transitive closure expressible in pure relational algebra?
A. Yes, directly
B. No (requires recursion)
C. Yes, using division
D. Yes, using antijoin
✅ Answer: B
Solution:
Transitive closure (reachability) requires recursion — not expressible in plain relational algebra (need recursive extensions like Datalog or SQL WITH RECURSIVE).
Q97.
Which expression returns the number of distinct A values in R (extended relational algebra with aggregation)?
A. COUNT(DISTINCT A)(R)
B. π(A)(R)
C. |π(A)(R)| (cardinality of projection)
D. Both A and C (equivalent)
✅ Answer: D
Solution:
Counting distinct A can be expressed as cardinality of π(A)(R) or as aggregate COUNT(DISTINCT A).
Q98.
Given R(A,B,C) and the equivalence π(A)(σ(B=10)(R)) = π(A)(σ(B=10)(π(A,B,C)(R))), is this always true?
A. Yes — tautology
B. No — projection could remove attributes used by selection (but here projection keeps B)
C. Only if C is irrelevant
D. Only if A is unique
✅ Answer: B
Solution:
It is safe here because π(A,B,C)(R) equals R (same attrs). In general, pushing projection that retains attributes used by selection is OK; otherwise it is not. So the statement as written is true in this specific form because projection retains B used by σ, but not always if projection dropped B.
Q99.
Let R(A,B,C) with A key. After decomposing into R1(A,B) and R2(A,C), is this decomposition: lossless? dependency-preserving?
A. Both lossless and dependency-preserving
B. Lossless but not dependency-preserving
C. Dependency-preserving but not lossless
D. Neither
✅ Answer: A
Solution:
Intersection is {A} and A is key ⇒ {A}→R1 and R2; decomposition is lossless. All FDs with LHS = A split across R1 and R2, so dependencies preserved.
Q100.
Which of the following is the safest way to optimize π(A)(σ(cond)(R ⋈ S))?
A. Push σ down to R or S depending on cond, then push π to remove unneeded attributes while preserving join attribute
B. Apply projection first, then selection, then join
C. Always compute Cartesian product first, then filter
D. Replace join by union
✅ Answer: A
Solution:
Standard optimization: push selections as far down as possible (reduce tuple counts), and push projections down but ensure join attributes needed for join are retained.