Week 3 — Lesson 1: Relational databases and structured data — Week 3 — Data sources and database types

The NorthPeak data is split across three tables in data/diagnostics.sqlite: machines, readings and incidents. A relational database stores data this way on purpose, with fixed columns and keys that connect the tables. To know the site of an incident, you follow a key from incidents to machines with a join. This lesson covers primary keys, foreign keys, the join, and the row count that tells you the join went right.

Tables, keys, joins

Structured data has a fixed shape. Every row has the same columns. Every column has one type. A CSV file is structured. A SQL table is structured, and the database enforces the shape.

Each table has a primary key: the column that identifies one row. In machines, it is machine_id. No two rows share it.

Another table can point to that key. In incidents, the column machine_id says which machine broke. It is a foreign key. It must match a machine_id that exists in machines.

A join follows the foreign key. It glues the incident row to its machine row. That is how you get the site of an incident, when the site is stored in another table.

On the NorthPeak data

PRAGMA table_info(machines) asks SQLite for the schema of a table:

text

   cid            name     type
0    0      machine_id    TEXT
1    1    machine_type    TEXT
2    2            site    TEXT
3    3    install_year INTEGER
4    4  rated_power_kw    REAL

machine_id appears in all three tables. Joining incidents to machines on it gives 155 rows: one per incident, now with a site and a type.

sql

SELECT i.incident_id, i.machine_id, m.site
FROM incidents i
JOIN machines m ON i.machine_id = m.machine_id
LIMIT 3

text

  incident_id machine_id     site
0    INC-0001       M001  Toronto
1    INC-0002       M001  Toronto
2    INC-0003       M001  Toronto

Why split the data at all? Because the site of M001 is stored once, not 365 times in readings and 5 times in incidents. Change it once, it changes everywhere. That is the strength of the relational model.

A common mistake

Forget the ON and the join explodes. SELECT COUNT(*) FROM incidents i JOIN machines m returns 6,200. That is 155 times 40: every incident glued to every machine. SQLite does not complain. Always check the row count after a join. It should equal the row count of the table you started from, here 155.