{"city": {"columns": [[0, "City_ID", "INT", 0, null, 1], [1, "Official_Name", "TEXT", 0, null, 0], [2, "Status", "TEXT", 0, null, 0], [3, "Area_km_2", "REAL", 0, null, 0], [4, "Population", "REAL", 0, null, 0], [5, "Census_Ranking", "TEXT", 0, null, 0]], "primary_keys": ["City_ID"], "foreign_keys": [], "unique_constraints": [[[0, 0, "City_ID"]]], "check_constraints": []}, "farm": {"columns": [[0, "Farm_ID", "INT", 0, null, 1], [1, "Year", "INT", 0, null, 0], [2, "Total_Horses", "REAL", 0, null, 0], [3, "Working_Horses", "REAL", 0, null, 0], [4, "Total_Cattle", "REAL", 0, null, 0], [5, "Oxen", "REAL", 0, null, 0], [6, "Bulls", "REAL", 0, null, 0], [7, "Cows", "REAL", 0, null, 0], [8, "Pigs", "REAL", 0, null, 0], [9, "Sheep_and_Goats", "REAL", 0, null, 0]], "primary_keys": ["Farm_ID"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Farm_ID"]]], "check_constraints": []}, "farm_competition": {"columns": [[0, "Competition_ID", "INT", 0, null, 1], [1, "Year", "INT", 0, null, 0], [2, "Theme", "TEXT", 0, null, 0], [3, "Host_city_ID", "INT", 0, null, 0], [4, "Hosts", "TEXT", 0, null, 0]], "primary_keys": ["Competition_ID"], "foreign_keys": [[0, 0, "city", "Host_city_ID", "City_ID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Competition_ID"]]], "check_constraints": []}, "competition_record": {"columns": [[0, "Competition_ID", "INT", 0, null, 1], [1, "Farm_ID", "INT", 0, null, 2], [2, "Rank", "INT", 0, null, 0]], "primary_keys": ["Competition_ID", "Farm_ID"], "foreign_keys": [[0, 0, "farm", "Farm_ID", "Farm_ID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "farm_competition", "Competition_ID", "Competition_ID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Competition_ID"], [1, 1, "Farm_ID"]]], "check_constraints": []}}
CREATE TABLE city (
    City_ID INT   PRIMARY KEY,
    Official_Name TEXT,
    Status TEXT,
    Area_km_2 REAL,
    Population REAL,
    Census_Ranking TEXT,
    UNIQUE (City_ID)
);

CREATE TABLE farm (
    Farm_ID INT   PRIMARY KEY,
    Year INT,
    Total_Horses REAL,
    Working_Horses REAL,
    Total_Cattle REAL,
    Oxen REAL,
    Bulls REAL,
    Cows REAL,
    Pigs REAL,
    Sheep_and_Goats REAL,
    UNIQUE (Farm_ID)
);

CREATE TABLE farm_competition (
    Competition_ID INT   PRIMARY KEY,
    Year INT,
    Theme TEXT,
    Host_city_ID INT,
    Hosts TEXT,
    UNIQUE (Competition_ID),
    FOREIGN KEY (Host_city_ID) REFERENCES city(City_ID)
);

CREATE TABLE competition_record (
    Competition_ID INT   PRIMARY KEY,
    Farm_ID INT   PRIMARY KEY,
    Rank INT,
    PRIMARY KEY (Competition_ID, Farm_ID),
    UNIQUE (Competition_ID, Farm_ID),
    FOREIGN KEY (Farm_ID) REFERENCES farm(Farm_ID),
    FOREIGN KEY (Competition_ID) REFERENCES farm_competition(Competition_ID)
);