GATE CS 2014 Set 3 Q30 — Normal Forms

To understand what the relational algebra query evaluates to, let us break it down into two parts separated by the set difference operator ($-$).

1. Left-Hand Side (LHS)

$$\text{LHS} = \Pi_{empId}(employee)$$ This expression simply retrieves the set of all empIds from the employee relation.

2. Right-Hand Side (RHS)

$$\text{RHS} = \Pi_{empId}\left(employee \bowtie_{(empId=eId) \wedge (empAge \leq depAge)} dependent\right)$$ This expression performs a join between employee and dependent on two conditions:

1. empId = eId: The dependent belongs to that specific employee.
2. empAge ≤ depAge: The employee's age is less than or equal to their dependent's age.

3. Set Difference (LHS − RHS)

$$\text{Query} = \text{LHS} - \text{RHS}$$ This subtracts the RHS from the set of all employees. The result contains employees who do not have any dependent with an age greater than or equal to their own age.

Since we are given that every employee has at least one associated dependent, this negation means that for the remaining employees, all of their dependents must be strictly younger than them.

Therefore, the query evaluates to the set of empIds of employees whose age is greater than that of all of his/her dependents.