Skip to main content

RA: employees in both DBMS and MIS

Short answer

Build employee number sets for projects "DBMS" and "MIS" using selection, join with work, and projection. Intersect to find employees working on both, join with employee, and project name.

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 emp name working in both projects DBMS and MIS. (Write a relational algebra expression.)

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

Relational algebra expression

D = π e_no ( work ⋈ σ p_name = "DBMS" (project) )
M = π e_no ( work ⋈ σ p_name = "MIS"  (project) )

π name ( employee ⋈ ( D ∩ M ) )

Explanation

  1. D is the set of employees working on the DBMS project, found by selecting the project, joining with work on p_no and projecting e_no.
  2. M is the set of employees working on MIS in the same way.
  3. D ∩ M keeps only the employees present in both sets, that is, working on both projects.
  4. Joining with employee on e_no and projecting name gives the names.

The two sets are built separately because one tuple of work holds only one project, so a single condition p_name = "DBMS" ∧ p_name = "MIS" can never be true.

Equivalent SQL

SELECT e.name FROM employee e
WHERE e.e_no IN (SELECT e_no FROM work JOIN project USING(p_no) WHERE p_name='DBMS')
  AND e.e_no IN (SELECT e_no FROM work JOIN project USING(p_no) WHERE p_name='MIS');

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →