Skip to main content

RA: parts used in DBMS and MIS

Short answer

Find part_no sets for projects "DBMS" and "MIS" via selection, join with use, and projection. Intersect the sets to get parts used in both, then natural join with part and project part_no and part_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 part no and part name used 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 = π part_no ( use ⋈ σ p_name = "DBMS" (project) )
M = π part_no ( use ⋈ σ p_name = "MIS"  (project) )

π part_no, part_name ( part ⋈ ( D ∩ M ) )

Explanation

  1. use ⋈ σ p_name="DBMS"(project) joins on p_no, so only the parts used in the DBMS project remain; π part_no gives set D.
  2. Set M is found the same way for the MIS project.
  3. The intersection D ∩ M gives part numbers used in both projects. (A single selection with p_name="DBMS" ∧ p_name="MIS" would always be empty, so the two sets must be built separately.)
  4. Joining with part adds part_name, and the last projection prints part_no and part_name.

Equivalent SQL

SELECT t.part_no, t.part_name FROM part t
WHERE t.part_no IN (SELECT part_no FROM use u JOIN project p
                    ON p.p_no = u.p_no WHERE p_name = 'DBMS')
  AND t.part_no IN (SELECT part_no FROM use u JOIN project p
                    ON p.p_no = u.p_no WHERE p_name = 'MIS');

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →