The following relation records the age of 500 employees of a company, where…
2021
The following relation records the age of 500 employees of a company, where empNo ( indicating the employee number) is the key:
\(empAge(\underline{empNo},age)\)
Consider the following relational algebra expression:
\(\Pi_{empNo}(empAge \Join_{(age>age1)} \rho_{empNo1,age1}(empAge))\)
What does the above expression generate?
- A.
Employee numbers of only those employees whose age is the maximum
- B.
Employee numbers of only those employees whose age is more than the age of exactly one other employee
- C.
Employee numbers of all employees whose age is not the minimum
- D.
Employee numbers of all employees whose age is the minimum
Attempted by 155 students.
Show answer & explanation
Correct answer: C
Key idea: the expression finds employees whose age is greater than some other employee's age.
The subexpression that renames the relation produces a copy with attributes empNo1 and age1, representing other employees.
The theta-join with condition age > age1 pairs each employee with every other employee who is younger than them (age greater than age1).
Projecting empNo returns the distinct employee numbers of those employees who had at least one such younger employee. Projection removes duplicates so each employee appears once.
Therefore the final result is the employee numbers of all employees whose age is not the minimum.
Example: if ages are 22, 25, 30, then employees aged 25 and 30 will appear because each is older than at least one other employee; the employee aged 22 (the minimum) will not appear.
A video solution is available for this question — log in and enroll to watch it.