critwire · Learn · Learn's data

Normalization d2

Prerequisites: Keys and constraints (d1)

Decide which table each fact belongs in, so that every fact is stored once and the tables can't contradict themselves: functional dependencies, the normal forms from first to third, nested records turned into tables, and when repeating a fact on purpose is safe. Everything runs in this browser, on the tables the Schema page describes and on critwire's published files.

Concept

Worked example

How does critwire's nested record become tables? These queries read the published file itself, species.parquet, with DuckDB's own SQL: read_parquet opens a file, "range".countriesWithOpenRecords reaches into the record, and unnest turns a list into rows. This page's tables were built from the file with exactly these. Here is the jaguar's record, its countries one list in one cell:

1NF gives each item its own row, which is how species_countries is filled:

2NF: the key of species_countries is (species_id, country_code). A country's name depends on country_code alone, so a country_name column there would repeat each name once per animal recorded in that country, and a rename would have to find every copy:

3NF: an animal's family depends on its genus, not on the animal. So species stores only the genus, and the family is in the genus's taxa row. A flat table with a family column would repeat these families, and one mistyped copy would put an animal in two families:

The record's sources is a list of structs: each item names a source, its license, when critwire retrieved it, and the fields it supplied. Does a source's license depend on the source? In the file, each source has one license, written once per animal it supplied:

So source → license goes into its own table, sources, one row per source, and SELECT DISTINCT over the file gives exactly one row each. Its primary key now holds the rule: had two animals' entries named two licenses for one source, the load would have failed instead of storing both. The file repeats each license 69 to 76 times, and that is fine, because critwire's build writes the file from one source every time, and gives every animal's entry for a source that source's one license. Which source gave which field is a fact about the animal and the field together, so it goes in species_sources, keyed (species_id, field). Joined back, they tell where each of the jaguar's facts came from:

The taxonomy and its own id are from the Catalogue of Life, the other outside ids from Wikidata and GBIF, the country list from GBIF's occurrence search. ids.iucn is critwire's own: critwire stores only the animal's IUCN id, never the Red List's data, whose terms don't allow republishing it.

The same question applies to a claim. /v1/range-listings holds what named sources list: each row is one source's claim about one animal in one country. Every mammal in critwire has rows from the Mammal Diversity Database (MDD), the American Society of Mammalogists' open list of mammal species; Licenses gives its license and where its data came from. A row says two things about the animal in that country: origin, how the source says it came to be there, and presence, whether the source says it's there. Are those one fact or two? This file is read like species.parquet, by name; here are the mammals' rows counted by both:

On this page, native, introduced and range words describe only /v1/range-listings rows, worded as what a named source lists ("MDD lists it in Kenya as native or introduced before 1500"); records are still never range.

Two facts, because neither fixes the other. native-or-pre-1500, MDD's value for native or introduced before 1500, comes with present (364 rows) and with uncertain (36); introduced also comes with both (7 rows from 6 sources, and 3). present comes with two origins, so presence → origin doesn't hold either. The plainest case is native with absent: 6 rows, from one source, a checklist of Central Asia's mammals. Each is that source's claim that the animal is native to the country and absent from it, and says nothing about why or since when. One status column would have to choose between those words, or hold a made-up value for every pair, and every question about one fact would have to pick it apart. Each row here is a named source's claim: no row means critwire holds no open listing, and a listed country with no record shows nothing either way.

Lab

Is a license a fact about a record or about its dataset? GBIF gives both: every dataset declares a license, and every record carries one. If dataset_key → license held, the license would belong in datasets (3NF), stored 400 times instead of 40,395.

  1. Test the rule on the records: does any dataset have records under two licenses?

    None, in this sample. That doesn't prove the rule (Concept). The full GBIF download the sample comes from refutes it: the iNaturalist research-grade dataset holds 9,581 CC BY records and 3,152 CC0 records. The sample just doesn't show it, because iNaturalist declares CC BY-NC for the dataset, so none of its records passed the sample's license rule (the lab data page).

  2. Is the record's license the dataset's declared one?

    Not always: this museum declares CC0 for its dataset and CC BY for these records. So there are two facts here, under one word. The record's license depends on the record and stays on occurrences; the dataset's declared license depends on the dataset and stays on datasets. Moving the first into datasets would publish these four records as CC0 and lose the credit their license asks for. (The sample's license is the stricter of the two.)

  3. Now the anomalies, on a flat table: each record with its animal's name and its dataset's title and publisher, the shape a spreadsheet export has. Every record carries its dataset's title, 400 datasets' titles stored 40,395 times. And there are fewer titles than datasets: two titles are shared, so a title can't stand in for a dataset key.

    Runs after: d2-flat

  4. An update anomaly: the museum renames its dataset, and the fix reaches one of its records. The table now gives one dataset two titles, which the same kind of query catches:

    Runs after: d2-flat

  5. A delete anomaly: 84 datasets have one record in the sample. Delete those records (say they all turned out to be misidentified) and their datasets vanish from the flat table, titles, publishers and all; datasets still lists them:

    Runs after: d2-flat

Each block runs on the tables as loaded, after the blocks it names, so step 5 starts from the flat table of step 3, not from step 4's rename.

Result

The nested record became tables along its dependencies: the country list into rows (1NF), the country's name into countries (2NF), the family into taxa and the source's license into sources (3NF), with the published file repeating them for readers, rebuilt each time. The license question has a two-part answer. In the sample, no dataset has records under two licenses, but the full download has one (iNaturalist), and one museum's records carry a stricter license than its dataset declares. So the record's license is a fact about the record and stays on occurrences; datasets.license is the dataset's own declaration, a different fact. The flat table showed what normalizing prevents: 40,395 copies of 400 titles, one dataset with two titles after a partial fix, and 84 datasets lost with their last records (316 left of 400). And a record count per dataset is a query, not a column: stored, it would be wrong after the next load. A listing's origin and presence are two facts as well, so two columns: 6 mammal rows say native and absent at once.

Exercise

Normalize records_flat (lab step 3, before the anomalies; your answer runs after it) into 3NF: r_species (species_id, common_name), r_datasets (dataset_key, title, publisher) and r_records (id, species_id, dataset_key, license), each with its primary key, and r_records' foreign keys to the other two. Fill them from records_flat, then show the split is lossless: count each table's rows, and the rows that joining the three back loses or adds compared with records_flat (both should be 0).

Show answer
CREATE TABLE r_species (
  species_id  INTEGER PRIMARY KEY,
  common_name VARCHAR NOT NULL
);
CREATE TABLE r_datasets (
  dataset_key VARCHAR PRIMARY KEY,
  title       VARCHAR NOT NULL,
  publisher   VARCHAR NOT NULL
);
CREATE TABLE r_records (
  id          BIGINT PRIMARY KEY,
  species_id  INTEGER NOT NULL REFERENCES r_species (species_id),
  dataset_key VARCHAR NOT NULL REFERENCES r_datasets (dataset_key),
  license     VARCHAR NOT NULL
);
INSERT INTO r_species SELECT DISTINCT species_id, common_name FROM records_flat;
INSERT INTO r_datasets SELECT DISTINCT dataset_key, title, publisher FROM records_flat;
INSERT INTO r_records SELECT id, species_id, dataset_key, license FROM records_flat;
CREATE VIEW r_joined AS
SELECT r.id, r.species_id, s.common_name, r.dataset_key, d.title, d.publisher, r.license
FROM r_records AS r
JOIN r_species AS s ON s.species_id = r.species_id
JOIN r_datasets AS d ON d.dataset_key = r.dataset_key;
SELECT (SELECT count(*) FROM r_species) AS species,
       (SELECT count(*) FROM r_datasets) AS datasets,
       (SELECT count(*) FROM r_records) AS records,
       (SELECT count(*) FROM (SELECT * FROM records_flat EXCEPT SELECT * FROM r_joined)) AS lost,
       (SELECT count(*) FROM (SELECT * FROM r_joined EXCEPT SELECT * FROM records_flat)) AS added;

The INSERT … SELECT DISTINCT into r_datasets works only because dataset_key → title, publisher holds in records_flat; run after lab step 4's partial rename, it fails on the key. Your table must now refuse the rename that caused it, a second title for the museum's dataset:

Runs after: d2-flat, d2-ex

Check

Your counts must equal the answer's, in any row order. Then the page runs the insert above after your answer, and it must fail on r_datasets' key.

Dialects

Schema: the browser's tables

critwire Learn schema v2, the tables in the browser

Every column, in words · The diagram alone