{"GEOGRAPHIC": {"columns": [[0, "CITY_NAME", "TEXT", 0, null, 1], [1, "COUNTY", "TEXT", 0, null, 0], [2, "REGION", "TEXT", 0, null, 0]], "primary_keys": ["CITY_NAME"], "foreign_keys": [], "unique_constraints": [[[0, 0, "CITY_NAME"]]], "check_constraints": []}, "RESTAURANT": {"columns": [[0, "ID", "INT", 0, null, 1], [1, "NAME", "TEXT", 0, null, 0], [2, "FOOD_TYPE", "TEXT", 0, null, 0], [3, "CITY_NAME", "TEXT", 0, null, 0], [4, "RATING", "REAL", 0, null, 0]], "primary_keys": ["ID"], "foreign_keys": [[0, 0, "GEOGRAPHIC", "CITY_NAME", "CITY_NAME", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "ID"]]], "check_constraints": []}, "LOCATION": {"columns": [[0, "RESTAURANT_ID", "INT", 0, null, 1], [1, "HOUSE_NUMBER", "INT", 0, null, 0], [2, "STREET_NAME", "TEXT", 0, null, 0], [3, "CITY_NAME", "TEXT", 0, null, 0]], "primary_keys": ["RESTAURANT_ID"], "foreign_keys": [[0, 0, "RESTAURANT", "RESTAURANT_ID", "RESTAURANT_ID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "GEOGRAPHIC", "CITY_NAME", "CITY_NAME", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "RESTAURANT_ID"]]], "check_constraints": []}}
CREATE TABLE GEOGRAPHIC (
    CITY_NAME TEXT   PRIMARY KEY,
    COUNTY TEXT,
    REGION TEXT,
    UNIQUE (CITY_NAME)
);

CREATE TABLE RESTAURANT (
    ID INT   PRIMARY KEY,
    NAME TEXT,
    FOOD_TYPE TEXT,
    CITY_NAME TEXT,
    RATING REAL,
    UNIQUE (ID),
    FOREIGN KEY (CITY_NAME) REFERENCES GEOGRAPHIC(CITY_NAME)
);

CREATE TABLE LOCATION (
    RESTAURANT_ID INT   PRIMARY KEY,
    HOUSE_NUMBER INT,
    STREET_NAME TEXT,
    CITY_NAME TEXT,
    UNIQUE (RESTAURANT_ID),
    FOREIGN KEY (RESTAURANT_ID) REFERENCES RESTAURANT(RESTAURANT_ID),
    FOREIGN KEY (CITY_NAME) REFERENCES GEOGRAPHIC(CITY_NAME)
);