---
title: "RA: projects count per department"
subject: "Database Management System"
chapter: 3
key: "semester_6/database_management_system:ra-projects-per-department"
source: https://www.bctengineer.com/q/semester_6/database_management_system/ra-projects-per-department
site: BCT Engineering
---

# RA: projects count per department

Database Management System · Chapter 3: Relational Languages & Relational Model

- Asked in: year not given
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/ra-projects-per-department
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-3

## Question

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.)

## 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.

## Answer

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

#### Relational algebra expression
```text
T = dep_id 𝒢 count(pid) (project)

π name, no_of_projects ( dep ⋈ ρ T(dep_id, no_of_projects) (T) )
```
In one line:
```text
π 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)
```sql
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;
```

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. What does dep_id 𝒢 count(pid)(project) give?
   A. The number of projects for each dep_id
   B. Total employees
   C. Department names
   D. All projects
   Answer: A. Grouping by dep_id with count(pid). (Key point: Group and count)

2. Why join with dep?
   A. To count more
   B. To rename
   C. To add the department name
   D. To remove duplicates
   Answer: C. project holds only dep_id. (Key point: Why join with dep)

3. What happens to a department with no project?
   A. It does not appear unless an outer join is used
   B. Appears with 0
   C. Error
   D. Appears with NULL
   Answer: A. Groups come from existing project tuples. (Key point: Empty departments)

## Related in Database Management System

- [RA: delete Electrical dept projects](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-delete-electrical-projects) — Database Management System, chapter 3
- [RA: employees on Network over 15 hours](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-network-project-15-hours) — Database Management System, chapter 3
- [RA: computer dept employees and supervisors](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-computer-emp-supervisor) — Database Management System, chapter 3
- [RA: employee-projects with no part used](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-emp-project-no-part) — Database Management System, chapter 3
- [RA: employees on no project](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-emp-no-project) — Database Management System, chapter 3
- [RA: projects where all used bolts size 6](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-projects-all-used-bolts) — Database Management System, chapter 3

## Related in other subjects

- [Dipole potential; no work along bisector](https://www.bctengineer.com/question.md?key=semester_1%2Fengineering_physics%3Adipole-potential-no-work-perpendicular) — Engineering Physics, chapter 7