Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Translate a simple SELECT (15 minutes)
Translate SELECT title FROM books WHERE price < 500; into relational algebra using σ and π.
Done when: a correct π title (σ price < 500 (books)) expression is given.
Hint
σ filters first, π then narrows the columns on top of that result.
Easy 2: Name the operator (15 minutes)
Name the operator used in each: keeping only rows where customer_id = 107; keeping only the title column; combining two relations' rows.
Done when: selection, projection and union are each correctly matched.
Hint
σ filters rows, π narrows columns, ∪ combines two relations' rows.
Easy 3: Count the Cartesian product (15 minutes)
If authors has 50 rows and books has 2,000 rows, state how many rows authors × books produces.
Done when: 100,000 is correctly computed.
Hint
Multiply the two row counts: 50 × 2000 = 100,000.
Medium
Medium 1: Express a join as product plus selection (25 minutes)
Write the relational algebra for joining books and authors on author_id, both as a join operator and as an equivalent product-plus-selection.
Done when: both equivalent forms are given correctly.
Hint
books ⋈ (books.author_id = authors.author_id) authors equals σ books.author_id = authors.author_id (books × authors).
Medium 2: When rename is required (25 minutes)
Explain why joining a table to itself, such as finding employees and their managers in one employees table, requires the rename operator.
Done when: the explanation correctly states that both copies of the same relation need distinct names to refer to them separately.
Hint
Without renaming, there would be no way to tell "the employee" apart from "the employee's manager" in the same expression.
Medium 3: Union needs matching shape (25 minutes)
Explain why customers ∪ books is not a valid relational algebra expression, even though both are relations.
Done when: the explanation correctly identifies that union requires both relations to have the same columns.
Hint
Union needs the same column structure on both sides; customers and books have entirely different columns.
Hard
Hard 1: Translate a three-table query (30 minutes)
Translate a query joining orders, order_items and books to list book titles for a given order, into a relational algebra expression using σ, π and ⋈.
Done when: a correct expression chaining two joins, a selection and a projection is given.
Hint
Join orders to order_items, join that to books, then select by the given order_id and project just the title.
Hard 2: Why the optimizer avoids the literal product (35 minutes)
Explain, using the 800,000-row Cartesian product from part 2 as the scenario, why a real database never actually builds that full product before filtering, even though the join is formally defined that way.
Done when: the explanation correctly notes that building and then filtering the full product would be enormously wasteful, and that real execution strategies, covered in Level 6, avoid materializing it.
Hint
Materializing 800,000 rows just to filter almost all of them away wastes massive amounts of memory and time; real engines filter as they go instead.
Hard 3: Set difference for a real question (40 minutes)
Using set difference, express "every customer who has never placed an order," assuming a relation customers_with_orders already exists giving each customer that has at least one order.
Done when: the correct expression customers − customers_with_orders (or equivalent reasoning) is given.
Hint
customers − customers_with_orders keeps exactly the customers present in the first relation but absent from the second.