CREATE TABLE customers
(
    CustomerID            INTEGER
            primary key,
    First                 TEXT,
    Last                  TEXT,
    StreetAddress         TEXT,
    City                  TEXT,
    State                 TEXT,
    ZipCode               INTEGER,
    Email                 TEXT,
    PhoneNumber           TEXT,
    FirstPurchaseDate     DATE,
    SubscribedToEmailList TEXT,
    Gender                TEXT
);
customers
CREATE TABLE geolocation
(
    LocationID INTEGER
            primary key,
    Latitude   REAL,
    Longitude  REAL,
    foreign key (LocationID) references location(LocationID)
);
geolocation
CREATE TABLE location
(
    LocationID    INTEGER
            primary key,
    LocationName  TEXT,
    StreetAddress TEXT,
    City          TEXT,
    State         TEXT,
    ZipCode       INTEGER,
    foreign key (LocationID) references geolocation(LocationID)
);
location
CREATE TABLE rootbeerbrand
(
    BrandID             INTEGER
            primary key,
    BrandName           TEXT,
    FirstBrewedYear     INTEGER,
    BreweryName         TEXT,
    City                TEXT,
    State               TEXT,
    Country             TEXT,
    Description         TEXT,
    CaneSugar           TEXT,
    CornSyrup           TEXT,
    Honey               TEXT,
    ArtificialSweetener TEXT,
    Caffeinated         TEXT,
    Alcoholic           TEXT,
    AvailableInCans     TEXT,
    AvailableInBottles  TEXT,
    AvailableInKegs     TEXT,
    Website             TEXT,
    FacebookPage        TEXT,
    Twitter             TEXT,
    WholesaleCost       REAL,
    CurrentRetailPrice  REAL
);
rootbeerbrand
CREATE TABLE rootbeer
(
    RootBeerID    INTEGER
            primary key,
    BrandID       INTEGER,
    ContainerType TEXT,
    LocationID    INTEGER,
    PurchaseDate  DATE,
    foreign key (LocationID) references geolocation(LocationID),
    foreign key (LocationID) references location(LocationID),
    foreign key (BrandID) references rootbeerbrand(BrandID)
);
rootbeer
CREATE TABLE rootbeerreview
(
    CustomerID INTEGER,
    BrandID    INTEGER,
    StarRating INTEGER,
    ReviewDate DATE,
    Review     TEXT,
    primary key (CustomerID, BrandID),
    foreign key (CustomerID) references customers(CustomerID),
    foreign key (BrandID) references rootbeerbrand(BrandID)
);
rootbeerreview
CREATE TABLE "transaction"
(
    TransactionID    INTEGER
            primary key,
    CreditCardNumber INTEGER,
    CustomerID       INTEGER,
    TransactionDate  DATE,
    CreditCardType   TEXT,
    LocationID       INTEGER,
    RootBeerID       INTEGER,
    PurchasePrice    REAL,
    foreign key (CustomerID) references customers(CustomerID),
    foreign key (LocationID) references location(LocationID),
    foreign key (RootBeerID) references rootbeer(RootBeerID)
);
transaction
original_column_name,column_name,column_description,data_format,value_description
LocationID,location id,the id of the location,integer,
Latitude,,the latitude of the location,real,
Longitude,,the longitude of the location,real,"commonsense evidence:
precise location / coordinate = POINT(latitude, longitude)"
geolocation
original_column_name,column_name,column_description,data_format,value_description
LocationID,location id,the unique id for the location,integer,
LocationName,location name,the name of the location,text,
StreetAddress,street address,the street address,text,
City,,the city where the location locates,text,
State,,the state code,text,
ZipCode,zip code,the zip code of the location,integer,
location
original_column_name,column_name,column_description,data_format,value_description
BrandID,brand id,the unique id for the brand,integer,
BrandName,brand name,the brand name,text,
FirstBrewedYear,first brewed year,the first brewed year of the brand,integer,"commonsense evidence:
brand with earlier first brewed year has a much longer brewed history"
BreweryName,brewery name ,the brewery name,text,
City,,the city where the brewery locates,text,
State,,the state code,text,
Country,,the country where the brewery locates,text,"commonsense evidence:
can find its corresponding continent. e.g., U.S.--> North America "
Description,,the description of the brand,text,
CaneSugar,cane sugar,whether the drink contains cane sugar,text,
CornSyrup,corn syrup,whether the drink contains the corn syrup,text,
Honey,,whether the drink contains the honey ,text,"commonsense evidence:
if the beer has honey, it means this beer is sweeter or has sweetness"
ArtificialSweetener,artificial sweetener,whether the drink contains the artificial sweetener ,text,"commonsense evidence:
if the beer has artificial sweetener, it means this beer is sweeter or has sweetness"
Caffeinated,,whether the drink is caffeinated,text,
Alcoholic,,whether the drink is alcoholic,text,
AvailableInCans,available in cans,whether the drink is available in cans,text,
AvailableInBottles,available in bottles,whether the drink is available in bottles,text,
AvailableInKegs,available in kegs,whether the drink is available in kegs,text,
Website,,the website of the brand,text,
FacebookPage,facebook page,the facebook page of the brand,text,"commonsense evidence:
if not, it means this brand doesn't advertise on facebook"
Twitter,,the twitter of the brand,text,"commonsense evidence:
if not, it means this brand doesn't advertise on twitter"
WholesaleCost,wholesale cost,the wholesale cost,real,
CurrentRetailPrice,current retail price,the current retail price,real,"commonsense evidence:
The unit profit available to wholesalers = current retail price - wholesale cost"
rootbeerbrand
original_column_name,column_name,column_description,data_format,value_description
TransactionID,transaction id,the unique id for the transaction,integer,
CreditCardNumber,credit card number,the number of the credit card used for payment,integer,
CustomerID,customer id,the customer id,integer,
TransactionDate,transaction date,the transaction date,date,yyyy-mm-dd
CreditCardType,credit card type,the credit card type,text,
LocationID,location id,the location id of the selling company ,integer,
RootBeerID,root beer id,the root beer id,integer,
PurchasePrice,purchase price,the unit purchase price of the root beer,real,us dollars
transaction
original_column_name,column_name,column_description,data_format,value_description
CustomerID,customer id,the id of the customer,integer,
BrandID,brand id,the id of the brand,integer,
StarRating,star rating,the star rating of the root beer,integer,"commonsense evidence:
root beer with higher star rating has higher market evaluation and acceptance"
ReviewDate,review date,the review date,date,yyyy-mm-dd
Review,,the specific review content,text,
rootbeerreview
original_column_name,column_name,column_description,data_format,value_description
CustomerID,customer id,the unique id for the customer,integer,
First,first name,the first name of the customer,text,
Last,last name,the last name of the customer,text,
StreetAddress,street address,the address of the customer,text,
City,,the city where the customer lives,text,
State,,the state code,text,"commonsense evidence: please refer to https://www23.statcan.gc.ca/imdb/p3VD.pl?Function=getVD&TVD=53971
and mention its corresponding state name in the question. i.e. New York-- NY"
ZipCode,zip code,the zip code,integer,
Email,,the customer's email,text,
PhoneNumber,phone number,the customer's phone number,text,
FirstPurchaseDate,first purchase date,the first purchase date of the customer,date,yyyy-mm-dd
SubscribedToEmailList,subscribed to email list,whether the customer subscribe to the email list,text,"commonsense evidence:
 'true' means the user permits the company to send regular emails to them. "
Gender,,the customer's gender,text,
customers
original_column_name,column_name,column_description,data_format,value_description
RootBeerID,root beer id,the unique id for the root beer,integer,
BrandID,brandid,the brand id,integer,
ContainerType,container type,the type of the container,text,
LocationID,location id,the location id of the selling company ,integer,
PurchaseDate,purchase date,the purchase date of the root beer,date,
rootbeer