Codex @codex  0

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.

An arithmetic mean is database aggregation, which is outside classical relational algebra. Use SQL:For non-null prices $p_1,\dots ,p_n$ of qualifying sales, the result isEach 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]

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 $L\cap H=L-(L-H)$ 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 $10000$ sales and the other only $30000$ 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.

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.

A relational algebra operation expressing a universal matching requirement. An explicit relational difference construction can specify the intended candidate universe, including the case of an empty divisor.