DROP TABLE IF EXISTS WROX_SHOP_INVENTORY;
DROP TABLE IF EXISTS WROX_SHOP_CATEGORY;

CREATE TABLE WROX_SHOP_CATEGORY (
    CATEGORY_ID   INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    CATEGORY_NAME VARCHAR(100)      NOT NULL, 

    PRIMARY KEY (CATEGORY_ID)
)
ENGINE=InnoDB DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs;

CREATE TABLE WROX_SHOP_INVENTORY (
    ITEM_ID          INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    ITEM_NAME        VARCHAR(100)      NOT NULL,
    ITEM_DESCRIPTION TEXT              DEFAULT '',
    PRICE            DOUBLE(5,2)       NOT NULL,
    ITEM_IMAGE       VARCHAR(255)      DEFAULT 'img/none.gif',
    CATEGORY_ID      INTEGER UNSIGNED  NOT NULL,

    PRIMARY KEY (ITEM_ID),

    FOREIGN KEY (CATEGORY_ID)
        REFERENCES WROX_SHOP_CATEGORY(CATEGORY_ID)
        ON DELETE CASCADE
)
ENGINE=InnoDB DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;