Learn · Schemas: browser · lab · star

Schema: the browser's tables

The tables every browser lab and the playground run on. The page builds them in your browser from four published files, so every learner starts from the same rows, and a reload starts fresh.

Six tables describe the animals: taxa, species, countries, species_countries, sources and species_sources, read from /v1/species.parquet and /v1/countries.parquet. Two more hold the lab's sample of open GBIF records: datasets and occurrences, read from the lab data. A record is one observation of an animal at a place and time; the course counts records, and never presents them as where an animal lives.

The diagram

An entity–relationship diagram (ERD) draws each table as a box with its columns, and each foreign key as a line to the table it points at. PK marks the primary key, FK a foreign key, UK a unique key. The ends of a line say how many rows meet there: || exactly one, |o zero or one, o{ zero or more, |{ one or more. The end with the crow's foot is the table that holds the foreign key.

The relationships, in words

• Each taxa row points at zero or one taxa row (another row of the same table); a taxa row has zero or more taxa rows: “parent of”.
• Each species row points at exactly one taxa row; a taxa row has zero or more species rows: “genus of”.
• Each species_countries row points at exactly one species row; a species row has zero or more species_countries rows: “recorded in”.
• Each species_countries row points at exactly one countries row; a countries row has zero or more species_countries rows: “has records of”.
• Each species_sources row points at exactly one species row; a species row has zero or more species_sources rows: “fields from”.
• Each species_sources row points at exactly one sources row; a sources row has one or more species_sources rows: “supplies”.
• Each occurrences row points at exactly one species row; a species row has zero or more occurrences rows: “recorded as”.
• Each occurrences row points at exactly one datasets row; a datasets row has zero or more occurrences rows: “publishes”.
• Each occurrences row points at zero or one countries row; a countries row has zero or more occurrences rows: “contains”.

A record's country is optional. country_code_gbif keeps GBIF's code as delivered, and country_code holds it only when it is a row of countries, so GBIF's ZZ (unknown) and XZ (high seas) never break the foreign key.

The view species_taxonomy

species_taxonomy (species_id, class, order_name, family, genus) gives each species its genus, family, order and class in one row, by walking taxa from the genus up through each parent_name. A view is a stored query, not a table: it holds no rows of its own, and reading it runs the query. That is why it isn't drawn.

The data dictionary

Every column: its type, its keys, what it means, and where its values come from.