GATE CS 2014 Set 3 Q.40 (CS Q.30): Relational Algebra and Tuple Relational Calculus

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)

Consider the following relational algebra query:

\Pi_{empId} (employee) - \Pi_{empId} (employee \bowtie_{(empId = eId) \land (empAge \le depAge)} dependent)

The above query evaluates to the set of empIds of employees whose age is greater than that of

some dependent.

all dependents.

some of his/her dependents.

all of his/her dependents.

Official answer and solution

Official answer: D
From: Official GATE 2014 answer key

Solution

Interpret the relational algebra query step-by-step using set difference to express universal quantification.

Analyze Sub-expressions:
- $\Pi_{empId}(employee)$ gives the set of all employee IDs in the company.
- The join $employee \bowtie_{(empId = eId) \land (empAge \le depAge)} dependent$ matches an employee with a dependent of that employee such that the employee's age is less than or equal to the dependent's age ( $empAge \le depAge$ ).
- $\Pi_{empId}(\dots)$ of this join produces the IDs of all employees who have at least one dependent whose age is greater than or equal to the employee's age.
Apply Set Difference:
$\Pi_{empId}(employee) - \Pi_{empId}(\text{employees with some dependent } depAge \ge empAge)$
Subtracting these yields all employees who do NOT have any dependent with $depAge \ge empAge$ .
Conclusion:
Since every employee has at least one associated dependent, this set contains employees for whom every single one of their dependents satisfies $empAge > depAge$ .
In plain English, this represents the set of employees whose age is greater than that of all of his/her dependents (option D).

Final answer

Common trap

Confusing 'all of his/her dependents' with 'all dependents in the company' (option B), which would compare against every dependent globally.

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

Source

Paper: GATE 2014 Computer Science and Information Technology (CS) Session 3
Question: 40 of 65, printed as CS Q.30
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