-- Schema: bird__chicago_crime
CREATE SCHEMA IF NOT EXISTS "bird__chicago_crime";

-- Table: Community_Area
CREATE TABLE "bird__chicago_crime"."Community_Area" (
    "community_area_no" BIGINT NOT NULL,
    "community_area_name" TEXT,
    "side" TEXT,
    "population" TEXT,
    CONSTRAINT "Community_Area_pkey" PRIMARY KEY ("community_area_no")
);

-- Table: Crime
CREATE TABLE "bird__chicago_crime"."Crime" (
    "report_no" BIGINT NOT NULL,
    "case_number" TEXT,
    "date" TEXT,
    "block" TEXT,
    "iucr_no" TEXT,
    "location_description" TEXT,
    "arrest" TEXT,
    "domestic" TEXT,
    "beat" BIGINT,
    "district_no" BIGINT,
    "ward_no" BIGINT,
    "community_area_no" BIGINT,
    "fbi_code_no" TEXT,
    "latitude" TEXT,
    "longitude" TEXT,
    CONSTRAINT "Crime_pkey" PRIMARY KEY ("report_no")
);

-- Table: District
CREATE TABLE "bird__chicago_crime"."District" (
    "district_no" BIGINT NOT NULL,
    "district_name" TEXT,
    "address" TEXT,
    "zip_code" BIGINT,
    "commander" TEXT,
    "email" TEXT,
    "phone" TEXT,
    "fax" TEXT,
    "tty" TEXT,
    "twitter" TEXT,
    CONSTRAINT "District_pkey" PRIMARY KEY ("district_no")
);

-- Table: FBI_Code
CREATE TABLE "bird__chicago_crime"."FBI_Code" (
    "fbi_code_no" TEXT NOT NULL,
    "title" TEXT,
    "description" TEXT,
    "crime_against" TEXT,
    CONSTRAINT "FBI_Code_pkey" PRIMARY KEY ("fbi_code_no")
);

-- Table: IUCR
CREATE TABLE "bird__chicago_crime"."IUCR" (
    "iucr_no" TEXT NOT NULL,
    "primary_description" TEXT,
    "secondary_description" TEXT,
    "index_code" TEXT,
    CONSTRAINT "IUCR_pkey" PRIMARY KEY ("iucr_no")
);

-- Table: Neighborhood
CREATE TABLE "bird__chicago_crime"."Neighborhood" (
    "neighborhood_name" TEXT NOT NULL,
    "community_area_no" BIGINT,
    CONSTRAINT "Neighborhood_pkey" PRIMARY KEY ("neighborhood_name")
);

-- Table: Ward
CREATE TABLE "bird__chicago_crime"."Ward" (
    "ward_no" BIGINT NOT NULL,
    "alderman_first_name" TEXT,
    "alderman_last_name" TEXT,
    "alderman_name_suffix" TEXT,
    "ward_office_address" TEXT,
    "ward_office_zip" TEXT,
    "ward_email" TEXT,
    "ward_office_phone" TEXT,
    "ward_office_fax" TEXT,
    "city_hall_office_room" BIGINT,
    "city_hall_office_phone" TEXT,
    "city_hall_office_fax" TEXT,
    "Population" BIGINT,
    CONSTRAINT "Ward_pkey" PRIMARY KEY ("ward_no")
);

-- Foreign Keys
ALTER TABLE "bird__chicago_crime"."Crime" ADD CONSTRAINT "fk_Crime_0" FOREIGN KEY ("fbi_code_no") REFERENCES "bird__chicago_crime"."FBI_Code" ("fbi_code_no") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__chicago_crime"."Crime" ADD CONSTRAINT "fk_Crime_1" FOREIGN KEY ("community_area_no") REFERENCES "bird__chicago_crime"."Community_Area" ("community_area_no") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__chicago_crime"."Crime" ADD CONSTRAINT "fk_Crime_4" FOREIGN KEY ("ward_no") REFERENCES "bird__chicago_crime"."Ward" ("ward_no") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__chicago_crime"."Neighborhood" ADD CONSTRAINT "fk_Neighborhood_0" FOREIGN KEY ("community_area_no") REFERENCES "bird__chicago_crime"."Community_Area" ("community_area_no") ON DELETE NO ACTION ON UPDATE NO ACTION;
