Question 5 of 8

Question 5 (10+10=20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Four relations: Employee(ID,Name,Address), Supplier(ID,Name), PurchaseOrder(OrderID,EmpIssuerID,SupplierID,Date), PurchaseItem(ItemID,OrderID,ItemName,ItemCost); each PurchaseOrder is issued by one employee to one supplier, and each PurchaseItem belongs to exactly one order.

Find. (i) total item cost per supplier; (ii) employees who ordered from EVERY supplier — each expressed first in SQL, then in relational algebra.

Approach. Query (i) is a straightforward join-then-group-by aggregate. Query (ii) is a classic relational division: "every supplier" means the set of suppliers an employee ordered from must be a superset of ALL suppliers, which SQL expresses with a double negation (no supplier exists that the employee did NOT order from) and relational algebra expresses directly with the division operator ÷.

1. (a)(i) SQL — total cost per supplier. Join Supplier to PurchaseOrder to PurchaseItem and aggregate.

   SELECT s.Name, SUM(pi.ItemCost) AS TotalCost
   FROM   Supplier s
          JOIN PurchaseOrder po ON po.SupplierID = s.ID
          JOIN PurchaseItem  pi ON pi.OrderID    = po.OrderID
   GROUP BY s.ID, s.Name;

   Grouping by both s.ID and s.Name (not Name alone) guards against two different suppliers sharing a name. A supplier with zero orders is dropped by the inner joins; a LEFT JOIN in its place would instead list it with a NULL/zero total, which is a reasonable alternative reading but not what "total cost of all items ever ordered" strictly requires.
2. (a)(ii) SQL — employees who ordered from every supplier (double NOT EXISTS). "No supplier exists such that this employee never ordered from it":

   SELECT e.Name
   FROM   Employee e
   WHERE  NOT EXISTS (
            SELECT s.ID FROM Supplier s
            WHERE NOT EXISTS (
              SELECT 1 FROM PurchaseOrder po
              WHERE po.EmpIssuerID = e.ID AND po.SupplierID = s.ID
            )
          );

   The inner NOT EXISTS finds suppliers this employee has NOT ordered from; wrapping it in an outer NOT EXISTS keeps only employees for whom that inner set is empty — i.e. employees with no missing supplier.
3. (b)(i) Relational algebra — total cost per supplier. Basic relational algebra has no aggregate operator, so this uses the standard extended-RA generalized projection / group-by operator, written 𝔹:

   Joined  = PurchaseOrder ⋈OrderID PurchaseItem
   PerSup  = SupplierID𝔹SUM(ItemCost)→TotalCost(Joined)
   Result  = πName,TotalCost( Supplier ⋈ID=SupplierID PerSup )

4. (b)(ii) Relational algebra — every supplier, via division. Project the employee/supplier pairs actually ordered, then divide by the full supplier set:

   EmpSup   = πEmpIssuerID,SupplierID(PurchaseOrder)
   AllSup   = πID(Supplier)
   Qualified = EmpSup ÷ AllSup            (÷ = relational division)
   Result    = πName( Employee ⋈ID=EmpIssuerID Qualified )

   Division keeps exactly the EmpIssuerID values whose full slice of matching SupplierID values contains ALL of AllSup — the algebraic mirror of the SQL double-NOT EXISTS.

Both forms were checked against a toy SQLite database: the SQL correctly returns only Alice for (a)(ii), and the per-supplier totals match a hand aggregate.