CREATE TABLE Community_Area
(
    community_area_no   INTEGER
            primary key,
    community_area_name TEXT,
    side                TEXT,
    population          TEXT
);
Community_Area
CREATE TABLE District
(
    district_no   INTEGER
            primary key,
    district_name TEXT,
    address       TEXT,
    zip_code      INTEGER,
    commander     TEXT,
    email         TEXT,
    phone         TEXT,
    fax           TEXT,
    tty           TEXT,
    twitter       TEXT
);
District
CREATE TABLE FBI_Code
(
    fbi_code_no   TEXT
            primary key,
    title         TEXT,
    description   TEXT,
    crime_against TEXT
);
FBI_Code
CREATE TABLE IUCR
(
    iucr_no               TEXT
            primary key,
    primary_description   TEXT,
    secondary_description TEXT,
    index_code            TEXT
);
IUCR
CREATE TABLE Neighborhood
(
    neighborhood_name TEXT
            primary key,
    community_area_no INTEGER,
    foreign key (community_area_no) references Community_Area(community_area_no)
);
Neighborhood
CREATE TABLE Ward
(
    ward_no                INTEGER
            primary key,
    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  INTEGER,
    city_hall_office_phone TEXT,
    city_hall_office_fax   TEXT,
    Population             INTEGER
);
Ward
CREATE TABLE Crime
(
    report_no            INTEGER
            primary key,
    case_number          TEXT,
    date                 TEXT,
    block                TEXT,
    iucr_no              TEXT,
    location_description TEXT,
    arrest               TEXT,
    domestic             TEXT,
    beat                 INTEGER,
    district_no          INTEGER,
    ward_no              INTEGER,
    community_area_no    INTEGER,
    fbi_code_no          TEXT,
    latitude             TEXT,
    longitude            TEXT,
    foreign key (ward_no) references Ward(ward_no),
    foreign key (iucr_no) references IUCR(iucr_no),
    foreign key (district_no) references District(district_no),
    foreign key (community_area_no) references Community_Area(community_area_no),
    foreign key (fbi_code_no) references FBI_Code(fbi_code_no)
);
Crime
original_column_name,column_name,column_description,data_format,value_description
iucr_no,iucr number,Unique identifier for the incident classification,text,
primary_description,primary description,The general description of the incident classification,text,"commonsense evidence:

It's the general description "
secondary_description,secondary description,The specific description of the incident classification,text,"commonsense evidence:

It's the specific description "
index_code,index code,"Uses values ""I"" and ""N"" to indicate crimes ",text,"commonsense evidence:

â¢ ""Indexed"" (severe, such as murder, rape, arson, and robbery)

â¢ ""Non-Indexed"" (less severe, such as vandalism, weapons violations, and peace disturbance) "
IUCR
original_column_name,column_name,column_description,data_format,value_description
fbi_code_no,fbi code number,unique code made by fbi to classify the crime cases,text,
title,,Short description of the kind of crime,text,
description,,Detailed description of the kind of crime,text,
crime_against,crime against,States who the crime is against. ,text,"Values are Persons, Property, Society, or ""Persons and Society"""
FBI_Code
original_column_name,column_name,column_description,data_format,value_description
report_no,report number,unique Numeric identifier of the crime report.,integer,
case_number,case number,Case number assigned to the reported crime.,text,"commonsense evidence: 

There is usually one case number for any reported crime except in the case of multiple homicide where a single report will produce one case number for each victim

if case_number appears > 1, it means this is a multiple homicide"
date,,Date of the occurrence of the incident being reported,text,
block,,A redacted address indicating in which city block the incident occurred,text,
iucr_no,Illinois Uniform Crime Reporting number,Illinois Uniform Crime Reporting code: a four digit code used to classify criminal incidents when taking reports.,text,
location_description,location description,A description of the kind of area where incident occurred,text,
arrest,,A true/false value indicating if an arrest has been made,text,
domestic,,A true/false value indicating if the incident is as case of domestic violence,text,
beat,,,integer,
district_no,district number,A number indicating the geographic subdivision of the police district where the incident occurred,integer,
ward_no,ward number,A two digit number identifying the legislative district (ward) where the incident occurred.,integer,
community_area_no,community area number,A number identifying one of 77 geographic areas where the incident occurred,integer,
fbi_code_no,fbi code number,A code identifying the type of crime reported according to the classification scheme used by the FBI.,text,
latitude,,The latitude where incident is reported to have occurred.,text,
longitude,,The longitude where incident is reported to have occurred.,text,"commonsense evidence: 

The precise location / coordinate: combines the longitude and latitude for plotting purposes. (latitude, longitude)"
Crime
original_column_name,column_name,column_description,data_format,value_description
community_area_no,community area number,unique community area number,integer,
community_area_name,community area name,community area name,text,
side,,district ,text,
population,,population of the community,text,
Community_Area
original_column_name,column_name,column_description,data_format,value_description
district_no,district number,unique number identifying the district,integer,
district_name,district name, name of the district,text,
address,,Street address of the police district building,text,
zip_code,zip code,ZIP code of the police district building,integer,
commander,,Name of the district's commanding officer,text,"commonsense evidence:

the person who should be responsible for the crime case. "
email,,Email address to contact district administrators,text,
phone,,Main telephone number for the district,text,
fax,,Fax number for the district,text,
tty,,Number of the district teletype machine.,text,"A teletype machine is a device that lets people who are deaf, hard of hearing, or speech-impaired use the telephone to communicate, by allowing them to type text messages."
twitter,,The district twitter handle. ,text,"Twitter is a social networking service on which users post and interact with brief messages known as ""tweets"""
District
original_column_name,column_name,column_description,data_format,value_description
neighborhood_name,neighborhood name,The neighborhood name as used in everyday communication,text,
community_area_no,community area number,The community area in which most of the neighborhood is located,integer,
Neighborhood
original_column_name,column_name,column_description,data_format,value_description
ward_no,,,integer,
alderman_first_name,,,text,
alderman_last_name,,,text,
alderman_name_suffix,,,text,
ward_office_address,ward_office_address,,text,
ward_office_zip,,,text,
ward_email,,,text,
ward_office_phone,,,text,
ward_office_fax,,,text,
city_hall_office_room,,,integer,
city_hall_office_phone,,,text,
city_hall_office_fax,,,text,
Population,,,text,
Ward