CREATE TABLE STORES(
        stor_id char(4) NOT NULL,
        stor_name varchar(40) NOT NULL,
        stor_address varchar(40) NOT NULL,
        city varchar(20) NOT NULL,
        state char(2) NOT NULL,
        zip char(5) NOT NULL,
        PRIMARY KEY(stor_id)
    );


CREATE TABLE DISCOUNTS(
        Discounttype varchar(40) NOT NULL,
        stor_id char(4) NOT NULL,
        lowqty SMALLINT,
        highqty SMALLINT,
        discount decimal(4,2),
        FOREIGN KEY (stor_id) REFERENCES STORES(stor_id)
    );


CREATE TABLE PUBLISHERS(
        pub_id char(4) NOT NULL,
        prob_name varchar(40),
        city varchar(20),
        state char(2),
        country varchar(30),
        PRIMARY KEY (pub_id)
    );


CREATE TABLE PUB_INFO(
        pub_id char(4) NOT NULL,
        logo BLOB,
        pr_info text,
        FOREIGN KEY (pub_id) REFERENCES PUBLISHERS(pub_id)
    );
ALTER TABLE PUBLISHERS ADD FOREIGN KEY (pub_id) REFERENCES PUB_INFO(pub_id);


CREATE TABLE JOBS(
        job_id smallint NOT NULL,
        job_desc varchar(50),
        min_lvl tinyint,
        max_lvl tinyint,
        PRIMARY kEY (job_id)

    );

CREATE TABLE EMPLOYEE(
        emp_id char(9) NOT NULL,
        fname varchar(20),
        minit   char(1),
        lname   varchar(30),
        job_id  SMALLINT NOT NULL,
        job_lvl tinyint,
        pub_id  char(4),
        hire_date datetime,
        PRIMARY KEY (emp_id),
        FOREIGN KEY (job_id) REFERENCES JOBS(job_id),
        FOREIGN KEY (pub_id) REFERENCES PUBLISHERS(pub_id)

    );


CREATE TABLE TITLES(
        title_id    varchar(6) NOT NULL,
        title   varchar(80),
        type    char(12),
        pub_id  char(4) NOT NULL,
        price   DECIMAL,
        advance DECIMAL,
        royalty int,
        ytd_sales int,
        notes   varchar(200),
        pubdate datetime,
        PRIMARY KEY (title_id),
        FOREIGN KEY (pub_id) REFERENCES PUBLISHERS(pub_id)
    );


CREATE TABLE ROYSCHED(
        title_id    varchar(6) NOT NULL,
        lorange     int,
        hirange     int,
        royalty     int,
        FOREIGN KEY(title_id) REFERENCES TITLES(title_id)
    );


CREATE TABLE AUTHORS(
        au_id   varchar(11) NOT NULL,
        au_lname    varchar(40),
        au_fname    varchar(20),
        phone   char(12),
        address varchar(40),
        city    varchar(20),
        state   char(2),
        zip     char(5),
        contract    bit,
        PRIMARY KEY (au_id)
    );


    CREATE TABLE TITLEAUTHOR(
        au_id   varchar(11),
        title_id    varchar(6) NOT NULL,
        au_ord  tinyint,
        royaltyper  int,
        FOREIGN KEY (au_id) REFERENCES AUTHORS(au_id),
        FOREIGN KEY (title_id) REFERENCES TITLES(title_id)
    );


CREATE TABLE SALES(
        stor_id char(4),
        ord_num varchar(20),
        ord_date    datetime,
        qty smallint,
        payterms    varchar(12),
        title_id    varchar(6),
        FOREIGN KEY (stor_id) REFERENCES STORES(stor_id),
        FOREIGN KEY (title_id) REFERENCES TITLES(title_id)
    );