week 9
9.2 Relational Algebra
Relational algebra is the formal mathematical language underlying SQL. The query optimiser works with relational algebra expressions, not SQL directly. Key operators:
|
Relational Algebra Operator |
SQL Equivalent |
Description |
|
Selection (σ) |
WHERE clause |
Filter rows based on a condition. E.g., σ(Level=300)(STUDENT) |
|
Projection (π) |
SELECT columns |
Keep only specified columns. E.g., π(Name, MatricNo)(STUDENT) |
|
Join (⋈) |
JOIN |
Combine related tuples from two relations based on a condition |
|
Union (∪) |
UNION |
Combine results of two compatible queries |
|
Intersection (∩) |
INTERSECT |
Rows that appear in BOTH query results |
|
Difference (−) |
EXCEPT / MINUS |
Rows in first result but NOT in second |
|
Cartesian Product (×) |
CROSS JOIN |
All combinations of tuples from two relations |
|
Rename (ρ) |
AS (alias) |
Rename a relation or attribute |
|
Relational Algebra Example: Find the names of all 300-level Computer Science students: Relational Algebra: π(FirstName, LastName)(σ(Level=300 AND DeptCode='CSC')(STUDENT)) SQL: SELECT FirstName, LastName FROM STUDENT WHERE Level=300 AND DeptCode='CSC'; The query optimiser evaluates whether it is more efficient to first filter (SELECT), then project (columns), or vice versa. Since selection reduces the number of rows, it is usually applied first. |