superstore_DDL.sql

CREATE TABLE CUSTOMERS_DIM(
    CUSTOMER_ID VARCHAR(10) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(50),
    CUSTOMER_SEGMENT VARCHAR(15),
    CITY VARCHAR(50),
    STATE VARCHAR(30),
    COUNTRY VARCHAR(15),
    POSTAL_CODE INT,
    REGION VARCHAR(10),
    REGIONAL_MANAGER VARCHAR(20)
);

CREATE TABLE PRODUCTS_DIM(
    PRODUCT_ID VARCHAR(20) PRIMARY KEY,
    PRODUCT_NAME VARCHAR(150),
    CATEGORY VARCHAR(20),
    SUB_CATEGORY VARCHAR(20),
    UNITS_SOLD INT
);

CREATE TABLE ORDERS_DIM(
    ORDER_ID VARCHAR(20) PRIMARY KEY,
    ORDER_DATE DATE,
    SHIP_DATE DATE,
    SHIP_MODE VARCHAR(15),
    RETURNED VARCHAR(3)
);

CREATE TABLE SALES_FACT(
    FACT_ID VARCHAR(10) PRIMARY KEY,
    ORDER_ID VARCHAR(20),
    CUSTOMER_ID VARCHAR(10),
    PRODUCT_ID VARCHAR(20),
    SALES FLOAT,
    QUANTITY INT,
    DISCOUNT FLOAT,
    PROFIT FLOAT,
    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS_DIM(ORDER_ID),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS_DIM(CUSTOMER_ID),
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS_DIM(PRODUCT_ID)
);

CREATE OR REPLACE SEQUENCE FACT_SEQ START = 1 INCREMENT = 1;