Relational Algebra

Context: FIT2094_MOC · a procedural, relationally complete query language over relations · results are relations (closure) · what a DBMS translates SQL into

Quick Revision

• 🎯 Objective: a procedural, relationally complete query language over relations ➔ states how.
• 📦 Core Components: unary π ➔ binary set ops / joins / product / division.
• ⚡ Key Constraint: closure — every result is a relation, so operators compose; no NULLs in pure form.

📝 Core

1. The Language

• Procedural ➔ specifies how (an order of operations).
• Relationally complete ➔ expresses any query relational calculus can.
• Operators ➔ $\sigma ,\pi$ (unary); $\cup ,\cap ,-,\times ,\bowtie ,÷$ (binary).

2. Closure

• Result is a relation ➔ queries compose (output feeds input).
• Nested algebra ➔ complex queries built from operators.

3. The DML Family

• Relational calculus ➔ non-procedural (yardstick for completeness).
• SQL ➔ declarative, transform-oriented.
• Graphical ➔ visual query-by-form.

⚙️ Core Implementation

🔹 SQL → algebra

query processor

SELECT project_manager
FROM PRDETAIL
WHERE project_code = '25-5A';

${\pi }_{\text{project\_manager}}({\sigma }_{\text{project\_code}=\text{’25-5A’}}(\text{PRDETAIL}))$ 💡 Common Mistake: Pure algebra has no NULLs ➔ it assumes complete information; NULLs are an SQL addition. Algebra’s explicit operation order is what enables optimisation.

⚖️ Core Decision Matrix

Language	Procedural?	Role
relational calculus	no	completeness benchmark
relational algebra	yes	execution/optimisation
SQL	no (declarative)	user queries
graphical	no	visual

When It Flips: algebra prescribes an operation order (good for the optimiser); calculus/SQL state only the result — the DBMS bridges them by compiling SQL to algebra. Relational join generalises FIT1058 relational composition to data.

📊 Exam Execution Trace

Applied Exercise

Problem: Translate SELECT project_manager FROM PRDETAIL WHERE project_code='25-5A' into algebra. Derivation Proof / Hand-Calculation Walkthrough:

$$\begin{array}{rl}\text{WHERE}& \Rightarrow {\sigma }_{\text{project\_code}=\text{’25-5A’}}(\text{PRDETAIL})\\ \text{column list}& \Rightarrow {\pi }_{\text{project\_manager}}(\dots )\end{array}$$

Final Extracted Output: ${\pi }_{\text{project\_manager}}({\sigma }_{\text{project\_code}=\text{’25-5A’}}(\text{PRDETAIL}))$.

🧠 Active Recall

What does the closure property mean, and why does it matter?

• Hint: Result is a relation.

Answer

• Short answer: Every query result is itself a relation, so outputs feed back as inputs and operators compose.
• Why: Building blocks ➔ a complex query is nested operations; the DBMS chains operators.

How does an SQL query relate to relational algebra inside a DBMS?

• Hint: Declarative vs procedural.

Answer

• Short answer: The query processor compiles declarative SQL into procedural algebra, then optimises/executes.
• Why: Operation order ➔ algebra’s explicit ordering enables optimisation.