{"architect": {"columns": [[0, "id", "TEXT", 0, null, 1], [1, "name", "TEXT", 0, null, 0], [2, "nationality", "TEXT", 0, null, 0], [3, "gender", "TEXT", 0, null, 0]], "primary_keys": ["id"], "foreign_keys": [], "unique_constraints": [[[0, 0, "id"]]], "check_constraints": []}, "bridge": {"columns": [[0, "architect_id", "INT", 0, null, 0], [1, "id", "INT", 0, null, 1], [2, "name", "TEXT", 0, null, 0], [3, "location", "TEXT", 0, null, 0], [4, "length_meters", "REAL", 0, null, 0], [5, "length_feet", "REAL", 0, null, 0]], "primary_keys": ["id"], "foreign_keys": [[0, 0, "architect", "architect_id", "id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 1, "id"]]], "check_constraints": []}, "mill": {"columns": [[0, "architect_id", "INT", 0, null, 0], [1, "id", "INT", 0, null, 1], [2, "location", "TEXT", 0, null, 0], [3, "name", "TEXT", 0, null, 0], [4, "type", "TEXT", 0, null, 0], [5, "built_year", "INT", 0, null, 0], [6, "notes", "TEXT", 0, null, 0]], "primary_keys": ["id"], "foreign_keys": [[0, 0, "architect", "architect_id", "id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 1, "id"]]], "check_constraints": []}}
CREATE TABLE architect (
    id TEXT   PRIMARY KEY,
    name TEXT,
    nationality TEXT,
    gender TEXT,
    UNIQUE (id)
);

CREATE TABLE bridge (
    architect_id INT,
    id INT   PRIMARY KEY,
    name TEXT,
    location TEXT,
    length_meters REAL,
    length_feet REAL,
    UNIQUE (id),
    FOREIGN KEY (architect_id) REFERENCES architect(id)
);

CREATE TABLE mill (
    architect_id INT,
    id INT   PRIMARY KEY,
    location TEXT,
    name TEXT,
    type TEXT,
    built_year INT,
    notes TEXT,
    UNIQUE (id),
    FOREIGN KEY (architect_id) REFERENCES architect(id)
);