190416

CREATE TABLE TB_CATEGORIES (
    CATEGORY_NO NUMBER(3, 0) PRIMARY KEY,
    CATEGORY_NAME VARCHAR2(100) NOT NULL,
    PARENT_CATEGORY_NO NUMBER(3, 0) REFERENCES TB_CATEGORIES(CATEGORY_NO)
);
 
CREATE TABLE TB_COMPANIES (
    COMPANY_NO NUMBER(3) PRIMARY KEY,
    COMPANY_NAME VARCHAR2(100),
    COMPANY_TEL VARCHAR2(20),
    COMPANY_CREATE_DATE DATE DEFAULT SYSDATE
);
    
CREATE TABLE TB_GOODS (
    PRODUCT_NO NUMBER(5, 0) PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(200) NOT NULL,
    COMPANY_NO NUMBER(3) REFERENCES TB_COMPANIES(COMPANY_NO),
    PRODUCT_PRICE NUMBER(7, 0),
    PRODUCT_STOCK NUMBER(4, 0),
    PRODUCT_IMAGE_NAME VARCHAR2(100),
    PRODUCT_SELL_YN CHAR(1) DEFAULT 'Y' CHECK (PRODUCT_SELL_YN IN ('Y', 'N')),
    PRODUCT_LIKES NUMBER(5, 0) DEFAULT 0,
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE,
    CATEGORY_NO NUMBER(3, 0) REFERENCES TB_CATEGORIES(CATEGORY_NO)
);
    
CREATE TABLE TB_CUSTOMERS (
    CUSTOMSER_NO NUMBER(5,0) NOT NULL PRIMARY KEY,
    CUSTOMSER_NAME VARCHAR2(50) NOT NULL,
    CUSTOMSER_ID VARCHAR2(20) UNIQUE,
    CUSTOMSER_PASSWORD VARCHAR2(20) NOT NULL,
    CUSTOMSER_GENDER CHAR(1) CHECK (CUSTOMSER_GENDER IN ('F', 'M')),
    CUSTOMSER_EMAIL VARCHAR2(256) UNIQUE,
    CUSTOMSER_PHONE VARCHAR2(20),
    CUSTOMSER_ZIPCODE CHAR(5),
    CUSTOMSER_ADDRESS VARCHAR2(200),
    CUSTOMSER_CREATE_DATE DATE
);
 
CREATE TABLE TB_ORDERS (
    ORDER_NO NUMBER(5, 0) PRIMARY KEY,
    CUSTOMER_NO NUMBER(5, 0) REFERENCES TB_CUSTOMERS(CUSTOMSER_NO),
    ORDER_STATE VARCHAR2(20) DEFAULT '주문완료' CHECK(ORDER_STATE IN ('주문완료', '결제완료', '배송중', '배송완료')),
    ORDER_CREATE_DATE DATE DEFAULT SYSDATE
);
 
CREATE TABLE TB_ORDER_ITEMS (
    ORDER_ITEM_NO NUMBER(7, 0) PRIMARY KEY,
    PRODUCT_NO NUMBER(5, 0) REFERENCES TB_GOODS(PRODUCT_NO),
    ORDER_ITEM_AMOUNT NUMBER(3, 0),
    ORDER_NO NUMBER(5, 0) REFERENCES TB_ORDERS(ORDER_NO)
);