Relational Algebra Query Solving: Step-by-Step Practice Guide

The 4-Step Method for Writing Relational Algebra Queries

In university examinations (DAVV BCA), students are given a standard relational schema and asked to write 5 to 7 relational algebra queries.

Always follow this 4-Step Blueprint:

Identify the Target Columns: Look at what the question asks you to display (e.g., “Find the names and salaries…”). These attributes will go into the final Projection (π).
Identify the Filtering Conditions: Look for criteria (e.g., “salary > 50000”, “department = IT”). These will form your Selection (σ) predicate.
Identify the Necessary Tables: If all required columns exist in one table, you do not need a join. If attributes are scattered across two or three tables, connect them using Natural Join (⋈).
Assemble the Expression: Write from the inside out:
π <Target_Columns> ( σ <Conditions> ( Table1 ⋈ Table2 ) )

The Standard Exam Database Schemas

We will solve queries on two classic university examination schemas:

Schema A: University System

STUDENT (Roll_No, Name, Branch, Marks, City)
COURSE (Course_ID, Title, Credits, Dept_ID)
ENROLL (Roll_No, Course_ID, Grade)
DEPARTMENT (Dept_ID, Dept_Name, Building)

Schema B: Enterprise Employee System

EMPLOYEE (Emp_ID, Emp_Name, Salary, Dept_No, Manager_ID)
DEPARTMENT (Dept_No, Dept_Name, Location)
PROJECT (Proj_ID, Proj_Name, Budget)
WORKS_ON (Emp_ID, Proj_ID, Hours)

1. Single Table Query Solutions

Query 1: “Find the names and branches of all students whose marks are greater than 85.”

Target Columns: Name, Branch
Condition: Marks > 85
Table: STUDENT
Relational Algebra Expression:
π Name, Branch (σ Marks > 85 (STUDENT))

Query 2: “List all distinct cities where students live.”

Target Columns: City
Table: STUDENT
Relational Algebra Expression:
π City (STUDENT)
(Automatic duplicate elimination handles uniqueness!).

Query 3: “Find employees who work in Department 10 and earn a salary greater than 45,000.”

Target Columns: Emp_Name, Salary
Condition: Dept_No = 10 ∧ Salary > 45000
Table: EMPLOYEE
Relational Algebra Expression:
π Emp_Name, Salary (σ Dept_No = 10 ∧ Salary > 45000 (EMPLOYEE))

2. Two-Table Join Query Solutions

Query 4: “Find the names of all students enrolled in the ‘Database Systems’ course.”

Required Tables: STUDENT, ENROLL, COURSE

Step-by-step using Assignment (←):

T1 ← σ Title = 'Database Systems' (COURSE)
T2 ← ENROLL ⋈ T1
T3 ← STUDENT ⋈ T2
Result ← π Name (T3)

Single-Line Expression:
π Name (STUDENT ⋈ ENROLL ⋈ (σ Title = 'Database Systems' (COURSE)))

Query 5: “Find the names of all employees who work in the ‘Research’ department.”

Required Tables: EMPLOYEE, DEPARTMENT
Condition: Dept_Name = 'Research'
Relational Algebra Expression:
π Emp_Name (EMPLOYEE ⋈ (σ Dept_Name = 'Research' (DEPARTMENT)))

Query 6: “List all employee names along with the projects they are working on.”

Required Tables: EMPLOYEE, WORKS_ON, PROJECT
Relational Algebra Expression:
π Emp_Name, Proj_Name (EMPLOYEE ⋈ WORKS_ON ⋈ PROJECT)

3. Set Operation Query Solutions (Union & Difference)

Query 7: “Find the roll numbers of students who are enrolled in Course ‘CS101’ OR Course ‘CS102’.”

Solution using Union (∪):
π Roll_No (σ Course_ID = 'CS101' (ENROLL)) ∪ π Roll_No (σ Course_ID = 'CS102' (ENROLL))

Query 8: “Find the roll numbers of students who are enrolled in Course ‘CS101’ but NOT in ‘CS102’.”

Solution using Set Difference (−):
π Roll_No (σ Course_ID = 'CS101' (ENROLL)) − π Roll_No (σ Course_ID = 'CS102' (ENROLL))

Query 9: “Find the names of students who have enrolled in NO courses whatsoever.”

All students minus students who appear in the ENROLL table:
π Name (STUDENT ⋈ (π Roll_No (STUDENT) − π Roll_No (ENROLL)))

4. Complex Query: The Division Operator (÷)

Query 10: “Find the names of students who have enrolled in ALL courses offered by the Computer Science department.”

Step 1: Get all courses offered by Computer Science (Dept_ID = 'CS'):
CS_Courses ← π Course_ID (σ Dept_ID = 'CS' (COURSE))
Step 2: Get the student-course pairs:
Student_Course_Pairs ← π Roll_No, Course_ID (ENROLL)
Step 3: Divide by CS_Courses:
Qualified_Rolls ← Student_Course_Pairs ÷ CS_Courses
Step 4: Fetch names:
Result ← π Name (STUDENT ⋈ Qualified_Rolls)

💡 DAVV Exam Preparation Tips

5-Mark Question: “Given EMPLOYEE(Emp_ID, Name, Salary, Dept_No) and DEPARTMENT(Dept_No, Dept_Name), write relational algebra queries to:

Find employees earning > 60,000.

Find employees working in ‘Finance’ department.”

Query 1: π Name (σ Salary > 60000 (EMPLOYEE))

Query 2: π Name (EMPLOYEE ⋈ (σ Dept_Name = 'Finance' (DEPARTMENT)))

10-Mark Question: “Explain how to formulate complex queries in Relational Algebra using Selection, Projection, Natural Join, Set Difference, and Division. Solve 5 standard student-course queries.”

Provide the step-by-step thinking breakdown (Target columns, Filters, Joins).

Show both the single-line nested format and the readable assignment operator (←) multi-step format.

Always include the classic “Enrolled in ALL courses” division query to demonstrate mastery.