CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Procedural or declarative (15 minutes)

State whether relational algebra and relational calculus are each procedural or declarative.

Done when: algebra is correctly marked procedural and calculus declarative.

Hint

Algebra describes steps; calculus describes a condition on the result.

Easy 2: Read a tuple calculus expression (15 minutes)

State in plain English what { t | t ∈ books ∧ t.price < 500 } returns.

Done when: the description correctly matches every book priced under 500.

Hint

It returns every row of books where price is under 500.

Easy 3: Translate to SQL (15 minutes)

Translate { t | t ∈ customers ∧ t.address = 'Pune' } into SQL.

Done when: a correct SELECT * FROM customers WHERE address = 'Pune'; is given.

Hint

SELECT * FROM customers WHERE address = 'Pune';

Medium

Medium 1: Write tuple calculus for a query (25 minutes)

Write the tuple relational calculus expression for "every author whose name starts with 'A'."

Done when: a correct expression using a tuple variable and a condition on its name attribute is given.

Hint

{ t | t ∈ authors ∧ t.name starts with 'A' }, matching SQL's LIKE 'A%'.

Medium 2: Convert tuple to domain calculus (25 minutes)

Convert { t | t ∈ authors ∧ t.name = 'Rohan Mehta' } into domain relational calculus, naming each attribute's value separately.

Done when: a correctly structured domain calculus expression with separate variables per attribute is given.

Hint

{ ⟨n⟩ | ⟨..., n, ...⟩ ∈ authors ∧ n = 'Rohan Mehta' }, naming the name attribute as its own variable.

Medium 3: Same query, three notations (25 minutes)

Write "every book under 500" as relational algebra, tuple relational calculus, and SQL, and confirm all three describe the same result.

Done when: all three forms are given correctly and shown to match.

Hint

Algebra: σ price < 500 (books); calculus: { t | t ∈ books ∧ t.price < 500 }; SQL: SELECT * FROM books WHERE price < 500;.

Hard

Hard 1: A condition calculus cannot express cleanly (30 minutes)

Explain why finding "the single most expensive book" is awkward to express directly in relational calculus, compared to how naturally an aggregate function expresses it in SQL.

Done when: the explanation correctly notes that calculus describes membership conditions on individual tuples, not comparisons across the whole set like "the maximum."

Hint

"Maximum" compares every tuple against every other tuple, which a single-tuple membership condition cannot express without extra machinery.

Hard 2: Declarative does not mean inefficient (35 minutes)

Explain why writing a query declaratively, as calculus or SQL, does not prevent a database from running it efficiently, even though the query itself names no steps.

Done when: the explanation correctly points to the query optimizer as the part of the system responsible for choosing efficient steps.

Hint

The optimizer translates a declarative request into an efficient procedural plan; the person writing the query never has to choose that plan.

Hard 3: Translate a join into tuple calculus (40 minutes)

Write the tuple relational calculus expression for "every book title, together with its author's name," joining books and authors.

Done when: a correctly structured expression using two tuple variables and a condition linking their author_id values is given.

Hint

{ ⟨bt, an⟩ | ∃ b ∈ books, a ∈ authors (b.title = bt ∧ a.name = an ∧ b.author_id = a.author_id) }.