Skip to main content

RA: employee-projects with no part used

Short answer

Find (e_no,p_no) from work minus use, join employee and project, project name and p_name: π name, p_name (employee ⋈ project ⋈ (π e_no,p_no(work) − π e_no,p_no(use))).

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) List names of employees and projects with which employees are associated with and for which they have used no part so far. (Write a relational algebra expression.)

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

Relational algebra expression

π name, p_name ( employee ⋈ project ⋈
      ( π e_no, p_no (work) − π e_no, p_no (use) ) )

Explanation

  1. π e_no, p_no (work) lists every (employee, project) association.
  2. π e_no, p_no (use) lists the pairs for which at least one part was used.
  3. The difference leaves the pairs where the employee works on the project but has used no part there.
  4. Joining with employee (on e_no) and project (on p_no) brings in the names, and the final projection prints the employee name and the project name.

Both operands of the − have the same attributes (e_no, p_no), so they are union-compatible.

Equivalent SQL

SELECT e.name, p.p_name
FROM work w JOIN employee e ON e.e_no = w.e_no
            JOIN project p ON p.p_no = w.p_no
WHERE NOT EXISTS (SELECT 1 FROM use u
                  WHERE u.e_no = w.e_no AND u.p_no = w.p_no);

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →