Relational Algebra is a procedural query language providing operations to manipulate relations. It forms the theoretical foundation of SQL. Core operations: Selection (σ), Projection (π), Cartesian Product (×), Union (∪), Set Difference (−), and Rename (ρ). Derived operations include Join (⋈), Intersection (∩), and Division (÷). Tuple Relational Calculus (TRC) is a non-procedural counterpart — you describe WHAT you want without specifying HOW. Both are tested heavily in GATE DBMS.
Real-life analogy: SQL behind the scenes
Every SQL query you write is translated by the DBMS into a relational algebra expression tree, optimised, then executed. Knowing RA means you understand exactly what happens for SELECT with WHERE, JOIN, and GROUP BY. You can predict query behaviour, write better SQL, and explain execution plans.
Core operations with SQL equivalents
| Operation | Symbol | SQL equivalent | Example |
|---|---|---|---|
| Selection | σ_cond(R) | WHERE clause | σ_Age>25(Employee) — rows where Age > 25 |
| Projection | π_attrs(R) | SELECT columns | π_Name,Salary(Employee) — only those columns |
| Cartesian Product | R × S | CROSS JOIN | Employee × Department — every combination |
| Union | R ∪ S | UNION | All students from two departments combined |
| Set Difference | R − S | EXCEPT | AllStudents − PassedStudents |
| Rename | ρ_name(R) | AS alias | ρ_Emp(Employee) |
| Natural Join | R ⋈ S | NATURAL JOIN | Join on all common attributes automatically |
| Theta Join | R ⋈_cond S | JOIN ON condition | Emp ⋈_{E.DeptID=D.DeptID} Dept |
| Division | R ÷ S | NOT EXISTS subquery | Students enrolled in ALL courses |
Relational algebra expressions and SQL equivalents
-- R(A): Employee(EmpID, Name, DeptID, Salary)
-- R(B): Department(DeptID, DeptName, Location)
-- 1. Selection + Projection
-- RA: π_Name,Salary(σ_Salary>50000(Employee))
SELECT Name, Salary FROM Employee WHERE Salary > 50000;
-- 2. Theta Join (most common join type)
-- RA: σ_E.DeptID=D.DeptID(Employee × Department)
SELECT E.Name, D.DeptName
FROM Employee E JOIN Department D ON E.DeptID = D.DeptID;
-- 3. Union: employees in Dept 1 OR Dept 2
-- RA: π_EmpID(σ_DeptID=1(Employee)) ∪ π_EmpID(σ_DeptID=2(Employee))
SELECT EmpID FROM Employee WHERE DeptID = 1
UNION
SELECT EmpID FROM Employee WHERE DeptID = 2;
-- 4. Set Difference: employees NOT in Dept 1
-- RA: π_EmpID(Employee) − π_EmpID(σ_DeptID=1(Employee))
SELECT EmpID FROM Employee
EXCEPT
SELECT EmpID FROM Employee WHERE DeptID = 1;
-- 5. Division: employees who worked on ALL projects
-- RA: WorksOn ÷ Project
SELECT DISTINCT w1.EmpID FROM WorksOn w1
WHERE NOT EXISTS (
SELECT ProjID FROM Project
EXCEPT
SELECT w2.ProjID FROM WorksOn w2 WHERE w2.EmpID = w1.EmpID
);Tuple Relational Calculus (TRC)
TRC syntax: {t | P(t)} — the set of all tuples t that satisfy predicate P. TRC is non-procedural: you declare WHAT you want, not HOW to compute it. Uses ∃ (existential) and ∀ (universal) quantifiers.
| RA expression | TRC equivalent | SQL equivalent |
|---|---|---|
| σ_Age>25(Employee) | {t | t ∈ Employee ∧ t.Age > 25} | SELECT * FROM Employee WHERE Age > 25 |
| π_Name(Employee) | {t.Name | t ∈ Employee} | SELECT Name FROM Employee |
Safe vs unsafe TRC expressions
TRC can express "unsafe" queries returning infinite results: {t | t ∉ Employee} — all tuples NOT in Employee is infinite. Safe TRC only produces values from the active domain. SQL is always safe by design.
Practice questions (GATE-style)
- Express in RA: names of employees earning more than 50000 in department named "IT". (Answer: π_Name(σ_Salary>50000(Employee ⋈_{E.DeptID=D.DeptID} σ_DeptName="IT"(Department))))
- R has 5 tuples, S has 3 tuples. Maximum tuples in R ⋈ S (natural join on common attribute)? (Answer: 15 if all 5 tuples of R match all 3 of S. Minimum is 0 if no matches.)
- What is R ∪ S where R={(1,a),(2,b)} and S={(2,b),(3,c)}? (Answer: {(1,a),(2,b),(3,c)} — set union removes duplicates.)
- RA is procedural and TRC is non-procedural. What does this mean? (Answer: RA specifies a sequence of operations (HOW to retrieve). TRC specifies a predicate describing desired result (WHAT to retrieve) without specifying retrieval procedure.)
- Express R ÷ S using basic operators. (Answer: R ÷ S = π_R-S(R) − π_R-S((π_R-S(R) × S) − R))
On LumiChats
LumiChats can translate any relational algebra expression to SQL and vice versa. Paste an RA expression like π_Name,Salary(σ_DeptID=5(Employee ⋈ Department)) and ask for equivalent SQL with explanation.
Try it free