Skip to main content

RA: projects count per department

Short answer

Group project by dep_id and count(pid), rename count as no_of_projects, natural join with dep to add department name, and project name and count.

Consider the relational schema: emp (eid, name, add, sup_eid) dep (dep_id, name) project (pid, title, dep_id) work (eid, pid, hours) Find total no of projects from each department, along with dep name. (Write a relational algebra expression.)

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

Relational algebra expression

T = dep_id 𝒢 count(pid) (project)

π name, no_of_projects ( dep ⋈ ρ T(dep_id, no_of_projects) (T) )

In one line:

π name, no_of_projects ( dep ⋈ ρ T(dep_id, no_of_projects) ( dep_id 𝒢 count(pid) (project) ) )

Explanation

  1. Projects are grouped by dep_id and count(pid) gives the number of projects in each group.
  2. The result is renamed so that the count column has the name no_of_projects.
  3. Joining with dep on dep_id adds the department name.
  4. The final projection prints the department name and its number of projects.

Example: Computer has 2 projects and Electrical has 1, so the output is (Computer, 2), (Electrical, 1). A department with no project does not appear unless an outer join is used.

Equivalent SQL (tested)

SELECT d.name, COUNT(p.pid) AS no_of_projects
FROM dep d JOIN project p ON p.dep_id = d.dep_id
GROUP BY d.dep_id, d.name;

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →