Consider the following relational database schema: EMP (eno, name, age) PROJ (pno, name) INVOLVED (eno, pno) EMP contains information about employees, PROJ about projects and INVOLVED about which employees are involved in which projects. The underlined attributes are the primary keys for the respective relations. What is the relational algebra expression which is equivalent to the SQL query: select eno from EMP where eno in (select eno from INVOLVED where pno = 3) A. pi_eno(EMP) - pi_eno(INVOLVED) B. pi_eno(sigma_pno=3(INVOLVED)) C. pi_eno(EMP) - pi_eno(sigma_pno!=3(INVOLVED)) D. pi_eno(EMP JOIN INVOLVED)

GATE 1997 · Databases · Relational Algebra · medium

Answer: B. pi_eno(sigma_pno=3(INVOLVED))

1. Translate the inner subquery: The inner SQL: 'select eno from INVOLVED where pno=3' translates to: - sigma_pno=3(INVOLVED): filter INVOLVED for rows where pno=3 - pi_eno(...): project onto eno Result: pi_eno(sigma_pno=3(INVOLVED)) — the set of employee numbers involved in project 3.
2. Evaluate all options: A. pi_eno(EMP) - pi_eno(INVOLVED): gives employees NOT involved in any project — wrong. B. pi_eno(sigma_pno=3(INVOLVED)): gives employees involved in project 3 — CORRECT. C. pi_eno(EMP) - pi_eno(sigma_pno!=3(INVOLVED)): gives employees who are ONLY in project 3 (not in any other project) — wrong; this would miss employees involved in project 3 AND other projects. D. pi_eno(EMP JOIN INVOLVED): gives employees involved in any project — wrong.