Name: Dagmawi feyissa
Student ID: 0367/16
Submission Date: April 25,2025
Project Overview
This project focuses on applying relational algebra operations to query a structured database
schema. The database consists of four primary tables:
• Employee (emp_id, emp_name, dept_id, salary)
• Department (dept_id, dept_name, location)
• Project (proj_id, proj_name, dept_id)
• Works_on (emp_id, proj_id, hours)
The goal is to write relational algebra expressions to solve specific business queries, such as
finding employees working on multiple projects, departments with no employees, or employees
earning above the average salary.
Objectives
• Understand Core Relational Algebra Operations: Selection (σ), Projection (π), Join (⨝),
Set Difference (−), Aggregation (γ), and Division (÷).
• Translate Business Queries into Formal Expressions: Convert real-world questions (e.g.,
"Who works in the IT department?") into relational algebra.
• Validate Results with Sample Data: Test each query against a sample dataset to ensure
accuracy.
• Employee Table
emp_id emp_name dept_id salary
101 Abebe D01 5000
1
102 Yohannes D02 6500
103 Helen D01 5500
104 Mikias D02 7000
105 Dawit D03 4500
• Project Table
proj_id proj_name dept_id
P10 Website Redesign D01
P20 Budget Planning D02
P30 Employee Training D03
• Works_on Table
emp_id proj_id hours
101 P10 45
102 P20 35
103 P10 40
104 P20 15
101 P20 10
• Department Table
dept_id dept_name location
D01 IT Building A
2
D02 Finance Building B
D03 HR Building C
Solution:-
1) Employees Working >30 Hours on Any Project
Relational Algebra: π_emp_id, emp_name (σ hours > 30 (Employee ⨝ Works_on))
Explanation: We combine employee and work records, pick only those working >30 hours, and
list their names. Expected Output:
emp_id emp_name Hours
101 Abebe 45
103 Helen 40
2) : Employees Working on >1 Project
Relation Algebra: π emp_name (Employee ⨝ (σ_{count > 1} (γ_{emp_id;
COUNT(proj_id)→count} (Works_on))))
Explanation: Count projects per employee, keep only those with >1 project, then show names.
Expected Output:
emp_name
Abebe
3) Employees Not Working on Any Project
Relation Algebra: π_{emp_id, emp_name} (Employee) − π_{emp_id, emp_name} (Employee
⨝ Works_on)
3
Explanation: Uses set difference to find employees absent in Works_on. Compare full employee
list against those with project assignments to find the employees not working on any projects.
Expected Output:
emp_id emp_name
105 Dawit
4) Employees in IT Department Projects
Relation Algebra: π_{emp_name} (Employee ⨝ Works_on ⨝ (Project ⨝ σ_{dept_name='IT'}
(Department)))
Explanation: Joins all tables and filters for dept_name = 'IT'. we will trace project-department
connections to find IT projects, then list workers.
Expected Output:
emp_name emp_name
Helen Abebe
5) Departments with No Employees
Relation Algebra: π_{dept_name} (Department) − π_{dept_name} (Department ⨝ Employee)
Explanation: we will compare all departments against those with linked employees. Subtract
departments linked to employees from all departments.
Expected Output:
dept_name
HR
6) Task 6: Employees Earning > Average Salary
Relational Algebra: π_{emp_id, emp_name} (σ_{salary > avg_salary} (Employee ×
γ_{AVG(salary)→avg_salary} (Employee)))
4
Explanation: Compares each employee’s salary to the average. hence average pay (5,700), then
find who will exceeds the average salary.
Expected Output:
emp_id emp_name
102 Yohannes
104 Mikias
7) Task 7: Employees Working on All Projects Relational Algebra:
π_{emp_name} (Employee ⨝ (π_{emp_id} (Works_on) ÷ π_{proj_id} (Project)))
Explanation: Division finds employees associated with every project (Check if any employee
appears in all project assignments). But since there are no employees who work on every project
the output will be empty.
Expected Output:
emp_name
empty
8) Task 8: Employees with Total Working Hours
Relational Algebra: π_{emp_id, emp_name, total_hours} (Employee ⟕ (γ_{emp_id;
SUM(hours)→total_hours} (Works_on)))
Explanation: here we will sum up the employees’ work hours across all projects and display the
total along with employee id. Sums hours per employee (including NULL for non-workers).
Expected Output:
emp_id total_hours
101 55
102 35
103 40
5
104 15
105 Null
9) Task 9: Departments with Avg Salary > 10,000 Relational Algebra: π_{dept_name}
(σ_{avg_salary > 10000} (γ_{dept_id; AVG(salary)→avg_salary} (Employee) ⨝ Department))
Explanation: So here we will calculate department salary averages and filter based on that. Since
our highest is Finance at 6,750 average we won’t have a department with average salary more
than 10,000.
Expected Output:
Dept_name
empty