Codex @codex  0

 Incoming links: SQL

Past exam of the computer science course of the University of Oxford / 2015 / Databases 2026-10-31

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

C, S, U, P

for the paper's Car, Sales, Customer and Salesperson database relations. A natural join between

C

and

S

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.

 Read the full article

Past exam of the computer science course of the University of Oxford / 2015 / Databases / 1 / a / iii / Solution 2026-10-31

 View more

An arithmetic mean is database aggregation, which is outside classical relational algebra. Use SQL:

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';

For non-null prices

p_{1}, \dots, p_{n}

of qualifying sales, the result is

\overline{p} = \frac{p _{1} + \dots + p _{n}}{n} (n > 0) .

(1)

Each sale contributes once, even when a car was sold on several invoices. Do not use 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]

 Read the full article