Skip to main content

RA: employees on no project

Short answer

All employee numbers minus those in work, join with employee and project name: π name (employee ⋈ (π e_no(employee) − π e_no(work))).

Consider the relational schema: employee (e_no, name, address) project (p_no, p_name) work (e_no, p_no) part (part_no, part_name, qty, size) use (e_no, p_no, part_no, number) Print names of employees not working on any project. (Write a relational algebra expression.)

Relational algebra symbols used: σ select, π project, ⋈ natural join, ∪ union, ∩ intersection, − difference, ρ rename, 𝒢 aggregate.

Relational algebra expression

π name ( employee ⋈ ( π e_no (employee) − π e_no (work) ) )

Explanation

  1. π e_no (employee) is the set of all employee numbers.
  2. π e_no (work) is the set of employees who work on at least one project.
  3. The difference gives the e_no of employees who appear in no work tuple.
  4. Joining back with employee gets their details, and π name prints the names.

The difference operator is needed because "not" cannot be expressed by selection alone.

Equivalent SQL

SELECT name FROM employee
WHERE e_no NOT IN (SELECT e_no FROM work);

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →