Five Stars and What They Hide — technical investigation

Why this sits under Technical Reports

This work starts from six explicit research questions rather than from a dataset someone handed over, uses authoritative public data, and structures its analyses so that every finding traces to a named query over a documented schema. The relational design was built to answer the questions, which is a research-design decision rather than an engineering convenience.

What it contributes is methodological rather than novel: a worked example of designing a schema around questions, and of keeping an analysis auditable end to end. That is why it sits under Technical Reports alongside the other coursework investigations, and why its strongest material is the chain from question to schema to query.

Research questions

Six, driving the schema rather than following it. The organising one: aggregate hygiene ratings summarise an authority in a single number, and the question is what that number conceals about variation within it.

Method

Load FSA FHRS data for seven local authorities into a normalised MySQL schema, validating rather than coercing on the way in. Map each research question to specific relations and a named query. Report what the queries return.

Data and schema

21,209 establishments. Six relations, described in the report as BCNF, with one documented exception.

The decomposition is answerable to the questions. If a question requires grouping by authority and rating band over time, that has to be a join across well-formed relations, not a scan over a denormalised table with the answer pre-baked into it.

The normalisation compromise, stated plainly

The Establishment relation does not satisfy strict 3NF: local_authority_id functionally determines scheme_id.

The dependency is real, because each authority operates exactly one rating scheme. Fully decomposing it would add a join to nearly every analytical query in exchange for a correctness property that the loader already enforces at ingestion.

That is a deliberate trade, and the original report states it explicitly rather than leaving it for a reader to find. Naming the exception is what makes the rest of the normalisation claim checkable.

Findings

Descriptive: rating distributions across the seven authorities, and the ways an authority's aggregate rating obscures the spread of establishments within it.

The general shape of the result is the one the title points at — a summary statistic that looks reassuring can sit on top of substantial internal variation.

Validity threats

• Seven authorities, which is the coverage the study was designed around.
• Rating recency varies — establishments are inspected on different schedules, and an aggregate mixes ratings from different dates. That is itself one of the things the analysis surfaces.
• Observational data, which describes the distributions rather than separating inspection stringency from underlying hygiene.
• A point-in-time snapshot rather than a time series.

Interpretation

The defensible conclusion is narrow: within these seven authorities, at this point in time, aggregate hygiene ratings hide meaningful internal variation, and the direction of that variation differs by authority.

That is the claim the design supports, and stating it at exactly that width is the point of mapping every question to a named query.

Where I would take this next

• A pinned, reproducible data snapshot, so every figure regenerates from the repository.
• Automated tests asserting the stated normal forms, turning the normalisation trade-off into an enforced invariant.
• Explicit control for rating date rather than pooling across it.
• Wider authority coverage, which is what a between-authority claim would need.

The engineering treatment of the same work — schema, loader, queries and application — is in the project case study.