Translate SQL for a database systems course
This SQL to relational algebra converter turns a supported SELECT query into a canonical expression and a query tree, then shows a version rewritten with selection pushdown and joins. Use it for Database Systems homework, query-processing practice or a final exam review where you need to explain how a declarative query becomes a sequence of relational operations. The loaded join example lets you compare both representations immediately.
The converter analyzes query text in your browser. It does not connect to a database, inspect a schema or execute SQL against sample rows. Its optimized tree illustrates specific logical rewrites, not a measured execution plan. There are no row-count estimates, index choices, timing results or claims that a particular database will use the same physical plan.
The translation rule and symbols
For a simple SELECT list FROM R, S WHERE condition, the canonical rule is projection(list, selection(condition, product(R, S))). In standard symbols that is π over σ over ×. Projection π keeps the requested columns; selection σ keeps rows satisfying the predicate; product × forms pairs of rows. A join ⋈ combines relations under a join condition, and rename ρ represents a table alias.
Read the tree upward from its leaves. Relation nodes supply inputs to selections or joins, and the root produces the requested output columns. A SELECT clause is a projection in this terminology, while the WHERE clause is a selection. Confusing SQL's SELECT keyword with the relational selection operator is a common source of reversed answers on database exams.
The page renders operator subscripts for readability. Copied text uses plain subscript markers around column lists and predicates, so its raw appearance differs from the visual display. Copying an expression preserves the notation for notes; it does not produce a runnable SQL statement or automatically compile a document's math syntax.
Worked join example from the library
Choose Join with WHERE. Its query is SELECT e.fname, e.lname, d.dname FROM Employee e, Department d WHERE e.dno = d.dnumber AND d.dname = 'Research'. The aliases e and d name Employee and Department. The converter library produces a canonical tree with a projection above the full WHERE selection, above the product of the renamed relations.
| Stage | Operation produced |
|---|---|
| Relation inputs | Rename Employee to e and Department to d |
| Canonical combination | Product of the renamed relations |
| Canonical filter | e.dno = d.dnumber ∧ d.dname = 'Research' |
| Output projection | e.fname, e.lname, d.dname |
| Optimized department input | Select d.dname = 'Research' before combining |
| Optimized combination | Join on e.dno = d.dnumber |
The library reports 2 rewrite explanations for this example. It pushes the Research predicate to d because that condition references only Department. It then replaces the product and cross-relation predicate with a join. The final projection remains above the join and keeps the same requested columns. These are structural transformations; the tool has no employee data from which to calculate the number of matching people.
The separate Simple selection example uses SELECT name, salary FROM Employee WHERE salary > 50000. The canonical and optimized trees are identical: the selection is already directly above Employee. An unchanged tree is a useful result when no supported rewrite can improve its structure; it is not an error or evidence that the input failed to parse.
Why qualified columns matter
In a multi-table query, write d.dname instead of an unqualified dname when you intend a predicate to apply to Department. Without schema information, the translator cannot infer the owner of an unqualified column. Its explanation may retain that predicate above the combination rather than guessing which input can be filtered.
Selection pushdown can reduce the rows passed to a later operation, but this tool cannot quantify the reduction. The canonical product is a logical expression, not a claim that a database literally materializes every pair before filtering. Production optimizers may choose indexes, rearrange inner joins and apply additional transformations based on statistics. The displayed heuristic version preserves the input relation order rather than searching all possible join orders.
Grouping, aggregates and HAVING
The converter supports GROUP BY and aggregate calls COUNT, SUM, AVG, MIN and MAX. It represents grouping with γ and a HAVING condition as a selection above the grouped result. In contrast, a WHERE condition filters the input before grouping. Keep that distinction when tracing which rows contribute to an aggregate.
Load GROUP BY to inspect the library-generated example: it groups Employee by dno, names COUNT(*) as staff and AVG(salary) as avg_pay, then applies COUNT(*) > 2. The tree places that condition above γ and the requested output projection above the condition. This is an extended relational-algebra representation for aggregation, so match the notation conventions used in your course solution.
Supported syntax and interpretation limits
The parser accepts comma-separated relations, inner JOIN … ON, NATURAL JOIN, aliases, comparisons and Boolean conditions with AND, OR, NOT and parentheses. It supports DISTINCT within its set-based algebra model. SQL normally retains duplicates unless instructed otherwise, while classical projection removes them. Treat this output as a teaching translation; do not assume it models every SQL bag-semantics or NULL detail.
Subqueries, UNION, outer joins and ORDER BY are outside this translator's supported input. Ordering is not an operator in the classical unordered relation model used here, even though database systems can extend their internal algebra to represent sorting. Unsupported syntax produces a message rather than an execution result. Start from a supplied example and add clauses gradually when diagnosing a parser issue.
Practice and next steps
For a written answer, label each relation, predicate and output attribute before drawing the tree. Check that a moved predicate references only the intended input, and keep a join condition distinct from a single-relation filter. Compare your derivation with the SQL translation guide. Use the SQL formatter for readable query text, or study dependencies with the attribute closure calculator. Formatting and dependency analysis answer different questions from query translation.