CREATE TABLE offices
(
    officeCode   TEXT not null
        primary key,
    city         TEXT not null,
    phone        TEXT not null,
    addressLine1 TEXT not null,
    addressLine2 TEXT,
    state        TEXT,
    country      TEXT not null,
    postalCode  TEXT not null,
    territory    TEXT not null
);
offices
CREATE TABLE employees
(
    employeeNumber INTEGER      not null
        primary key,
    lastName       TEXT  not null,
    firstName      TEXT  not null,
    extension      TEXT  not null,
    email          TEXT not null,
    officeCode     TEXT  not null,
    reportsTo      INTEGER,
    jobTitle       TEXT  not null,
    foreign key (officeCode) references offices(officeCode),
    foreign key (reportsTo) references employees(employeeNumber)
);
employees
CREATE TABLE customers
(
    customerNumber         INTEGER     not null
        primary key,
    customerName           TEXT not null,
    contactLastName        TEXT not null,
    contactFirstName       TEXT not null,
    phone                  TEXT not null,
    addressLine1           TEXT not null,
    addressLine2           TEXT,
    city                   TEXT not null,
    state                  TEXT,
    postalCode             TEXT,
    country                TEXT not null,
    salesRepEmployeeNumber INTEGER,
    creditLimit            REAL,
    foreign key (salesRepEmployeeNumber) references employees(employeeNumber)
);
customers
CREATE TABLE orders
(
    orderNumber    INTEGER     not null
        primary key,
    orderDate      DATE        not null,
    requiredDate   DATE        not null,
    shippedDate    DATE,
    status         TEXT not null,
    comments       TEXT,
    customerNumber INTEGER     not null,
    foreign key (customerNumber) references customers(customerNumber)
);
orders
CREATE TABLE payments
(
    customerNumber INTEGER     not null,
    checkNumber    TEXT not null,
    paymentDate    DATE        not null,
    amount         REAL        not null,
    primary key (customerNumber, checkNumber),
    foreign key (customerNumber) references customers(customerNumber)
);
payments
CREATE TABLE productlines
(
    productLine     TEXT not null
        primary key,
    textDescription TEXT,
    htmlDescription TEXT,
    image           BLOB
);
productlines
CREATE TABLE products
(
    productCode        TEXT not null
        primary key,
    productName        TEXT not null,
    productLine        TEXT not null,
    productScale      TEXT not null,
    productVendor      TEXT not null,
    productDescription TEXT        not null,
    quantityInStock    INTEGER     not null,
    buyPrice           REAL        not null,
    MSRP               REAL        not null,
    foreign key (productLine) references productlines(productLine)
);
products
CREATE TABLE "orderdetails"
(
    orderNumber     INTEGER not null
        references orders,
    productCode     TEXT    not null
        references products,
    quantityOrdered INTEGER not null,
    priceEach       REAL    not null,
    orderLineNumber INTEGER not null,
    primary key (orderNumber, productCode)
);
orderdetails
original_column_name,column_name,column_description,data_format,value_description
officeCode,office code,unique ID of the office,text,unique ID of the office
city,city,,text,
phone,,phone number,text,
addressLine1,,addressLine1,text,
addressLine2,,addressLine2,text,"commonsense evidence: 
addressLine1 + addressLine2 = entire address"
state,,,text,
country,,country,text,
postalCode,,postalCode,text,
territory,,territory,text,
offices
original_column_name,column_name,column_description,data_format,value_description
orderNumber,order number,unique order number,integer,unique order number
orderDate,order date,order date,date,
requiredDate,required Date,required Date,date,
shippedDate,shipped date,shipped Date,date,
status,,status,text,
comments,,comments,text,
customerNumber,customer number,customer number,integer,
orders
original_column_name,column_name,column_description,data_format,value_description
employeeNumber,Employee Number,unique string ID of the employees,integer,
lastName,last name,last name of employees,text,
firstName,first name,first name of employees,text,
extension,,extension number,text,
email,,email,text,
officeCode,office code,office code of the employees,text,
reportsTo,reports to,represents for organization structure such as who reports to whom,integer,"commonsense evidence: 
""reportsTO"" is the leader of the ""employeeNumber"""
jobTitle,job title,job title,text,
employees
original_column_name,column_name,column_description,data_format,value_description
customerNumber,customer number,customer number,integer,
checkNumber,check Number,check Number,text,
paymentDate,payment Date,payment Date,date,
amount,,amount,real,
payments
original_column_name,column_name,column_description,data_format,value_description
productCode,product code,unique product code,text,
productName,product name,product name,text,
productLine,product line,product line name,text,
productScale,product scale,product scale,text,
productVendor,product vendor,product vendor,text,
productDescription,product description,product description,text,
quantityInStock,quantity in stock ,quantity in stock ,integer,
buyPrice,buy price,buy price from vendors,real,
MSRP,Manufacturer Suggested Retail Price,Manufacturer Suggested Retail Price,real,"commonsense evidence: 
expected profits: msrp - buyPrice"
products
original_column_name,column_name,column_description,data_format,value_description
productLine,product line,unique product line name,text,
textDescription,text description,text description,text,
htmlDescription,html description,html description,text,
image,,image,blob,
productlines
original_column_name,column_name,column_description,data_format,value_description
orderNumber,order number,order number,integer,
productCode,product code,product code,text,
quantityOrdered,quantity ordered,quantity ordered,integer,
priceEach,price for each,price for each,real,"commonsense evidence: 
total price = quantityOrdered x priceEach"
orderLineNumber,order Line Number,order Line Number,integer,
orderdetails
original_column_name,column_name,column_description,data_format,value_description
customerNumber,customer number,unique id number of customer,integer,
customerName,customer name,the name when the customer registered,text,
contactLastName,contact last name,contact last name,text,
contactFirstName,contact first name,contact first name,text,
phone,,phone,text,
addressLine1,,addressLine1,text,
addressLine2,,addressLine2,text,"commonsense evidence: 
addressLine1 + addressLine2 = entire address"
city,,city,text,
state,,state,text,
postalCode,,postalCode,text,
country,,country,text,
salesRepEmployeeNumber,sales representative employee number,sales representative employee number,integer,
creditLimit,credit limit,credit limit,real,
customers