CREATE TABLE authors
(
    au_id   varchar(11) NOT NULL,
    au_lname   VARCHAR(40) NOT NULL,
    au_fname   VARCHAR(40) NOT NULL,
    phone      VARCHAR(12),
    address    VARCHAR(40),
    city       VARCHAR(20),
    state      CHAR(2),
    zip        CHAR(5),
    PRIMARY KEY (au_id)
)

CREATE TABLE publishers
(
    pub_id       VARCHAR(4) NOT NULL,      
    pub_name   VARCHAR(40),
    city       VARCHAR(20),
    state      CHAR(2),
    PRIMARY KEY (pub_id)
)

CREATE TABLE roysched
(
    title_id   VARCHAR(6) NOT NULL,
    lorange    int,
    hirange    int,
    royalty    int NOT NULL,
    PRIMARY KEY (title_id, royalty)
)

CREATE TABLE sales
(
    stor_id    VARCHAR(4) NOT NULL,
    ord_num    VARCHAR(20) NOT NULL,   
    orderdate  datetime NOT NULL,
    qty        int NOT NULL,
    payterms   VARCHAR(12) NOT NULL,
    title_id   VARCHAR(6) NOT NULL,
    PRIMARY KEY (stor_id, ord_num, title_id)
)

CREATE TABLE titleauthor
(
    au_id      VARCHAR(11) NOT NULL,
    title_id   VARCHAR(6)  NOT NULL,
    au_ord     int,
    royaltyper int,
    PRIMARY KEY (au_id, title_id)
)

CREATE TABLE titles
(
    title_id   VARCHAR(6) NOT NULL,
    title      VARCHAR(80) NOT NULL,
    titletype  VARCHAR(12) NOT NULL,
    pub_id     VARCHAR(4),
    price      DECIMAL(10,2),
    advance    DECIMAL(10,2),
    royalty    int,
    ytd_sales  int,
    notes      TEXT,
    pubdate    datetime NOT NULL,
    PRIMARY KEY (title_id)
)

CREATE TABLE stores
(
    stor_id   VARCHAR(4) NOT NULL,
    stor_name  VARCHAR(40),
    stor_address  VARCHAR(40),
    city VARCHAR(20),
    state   CHAR(2),
    zip  CHAR(5),
    PRIMARY KEY (stor_id)
)   
                      
CREATE TABLE discount
(
    discount_type VARCHAR(40) NOT NULL,
    stor_id VARCHAR(4),
    lowqty  int,
    highqty int,
    discount  FLOAT NOT NULL,
    PRIMARY KEY (discount_type)
)