Consider the relational schema given below, where eId of the relation…
2014
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) \wedge (empAge \leq depAge)} dependent)\)
The above query evaluates to the set of empIds of employees whose age is greater than that of
- A.
some dependent.
- B.
all dependents.
- C.
some of his/her dependents.
- D.
all of his/her dependents.
Attempted by 124 students.
Show answer & explanation
Correct answer: D
Key insight: the query keeps employees for whom no dependent is as old as or older than the employee.
employee ⋈_{(empId = eID) ∧ (empAge ≤ depAge)} dependent produces employee–dependent pairs where the dependent is at least as old as the employee.
Π_empId of that join gives empIds of employees who have at least one dependent with depAge ≥ empAge.
Subtracting those empIds from Π_empId(employee) yields employees who do not have any dependent with depAge ≥ empAge. Given every employee has at least one dependent, this means the employee's age is greater than the age of each of their dependents.
Therefore, the query returns the empIds of employees who are older than all of their dependents.
A video solution is available for this question — log in and enroll to watch it.