Alternative databases, data warehouses, OLAP and distributed databases (HL) – IB Computer Science exam questions with mark scheme

1CoreHL only3 marks

A music-streaming company stores user profiles in a NoSQL document database instead of a relational database. Each profile is stored as one document, for example:

{ "userID": "u4471",
  "name": "Priya",
  "playlists": ["Gym", "Revision"],
  "devices": [{"type": "phone", "os": "Android"}] }

(a) Outline what is meant by a NoSQL database. [1]

(b) Outline two reasons why a document database suits these user profiles better than a relational database. [2]

(a) A database that does not store data in related tables of fixed rows and columns / is non-relational (e.g. uses documents, key–value pairs, wide columns or graphs); accept "not only SQL" only if non-tabular storage is mentioned(1)
(b) First reason, any one from: flexible / dynamic schema, so different profiles can hold different fields (e.g. extra devices, no playlists) without altering a table structure or storing many NULLs / nested or array data (playlists, devices) is stored inside one document, so a whole profile is read in one access with no joins / scales out horizontally across many servers to handle millions of users(1)
(b) Second reason: a different one from the list above, outlined and linked to the profiles. Do not award two reasons that are the same idea(1)
Answer: Non-relational storage; flexible schema; nested data read without joins (or horizontal scaling).
Examiner tip: Saying "NoSQL means no SQL" earns nothing; say how the data is stored instead of tables.

2CoreHL only3 marks

A city council uses a spatial database to manage the locations of its streetlights, roads and park boundaries.

(a) Outline what is meant by a spatial database. [2]

(b) Suggest one query the council could run that would be difficult to answer with a standard relational database that has no spatial features. [1]

Stores data about objects in geometric/geographical space, e.g. points, lines and polygons (or latitude/longitude coordinates)(1)
Supports spatial queries/indexes, e.g. distance, nearest-neighbour, "within" / overlap / intersection calculations (accept R-tree or spatial index)(1)
A query that uses location relationships, e.g. "find all streetlights within 200 m of a school", "which park boundary contains this point", "the nearest working streetlight to a reported fault", "which roads cross this flood zone"; do not accept a query that only matches a text field such as a street name(1)
Answer: Database storing geometric data (points, lines, polygons) with spatial operations; e.g. all streetlights within 200 m of a given point.
Examiner tip: The query must depend on distance, containment or overlap; filtering by a street name is ordinary SQL.

3StandardHL only5 marks

An online multiplayer game shows a live leaderboard that is updated thousands of times per second. The developers are choosing between an in-memory database and a cloud database service.

(a) Outline what is meant by an in-memory database. [1]

(b) Explain why an in-memory database suits the live leaderboard. [2]

(a) A database that keeps its data in main memory (RAM) rather than on disk / secondary storage as its primary store(1)
(b) RAM access is much faster than disk access / no disk I/O for reads and writes(1)
(b) so the very high rate of score updates and reads can be handled with low latency and the leaderboard stays current in real time(1)
(c) Risk: RAM is volatile, so data is lost if power fails or the server crashes (accept: RAM is more expensive per GB so dataset size is limited)(1)
(c) Mitigation linked to the risk: write snapshots / a transaction log to persistent storage, or replicate to another server (for cost risk: keep only hot data in memory and archive older data on disk)(1)
Answer: (a) Data held in RAM. (b) Fast access means rapid updates at low latency. (c) Volatile, data lost on failure; reduce with snapshots/logging/replication.
Examiner tip: In (c) the mitigation must fix the risk you named; "use a backup" alone is too vague.

Stuck on Alternative databases, data warehouses, OLAP and distributed databases (HL)?

Get it sorted in a free 30-minute session

We work through your weakest questions on this topic on Zoom. I reply the same day.

Free 30-min session on this topic Send me 5 questions a day

4StandardHL only4 marks

A national chain of gyms copies data each night from its booking, membership and payments systems into a data warehouse.

Identify the data-warehouse objective (from: subject-oriented, integrated, time-variant, non-volatile) that is shown by each statement. [4]

Membership records are stored with the date of every change, so the gym can compare member numbers in 2022 with 2026.
The booking system stores gender as M/F and the payments system as 1/2; both are converted to one format when loaded.
Once loaded, rows are never edited or deleted by users; new nightly data is only added.
Data is organised around themes such as "members" and "class attendance" rather than around the individual applications that created it.

(i) time-variant(1)
(ii) integrated(1)
(iii) non-volatile (accept append-only)(1)
(iv) subject-oriented(1)
Answer: (i) time-variant (ii) integrated (iii) non-volatile (iv) subject-oriented
Examiner tip: Time-variant is about keeping history over time; non-volatile is about data not being changed after loading. Do not swap them.

5HardHL only6 marks

A supermarket chain runs its tills on an operational (OLTP) relational database. Head office wants to analyse five years of sales to plan promotions and decides to build a separate data warehouse rather than run the analysis on the till database.

Explain how the append-only and query-optimized objectives of a data warehouse help head office, and why a separate warehouse is better than querying the till database directly. [6]

Append-only: new data (e.g. each day's sales) is added in batches and existing records are not updated or overwritten(1)
so the full history is kept and analyses can be repeated / compared over the five years with consistent results (accept: reports are reproducible / audit trail)(1)
Query-optimized: structured for fast reading of large volumes, e.g. denormalized / star schema, pre-aggregated summaries, column storage or extra indexes(1)
so complex analytical queries (totals by store, product, week) run quickly even across millions of rows(1)
Long analytical queries on the till database would slow down / lock tables used by live transactions, delaying customers at the tills(1)
The till database is normalized and optimized for many small inserts/updates, and may keep only recent data, so it is poorly suited to analysing five years of history (accept: warehouse can integrate data from other sources too)(1)
Answer: Append-only keeps complete, stable history; query-optimized design makes big analytical queries fast; separation protects till performance and the OLTP design is unsuited to historical analysis.
Examiner tip: Each objective needs a "so what" for the supermarket; naming the term alone gets one mark at most.

6StandardHL only5 marks

An electronics retailer stores quarterly sales (£000) in a three-dimensional OLAP cube with dimensions Region, Product and Time, as shown in the diagram.

Region	Product	Q1	Q2	Q3	Q4
North	Laptops	120	95	110	150
North	Tablets	60	55	70	85
South	Laptops	140	100	125	170
South	Tablets	75	65	80	90

(a) A manager looks only at Q3, for all regions and products. Identify this OLAP operation and calculate the total sales it shows. [2]

(b) The manager changes the Time dimension from quarters to whole years. Identify this OLAP operation and calculate the yearly sales for the South region (both products). [2]

(c) State the name of the operation that selects North, Laptops, Q1–Q2 only (restricting two or more dimensions). [1]

(a) Slice(1)
(a) 385 (£000) (accept £385 000)(1)
(b) Roll-up (accept consolidation / aggregation; do not accept drill-down)(1)
(b) 845 (£000) (accept £845 000)(1)
(c) Dice(1)
Answer: (a) Slice, 385 (b) Roll-up, 845 (c) Dice
Examiner tip: A slice fixes one dimension at one value; a dice picks a sub-cube using values from two or more dimensions.

7StandardHL only6 marks

A bank applies data mining to its customer and transaction data.

(a) Identify the data-mining technique used in each case. [4]

Flagging a card payment of £4900 at 03:00 in another country for a customer who normally spends under £60 a day.
Predicting how much a customer will save next year from their income and age.
Finding that customers who open a current account often open a savings account within three months, and then apply for a credit card.
Grouping customers into segments with similar behaviour, without any segment labels known in advance.

(b) Distinguish between classification and clustering. [2]

(i) anomaly detection (accept outlier detection)(1)
(ii) regression(1)
(iii) sequential patterns (accept sequential pattern mining; do not accept association rules, as the order in time matters)(1)
(iv) clustering(1)
(b) Classification assigns items to predefined / known classes (labels), learned from labelled training data (e.g. loan approve / reject)(1)
(b) Clustering discovers groups of similar items with no predefined labels (unsupervised); the groups are found from the data(1)
Answer: (i) anomaly detection (ii) regression (iii) sequential patterns (iv) clustering; (b) classification uses known labels, clustering finds unknown groups.
Examiner tip: If the scenario includes an order in time ("then", "within three months"), it is a sequential pattern, not an association rule.

8HardHL only6 marks

A shop mines its till data for association rules. Ten transactions are shown.

Transaction	Items
1	pasta, sauce, cheese
2	pasta, sauce
3	bread, milk
4	pasta, cheese
5	pasta, sauce, bread
6	milk, cheese
7	pasta, sauce, milk
8	bread, cheese
9	pasta, sauce, cheese
10	milk, bread

(a) Calculate the support of the item set {pasta, sauce}. [2]

(b) Calculate the confidence of the rule pasta → sauce. Give your answer to 3 significant figures. [2]

(c) The shop only accepts rules with support of at least 40% and confidence of at least 70%. Determine, with working, whether the rule pasta → cheese is accepted. [2]

(a) {pasta, sauce} appears together in 5 transactions (1, 2, 5, 7, 9)(1)
(a) support = 5/10 = 0.5 (accept 50%)(1)
(b) pasta appears in 6 transactions; confidence = support count of {pasta, sauce} / count of pasta = 5/6(1)
(b) 0.833 (accept 83.3%); allow follow-through from (a) count(1)
(c) support of {pasta, cheese} = 3/10 = 30% and confidence = 3/6 = 50%(1)
(c) Not accepted, because both are below the thresholds (support 30% < 40% and/or confidence 50% < 70%); conclusion must follow from correct or follow-through values(1)
Answer: (a) 0.5 (b) 0.833 (c) Support 30%, confidence 50%: rejected.
Examiner tip: Confidence divides by the count of the left-hand item only, not by the total number of transactions.

9HardHL only6 marks

A mail-order company stores its data in a distributed database across three sites, as shown in the diagram. The Customer table is split between the sites and the Product table is held in full at every site.

(a) Identify the type of partitioning used for the Customer table. [1]

(b) Explain one benefit of partitioning the Customer table in this way. [2]

(d) Outline one disadvantage of replicating the Product table. [1]

(a) Horizontal partitioning (accept sharding; do not accept vertical)(1)
(b) Each site stores the customer rows it uses most (its own region), so most queries are answered locally(1)
(b) giving faster responses / less network traffic over the WAN (accept: each site handles a smaller table, so load is spread and the system scales)(1)
(c) If one site or the WAN link to it fails, the Product data is still available at the other sites (and locally at each site)(1)
(c) so queries / orders that need product details can still be processed and there is no single point of failure(1)
(d) Every change to Product (e.g. a price update) must be applied to all three copies, increasing update traffic/time, and copies may become temporarily inconsistent (accept: extra storage used)(1)
Answer: (a) Horizontal (b) local access to regional rows, faster/less WAN traffic (c) other copies remain available if a site fails (d) updates must be propagated to all copies; risk of inconsistency.
Examiner tip: Horizontal partitioning splits by rows (here by Region); vertical splits by columns. Check which the diagram shows.

10StretchHL only8 marks

A theatre chain runs a distributed database with sites in three cities. Customers can book any performance from any site.

(a) Describe what is meant by location transparency in a distributed database. [2]

(b) Two customers, at the Cardiff and Belfast sites, try to book the last remaining seat for the same performance at the same moment. The seat data is stored at a third site, Norwich. During one booking the network link to Norwich fails part-way through.

Explain how concurrency control and the ACID properties ensure that the seat is not double-booked and that the database stays correct. [6]

(a) Users / applications access data without needing to know at which site it is physically stored(1)
(a) Queries are written as if the database were a single local database; the DBMS finds and routes the request to the correct site(s)(1)
(b) Concurrency control: the first transaction obtains a lock (exclusive/write lock) on the seat record (accept timestamp ordering / optimistic validation)(1)
(b) The second transaction must wait (or is rejected/rolled back) until the lock is released, so it then sees the seat as already booked(1)
(b) Isolation: concurrent transactions behave as if run one after another, so neither reads an uncommitted / out-of-date seat status (no lost update)(1)
(b) Atomicity: the booking (mark seat taken + record payment/booking) happens completely or not at all(1)
(b) When the link fails mid-transaction, the incomplete transaction is rolled back (e.g. coordinated by a two-phase commit, which only commits if all sites vote ready), so no half-booking remains(1)
(b) Consistency / durability: the database moves only between valid states (a seat has at most one booking) and once a booking is committed it is permanently stored (e.g. via logs) even if a site later fails(1)
Answer: (a) Data location hidden from users; DBMS routes requests. (b) Locking serialises access to the seat; isolation stops lost updates; atomicity + two-phase commit roll back the interrupted booking; consistency and durability keep valid, permanent results.
Examiner tip: Link each ACID property to the seat scenario; listing A, C, I, D with textbook definitions earns little.

Stuck on this topic?

Book a free 30-minute session on WhatsApp and we'll find exactly which step is costing you the marks. Or build a timed paper on it.

Free 30-minute session