TABLES.sql

USE RENTIT_DATABASE;
CREATE TABLE USER (
    USER_ID int(10) NOT NULL AUTO_INCREMENT UNIQUE,
    USER_CODE varchar(20),
    USER_PASSWORD varchar(255),
    USER_LNAME varchar(100),
    USER_FNAME varchar(100) ,
    USER_STREET varchar(255) ,
    USER_CITY varchar(255) ,
    USER_STATE CHAR (2),
    USER_CC_NUM char(16) UNIQUE,
    USER_CC_EXP char(5),
    USER_CC_CODE char(3),
    USER_EMAIL varchar(255),
    PRIMARY KEY (USER_ID)
    ); 
    CREATE TABLE CATEGORY (
    CATEGORY_ID CHAR(4) NOT NULL,
    CATEGORY_NAME char(30) NOT NULL,
    PRIMARY KEY (CATEGORY_ID)
    ); 
    CREATE TABLE ITEM (
    ITEM_ID int(10) NOT NULL AUTO_INCREMENT,
    ITEM_TITLE varchar(255) NOT NULL,
    ITEM_AMOUNT decimal(18,2) NOT NULL,
    ITEM_DESC varchar(255),
    USER_ID int(10) NOT NULL,
    CATEGORY_ID CHAR(4) NOT NULL,
    PRIMARY KEY (ITEM_ID),
    FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID),
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID)
    ); 
    CREATE TABLE IMAGE (
	ITEM_ID int(10) NOT NULL,
	IMAGE_ONE LONGBLOB  NOT NULL,
    IMAGE_TWO LONGBLOB,
    IMAGE_THREE LONGBLOB,
    IMAGE_FOUR LONGBLOB,
    PRIMARY KEY (ITEM_ID),
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM(ITEM_ID)
    ); 
    CREATE TABLE SELLER (
    SELLER_ID int (10) UNIQUE NOT NULL AUTO_INCREMENT,
    USER_ID int(10) NOT NULL UNIQUE,
    PRIMARY KEY (SELLER_ID),
    FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID)
    );
     CREATE TABLE BUYER (
    BUYER_ID int (10) UNIQUE NOT NULL AUTO_INCREMENT,
    USER_ID int(10) NOT NULL UNIQUE,
    PRIMARY KEY (BUYER_ID),
    FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID)
    );
    CREATE TABLE TRANSACTION (
    TRANS_ID int(10) NOT NULL AUTO_INCREMENT UNIQUE,
	BUYER_ID int (10) NOT NULL,
    SELLER_ID int (10) NOT NULL,
    ITEM_ID int(10) NOT NULL,
    TRANS_DATE date NOT NULL,
    TRANS_TIME datetime NOT NULL,
    TRANS_AMOUNT_TO_RENTIT decimal (18,2) NOT NULL,
    PRIMARY KEY (TRANS_ID),
    FOREIGN KEY (BUYER_ID) REFERENCES BUYER(BUYER_ID),
    FOREIGN KEY (SELLER_ID) REFERENCES SELLER(SELLER_ID),
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM(ITEM_ID)
    ); 
    
    CREATE TABLE ASSIGNMENT (
    ITEM_ID int(10) NOT NULL,
    CATEGORY_ID CHAR(4) NOT NULL, 
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM(ITEM_ID),
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID),
    PRIMARY KEY (ITEM_ID,CATEGORY_ID)
    );