ISYS2120 Chap.5 Relational Algebra and Advanced SQL
Relational Algebra and Advanced SQL
Relational Algebra and Advanced SQL exposes the intermediate relations behind a query. Selection keeps rows, projection keeps columns, set operators compare compatible relations, joins combine matching tuples, and rename resolves schema-level naming problems. Because every operator returns a relation, a complex expression can be traced one result at a time and rearranged with equivalence rules.
Relational division supplies the demanding universal pattern: find candidates connected to every required tuple. The SQL half extends that reasoning through GROUP BY and HAVING, NULL-aware aggregates, outer joins, nested subqueries and correlated EXISTS tests. Examination answers must translate among an English request, algebra and SQL without confusing SQL SELECT with algebraic selection.
The reliable endpoint is always a computed relation whose rows can be checked against the original stakeholder request.
What this chapter covers
- 01
Use algebra to expose query structure
- 02
Select rows before expensive combinations
- 03
Project attributes with intent
- 04
Rename to control scope
- 05
Construct and constrain products
- 06
Apply set operators compatibly
- 07
Recognise division questions
- 08
Translate algebra into SQL
- 09
Use subqueries for staged reasoning
- 10
Express universal conditions with NOT EXISTS
- 11
Compare inner and outer joins
- 12
Cross-check advanced queries
- 13
Prove two query forms are equivalent
Use division for an all-tools request
- +1The divisor Required contains Hammer and Drill, so a candidate depot must have a ToolStock tuple paired with each of those values.
- +1D1 has both required pairs. D2 has Hammer but lacks Drill. D3 has both required pairs; its extra Saw tuple does not disqualify it.
- +1Form the quotient on depot_id. Retain D1 and D3, and exclude D2 because one required pairing is missing.
- +1Run the equivalent double-NOT-EXISTS SQL and compare its ordered output with the hand-computed quotient.
Key terms
- Relational algebra
- A language whose operands and results are relations. Expressions combine operators so each intermediate result can feed the next operation.
- Relation
- A set of tuples over named attributes and their domains. In the mathematical model, NULL, duplicate tuples and row order are absent.
- Expression
- A composition of relation variables, constant relations and operators that calculates a new relation.
- Selection
- An algebra operator that retains only tuples satisfying a Boolean predicate while preserving the input attributes.
- Projection
- An algebra operator that keeps the listed attributes and removes duplicate tuples from the resulting relation.
- Set difference
- The tuples that occur in the first union-compatible relation but not in the second. Corresponding attributes must share names and domains.
- Theta-join
- A join that pairs tuples from two relations and retains only combinations satisfying an explicit Boolean condition.
- Rename
- A schema-level algebra operator that gives a relation or its attributes new names so later operations are well defined.
- Relational division
- An operator returning candidates related to every tuple in a divisor relation. It expresses universal all-required-items requests.
- GROUP BY
- A SQL clause that partitions surviving rows into groups sharing the listed attribute values before aggregate results are formed.
- HAVING
- A SQL clause that filters completed groups using grouping attributes or aggregate conditions.
- Outer join
- A join that preserves specified unmatched input rows and supplies NULL for attributes from the missing side.
- Correlated subquery
- A nested query that refers to a row from its outer query and is evaluated in that row's context, often with EXISTS.
Relational Algebra and Advanced SQL FAQ
Why use relational algebra when SQL already runs the query?
Algebra exposes the operator structure independently of SQL syntax. It makes selection, projection, joins, set operations and division visible, helping you locate accidental products and reason about equivalent query plans.
What must be true before applying a set operator?
The two inputs must be union compatible: corresponding attributes need compatible domains and aligned meanings. Matching column counts alone is insufficient if one position represents a member identifier and the other represents an event identifier.
Which information needs suggest relational division?
Look for universal language such as every required station or all active workshops. Division returns candidates related to every member of the divisor, unlike an ordinary join that establishes only at least one matching fact.
How does double NOT EXISTS express an all condition?
The outer query keeps a candidate when there does not exist a required item for which no matching fact exists. Both correlations matter; removing one can reverse the meaning or test an unrelated global condition.
When does a left join stop behaving like an outer join?
A condition on the nullable right side placed in WHERE can reject the NULL-extended unmatched rows, effectively restoring inner-join behaviour. Put the qualifying condition in ON when unmatched left rows must remain visible.
Exam move
Use a trace-table routine for relational algebra. Write the schema and tuples of every input, calculate one operator at a time, and label each intermediate relation before composing the final expression. Alternate translation directions: English to algebra, algebra to plain stakeholder purpose, SQL to algebra, and algebra back to SQL.
Keep the crucial vocabulary contrast visible: SQL SELECT corresponds to algebraic projection, while SQL WHERE corresponds to algebraic selection. For joins and set operations, verify schema compatibility before touching tuples. For division, list the divisor and audit every candidate against every required value.
In advanced SQL, run GROUP BY and HAVING on hand-counted groups, compare COUNT(*) with NULL-ignoring aggregates, and draw unmatched rows before choosing an outer join. Finish each session with one correlated subquery and its double-NOT-EXISTS reading. The examination rewards a meaningful request and a checkable resulting relation, so never stop at reciting operator names.
Working through Relational Algebra and Advanced SQL in ISYS2120? Sia is AskSia’s AI Information Technology tutor — ask any ISYS2120 Relational Algebra and Advanced SQL question and get a clear, step-by-step explanation grounded in how ISYS2120 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
