week 9
Teaching and Learning Methods to be Employed
1. Illustrated lecture on query processing pipeline
2. Lab: Using EXPLAIN in MySQL to analyse query execution plans
3. Index creation and performance comparison exercise
4. Collection of Assignment 3
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Describe the steps in query processing from SQL statement to query result.
2. Explain the role of the query optimiser in a DBMS.
3. Understand relational algebra as the basis for query evaluation.
4. Describe the structure and benefits of database indexes.
5. Use MySQL's EXPLAIN statement to interpret a query execution plan.
6. Apply basic query optimisation techniques.
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. |