RA: delete Electrical dept projects

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

Relational algebra expression

E ← π dep_id ( σ name = "Electrical" (dep) )

project ← project − ( project ⋈ E )

Explanation

1. σ name="Electrical"(dep) selects the Electrical department, and π dep_id gives its id as relation E.
2. project ⋈ E keeps the projects whose dep_id matches (the join is on the common attribute dep_id). Because E has only dep_id, the result keeps the schema of project (pid, title, dep_id), so the difference is valid.
3. Subtracting these tuples from project deletes them, and the result is assigned back to project.

To keep the database consistent, the work tuples of the deleted projects should also be removed:

work ← work − ( work ⋈ π pid ( project ⋈ E ) )

This must be done before project is changed, otherwise the pids can no longer be found.

Equivalent SQL

DELETE FROM project
WHERE dep_id IN (SELECT dep_id FROM dep WHERE name = 'Electrical');