Integrated Database Project: SQL and Big Data

AQA 4.10, 4.11 — Integrated database project

Retrieval: this capstone lesson draws on every lesson in this sequence — relational modelling, SQL querying/joins/aggregation, transactions/integrity, and Big Data evaluation — applied together to realistic scenarios, exactly as a genuine workplace problem would require.

Understand — the business-problem method

A real request rarely arrives as a ready-made SQL query. It arrives as a question in plain English, and answering it well requires working through, in order: what data is actually needed → how the relevant tables relate to each other → what query structure answers the question → how to verify the result is genuinely correct, not merely "a table of numbers that looks plausible."

Worked business problem 1

"Which customers have spent the most money with us overall, and what should we consider before rewarding just the top spender?"

• What data is needed: every customer's total spend across all their order items.
• How tables relate: customers → orders (one-to-many) → order_items (one-to-many) → products (for price).
• Query:

SELECT customers.name, SUM(products.price * order_items.quantity) AS total_spent
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON order_items.product_id = products.id
GROUP BY customers.name
ORDER BY total_spent DESC;

• Verify: spot-check one customer's total by hand-adding their individual order items' (price × quantity) before trusting the aggregated figure.
• Justify/evaluate: rewarding only the single top spender ignores customers who are reliably, repeatedly profitable over time versus one unusually large single purchase — a genuine analytical judgement beyond just running the query.

Worked business problem 2

"Are we at risk of running out of stock on any product customers are actively ordering?"

• What data is needed: each product's current stock, compared against how much of it has recently been ordered.
• How tables relate: products ← order_items (one-to-many).
• Query:

SELECT products.name, products.stock, SUM(order_items.quantity) AS total_ordered
FROM products
JOIN order_items ON order_items.product_id = products.id
GROUP BY products.name, products.stock
HAVING products.stock < SUM(order_items.quantity)
ORDER BY products.stock ASC;

• Verify: manually confirm at least one flagged product's stock genuinely is lower than its summed ordered quantity.
• Justify: explain why HAVING, not WHERE, is required here (the comparison depends on an aggregated SUM, exactly the earlier lesson's WHERE/HAVING distinction).

Evaluate — when this scenario would outgrow a relational database

This course's sample business is tiny — every query above runs instantly. Evaluate: if this retailer grew to millions of customers and billions of order-item rows worldwide, with live stock updates needed within seconds across thousands of warehouses, would a single relational database (as used throughout this sequence) remain appropriate?

(At that scale, a single relational database would face genuine strain: JOIN and GROUP BY operations that are instant on 7 orders would become expensive at billions of rows; a single machine likely could not hold or process the data fast enough (Volume, Velocity). The BUSINESS QUESTIONS themselves (top spenders, stock risk) remain conceptually identical - what changes is the INFRASTRUCTURE needed to answer them at scale, likely requiring the kind of distributed processing (MapReduce-style) covered in the previous lesson, rather than a fundamentally different set of questions.)

Check your understanding

A manager asks: "Which product category generates the most revenue, and does that match how much stock we're currently holding in that category?" Using the business-problem method (what data → how tables relate → what query → how to verify), outline your approach without writing the full query. (4 marks)

(What data: category, price and quantity sold per order item, plus current stock, all grouped by category. How tables relate: order_items -> products (for price, category and stock) via product_id. Query structure: JOIN order_items to products, GROUP BY category, SUM(pricequantity) for revenue and SUM(stock) for total stock held, ordered by revenue descending. Verify: manually check one category's revenue and stock figures against the raw order_items/products rows for that category before trusting the aggregated comparison.)*

Challenge

A colleague proposes moving this course's entire sample database to a Big Data platform "to future-proof it." Evaluate this proposal, and justify your recommendation.

(Not appropriate at the current scale - 5 customers, 6 products and 7 orders face no volume, velocity, variety or veracity problem at all; a relational database handles this instantly and simply. Adopting Big Data infrastructure here would add genuine cost and complexity (skills, infrastructure, governance, per the previous lesson) with no corresponding benefit - the right recommendation is to keep the current relational approach, and revisit the decision only if genuine scale/speed/variety problems actually emerge.)

This sequence's SQL and Big Data content is now complete. A synoptic assessment combining these database concepts with Sequence 12's advanced data structures remains planned for later in the course, as previously discussed.

Looking ahead: Sequence 17 (Functional Programming) returns to a programming-intensive focus — a genuinely different paradigm from the object-oriented work in Sequence 11.