Database query Created 2026-10-31 Updated 2026-11-05
A database query requests information computed from stored data. It can filter database tuples, project selected database attributes, combine database relations or aggregate groups. For example, a query can return the count of orders per customer rather than retrieving every order separately. The query specifies a result; a database query plan chooses operations for computing it. Equivalent result specifications can have very different costs under different indexes and data sizes. In SQL, duplicate rows and null values require conventions beyond the simplest mathematical relational algebra.
Database query language Created 2026-10-31 Updated 2026-11-05
A database query language expresses requests over a database using a defined syntax and semantics. A declarative language describes the desired result while allowing a query optimizer to choose its evaluation. SQL is a prominent example for relational databases; relational algebra supplies operations useful for representing and transforming query plans. Expressive power, treatment of duplicates and null values, and the ability to update data vary by language. A query that is syntactically accepted is not automatically safe or efficient on arbitrary datasets.
Solutions to all three questions of the examination dated 3 June 2015. The cover permits answers to two questions.[ref]
Use classical relational algebra with set semantics. Write for the paper's
Car, Sales, Customer and Salesperson database relations. A natural join between and matches serialnumber; joins involving a salesperson explicitly match salespersonid to empid. Identifiers, rather than names, distinguish customers and salespeople. The calculations use the ordinary non-null relational model; the SQL aggregate below also explains its empty-input behavior. Past exam of the computer science course of the University of Oxford 2015 Databases 1 a iii Solution 2026-10-31
An arithmetic mean is database aggregation, which is outside classical relational algebra. Use SQL:For non-null prices of qualifying sales, the result isEach sale contributes once, even when a car was sold on several invoices. Do not use
SELECT AVG(s.price) AS average_sale_price
FROM Sales AS s
JOIN Car AS c ON c.serialnumber = s.serialnumber
WHERE c.make = 'Saab';DISTINCT inside AVG: two different sales at the same price must both count. The car's database primary key makes the relational join match each sale to exactly one car, so the relational join introduces no extra weighting.If there are no qualifying non-null prices,
AVG returns SQL NULL. It ignores null prices if the implementation permits them; it does not replace an absent arithmetic mean with zero.[ref]Aggregate Functions in the PostgreSQL documentation. Section 9.21,
avg and the paragraph on aggregates with no selected rows Past exam of the computer science course of the University of Oxford 2015 Databases 2 a Solution 2026-10-31
Find the salesperson identifiers satisfying each price condition using relational selection and relational projection:Intersect these identifier sets, relational join the result to the salesperson records, and apply relational projection to names:If intersection is not a primitive of the chosen relational algebra, use with relational difference. There is no need to access
Car: the foreign key already ensures that every sale refers to a car.Intersecting names instead of identifiers is wrong. Two different salespeople called Alex, one making only sales and the other only sales, would make Alex appear in both name sets although neither person satisfies the condition. Nor can one sale satisfy both price tests: the two distinct prices require distinct sale records.
Relational algebra 2026-10-31
An algebra of operations on database relations. Classical relational algebra has relational selection, relational projection, relational join, Cartesian product, union and relational difference; database aggregation requires an extension.
Relational model Created 2026-10-31 Updated 2026-11-05
The relational model represents data as database relations, which are sets of database tuples over specified value domains. A relational schema names the database attributes and states database integrity constraints; an instance supplies their current values. Relational algebra describes operations on this logical data, while storage order, indexes and access paths belong to its implementation.
For example, the database relation
Reading(sample, value) can contain . A relational selection for values above five followed by relational projection onto sample identifiers returns . Changing the underlying file layout or adding an index need not change that query's meaning. Mathematical database relations have set semantics; SQL often uses bag semantics and has additional SQL NULL behavior.In his 1970 paper, E. F. Codd argued for data independence: application logic should not rely on particular stored orderings, indexes or navigational paths. His relational formulation made that separation central to database design.[ref]
Safe tuple relational calculus 2026-11-05
Safe tuple relational calculus restricts tuple relational calculus so that output values come from the finite input relations or explicit constants, with suitable restrictions on quantification. This prevents an apparently simple database query from demanding an infinite result over an unrestricted value universe. Database query safety is a semantic requirement; merely writing a familiar-looking formula does not ensure it.[ref]
Relational Model slides for Database System Concepts by Abraham Silberschatz and others. slides3.82 and3.86, Safety of Expressions
For a finite unary relation , the database query safely returns . In contrast, over all integers requests every other unary database tuple, an infinite collection. Restricting negation to tuples already drawn from another finite relation gives an expressible finite set difference. Under the usual finite relational model, the safe calculus and relational algebra express the same queries.
Tuple relational calculus 2026-11-05
Tuple relational calculus describes query answers by predicates over database tuples rather than a sequence of storage operations. A set-builder expression returns tuples satisfying a formula built from relation membership, attribute comparisons, logical connectives and quantifiers. For example,selects employee tuples whose salary exceeds100. A join can be expressed using existentially quantified tuples from two relations and equality of their shared identifying attribute.
The formula needs a safety restriction. Over an unrestricted infinite value universe, a query such as can demand infinitely many absent tuples. Safe tuple relational calculus restricts results to information grounded in the input and explicit constants. Under the usual finite-relation assumptions, its expressive power agrees with relational algebra. This gives a declarative query language while leaving the database system free to choose an efficient query plan.