CREATE TABLE Categories
(
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT,
    Description TEXT
);
Categories
CREATE TABLE Customers
(
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT
);
Customers
CREATE TABLE Employees
(
    EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName TEXT,
    FirstName TEXT,
    BirthDate DATE,
    Photo TEXT,
    Notes TEXT
);
Employees
CREATE TABLE Shippers(
    ShipperID INTEGER PRIMARY KEY AUTOINCREMENT,
    ShipperName TEXT,
    Phone TEXT
);
Shippers
CREATE TABLE Suppliers(
    SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
    SupplierName TEXT,
    ContactName TEXT,
    Address TEXT,
    City TEXT,
    PostalCode TEXT,
    Country TEXT,
    Phone TEXT
);
Suppliers
CREATE TABLE Products(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName TEXT,
    SupplierID INTEGER,
    CategoryID INTEGER,
    Unit TEXT,
    Price REAL DEFAULT 0,
	FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
	FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
);
Products
CREATE TABLE Orders(
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerID INTEGER,
    EmployeeID INTEGER,
    OrderDate DATETIME,
    ShipperID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
    FOREIGN KEY (ShipperID) REFERENCES Shippers (ShipperID)
);
Orders
CREATE TABLE OrderDetails(
    OrderDetailID INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderID INTEGER,
    ProductID INTEGER,
    Quantity INTEGER,
	FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
	FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
);
OrderDetails
original_column_name,column_name,column_description,data_format,value_description
OrderID,Order ID,the unique id for orders,integer,
CustomerID,Customer ID,the unique id for customers,text,
EmployeeID,Employee ID,the unique id for employees,integer,
OrderDate,Order Date,the order date,datetime,
RequiredDate,Required Date,the required date of the order,datetime,
ShippedDate,Shipped Date,the shipped date of the order,datetime,
ShipVia,Ship Via,the shippers id,integer,
Freight,,the freight of the product,real,
ShipName,Ship Name,name of the consignee,text,
ShipAddress,Ship Address,address ships to,text,
ShipCity,Ship City,the ship city,text,
ShipRegion,Ship Region,the ship region,text,
ShipPostalCode,Ship Postal Code,the postal code,text,
ShipCountry,Ship Country,the country ,text,
ShipperID,Shipper ID,the id of the shipper,integer
Orders
original_column_name,column_name,column_description,data_format,value_description
SupplierID,Supplier ID,the unique id for suppliers,integer,
SupplierName,Supplier Name,the supplier name,text,
ContactName,Contact Name,the contact person's name representing the company,text,
ContactTitle,Contact Title,the title of the contact person,text,
Address,,the address of the supplier,text,
City,,the city where the supplier is located,text,
Region,,the region where the supplier is located,text,
PostalCode,Postal Code,the postal code,text,
Country,,the country,text,
Phone,,the phone number,text,
Fax,,the fax number,text,
HomePage,,the home page url of the supply company,text,
Suppliers
original_column_name,column_name,column_description,data_format,value_description
EmployeeID,Employee ID,the unique id for employees,integer,
LastName,Last Name,the employee's last name,text,
FirstName,First Name,the employee's first name,text,"commonsense evidence:
the employee's full name is 'first_name last_name'. 
"
Title,,the employee's position title,text,
TitleOfCourtesy,Title Of Courtesy,the title of the courtesy,text,"commonsense evidence:
 Ms.: Mr. was used as a shortening of master, a title used for men of high authority
 Dr.: it is used as a designation for a person who has obtained a doctorate (commonly a PhD).
 Mrs.: It was used as a shortening of mistress a title used for women of high rank or a woman who was the female head of a household
 Ms: an honorific used to refer to any woman, regardless of marital status"
BirthDate,Birth Date,the birth date of the employee,datetime,
HireDate,Hire Date,the hire date of the employee,datetime,
Address,,the address of the employee,text,
City,,the city where the employee is located,text,
Region,,the region where the employee is located,text,
PostalCode,Postal Code,the postal code ,text,
Country,,the country where the employee is located,text,
HomePhone,Home Phone,employee's home phone number,text,
Extension,,employee's extension number,text,
Photo,,the photo of the employee,blob,
Notes,,some additional information of the employee,text,
ReportsTo,Reports To,the employee id that the employee directly reports to,integer,"commonsense evidence:
reportsto represents a hierarchical relationship where the person being reported to is usually the direct supervisor of the reporter"
PhotoPath,Photo Path,the url of the employee's photo,text,
Salary,,the employee's salary ,real,
Employees
original_column_name,column_name,column_description,data_format,value_description
CustomerID,Customer ID,the unique id for customers,text,
CustomerName,Customer Name,the customer name,text,
ContactName,Contact Name,the contact person's name representing the company,text,
ContactTitle,Contact Title,the title of the contact person,text,
Address,,the address of the customer,text,
City,,the city where the customer is located,text,
Region,,the region where the customer is located,text,
PostalCode,Postal Code,the postal code,text,
Country,,the country,text,
Phone,,the phone number,text,
Fax,,the fax number,text,
Customers
original_column_name,column_name,column_description,data_format,value_description
OrderID,Order ID,the unique id for orders,integer,
ProductID,Product ID,the unique id for products,integer,
UnitPrice,Unit Price,the unit price of the products,real,
Quantity,,the quantity of the ordered products,integer,
Discount,,the discount,real,"commonsense evidence:
the total payment of the order = unit price * quantity * (1-discount)"
OrderDetailID,order detail id,id of the order detail,text,
OrderDetails
original_column_name,column_name,column_description,data_format,value_description
CategoryID,Category ID,the unique id for the category,integer,
CategoryName,Category Name,the category name,text,
Description,,the detailed description of the category,text,
Picture,,the picture of the category,blob,
Categories
original_column_name,column_name,column_description,data_format,value_description
ShipperID,Shipper ID,the unique id for shippers,integer,
ShipperName,Shipper Name,the shipped company name,text,
Phone,,the phone of the company,text,
Shippers
original_column_name,column_name,column_description,data_format,value_description
ProductID,Product ID,the unique id for products,integer,
ProductName,Product Name,the name of the product,text,
SupplierID,Supplier ID,the unique id for supplier,integer,
CategoryID,Category ID,the unique id for the product category,integer,
QuantityPerUnit,Quantity Per Unit,the quantity per unit of the product,text,
Unit,,the unit of the product,text
Price,Price,the price,real,
Products