CREATE TABLE authors
(
    au_id    TEXT
        primary key,
    au_lname TEXT not null,
    au_fname TEXT not null,
    phone    TEXT    not null,
    address  TEXT,
    city     TEXT,
    state    TEXT,
    zip      TEXT,
    contract TEXT     not null
);
authors
CREATE TABLE jobs
(
    job_id   INTEGER
        primary key,
    job_desc TEXT not null,
    min_lvl  INTEGER     not null,
    max_lvl  INTEGER     not null
);
jobs
CREATE TABLE publishers
(
    pub_id   TEXT
        primary key,
    pub_name TEXT,
    city     TEXT,
    state    TEXT,
    country  TEXT
);
publishers
CREATE TABLE employee
(
    emp_id    TEXT
        primary key,
    fname     TEXT not null,
    minit     TEXT,
    lname     TEXT not null,
    job_id    INTEGER     not null,
    job_lvl   INTEGER,
    pub_id    TEXT     not null,
    hire_date DATETIME    not null,
    foreign key (job_id) references jobs(job_id)
            on update cascade on delete cascade,
    foreign key (pub_id) references publishers(pub_id)
            on update cascade on delete cascade
);
employee
CREATE TABLE pub_info
(
    pub_id  TEXT
        primary key,
    logo    BLOB,
    pr_info TEXT,
    foreign key (pub_id) references publishers(pub_id)
            on update cascade on delete cascade
);
pub_info
CREATE TABLE stores
(
    stor_id     TEXT
        primary key,
    stor_name    TEXT,
    stor_address TEXT,
    city         TEXT,
    state        TEXT,
    zip          TEXT
);
stores
CREATE TABLE discounts
(
    discounttype TEXT   not null,
    stor_id      TEXT,
    lowqty       INTEGER,
    highqty      INTEGER,
    discount     REAL not null,
    foreign key (stor_id)  references stores(stor_id)
            on update cascade on delete cascade
);
discounts
CREATE TABLE titles
(
    title_id  TEXT
        primary key,
    title     TEXT not null,
    type      TEXT    not null,
    pub_id    TEXT,
    price     REAL,
    advance   REAL,
    royalty   INTEGER,
    ytd_sales INTEGER,
    notes     TEXT,
    pubdate   DATETIME    not null,
    foreign key (pub_id) references publishers(pub_id)
            on update cascade on delete cascade
);
titles
CREATE TABLE roysched
(
    title_id TEXT not null,
    lorange  INTEGER,
    hirange  INTEGER,
    royalty  INTEGER,
    foreign key (title_id)  references titles(title_id)
            on update cascade on delete cascade
);
roysched
CREATE TABLE sales
(
    stor_id  TEXT     not null,
    ord_num  TEXT  not null,
    ord_date DATETIME    not null,
    qty      INTEGER     not null,
    payterms TEXT  not null,
    title_id TEXT   not null,
    primary key (stor_id, ord_num, title_id),
    foreign key (stor_id)   references stores(stor_id)
            on update cascade on delete cascade,
    foreign key (title_id)  references titles(title_id)
            on update cascade on delete cascade

);
sales
CREATE TABLE titleauthor
(
    au_id      TEXT not null,
    title_id   TEXT  not null,
    au_ord     INTEGER,
    royaltyper INTEGER,
    primary key (au_id, title_id),
    foreign key (au_id) references authors(au_id)
            on update cascade on delete cascade,
    foreign key (title_id)    references titles (title_id)
            on update cascade on delete cascade
);
titleauthor
original_column_name,column_name,column_description,data_format,value_description
title_id,,unique id number identifying title,text,
lorange,low range,low range,integer,
hirange,high range,high range,integer,
royalty,,royalty,integer,
roysched
original_column_name,column_name,column_description,data_format,value_description
au_id,author id,author id,text,
title_id,title id,title id,text,
au_ord,author ordering,author ordering,integer,
royaltyper,,royaltyper,integer,
titleauthor
original_column_name,column_name,column_description,data_format,value_description
au_id,author id,unique number identifying authors,text,
au_lname,author last name,author last name,text,
au_fname,author first name,author first name,text,
phone,,phone number,text,
address,,address,text,
city,,city ,text,
state,,state ,text,
zip,,zip code,text,
contract,,contract status,text,"commonsense evidence:
0: not on the contract
1: on the contract"
authors
original_column_name,column_name,column_description,data_format,value_description
job_id,job id,unique id number identifying the jobs,integer,
job_desc,job description,job description,text,"commonsense evidence:
staff should be mentioned"
min_lvl,min level,min job level,integer,
max_lvl,max level,max job level,integer,"commonsense evidence:
level range for jobs mentioned in job_desc is (min_lvl, max_lvl)"
jobs
original_column_name,column_name,column_description,data_format,value_description
stor_id,store id,id number identifying stores,text,
ord_num,order number,id number identifying the orders,text,
ord_date,order date,the date of the order,datetime,
qty,quantity,quantity of sales ,integer,
payterms,,payments,text,
title_id,title id,id number identifying titles,text,
sales
original_column_name,column_name,column_description,data_format,value_description
pub_id,publisher id,unique id number identifying publisher,text,
pub_name,publisher name,publisher name,text,
city,city,city ,text,
state,state,state,text,
country,country,country,text,
publishers
original_column_name,column_name,column_description,data_format,value_description
stor_id,store id,unique id number of stores,text,
stor_name,store name,,text,
stor_address,store address,,text,
city,,city name,text,
state,,state code,text,
zip,,zip code,text,
stores
original_column_name,column_name,column_description,data_format,value_description
emp_id,employee id,unique number identifying employees ,text,
fname,first name,first name of employees,text,
minit,,middle name,text,
lname,last name,last name,text,
job_id,job id,number identifying jobs,integer,
job_lvl,job level,job level,integer,"commonsense evidence:
higher value means job level is higher"
pub_id,publisher id,id number identifying publishers,text,
hire_date,,hire date,datetime,
employee
original_column_name,column_name,column_description,data_format,value_description
title_id,title id,title id,text,
title,,title,text,
type,,type of titles,text,
pub_id,publisher id,publisher id,text,
price,,price,real,
advance,,pre-paid amount,real,
royalty,,royalty,integer,
ytd_sales,year to date sales,year to date sales,integer,
notes,,notes if any,text,"commonsense evidence:
had better understand notes contents and put some of them into questions if any"
pubdate,publication date,publication date,datetime,
titles
original_column_name,column_name,column_description,data_format,value_description
pub_id,publication id,unique id number identifying publications,text,
logo,,logo of publications,blob,
pr_info,publisher's information,publisher's information,text,
pub_info
original_column_name,column_name,column_description,data_format,value_description
discounttype,discount type,discount type,text,
stor_id,store id,store id,text,
lowqty,low quantity,low quantity (quantity floor),integer,"commonsense evidence: 
The minimum quantity to enjoy the discount"
highqty,high quantity ,high quantity (max quantity),integer,"commonsense evidence: 
The maximum quantity to enjoy the discount"
discount,discount,discount,real,
discounts