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.

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.

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.

Why warehouse data has particular characteristics

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.

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.

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.

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.

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.

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

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.

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.