Aggregate SQL, database views and ACID transactions
Build analytical SQL, compare view types and explain reliable transaction processing under failure and concurrency.
Reporting + reliability
- construct average, COUNT, MAX, MIN and SUM on grouped data;
- describe virtual and materialized views for complexity, consistency, independence, performance, simplification, read/update behaviour and security;
- describe ACID;
- use BEGIN TRANSACTION, COMMIT and ROLLBACK in transaction reasoning.
Rapid Recall Deck
Attempt each card first. If a term is new, flip and study it, then answer it again without looking. Mark only secure recall as “Know it”.
Grouped aggregate report
Five aggregates
Using VISIT(VisitID, PatID, DocID, VisitDate, Treatment, Cost), construct a query that returns each doctor with visit count, average cost, minimum cost, maximum cost and total cost.
SELECT DocID,
COUNT(*) AS VisitCount,
AVG(Cost) AS AverageCost,
MIN(Cost) AS MinCost,
MAX(Cost) AS MaxCost,
SUM(Cost) AS TotalCost
FROM VISIT
GROUP BY DocID;SQL uses AVG() for the Guide’s required average calculation.
Aggregate filters
Decision query
Using VISIT, construct a grouped query that shows only doctors whose total billed cost exceeds 10,000 and whose average visit cost is at least 200, with the highest total first.
SELECT DocID, AVG(Cost) AS AverageCost, SUM(Cost) AS TotalCost FROM VISIT GROUP BY DocID HAVING SUM(Cost) > 10000 AND AVG(Cost) >= 200 ORDER BY TotalCost DESC;
Virtual vs materialized
Virtual view
Reception sees only PatientName and VisitDate through a view. Explain benefits.
The view hides sensitive/complex base-table details, simplifies repeated queries and gives a logical interface based on current underlying data. Simple views may be updatable depending on DBMS/query rules.
Materialized view
A dashboard reruns an expensive hourly summary. Explain the benefit/cost.
A stored snapshot speeds repeated reads, but uses storage and must be refreshed; it can be stale between refreshes.
Required design dimensions
Describe the view trade-offs
Explain how views can affect data complexity, consistency/independence, performance, query simplification, read-only/updatable behaviour and security.
Views hide schema/query complexity and can give users/apps a stable simplified interface. Virtual views may add query work; materialized views can improve reads but need refresh/storage. Updatability depends on view complexity/DBMS; many aggregate/materialized views are read-focused. Restricting rows/columns supports security.
Explain one transaction
Bank transfer
BEGIN TRANSACTION; UPDATE ACCOUNT SET Balance = Balance - 100 WHERE AccountID = 1; UPDATE ACCOUNT SET Balance = Balance + 100 WHERE AccountID = 2; COMMIT;
Use the transfer to explain atomicity, consistency, isolation and durability.
Atomicity: debit+credit succeed together or neither remains. Consistency: constraints/rules remain valid. Isolation: concurrent work does not act on an invalid halfway state. Durability: after COMMIT, the successful result persists after failure.
Failure + concurrency
ROLLBACK
The debit succeeds but the credit fails before commit. What should happen?
Issue ROLLBACK so the uncommitted debit is undone; this directly demonstrates atomicity and helps preserve consistency.
Isolation
Two users buy the final item at the same time. What can go wrong without isolation?
Both may read “1 available” before either commits and both sell it. Isolation/concurrency control prevents invalid interleaving/stale decisions.
HL database mini-set
Paper 1 practice
(1) Construct department COUNT + AVG salary query. (2) Describe virtual vs materialized view. (3) Explain isolation.
SELECT Department, COUNT(*) AS StaffCount, AVG(Salary) AS AverageSalary FROM EMPLOYEE GROUP BY Department;
Virtual view computes from base data when used; materialized stores a refreshable result snapshot. Isolation prevents concurrent transactions from exposing/acting on invalid intermediate states.
Finish the learning cycle
Case study / exam preparation
Post-IA focus: add one case-study note about reliable large-scale data storage/processing, then continue Paper 1 retrieval.
Paper 1 — short practice
Write one grouped aggregate query, compare view types and explain ACID from memory.