Stage 2 summary. Modelling and statistics

Modelling runs at three levels and confusing them makes design conversations circular. The conceptual model names the real-world things and how they relate: a customer places orders, an order contains products. The logical model expresses that as attributes, keys, relationships and constraints, without committing to a database product. The physical model decides table names, column types, indexes and partitioning. The hard work is nearly always conceptual. Customer is easy to say and difficult to model, because whether a customer is a person, a household, an account or a supply point decides how many customers the organisation has and how consent applies.

A primary key guarantees that one row identifies one thing, and a foreign key lets the database refuse a reference to something that does not exist. A natural key uses a real-world value such as an email address or a product code, and it betrays the model as soon as that value changes, is reused, turns out not to be unique, or has to be redacted. A surrogate key is an internal identifier no business process can revise, which is why it survives. Operations and analytics model the same subject differently on purpose, one shaped to keep concurrent writes correct and the other to answer questions over large scans.

Normalisation organises a schema so each fact is stored in exactly one place. First normal form requires every cell to hold a single value; second normal form requires every non-key column to depend on the whole key rather than part of it; third normal form requires that no non-key column is determined by another, which moves a city determined by its postcode into its own table. What that buys is the removal of three anomalies: an update landing on some copies of a fact and not others, an insertion that cannot be made because the fact has nowhere to live, and a deletion that takes the last record of something with it. The cost is joins, fair for small keyed reads and poor for scans over hundreds of millions of rows.

Analysis wants a different shape. A star schema puts one fact table of measurements at the centre with descriptive dimensions around it, and no dimension joins another. The first decision is the grain: a written statement of what one fact row represents, such as one row per order line. It is declared before any column is chosen, because it settles which measures may be added up and which dimensions can join. An order-level discount summed at order-line grain is counted once per line, which is the arithmetic behind two dashboards that agree for eleven months and then differ by four per cent. Dimensions that change over time force a second decision, between overwriting an attribute and versioning it, and the wrong answer is leaving it unmade. One big table folds the star flat into the easy-to-query, expensive-to-change corner: defensible as a serving artefact rebuilt from a modelled core, damaging as the only place meaning is defined. A data vault earns its complexity when many sources describe the same entities and every attribute must trace to its load.

Master data is the small set of entities many systems must refer to consistently: customers, patients, suppliers, products, sites, assets. Transactional data records events and nearly always points at one of them. Reference data is the controlled list of permitted values, with a publisher and a version, and two systems holding different vintages of it produce joins that are wrong in ways that look plausible. The test is what breaks when a value changes: a transaction correction fixes one event, a reference code change reclassifies a population, and a master data change alters who the organisation thinks it is dealing with. Entity resolution is deciding which records describe the same real thing, and it is hard because the systems at the seams were never asked to share a key. Names vary lawfully, addresses vary structurally and dates of birth are mistyped.

The chain has five steps: standardise every field, block so only pairs agreeing on something cheap are compared, compare each field on a scale rather than as equal or not, score the agreement pattern into one number, and decide against a threshold. Fellegi and Sunter set out the intuition in 1969. A field's weight is the ratio between how often it agrees for a true pair and how often it agrees by chance, so a rare surname carries evidence and a common forename almost none. Blocking is the step whose errors are invisible, because a pair never generated cannot appear in any precision measurement. The threshold is a business decision rather than a statistical output, since a false match gives one person another's history while a missed match leaves a vulnerable customer flag on the record nobody reads. Splink, the Ministry of Justice's open library, is the worked public-sector implementation.

A vector is an ordered list of numbers standing for a thing, which is what an embedding is. A matrix is a table of them, which is what a dataset is, and multiplying a data matrix by a weight matrix is the core computation inside a regression, a neural network layer and a principal component analysis alike. The dot product measures how far two vectors point the same way, which is why users with similar preference vectors get similar recommendations. Training is then optimisation: a loss function scores how wrong the predictions are, and each step moves the parameters against the gradient by a distance set by the learning rate.

Two ideas do the reasoning work on top of that. Bayes' theorem updates a belief in light of evidence, which is why a test with a high detection rate still produces mostly false alarms when the condition is rare: the prior matters as much as the test. Entropy measures how much uncertainty a distribution holds, which is a way to say how much a feature tells you rather than how strongly it correlates. Neither is needed to run a library, and that is the point: they are what let a practitioner see why an accuracy figure on a rare event is close to meaningless.

A distribution describes how values spread, and the shape decides which statistics can be trusted. On an approximately normal distribution the mean, median and mode sit close together and the 68, 95 and 99.7 rule holds across one, two and three standard deviations. Income, house prices, insurance claims and outage durations are usually not normal, and the same rule applied to them understates the tail badly. When incident duration has a mean of 62 minutes and a median of 18, both numbers are true and answer different questions: the median describes a typical incident, the mean warns that rare severe events dominate total exposure.

Correlation is the half that gets misused. The Pearson coefficient runs from minus one through zero to plus one and measures the strength and direction of a linear relationship, nothing more. Ice cream sales and drowning rates rise together because temperature drives both, and a model treating one as the cause recommends the wrong intervention. The working test is not whether two variables move together but what else could make them: confounding, reverse causation, selection bias, seasonality or a change in measurement. Until those are ruled out a correlation is a reason to investigate rather than permission to act, and establishing cause needs a controlled experiment, a credible natural experiment, or an observational design whose assumptions are written down.

An A/B test is a randomised comparison, and randomisation is what makes the groups comparable before the change is applied. Five requirements make the result worth acting on: random assignment, a single changed variable, a sample size fixed by a power calculation against a stated minimum detectable effect, a hypothesis and success metric agreed in advance, and a duration covering weekday, weekend and payday variation. Stopping early because one arm looks ahead turns an intended five per cent false positive rate into something several times larger.

A p-value answers one narrow question: if there were no real difference, how unusual would a result this extreme be under the model's assumptions. It is not the probability that the treatment works, and 0.049 is not meaningfully different from 0.051. The American Statistical Association's 2016 statement was written against that binary reading. Two p-hacking patterns are worth naming: testing many variants and reporting only the one that cleared the threshold, since five tests at five per cent give roughly a 23 per cent chance of one false positive, and changing the outcome measure after seeing the data. A confidence interval shows the range of effects the data is consistent with. The evidence chain that survives challenge runs from the population sampled, through the measure and the design, to the effect size and its uncertainty, and then to a claim stated no more strongly than that chain supports.

Analytics forms a ladder of questions. Descriptive work says what happened, diagnostic work says why, predictive work estimates what is likely next, and prescriptive work recommends an action and so carries the consequence of being followed. Machine learning problems sort by the learning signal: supervised learning pairs inputs with correct outputs, covering classification and regression; unsupervised learning finds structure without labels, through clustering, dimensionality reduction and anomaly detection; reinforcement learning optimises actions against a reward; and self-supervised learning derives its labels from the data itself.

Evaluation is where projects are won or lost. A model that fits training data including its noise scores highly there and fails on anything new, which is overfitting; one too simple to capture the structure scores poorly on both, which is underfitting. The defence is a genuine separation of training, validation and test data, cross-validation when data is scarce, and vigilance about leakage, where information unavailable at prediction time reaches the model and produces a score that cannot be reproduced. A confusion matrix makes the choice of error explicit: precision matters when a false positive is costly, recall when a missed case is. Once a model ships the distribution drifts from the one it learned, which is why monitoring, a rollback path and a named owner belong in the design. Stage 4 takes up the AI-specific risks, and EU AI Act Article 10 sets what high-risk systems must show about their data.