# Relational Algebra — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/r-algebra

> Express queries with selection, projection, joins, set operations and division.

## 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.

```text
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.

**Quiz:** Which relational algebra operator answers "students enrolled in all core courses"?

- [ ] Union
- [ ] Projection
- [ ] Rename
- [x] Division

*Answer:* Division. Division expresses "for all" queries over a set of required values.