Alternative approaches to storing data

Data warehouses and business intelligence

OLAP and data mining

Distributed database features

Database approaches suit different data structures, hosting arrangements and access requirements. To outline each approach, identify what sets it apart and the storage need it addresses.

### Four approaches

**NoSQL** covers database approaches that do not require the conventional relational table model. Records can vary in structure.

A **cloud database** runs on cloud computing infrastructure and is accessed over a network. Hosting does not dictate the data model: it can be relational or NoSQL.

A **spatial database** stores data about locations, shapes and spatial relationships, and supports queries on that data. Rather than just matching ordinary attribute values, it can answer questions about distance, containment or overlap.

An **in-memory database** keeps its working data primarily in main memory instead of relying on secondary storage for each access. Avoiding repeated storage access supports rapid processing. Keeping data after power loss, though, requires additional persistence arrangements.

The comparison below shows what each approach changes and the storage need it addresses.

{{a137137cc14102ac0b6f4b522abf0ae64d7e10af}}

| Approach | Distinguishing feature | Storage need it addresses |
|---|---|---|
| NoSQL | Data need not use a conventional relational table structure | Records with varied or changing attributes |
| Cloud | Database runs on cloud infrastructure | Network access and adjustable hosting capacity |
| Spatial | Data and operations represent geographic or geometric relationships | Queries involving locations, areas and distances |
| In-memory | Working data is held primarily in main memory | Very low-latency access and processing |

### Matching an approach to a use

A geographic information system for the invented **Merebank Flood Service** could use a spatial database to find which monitoring stations lie inside a flood-warning area. Here, the key capability is querying the relationship between points and an area.

For real-time analytics, the invented **StagePulse Monitoring** service could use an in-memory database to keep current equipment readings ready for rapid analysis. These are not the only applications; the choice depends on the data and the operations required.

The categories can overlap. A spatial database could run in the cloud, while a cloud-hosted NoSQL database could use memory to accelerate access.

**Key terms:** NoSQL, cloud, spatial, in-memory.

Four parallel panels comparing database approaches: NoSQL—varied or changing attributes; Cloud—cloud infrastructure and network access; Spatial—locations, areas and distances; In-memory—working data in main memory and low-latency access. Show these as different characteristics, not mutually exclusive categories.

A **data warehouse** brings historical data from different sources into one store for analysis and reporting. It primarily supports decisions, rather than handling the continual changes that everyday transactions involve.

**Business intelligence** uses organizational data to produce information that supports decisions. By providing a consistent historical view across operational systems, a warehouse makes this possible.

### From operational records to decision-making

The invented organization **Lantern Equipment Hire** keeps separate records for bookings, payments and maintenance. Managers can investigate how demand, revenue and maintenance vary over time once a warehouse combines the relevant data from these systems.

The figure traces this relationship. Bookings, payments and maintenance supply integrated historical data to the data warehouse; the warehouse then supports analysis and business decisions.

{{8f2c404b929b4bed3cc55ec15d33300199ba1be4}}

### Why warehouse data has particular characteristics

| Characteristic | Meaning | Contribution to storage and analysis |
|---|---|---|
| **Append-only data** | New records are added; existing historical records are normally kept | Keeps earlier observations available so changes and trends can be investigated |
| **Subject-oriented data** | Data is organized around subjects such as equipment, customers or revenue | Brings together the information needed to answer questions about a business subject |
| **Integrated data** | Data from different sources is combined using consistent names, formats, units and identifiers | Makes records comparable and stops differences between source systems from distorting results |
| **Time-variant data** | Records keep their historical context, such as the date or period they relate to | Supports comparisons between periods and analysis of trends |
| **Non-volatile data** | Once loaded, data stays stable rather than being routinely changed or deleted by operational activity | Gives repeated historical analysis a dependable basis |
| **Data optimized for query performance** | Data is arranged so analytical retrieval and aggregation are efficient | Reduces the work needed to answer large-scale reporting questions |

### How the characteristics work together

Suppose one source records hire duration in hours while another uses days. Before managers compare utilization, integration must reconcile those units. Bookings and maintenance also need a common equipment identifier so they refer to the same equipment.

Dated records make the data time-variant, allowing managers to compare equipment demand across periods. Keeping earlier records instead of replacing them preserves the evidence behind those comparisons.

Append-only and non-volatile are related, but they describe different ideas. Append-only concerns how new information is added; non-volatile concerns how stable information remains once loaded. Both describe normal analytical operating characteristics. They don't rule out exceptional corrections.

Query optimization serves a different workload from transaction processing. To find totals across many historical bookings, a manager needs arrangements that make aggregation efficient. Arrangements focused only on updating one current booking are less useful for that task.

**Key terms:** data warehouse, business intelligence, append-only data, subject-oriented data, integrated data, time-variant data, non-volatile data, data optimized for query performance.

Left-to-right flow diagram: source boxes Bookings, Payments and Maintenance feed Integrated historical data, then Data warehouse, then Analysis, then Business decisions. Emphasize combining operational sources into a historical analytical store.

Examining data from selected viewpoints is different from discovering useful patterns within it. Both support business intelligence, but they answer different kinds of question.

### Online analytical processing

**Online analytical processing (OLAP)** lets users interactively analyse data across multiple dimensions, often using aggregated values. Users can investigate a measure from different viewpoints and change the level of detail they see.

For Lantern Equipment Hire, the measure could be the number of hires, with month, depot and equipment category as the dimensions. A manager could view all depots or select just one. They could also compare equipment categories or move from monthly totals to a yearly total. The figure shows these dimensions around the measure.

{{e298f04d45044d0bf8cd3d63314760372c20a3fa}}

By moving from a broad result to the detail behind it, a manager can use OLAP to support business intelligence. If total hires rise, for example, the manager can investigate the figures by depot and equipment category to identify where the increase occurred.

### Data mining

**Data mining** involves discovering useful patterns, relationships or unusual cases within data sets. It can reveal information that individual records or routine totals do not make obvious.

In practice, data mining may examine very large data sets. The small invented extract below makes the differences between techniques visible. On its own, it is not enough to establish reliable business conclusions.

| Customer | Visits | Spending in currency units | Known customer category |
|---|---:|---:|---|
| A | 1 | 10 | Occasional |
| B | 2 | 20 | Occasional |
| C | 2 | 20 | Occasional |
| D | 6 | 60 | Frequent |
| E | 7 | 70 | Frequent |
| F | 2 | 90 | Unknown |

### Six data mining techniques

**Classification** uses a record's attributes to assign it to a predefined category. Here, the labelled customers suggest that higher visit counts belong to the Frequent category, while lower counts belong to Occasional. Using visits, F would be assigned to Occasional. A business could use classification to direct different communications to established customer categories.

**Clustering** groups records by similarity without requiring predefined category labels. If we ignore the known categories, visits and spending suggest a low-activity group containing A, B and C, with D and E forming a higher-activity group. F does not fit either group closely. Clustering can reveal customer segments that the organization had not defined beforehand.

**Regression** estimates a numerical value from one or more input attributes. For A to E, spending follows the relationship of 10 currency units per visit. A customer making 3 visits would therefore have an estimated spending of 30 currency units. These estimates can support revenue or demand planning, though real data need not follow a perfect relationship.

**Association rule discovery** identifies items or events that tend to occur together. Suppose hire records repeatedly contain both a work lamp and an extension cable. This suggests an association that could inform equipment bundles or stock planning. It does not show that one item causes the other to be hired.

**Sequential pattern discovery** identifies recurring patterns in which order matters. Repeated records of an initial hire followed later by an extension could help the organization anticipate extension requests. The order distinguishes this from merely finding two items in the same hire.

**Anomaly detection** identifies records that differ substantially from an expected pattern. F has 2 visits but spends 90 currency units, compared with 20 for B and C. That merits investigation, but an unusual record could reflect a valid expensive hire rather than an error.

### Turning findings into decisions

With OLAP, users explore selected dimensions and totals. Data mining can instead produce category assignments, groups, numerical estimates, associations, ordered patterns or unusual cases. To turn these outputs into useful business intelligence, users must interpret them in context rather than treat them as automatic explanations.

**Key terms:** online analytical processing (OLAP), data mining, classification, clustering, regression, association rule discovery, sequential pattern discovery, anomaly detection, business intelligence.

Schematic OLAP cube with dimensions Month, Depot and Equipment category, containing the measure Number of hires. Show a selected depot and the relationship between monthly totals and a yearly total without inventing numerical values or category names.

A **distributed database** is a logically connected database that stores its data across multiple networked sites. Users may access it as one database, even though different places handle storage and processing.

Describe how the data is distributed, then explain how the system keeps access reliable, consistent and secure.

### Partitioning and replication

**Data partitioning** divides a database into subsets stored at different sites. With **Replication**, multiple sites hold copies of the same data.

Lantern Equipment Hire could partition bookings between a North depot and a South depot while keeping a replicated equipment catalogue at both. The figure shows the distinction: bookings are divided; the catalogue is copied.

{{39827242168755a9ae20cdeeeb4d8c3bd265ddaf}}

Partitioning can keep relevant records near their main users while spreading the workload. Replication can improve availability and let users read from a nearby copy. Changes still need coordination, or the copies may give conflicting answers.

### Features of a distributed database

| Feature | What it does |
|---|---|
| **Concurrency control** | Coordinates simultaneous transactions so their operations do not interfere incorrectly, such as two customers securing the same available item |
| **Data consistency** | Keeps data in agreement with required rules and coordinates related values and replicas |
| Data partitioning | Divides records or other parts of the database among sites |
| **Data security** | Restricts access and protects data stored at sites and transmitted between them |
| **Distribution transparency** | Hides how data is distributed so users can work with a single logical database |
| **Fault tolerance** | Lets the system continue providing an appropriate service despite failures, for example by using another replica when a site becomes unavailable |
| **Global query processing** | Coordinates a query that needs data from multiple sites and combines the results |
| **Location transparency** | Lets users access data without knowing its physical storage location |
| Replication | Stores copies of the same data at more than one site |
| **Scalability** | Lets the system accommodate growth in data or workload by increasing resources, including adding sites |

Location transparency is a particular aspect of distribution transparency. A user should need only the logical table name, even if the storage location changes. Users should not have to manage the underlying distribution themselves.

### Why consistency needs coordination

Suppose two sites each show the same item as available. If each accepts bookings independently, both may promise that item to different customers. Storing another copy isn't enough: the system must coordinate concurrent access and the resulting updates.

Coordination becomes harder when the network is slow or a site is unavailable. Replication therefore does not automatically guarantee immediate agreement or uninterrupted access. The database must control how it accepts and shares updates.

### ACID and reliable transactions

**ACID** is the set of transaction properties atomicity, consistency, isolation and durability. For a distributed database, these properties must cover the transaction's effects across every participating site.

| Property | Meaning | Distributed booking example |
|---|---|---|
| **Atomicity** | A transaction takes effect completely or has no effect | The booking record and the change to equipment availability must both take effect, even if they are stored at different sites |
| **Consistency** | A transaction preserves the database's defined rules and constraints | A successful booking must leave the equipment allocation valid |
| **Isolation** | Concurrent transactions must not interfere in ways that produce an invalid combined result | Competing bookings for the same item must be coordinated |
| **Durability** | Once committed, a transaction's effects persist despite subsequent failure | A confirmed booking must remain recorded after a restart |

Atomicity prevents partial cross-site changes. Isolation controls interference between transactions, while durability protects committed results. Consistency requires transactions to preserve the database's rules. Replicated copies must also stay appropriately synchronized to provide a coherent distributed view.

Sites need to coordinate to provide these guarantees. Distributing or replicating data does not, by itself, establish ACID behaviour.

**Key terms:** distributed database, concurrency control, data consistency, data partitioning, data security, distribution transparency, fault tolerance, global query processing, location transparency, replication, scalability, ACID, atomicity, consistency, isolation, durability.

Two-site distributed database diagram with North depot and South depot connected by a Network. Each site contains its own Bookings partition and a copy of the Equipment catalogue. Label the division of bookings Data partitioning and the catalogue copies Replication; place Single logical database above both sites to illustrate distribution transparency.

Database programming

Recipe records contain different attributes: some include allergen information, while others include preparation equipment or optional ingredient substitutions. Describe how a NoSQL database could accommodate these records.

The regional systems use different identifiers for the same meal kit and record delivered quantities in different units. A customer's current region field is overwritten when they move, but each delivery record retains the region to which that delivery was made. Explain how the design of a data warehouse could support reliable comparisons of meal-kit demand between regions over several years.

Describe how managers could use online analytical processing (OLAP) to investigate an increase in the total number of meal kits delivered.

Managers want to discover whether customers repeatedly buy a particular meal kit and then buy a different meal kit in a later order. Suggest a suitable data mining technique and justify its use.

Describe how a spatial database could support a driver's search for available charging stations within a chosen distance of their current position.

RillCharge partitions reservation records by city and replicates station information at several database sites. Describe how these arrangements support the service and why the replicated information requires coordination.

Two drivers use different database sites to reserve the same socket for the same time interval. Explain how the isolation property of ACID can prevent both reservations from being confirmed.

Station-status readings arrive continuously, and the application must display changes with very little delay. Justify using an in-memory database for current status readings, while keeping confirmed reservations in storage that survives a power failure.

Computer Science

IB Computer Science focuses on computational thinking and programming skills. Students learn about system fundamentals, network architecture, and software development, applying their knowledge to solve real-world problems through practical projects.

A3.4 Alternative databases and data warehouses

Question 1

[3]

[5]

[3]

[3]

Question 2

[3]

[5]

[3]

[3]