Relational Algebra

The mathematics behind SQL

Relational algebra is a procedural query language of operators that take relations and produce relations, so they can be composed. Selection σ_condition(R) keeps tuples satisfying a condition (rows). Projection π_attributes(R) keeps listed attributes and removes duplicates (columns). Rename ρ gives a relation or attributes new names. Set operations: union ∪, difference − and intersection ∩ require union-compatible relations (same degree and compatible domains). Cartesian product × pairs every tuple of one relation with every tuple of another. Joins combine related tuples: the theta join applies a condition to the product, the natural join ⋈ joins on equal values of common attributes and keeps one copy of them, and outer joins keep unmatched tuples padded with nulls. Division ÷ answers "for all" questions, such as students who have taken every core course. SQL is based on relational algebra and relational calculus, though SQL works with bags (duplicates allowed) unless you write DISTINCT.

Relational algebra next to SQL

Each query written in algebra and in SQL.

STUDENT(roll_no, name, dept)   ENROLMENT(roll_no, course_id, grade)   CORE(course_id)

1. names of CSE students
   pi_name( sigma_{dept='CSE'}(STUDENT) )
   SELECT DISTINCT name FROM student WHERE dept = 'CSE';

2. names of students with an A grade in any course
   pi_name( STUDENT natural-join sigma_{grade='A'}(ENROLMENT) )
   SELECT DISTINCT s.name FROM student s JOIN enrolment e ON e.roll_no = s.roll_no
   WHERE e.grade = 'A';

3. roll numbers enrolled in every core course (division)
   pi_{roll_no, course_id}(ENROLMENT) ÷ CORE
   SELECT roll_no FROM enrolment WHERE course_id IN (SELECT course_id FROM core)
   GROUP BY roll_no HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM core);

Kitchen operations

Selection is picking only ripe tomatoes, projection is keeping only the parts you need, a join is pairing each dish with its matching sauce, and division is finding the chefs who can cook every dish on the menu.

Quick check: Which relational algebra operator answers "students enrolled in all core courses"?

• Union
• Projection
• Rename
• Division