CREATE TABLE city
(
    city_id    INTEGER
            primary key,
    city_name  TEXT,
    state      TEXT,
    population INTEGER,
    area       REAL
);
city
CREATE TABLE customer
(
    cust_id        INTEGER
            primary key,
    cust_name      TEXT,
    annual_revenue INTEGER,
    cust_type      TEXT,
    address        TEXT,
    city           TEXT,
    state          TEXT,
    zip            REAL,
    phone          TEXT
);
customer
CREATE TABLE driver
(
    driver_id  INTEGER
            primary key,
    first_name TEXT,
    last_name  TEXT,
    address    TEXT,
    city       TEXT,
    state      TEXT,
    zip_code   INTEGER,
    phone      TEXT
);
driver
CREATE TABLE truck
(
    truck_id   INTEGER
            primary key,
    make       TEXT,
    model_year INTEGER
);
truck
CREATE TABLE shipment
(
    ship_id   INTEGER
            primary key,
    cust_id   INTEGER,
    weight    REAL,
    truck_id  INTEGER,
    driver_id INTEGER,
    city_id   INTEGER,
    ship_date TEXT,
    foreign key (cust_id) references customer(cust_id),
    foreign key (city_id) references city(city_id),
    foreign key (driver_id) references driver(driver_id),
    foreign key (truck_id) references truck(truck_id)
);
shipment
original_column_name,column_name,column_description,data_format,value_description
driver_id,driver id,Unique identifier for the driver,integer,
first_name,first name,First given name of the driver,text,
last_name,last name ,Family name of the driver,text,commonsense evidence: full name = first_name + last_name
address,,Street address of the driver's home,text,
city,,City the driver lives in,text,
state,,State the driver lives in,text,"commonsense evidence: 
please mention its full name in the question, by referring to 
https://www23.statcan.gc.ca/imdb/p3VD.pl?Function=getVD&TVD=53971 
e.g., NY --> New York"
zip_code,zip code,postal code of the driver's address,integer,
phone,,telephone number of the driver,text,
driver
original_column_name,column_name,column_description,data_format,value_description
cust_id,customer id ,Unique identifier for the customer,integer,
cust_name,customer name,Business name of the customer,text,
annual_revenue,annual revenue,Annual revenue of the customer,integer,
cust_type,customer type,Whether the customer is a manufacturer or a wholes,text,
address,,Physical street address of the customer,text,
city,,City of the customer's address,text,
state,,State of the customer's address,text,"commonsense evidence: 
please mention its full name in the question, by referring to 
https://www23.statcan.gc.ca/imdb/p3VD.pl?Function=getVD&TVD=53971
e.g., NY --> New York"
zip,,Postal code of the customer's address,real,
phone,,Telephone number to reach the customer,text,
customer
original_column_name,column_name,column_description,data_format,value_description
ship_id,ship id,Unique identifier of the shipment,integer,
cust_id,customer id ,A reference to the customer table that indicates which customer the shipment is for,integer,
weight,,The number of pounds being transported on the shipment,real,
truck_id,truck id,A reference to the truck table that indicates which truck is used in the shipment,integer,
driver_id,driver id,A reference to the driver table that indicates which driver transported the goods in the shipment,integer,
city_id,city id,A reference to the city table that indicates the destination of the shipment,integer,
ship_date,ship date,the date the items were received by the driver,text,yyyy-mm-dd
shipment
original_column_name,column_name,column_description,data_format,value_description
city_id,city id,unique identifier for the city,integer,
city_name,city name,name of the city,text,
state,,state in which the city is,text,
population ,,population of the city,integer,
area,,square miles the city covers,real,"commonsense evidence: 
population density (land area per capita) = area / population "
city
original_column_name,column_name,column_description,data_format,value_description
truck_id,truck id,Unique identifier of the truck table,integer,
make,,The brand of the truck,text,"commonsense evidence: 
 Peterbilt headquarter: Texas (TX) 
 Mack headquarter: North Carolina (NC) 
 Kenworth headquarter: Washington (WA) 
can ask question about headquarters of the truck"
model_year,model year,The year the truck was manufactured,integer,commonsense evidence: The truck with earlier model year means this truck is newer.
truck