---
title: "RA: computer dept employees and supervisors"
subject: "Database Management System"
chapter: 3
key: "semester_6/database_management_system:ra-computer-emp-supervisor"
source: https://www.bctengineer.com/q/semester_6/database_management_system/ra-computer-emp-supervisor
site: BCT Engineering
---

# RA: computer dept employees and supervisors

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

- Asked in: year not given
- Video: [Relational Algebra Queries using EMP, DEPT & DEPENDENT Schema | DBMS Module 2 | BCS403 | VTU](https://www.youtube.com/watch?v=3SfdFbsxHnY) — Express VTU 4 All
- 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-computer-emp-supervisor
- 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)
List name of all employees from computer dept. along with name of their supervisor. (Write a relational algebra expression.)

## Short answer

Identify computer department employees via project and work joins. Rename emp as supervisor relation, join on sup_eid = eid, and project employee name and supervisor name.

## Answer

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

Both emp and dep have an attribute called name, and emp refers to itself through sup_eid, so renaming is needed. Assumption: an employee belongs to the Computer department if he or she works on a project of that department (emp has no dep_id of its own).

#### Relational algebra expression
```text
C  = π eid ( work ⋈ project ⋈ σ name = "Computer" (dep) )
E  = ρ E ( emp ⋈ C )                       -- computer employees, as E
S  = ρ S (eid, sname, sadd, ssup) ( emp )  -- the same relation as supervisors

π E.name, S.sname ( σ E.sup_eid = S.eid ( E × S ) )
```
or as a join:  π E.name, S.sname ( E ⋈ E.sup_eid = S.eid  S ).

#### Explanation
1. σ name="Computer"(dep) picks the department; joining with project (on dep_id) and work (on pid) finds the eid of employees working on its projects.
2. emp ⋈ C restricts emp to those employees.
3. emp is renamed a second time as S so that the supervisor of an employee can be matched, E.sup_eid = S.eid.
4. The projection prints the employee name and the supervisor name.

#### Equivalent SQL (tested)
```sql
SELECT e.name, s.name
FROM emp e JOIN emp s ON e.sup_eid = s.eid
WHERE e.eid IN (SELECT eid FROM work JOIN project USING(pid)
                JOIN dep USING(dep_id) WHERE dep.name = 'Computer');
```

## Check yourself

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

1. Why is emp renamed twice (E and S)?
   A. emp is joined with itself via sup_eid, so the copies need distinct names
   B. To remove duplicates
   C. emp and dep share eid
   D. To compute count
   Answer: A. Self-join needs two aliases. (Key point: Why rename)

2. Which condition matches an employee to the supervisor?
   A. E.eid = S.eid
   B. E.name = S.name
   C. E.sup_eid = S.eid
   D. E.sup_eid = S.sup_eid
   Answer: C. sup_eid refers to the supervisor's eid. (Key point: Join condition)

3. How are Computer-department employees found?
   A. work join project join σ name=Computer(dep), since emp has no dep_id
   B. From emp.dep_id directly
   C. From dep alone
   D. From supervisors only
   Answer: A. The department is reached through projects they work on. (Key point: Computer employees)

## Related in Database Management System

- [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: 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: projects count per department](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-projects-per-department) — Database Management System, chapter 3
- [RA: employees on project DBMS](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-emp-project-dbms) — 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: employees in both DBMS and MIS](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-emp-both-dbms-mis) — Database Management System, chapter 3