Databases: relational, NoSQL and the data warehouse

Why this matters

Almost every question an analyst answers starts in a database, and the kind of database decides how the question has to be asked. A relational database wants SQL and joins. A document store hands back nested records that must be flattened first. A data warehouse is built for exactly the summarising queries that would slow a transactional system to a crawl.

The exam tests this as recognition in context: given a description of a system, which type of database is it, which schema would suit a reporting need, and why the table you want is not shaped the way you expected. This lesson covers the main database types and the schemas analysts meet most often.

The lesson

Tables, keys and relationships in a relational database

A relational database stores data in tables made of rows and columns. Each row is one record, such as one customer or one order, and each column holds one attribute of that record, such as a name or a date. Every value in a column has the same data type.

A primary key is the column, or combination of columns, that identifies each row uniquely. In the course's practice database, every customer has a customer_id and no two customers share one. A foreign key is a column that refers to the primary key of another table: each order carries the customer_id of the customer who placed it. That reference is the relationship between the two tables.

Relationships have a cardinality. One customer can place many orders, so customers and orders are one-to-many. A product can appear in many orders and an order could contain many products, which is many-to-many; relational designs resolve that with a linking table in between. Knowing the cardinality of a relationship tells you in advance what a join will do to your row count, which becomes important in domain 2.

Relational databases enforce their rules. A primary key cannot be duplicated, and a foreign key can be required to match an existing row. Those constraints are why relational systems are trusted for records that must be exactly right, such as payments and stock levels. The language for querying them is SQL, and products such as PostgreSQL, MySQL, Microsoft SQL Server, Oracle and SQLite all speak it.

Document, key-value, column-family and graph databases, and what each is for

Non-relational databases, usually called NoSQL, relax the fixed table structure to gain flexibility or scale. There are four main families.

A document database stores each record as a self-contained document, usually JSON. One customer document can hold the customer's details and a list of their addresses together, and two documents in the same collection need not have the same fields. MongoDB is the best-known example. It suits data whose shape varies or changes often.

A key-value store is the simplest: each value is retrieved by a unique key, like a dictionary. It is extremely fast for looking up one item, such as a session or a cached result, and poor at answering questions across many items.

A column-family (wide-column) database, such as Apache Cassandra, groups data by column families and spreads it across many machines. It suits very large volumes of writes, such as sensor or event data.

A graph database stores nodes and the edges between them, and is built for questions about connections: who knows whom, which accounts share a device, the shortest route between two points. Relationships that would need many joins in a relational database are a single step in a graph.

For an analyst, the practical consequence is that NoSQL data often has to be reshaped before analysis: nested documents flattened into rows, or a graph exported as a table of edges.

OLTP against OLAP: the database that runs the business and the one that analyses it

Databases are also distinguished by the work they do.

Online transaction processing (OLTP) systems run the business. They record each sale, booking or payment as it happens, handle many small reads and writes at once, and must stay fast and consistent while doing so. Their tables are designed to avoid storing the same fact twice.

Online analytical processing (OLAP) systems exist to answer questions about the business: total sales by region by month, or the average basket size this year against last. These queries read millions of rows and summarise them. They are mostly reads, often run in batches, and would slow an OLTP system for everyone else if run against it directly.

This is why organisations copy data from their transactional systems into a separate data warehouse, a database designed for analysis. The warehouse collects data from many sources, keeps history, and is organised for summarising rather than for recording.

Star and snowflake schemas, fact tables and dimension tables

A warehouse is usually organised as a dimensional model, which has two kinds of table.

A fact table holds the measurements of a business process: one row per sale, with numeric measures such as quantity and amount, plus keys that point to the things the sale was about. Fact tables are long and narrow and grow every day.

Dimension tables hold the descriptive context: the customer, the product, the store, the date. They are shorter and wider, full of attributes to filter and group by, such as a product's category or a date's month and quarter.

A star schema places one fact table at the centre with dimension tables joined directly to it, so the diagram looks like a star. It is simple to query and fast to summarise, which is why it is the default design for reporting.

A snowflake schema normalises the dimensions further, splitting them into sub-tables: product might link to a separate category table, which links to a department table. It saves some storage and avoids repeated values, at the cost of more joins in every query.

Normalisation, and why a warehouse deliberately undoes some of it

Normalisation is the process of organising a relational database so that each fact is stored once. A customer's address lives in one row of one table, and everything that needs it refers to that row by key. The benefit is integrity: change the address once and it is correct everywhere, with no copy left behind to disagree. The common levels are called normal forms; in plain terms, first normal form means one value per cell, and higher forms remove columns that depend on something other than the table's key.

Normalisation suits OLTP, where data is written constantly and must never contradict itself. It is costly for analysis, because answering one question may need many joins.

A warehouse therefore denormalises deliberately. A star schema's product dimension repeats the category name on every product row instead of storing it once in a separate table. That repetition would be a design fault in a transactional system. In a warehouse it is a choice: the data is loaded in controlled batches rather than edited row by row, so the risk of copies disagreeing is managed by the load process, and the reward is simpler, faster queries.