CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
);
employee
CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
);
establishment
CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
);
inspection
CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
);
inspection_point
CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
);
violation
original_column_name,column_name,column_description,data_format,value_description
point_id,point id,the unique id for the inspection point,integer,
Description,,the specific description of the inspection results,text,
category,,the inspection category,text,
code,,the sanitary operating requirement code,text,commonsense evidence: Each code stands for an operating requirement
fine,,the fines for food safety problems,integer,"commonsense evidence: The fine of more serious food safety problems will be higher. 
 Minor: 100 
 Serious: 250 
 Critical: 500"
point_level,,point level,text,Critical / Serious/ Minor
inspection_point
original_column_name,column_name,column_description,data_format,value_description
inspection_id,inspection id,the unique id for the inspection,integer,
inspection_date,inspection date,the date of the inspection,date,yyyy-mm-dd
inspection_type,inspection type,the type of the inspection,text  s,
results,,the inspection results,text," Pass 
 Pass w/ Conditions 
 Fail 
 Out of Business 
 Business Not Located 
 No Entry 
 Not Ready 
commonsense evidence: the quality is verified: Pass + Pass w/ Conditions"
employee_id,employee id,the id of the employee who is responsible for the inspection,integer,
license_no,license number,the license number of the facility that is inspected,integer,
followup_to,followup to,the id of inspections that this inspection is following up,integer,"commonsense evidence: 'No data' means the inspection has no followup.
if followup_to has value: 
 it's the parent inspection id, and this value represents child inspection id  
 the entire inspection: followup_to + inspection_id"
inspection
original_column_name,column_name,column_description,data_format,value_description
inspection_id,inspection id,the unique id for the inspection,integer,
point_id,point id,the inspection point id,integer,
fine,,the fine of the violation,integer,"commonsense evidence: The fine of more serious food safety problems will be higher. 
 Minor: 100 
 Serious: 250 
 Critical: 500"
inspector_comment,,the comment of the inspector,text,
violation
original_column_name,column_name,column_description,data_format,value_description
license_no,license number,the license number of the facility,integer,
dba_name,doing business as name,the dba name of the facility,text,"commonsense evidence: DBA stands for doing business as. It's also referred to as your business's assumed, trade or fictitious name."
aka_name,as know as name,the aka name of the facility,text,commonsense evidence: the aka name is used to indicate another name that a person or thing has or uses
facility_type,facility type,the facility type,text,"commonsense evidence: can ask some questions about its categories, for i.e.:  
 ""Restaurant"" and ""Cafeteria"" refers to restaurants 
 PASTRY school and ""CULINARY ARTS SCHOOL"" belongs to ""Education"" or ""School"". Please design this and mention your evidence in the commonsense part of questions, thanks"
risk_level,risk level,the risk level of the facility,integer,commonsense evidence: Facilities with higher risk level have more serious food safety issues
address,,physical street address of the facility,text,
city,,city of the facility's address,text,
state,,state of the facility's address,text,
zip,,postal code of the facility's address,integer,
latitude,,the latitude of the facility,real,
longitude,,the longitude of the facility,real,"commonsense evidence: location = POINT(latitude, longitude)"
ward,,the ward number of the facility,integer,"commonsense evidence: Ward number is the number assigned by the local authorities to identify the population count in each town, village and district usually for electoral purposes or to extract property and locality details"
establishment
original_column_name,column_name,column_description,data_format,value_description
employee_id,employee id,the unique id for the employee,integer,
first_name,first name,first name of the employee,text,
last_name,last name,last name of the employee,text,commonsense evidence: full name: first name + last_name
address,,physical street address of the employee,text,
city,,city of the employee's address,text,
state,,state of the customer's address,text,
zip,,postal code of the employee's address,integer,
phone,,telephone number to reach the customer,text,
title,,career title of the employee,text," Sanitarian 
 Supervisor 
 Division Manager"
salary,,the salary of the employee,integer,"us dollars / year
commonsense evidence: monthly salary: salary / 12"
supervisor,,the employee id of the employee's supervisor,integer,
employee