GATE CS 2021 Set 1 Q.37 (CS Q.27): Relational Algebra and Tuple Relational Calculus

The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key:

\text{empAge}(\text{empNo}, \text{age})

Consider the following relational algebra expression:

\Pi_{\text{empNo}}(\text{empAge} \bowtie_{(\text{age} > \text{age1})} \rho_{\text{empNo1}, \text{age1}}(\text{empAge}))

What does the above expression generate?

Employee numbers of only those employees whose age is the maximum.

Employee numbers of only those employees whose age is more than the age of exactly one other employee.

Employee numbers of all employees whose age is not the minimum.

Employee numbers of all employees whose age is the minimum.

Official answer and solution

Official answer: C
From: Official GATE 2021 answer key

Solution

Analyze the theta-join condition on self-joined relation empAge to determine which employee records survive the join.

The expression performs a theta-join between empAge and a renamed copy $\rho_{\text{empNo1}, \text{age1}}(\text{empAge})$ with condition $\text{age} > \text{age1}$ :

\Pi_{\text{empNo}}(\text{empAge} \bowtie_{(\text{age} > \text{age1})} \rho_{\text{empNo1}, \text{age1}}(\text{empAge}))

An employee tuple with age $A$ successfully pairs with at least one tuple with age $A_1$ in the second relation if and only if there exists some employee in the company who has a strictly smaller age ( $A > A_1$ ).

If an employee has the minimum age in the company, there is no employee with age strictly smaller than theirs; thus no tuple satisfies $\text{age} > \text{age1}$ , and their employee number will never appear in the join result.
For every employee whose age is NOT the minimum, there exists at least one employee with a strictly lower age (namely, the employee(s) with minimum age), so this condition evaluates to true and their empNo is included.

Therefore, the expression generates the employee numbers of all employees whose age is not the minimum (option C).

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2021 Computer Science and Information Technology (CS, Set-1)
Question: 37 of 65, printed as CS Q.27
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source