30 SH.sql

-- Generated by Oracle SQL Developer Data Modeler 3.0.0.660.660
--   at:        2011-01-07 12:29:29 GMT
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g




CREATE TABLE SALES_TRANSACTIONS_EXT 
    ( 
    PROD_ID NUMBER (22) , 
    CUST_ID NUMBER (22) , 
    TIME_ID  DATE , 
    CHANNEL_ID NUMBER (22) , 
    PROMO_ID NUMBER (22) , 
    QUANTITY_SOLD NUMBER (22) , 
    AMOUNT_SOLD NUMBER (10, 2) , 
    UNIT_COST NUMBER (10, 2) , 
    UNIT_PRICE NUMBER (10, 2) 
    ) 
    
;

CREATE TABLE CHANNELS 
    ( 
     CHANNEL_ID NUMBER  NOT NULL , 
     CHANNEL_DESC VARCHAR2 (20 BYTE)  NOT NULL , 
     CHANNEL_CLASS VARCHAR2 (20 BYTE)  NOT NULL , 
     CHANNEL_CLASS_ID NUMBER  NOT NULL , 
     CHANNEL_TOTAL VARCHAR2 (13 BYTE)  NOT NULL , 
     CHANNEL_TOTAL_ID NUMBER  NOT NULL 
    ) LOGGING 
;



COMMENT ON COLUMN CHANNELS.CHANNEL_ID IS 'primary key column' 
;

COMMENT ON COLUMN CHANNELS.CHANNEL_DESC IS 'e.g. telesales, internet, catalog' 
;

COMMENT ON COLUMN CHANNELS.CHANNEL_CLASS IS 'e.g. direct, indirect' 
;

ALTER TABLE CHANNELS 
    ADD CONSTRAINT CHANNELS_PK PRIMARY KEY ( CHANNEL_ID ) NOVALIDATE ;



CREATE TABLE COSTS 
    ( 
     PROD_ID NUMBER (6)  NOT NULL , 
     TIME_ID DATE  NOT NULL , 
     PROMO_ID NUMBER (6)  NOT NULL , 
     CHANNEL_ID NUMBER  NOT NULL , 
     UNIT_COST NUMBER (10,2)  NOT NULL , 
     UNIT_PRICE NUMBER (10,2)  NOT NULL 
    ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) 
    DISABLE ROW MOVEMENT 
    PARTITION BY RANGE ( TIME_ID ) 
    ( 
    PARTITION COSTS_1995 VALUES LESS THAN ( TO_DATE(' 1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_1996 VALUES LESS THAN ( TO_DATE(' 1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_H1_1997 VALUES LESS THAN ( TO_DATE(' 1997-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_H2_1997 VALUES LESS THAN ( TO_DATE(' 1998-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q1_1998 VALUES LESS THAN ( TO_DATE(' 1998-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q2_1998 VALUES LESS THAN ( TO_DATE(' 1998-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q3_1998 VALUES LESS THAN ( TO_DATE(' 1998-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q4_1998 VALUES LESS THAN ( TO_DATE(' 1999-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q1_1999 VALUES LESS THAN ( TO_DATE(' 1999-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q2_1999 VALUES LESS THAN ( TO_DATE(' 1999-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q3_1999 VALUES LESS THAN ( TO_DATE(' 1999-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q4_1999 VALUES LESS THAN ( TO_DATE(' 2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q1_2000 VALUES LESS THAN ( TO_DATE(' 2000-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q2_2000 VALUES LESS THAN ( TO_DATE(' 2000-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q3_2000 VALUES LESS THAN ( TO_DATE(' 2000-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q4_2000 VALUES LESS THAN ( TO_DATE(' 2001-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q1_2001 VALUES LESS THAN ( TO_DATE(' 2001-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q2_2001 VALUES LESS THAN ( TO_DATE(' 2001-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q3_2001 VALUES LESS THAN ( TO_DATE(' 2001-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q4_2001 VALUES LESS THAN ( TO_DATE(' 2002-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q1_2002 VALUES LESS THAN ( TO_DATE(' 2002-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q2_2002 VALUES LESS THAN ( TO_DATE(' 2002-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q3_2002 VALUES LESS THAN ( TO_DATE(' 2002-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 

    PARTITION COSTS_Q4_2002 VALUES LESS THAN ( TO_DATE(' 2003-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 


    PARTITION COSTS_Q1_2003 VALUES LESS THAN ( TO_DATE(' 2003-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q2_2003 VALUES LESS THAN ( TO_DATE(' 2003-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q3_2003 VALUES LESS THAN ( TO_DATE(' 2003-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION COSTS_Q4_2003 VALUES LESS THAN ( TO_DATE(' 2004-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 5 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) 
    ) 
;



CREATE BITMAP INDEX COSTS_PROD_BIX ON COSTS 
    ( 
     PROD_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION COSTS_1995 ,
    PARTITION COSTS_1996 ,
    PARTITION COSTS_H1_1997 ,
    PARTITION COSTS_H2_1997 ,
    PARTITION COSTS_Q1_1998 ,
    PARTITION COSTS_Q2_1998 ,
    PARTITION COSTS_Q3_1998 ,
    PARTITION COSTS_Q4_1998 ,
    PARTITION COSTS_Q1_1999 ,
    PARTITION COSTS_Q2_1999 ,
    PARTITION COSTS_Q3_1999 ,
    PARTITION COSTS_Q4_1999 ,
    PARTITION COSTS_Q1_2000 ,
    PARTITION COSTS_Q2_2000 ,
    PARTITION COSTS_Q3_2000 ,
    PARTITION COSTS_Q4_2000 ,
    PARTITION COSTS_Q1_2001 ,
    PARTITION COSTS_Q2_2001 ,
    PARTITION COSTS_Q3_2001 ,
    PARTITION COSTS_Q4_2001 ,
    PARTITION COSTS_Q1_2002 ,
    PARTITION COSTS_Q2_2002 ,
    PARTITION COSTS_Q3_2002 ,
    PARTITION COSTS_Q4_2002 ,
    PARTITION COSTS_Q1_2003 ,
    PARTITION COSTS_Q2_2003 ,
    PARTITION COSTS_Q3_2003 ,
    PARTITION COSTS_Q4_2003 
    ) 
    PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX COSTS_TIME_BIX ON COSTS 
    ( 
     TIME_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION COSTS_1995 ,
    PARTITION COSTS_1996 ,
    PARTITION COSTS_H1_1997 ,
    PARTITION COSTS_H2_1997 ,
    PARTITION COSTS_Q1_1998 ,
    PARTITION COSTS_Q2_1998 ,
    PARTITION COSTS_Q3_1998 ,
    PARTITION COSTS_Q4_1998 ,
    PARTITION COSTS_Q1_1999 ,
    PARTITION COSTS_Q2_1999 ,
    PARTITION COSTS_Q3_1999 ,
    PARTITION COSTS_Q4_1999 ,
    PARTITION COSTS_Q1_2000 ,
    PARTITION COSTS_Q2_2000 ,
    PARTITION COSTS_Q3_2000 ,
    PARTITION COSTS_Q4_2000 ,
    PARTITION COSTS_Q1_2001 ,
    PARTITION COSTS_Q2_2001 ,
    PARTITION COSTS_Q3_2001 ,
    PARTITION COSTS_Q4_2001 ,
    PARTITION COSTS_Q1_2002 ,
    PARTITION COSTS_Q2_2002 ,
    PARTITION COSTS_Q4_2002 ,
    PARTITION COSTS_Q1_2003 ,
    PARTITION COSTS_Q2_2003 ,
    PARTITION COSTS_Q3_2003 ,
    PARTITION COSTS_Q3_2002 ,
    PARTITION COSTS_Q4_2003 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;



CREATE TABLE COUNTRIES 
    ( 
     COUNTRY_ID NUMBER  NOT NULL , 
     COUNTRY_ISO_CODE CHAR (2 BYTE)  NOT NULL , 
     COUNTRY_NAME VARCHAR2 (40 BYTE)  NOT NULL , 
     COUNTRY_SUBREGION VARCHAR2 (30 BYTE)  NOT NULL , 
     COUNTRY_SUBREGION_ID NUMBER  NOT NULL , 
     COUNTRY_REGION VARCHAR2 (20 BYTE)  NOT NULL , 
     COUNTRY_REGION_ID NUMBER  NOT NULL , 
     COUNTRY_TOTAL VARCHAR2 (11 BYTE)  NOT NULL , 
     COUNTRY_TOTAL_ID NUMBER  NOT NULL , 
     COUNTRY_NAME_HIST VARCHAR2 (40 BYTE) 
    ) LOGGING 
;



COMMENT ON COLUMN COUNTRIES.COUNTRY_ID IS 'primary key' 
;

COMMENT ON COLUMN COUNTRIES.COUNTRY_NAME IS 'country name' 
;

COMMENT ON COLUMN COUNTRIES.COUNTRY_SUBREGION IS 'e.g. Western Europe, to allow hierarchies' 
;

COMMENT ON COLUMN COUNTRIES.COUNTRY_REGION IS 'e.g. Europe, Asia' 
;

ALTER TABLE COUNTRIES 
    ADD CONSTRAINT COUNTRIES_PK PRIMARY KEY ( COUNTRY_ID ) NOVALIDATE ;



CREATE TABLE CUSTOMERS 
    ( 
     CUST_ID NUMBER  NOT NULL , 
     CUST_FIRST_NAME VARCHAR2 (20 BYTE)  NOT NULL , 
     CUST_LAST_NAME VARCHAR2 (40 BYTE)  NOT NULL , 
     CUST_GENDER CHAR (1 BYTE)  NOT NULL , 
     CUST_YEAR_OF_BIRTH NUMBER (4)  NOT NULL , 
     CUST_MARITAL_STATUS VARCHAR2 (20 BYTE) , 
     CUST_STREET_ADDRESS VARCHAR2 (40 BYTE)  NOT NULL , 
     CUST_POSTAL_CODE VARCHAR2 (10 BYTE)  NOT NULL , 
     CUST_CITY VARCHAR2 (30 BYTE)  NOT NULL , 
     CUST_CITY_ID NUMBER  NOT NULL , 
     CUST_STATE_PROVINCE VARCHAR2 (40 BYTE)  NOT NULL , 
     CUST_STATE_PROVINCE_ID NUMBER  NOT NULL , 
     COUNTRY_ID NUMBER  NOT NULL , 
     CUST_MAIN_PHONE_NUMBER VARCHAR2 (25 BYTE)  NOT NULL , 
     CUST_INCOME_LEVEL VARCHAR2 (30 BYTE) , 
     CUST_CREDIT_LIMIT NUMBER , 
     CUST_EMAIL VARCHAR2 (30 BYTE) , 
     CUST_TOTAL VARCHAR2 (14 BYTE)  NOT NULL , 
     CUST_TOTAL_ID NUMBER  NOT NULL , 
     CUST_SRC_ID NUMBER , 
     CUST_EFF_FROM DATE , 
     CUST_EFF_TO DATE , 
     CUST_VALID VARCHAR2 (1 BYTE) 
    ) LOGGING 
;



COMMENT ON COLUMN CUSTOMERS.CUST_ID IS 'primary key' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_FIRST_NAME IS 'first name of the customer' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_LAST_NAME IS 'last name of the customer' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_GENDER IS 'gender; low cardinality attribute' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_YEAR_OF_BIRTH IS 'customer year of birth' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_MARITAL_STATUS IS 'customer marital status; low cardinality attribute' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_STREET_ADDRESS IS 'customer street address' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_POSTAL_CODE IS 'postal code of the customer' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_CITY IS 'city where the customer lives' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_STATE_PROVINCE IS 'customer geography: state or province' 
;

COMMENT ON COLUMN CUSTOMERS.COUNTRY_ID IS 'foreign key to the countries table (snowflake)' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_MAIN_PHONE_NUMBER IS 'customer main phone number' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_INCOME_LEVEL IS 'customer income level' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_CREDIT_LIMIT IS 'customer credit limit' 
;

COMMENT ON COLUMN CUSTOMERS.CUST_EMAIL IS 'customer email id' 
;

CREATE BITMAP INDEX CUSTOMERS_GENDER_BIX ON CUSTOMERS 
    ( 
     CUST_GENDER ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX CUSTOMERS_MARITAL_BIX ON CUSTOMERS 
    ( 
     CUST_MARITAL_STATUS ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX CUSTOMERS_YOB_BIX ON CUSTOMERS 
    ( 
     CUST_YEAR_OF_BIRTH ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

ALTER TABLE CUSTOMERS 
    ADD CONSTRAINT CUSTOMERS_PK PRIMARY KEY ( CUST_ID ) NOVALIDATE ;



CREATE TABLE DIMENSION_EXCEPTIONS 
    ( 
     STATEMENT_ID VARCHAR2 (30 BYTE) , 
     OWNER VARCHAR2 (30 BYTE)  NOT NULL , 
     TABLE_NAME VARCHAR2 (30 BYTE)  NOT NULL , 
     DIMENSION_NAME VARCHAR2 (30 BYTE)  NOT NULL , 
     RELATIONSHIP VARCHAR2 (11 BYTE)  NOT NULL , 
     BAD_ROWID ROWID  NOT NULL 
    ) LOGGING 
;





CREATE TABLE MLOG$_PRODUCTS 
    ( 
     PROD_ID NUMBER (6) , 
     SEQUENCE$$ NUMBER , 
     SNAPTIME$$ DATE , 
     DMLTYPE$$ VARCHAR2 (1 BYTE) , 
     OLD_NEW$$ VARCHAR2 (1 BYTE) , 
     CHANGE_VECTOR$$ RAW (255) 
    ) LOGGING 
    PARALLEL 
;





CREATE TABLE MLOG$_SALES 
    ( 
     PROD_ID NUMBER , 
     CUST_ID NUMBER , 
     TIME_ID DATE , 
     CHANNEL_ID NUMBER , 
     PROMO_ID NUMBER , 
     QUANTITY_SOLD NUMBER (10,2) , 
     AMOUNT_SOLD NUMBER (10,2) , 
     M_ROW$$ VARCHAR2 (255 BYTE) , 
     SEQUENCE$$ NUMBER , 
     SNAPTIME$$ DATE , 
     DMLTYPE$$ VARCHAR2 (1 BYTE) , 
     OLD_NEW$$ VARCHAR2 (1 BYTE) , 
     CHANGE_VECTOR$$ RAW (255) 
    ) LOGGING 
;





CREATE TABLE PRODUCTS 
    ( 
     PROD_ID NUMBER (6)  NOT NULL , 
     PROD_NAME VARCHAR2 (50 BYTE)  NOT NULL , 
     PROD_DESC VARCHAR2 (4000 BYTE)  NOT NULL , 
     PROD_SUBCATEGORY VARCHAR2 (50 BYTE)  NOT NULL , 
     PROD_SUBCATEGORY_ID NUMBER  NOT NULL , 
     PROD_SUBCATEGORY_DESC VARCHAR2 (2000 BYTE)  NOT NULL , 
     PROD_CATEGORY VARCHAR2 (50 BYTE)  NOT NULL , 
     PROD_CATEGORY_ID NUMBER  NOT NULL , 
     PROD_CATEGORY_DESC VARCHAR2 (2000 BYTE)  NOT NULL , 
     PROD_WEIGHT_CLASS NUMBER (3)  NOT NULL , 
     PROD_UNIT_OF_MEASURE VARCHAR2 (20 BYTE) , 
     PROD_PACK_SIZE VARCHAR2 (30 BYTE)  NOT NULL , 
     SUPPLIER_ID NUMBER (6)  NOT NULL , 
     PROD_STATUS VARCHAR2 (20 BYTE)  NOT NULL , 
     PROD_LIST_PRICE NUMBER (8,2)  NOT NULL , 
     PROD_MIN_PRICE NUMBER (8,2)  NOT NULL , 
     PROD_TOTAL VARCHAR2 (13 BYTE)  NOT NULL , 
     PROD_TOTAL_ID NUMBER  NOT NULL , 
     PROD_SRC_ID NUMBER , 
     PROD_EFF_FROM DATE , 
     PROD_EFF_TO DATE , 
     PROD_VALID VARCHAR2 (1 BYTE) 
    ) LOGGING 
;



COMMENT ON COLUMN PRODUCTS.PROD_ID IS 'primary key' 
;

COMMENT ON COLUMN PRODUCTS.PROD_NAME IS 'product name' 
;

COMMENT ON COLUMN PRODUCTS.PROD_DESC IS 'product description' 
;

COMMENT ON COLUMN PRODUCTS.PROD_SUBCATEGORY IS 'product subcategory' 
;

COMMENT ON COLUMN PRODUCTS.PROD_SUBCATEGORY_DESC IS 'product subcategory description' 
;

COMMENT ON COLUMN PRODUCTS.PROD_CATEGORY IS 'product category' 
;

COMMENT ON COLUMN PRODUCTS.PROD_CATEGORY_DESC IS 'product category description' 
;

COMMENT ON COLUMN PRODUCTS.PROD_WEIGHT_CLASS IS 'product weight class' 
;

COMMENT ON COLUMN PRODUCTS.PROD_UNIT_OF_MEASURE IS 'product unit of measure' 
;

COMMENT ON COLUMN PRODUCTS.PROD_PACK_SIZE IS 'product package size' 
;

COMMENT ON COLUMN PRODUCTS.SUPPLIER_ID IS 'this column' 
;

COMMENT ON COLUMN PRODUCTS.PROD_STATUS IS 'product status' 
;

COMMENT ON COLUMN PRODUCTS.PROD_LIST_PRICE IS 'product list price' 
;

COMMENT ON COLUMN PRODUCTS.PROD_MIN_PRICE IS 'product minimum price' 
;

CREATE BITMAP INDEX PRODUCTS_PROD_STATUS_BIX ON PRODUCTS 
    ( 
     PROD_STATUS ASC 
    ) 
       LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE INDEX PRODUCTS_PROD_SUBCAT_IX ON PRODUCTS 
    ( 
     PROD_SUBCATEGORY ASC 
    ) 
       LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE INDEX PRODUCTS_PROD_CAT_IX ON PRODUCTS 
    ( 
     PROD_CATEGORY ASC 
    ) 
       LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

ALTER TABLE PRODUCTS 
    ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY ( PROD_ID ) NOVALIDATE ;



CREATE TABLE PROMOTIONS 
    ( 
     PROMO_ID NUMBER (6)  NOT NULL , 
     PROMO_NAME VARCHAR2 (30 BYTE)  NOT NULL , 
     PROMO_SUBCATEGORY VARCHAR2 (30 BYTE)  NOT NULL , 
     PROMO_SUBCATEGORY_ID NUMBER  NOT NULL , 
     PROMO_CATEGORY VARCHAR2 (30 BYTE)  NOT NULL , 
     PROMO_CATEGORY_ID NUMBER  NOT NULL , 
     PROMO_COST NUMBER (10,2)  NOT NULL , 
     PROMO_BEGIN_DATE DATE  NOT NULL , 
     PROMO_END_DATE DATE  NOT NULL , 
     PROMO_TOTAL VARCHAR2 (15 BYTE)  NOT NULL , 
     PROMO_TOTAL_ID NUMBER  NOT NULL 
    ) LOGGING 
;



COMMENT ON COLUMN PROMOTIONS.PROMO_ID IS 'primary key column' 
;

COMMENT ON COLUMN PROMOTIONS.PROMO_NAME IS 'promotion description' 
;

COMMENT ON COLUMN PROMOTIONS.PROMO_SUBCATEGORY IS 'enables to investigate promotion hierarchies' 
;

COMMENT ON COLUMN PROMOTIONS.PROMO_CATEGORY IS 'promotion category' 
;

COMMENT ON COLUMN PROMOTIONS.PROMO_COST IS 'promotion cost, to do promotion effect calculations' 
;

COMMENT ON COLUMN PROMOTIONS.PROMO_BEGIN_DATE IS 'promotion begin day' 
;

COMMENT ON COLUMN PROMOTIONS.PROMO_END_DATE IS 'promotion end day' 
;

ALTER TABLE PROMOTIONS 
    ADD CONSTRAINT PROMO_PK PRIMARY KEY ( PROMO_ID ) NOVALIDATE ;



CREATE TABLE REAL_TIME_MONITORING 
    ( 
     REPORT CLOB 
    )    NOLOGGING 
    LOB ( REPORT ) STORE AS LS1 
        ( 
               STORAGE ( 
            PCTINCREASE 0 
            MINEXTENTS 1 
            MAXEXTENTS UNLIMITED 
            FREELISTS 1 
            BUFFER_POOL DEFAULT 
        ) 
        CHUNK 8192 
        RETENTION 
        ENABLE STORAGE IN ROW
        NOCACHE NOLOGGING 
    ) 
;





CREATE GLOBAL TEMPORARY TABLE RUPD$_PRODUCTS 
    ( 
     PROD_ID NUMBER (6) , 
     DMLTYPE$$ VARCHAR2 (1 BYTE) , 
     SNAPID NUMBER (38) , 
     CHANGE_VECTOR$$ RAW (255) 
    ) 
    ON COMMIT PRESERVE ROWS 
;





CREATE TABLE SALES 
    ( 
     PROD_ID NUMBER (6)  NOT NULL , 
     CUST_ID NUMBER  NOT NULL , 
     TIME_ID DATE  NOT NULL , 
     CHANNEL_ID NUMBER  NOT NULL , 
     PROMO_ID NUMBER (6)  NOT NULL , 
     QUANTITY_SOLD NUMBER (10,2)  NOT NULL , 
     AMOUNT_SOLD NUMBER (10,2)  NOT NULL 
    ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) 
    DISABLE ROW MOVEMENT 
    PARTITION BY RANGE ( TIME_ID ) 
    ( 
    PARTITION SALES_1995 VALUES LESS THAN ( TO_DATE(' 1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_1996 VALUES LESS THAN ( TO_DATE(' 1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_H1_1997 VALUES LESS THAN ( TO_DATE(' 1997-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_H2_1997 VALUES LESS THAN ( TO_DATE(' 1998-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q1_1998 VALUES LESS THAN ( TO_DATE(' 1998-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q2_1998 VALUES LESS THAN ( TO_DATE(' 1998-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q3_1998 VALUES LESS THAN ( TO_DATE(' 1998-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q4_1998 VALUES LESS THAN ( TO_DATE(' 1999-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q1_1999 VALUES LESS THAN ( TO_DATE(' 1999-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q2_1999 VALUES LESS THAN ( TO_DATE(' 1999-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q3_1999 VALUES LESS THAN ( TO_DATE(' 1999-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q4_1999 VALUES LESS THAN ( TO_DATE(' 2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q1_2000 VALUES LESS THAN ( TO_DATE(' 2000-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q2_2000 VALUES LESS THAN ( TO_DATE(' 2000-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q3_2000 VALUES LESS THAN ( TO_DATE(' 2000-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q4_2000 VALUES LESS THAN ( TO_DATE(' 2001-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q1_2001 VALUES LESS THAN ( TO_DATE(' 2001-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q2_2001 VALUES LESS THAN ( TO_DATE(' 2001-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q3_2001 VALUES LESS THAN ( TO_DATE(' 2001-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q4_2001 VALUES LESS THAN ( TO_DATE(' 2002-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q1_2002 VALUES LESS THAN ( TO_DATE(' 2002-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 

    PARTITION SALES_Q2_2002 VALUES LESS THAN ( TO_DATE(' 2002-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) ,         
    PARTITION SALES_Q3_2002 VALUES LESS THAN ( TO_DATE(' 2002-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q4_2002 VALUES LESS THAN ( TO_DATE(' 2003-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q1_2003 VALUES LESS THAN ( TO_DATE(' 2003-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q2_2003 VALUES LESS THAN ( TO_DATE(' 2003-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q3_2003 VALUES LESS THAN ( TO_DATE(' 2003-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 0 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) , 
    PARTITION SALES_Q4_2003 VALUES LESS THAN ( TO_DATE(' 2004-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') ) 
        PCTFREE 5 
        PCTUSED 40 
        MAXTRANS 255 
        NOLOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) 
    ) 
;



COMMENT ON COLUMN SALES.PROD_ID IS 'FK to the products dimension table' 
;

COMMENT ON COLUMN SALES.CUST_ID IS 'FK to the customers dimension table' 
;

COMMENT ON COLUMN SALES.TIME_ID IS 'FK to the times dimension table' 
;

COMMENT ON COLUMN SALES.CHANNEL_ID IS 'FK to the channels dimension table' 
;

COMMENT ON COLUMN SALES.PROMO_ID IS 'promotion identifier, without FK constraint (intentionally) to show outer join optimization' 
;

COMMENT ON COLUMN SALES.QUANTITY_SOLD IS 'product quantity sold with the transaction' 
;

COMMENT ON COLUMN SALES.AMOUNT_SOLD IS 'invoiced amount to the customer' 
;

CREATE INDEX COST_IX ON SALES 
    ( 
     AMOUNT_SOLD ASC 
    ) 
    PCTFREE 0 
    STORAGE ( 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX SALES_PROD_BIX ON SALES 
    ( 
     PROD_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION SALES_1995 ,
    PARTITION SALES_1996 ,
    PARTITION SALES_H1_1997 ,
    PARTITION SALES_H2_1997 ,
    PARTITION SALES_Q1_1998 ,
    PARTITION SALES_Q2_1998 ,
    PARTITION SALES_Q3_1998 ,
    PARTITION SALES_Q4_1998 ,
    PARTITION SALES_Q1_1999 ,
    PARTITION SALES_Q2_1999 ,
    PARTITION SALES_Q3_1999 ,
    PARTITION SALES_Q4_1999 ,
    PARTITION SALES_Q1_2000 ,
    PARTITION SALES_Q2_2000 ,
    PARTITION SALES_Q3_2000 ,
    PARTITION SALES_Q4_2000 ,
    PARTITION SALES_Q1_2001 ,
    PARTITION SALES_Q2_2001 ,
    PARTITION SALES_Q3_2001 ,
    PARTITION SALES_Q4_2001 ,
    PARTITION SALES_Q1_2002 ,
    PARTITION SALES_Q3_2002 ,
    PARTITION SALES_Q4_2002 ,
    PARTITION SALES_Q1_2003 ,
    PARTITION SALES_Q2_2003 ,
    PARTITION SALES_Q3_2003 ,
    PARTITION SALES_Q2_2002 ,
    PARTITION SALES_Q4_2003 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX SALES_CUST_BIX ON SALES 
    ( 
     CUST_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION SALES_1995 ,
    PARTITION SALES_1996 ,
    PARTITION SALES_H1_1997 ,
    PARTITION SALES_H2_1997 ,
    PARTITION SALES_Q1_1998 ,
    PARTITION SALES_Q2_1998 ,
    PARTITION SALES_Q3_1998 ,
    PARTITION SALES_Q4_1998 ,
    PARTITION SALES_Q1_1999 ,
    PARTITION SALES_Q2_1999 ,
    PARTITION SALES_Q3_1999 ,
    PARTITION SALES_Q4_1999 ,
    PARTITION SALES_Q1_2000 ,
    PARTITION SALES_Q2_2000 ,
    PARTITION SALES_Q3_2000 ,
    PARTITION SALES_Q4_2000 ,
    PARTITION SALES_Q1_2001 ,
    PARTITION SALES_Q2_2001 ,
    PARTITION SALES_Q3_2001 ,
    PARTITION SALES_Q4_2001 ,
    PARTITION SALES_Q1_2002 ,
    PARTITION SALES_Q3_2002 ,
    PARTITION SALES_Q4_2002 ,
    PARTITION SALES_Q1_2003 ,
    PARTITION SALES_Q2_2003 ,
    PARTITION SALES_Q3_2003 ,
    PARTITION SALES_Q2_2002 ,
    PARTITION SALES_Q4_2003 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX SALES_TIME_BIX ON SALES 
    ( 
     TIME_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION SALES_1995 ,
    PARTITION SALES_1996 ,
    PARTITION SALES_H1_1997 ,
    PARTITION SALES_H2_1997 ,
    PARTITION SALES_Q1_1998 ,
    PARTITION SALES_Q2_1998 ,
    PARTITION SALES_Q3_1998 ,
    PARTITION SALES_Q4_1998 ,
    PARTITION SALES_Q1_1999 ,
    PARTITION SALES_Q2_1999 ,
    PARTITION SALES_Q3_1999 ,
    PARTITION SALES_Q4_1999 ,
    PARTITION SALES_Q1_2000 ,
    PARTITION SALES_Q2_2000 ,
    PARTITION SALES_Q3_2000 ,
    PARTITION SALES_Q4_2000 ,
    PARTITION SALES_Q1_2001 ,
    PARTITION SALES_Q2_2001 ,
    PARTITION SALES_Q3_2001 ,
    PARTITION SALES_Q4_2001 ,
    PARTITION SALES_Q1_2002 ,
    PARTITION SALES_Q3_2002 ,
    PARTITION SALES_Q4_2002 ,
    PARTITION SALES_Q1_2003 ,
    PARTITION SALES_Q2_2003 ,
    PARTITION SALES_Q3_2003 ,
    PARTITION SALES_Q2_2002 ,
    PARTITION SALES_Q4_2003 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX SALES_CHANNEL_BIX ON SALES 
    ( 
     CHANNEL_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION SALES_1995 ,
    PARTITION SALES_1996 ,
    PARTITION SALES_H1_1997 ,
    PARTITION SALES_H2_1997 ,
    PARTITION SALES_Q1_1998 ,
    PARTITION SALES_Q2_1998 ,
    PARTITION SALES_Q3_1998 ,
    PARTITION SALES_Q4_1998 ,
    PARTITION SALES_Q1_1999 ,
    PARTITION SALES_Q2_1999 ,
    PARTITION SALES_Q3_1999 ,
    PARTITION SALES_Q4_1999 ,
    PARTITION SALES_Q1_2000 ,
    PARTITION SALES_Q2_2000 ,
    PARTITION SALES_Q3_2000 ,
    PARTITION SALES_Q4_2000 ,
    PARTITION SALES_Q1_2001 ,
    PARTITION SALES_Q2_2001 ,
    PARTITION SALES_Q3_2001 ,
    PARTITION SALES_Q4_2001 ,
    PARTITION SALES_Q1_2002 ,
    PARTITION SALES_Q3_2002 ,
    PARTITION SALES_Q4_2002 ,
    PARTITION SALES_Q1_2003 ,
    PARTITION SALES_Q2_2003 ,
    PARTITION SALES_Q3_2003 ,
    PARTITION SALES_Q2_2002 ,
    PARTITION SALES_Q4_2003 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX SALES_PROMO_BIX ON SALES 
    ( 
     PROMO_ID ASC 
    ) 
    LOCAL 
    ( 
    PARTITION SALES_1995 ,
    PARTITION SALES_1996 ,
    PARTITION SALES_H1_1997 ,
    PARTITION SALES_H2_1997 ,
    PARTITION SALES_Q1_1998 ,
    PARTITION SALES_Q2_1998 ,
    PARTITION SALES_Q3_1998 ,
    PARTITION SALES_Q4_1998 ,
    PARTITION SALES_Q1_1999 ,
    PARTITION SALES_Q2_1999 ,
    PARTITION SALES_Q3_1999 ,
    PARTITION SALES_Q4_1999 ,
    PARTITION SALES_Q1_2000 ,
    PARTITION SALES_Q2_2000 ,
    PARTITION SALES_Q3_2000 ,
    PARTITION SALES_Q4_2000 ,
    PARTITION SALES_Q1_2001 ,
    PARTITION SALES_Q2_2001 ,
    PARTITION SALES_Q3_2001 ,
    PARTITION SALES_Q4_2001 ,
    PARTITION SALES_Q1_2002 ,
    PARTITION SALES_Q3_2002 ,
    PARTITION SALES_Q4_2002 ,
    PARTITION SALES_Q1_2003 ,
    PARTITION SALES_Q2_2003 ,
    PARTITION SALES_Q3_2003 ,
    PARTITION SALES_Q2_2002 ,
    PARTITION SALES_Q4_2003 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;



CREATE TABLE SUPPLEMENTARY_DEMOGRAPHICS 
    ( 
     CUST_ID NUMBER  NOT NULL , 
     EDUCATION VARCHAR2 (21 BYTE) , 
     OCCUPATION VARCHAR2 (21 BYTE) , 
     HOUSEHOLD_SIZE VARCHAR2 (21 BYTE) , 
     YRS_RESIDENCE NUMBER , 
     AFFINITY_CARD NUMBER (10) , 
     BULK_PACK_DISKETTES NUMBER (10) , 
     FLAT_PANEL_MONITOR NUMBER (10) , 
     HOME_THEATER_PACKAGE NUMBER (10) , 
     BOOKKEEPING_APPLICATION NUMBER (10) , 
     PRINTER_SUPPLIES NUMBER (10) , 
     Y_BOX_GAMES NUMBER (10) , 
     OS_DOC_SET_KANJI NUMBER (10) , 
     COMMENTS VARCHAR2 (4000 BYTE) 
    ) LOGGING 
;



CREATE INDEX SUP_TEXT_IDX ON SUPPLEMENTARY_DEMOGRAPHICS 
    ( 
     COMMENTS ASC 
    ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

ALTER TABLE SUPPLEMENTARY_DEMOGRAPHICS 
    ADD CONSTRAINT SUPP_DEMO_PK PRIMARY KEY ( CUST_ID ) DISABLE NOVALIDATE ;



CREATE TABLE TIMES 
    ( 
     TIME_ID DATE  NOT NULL , 
     DAY_NAME VARCHAR2 (9 BYTE)  NOT NULL , 
     DAY_NUMBER_IN_WEEK NUMBER (1)  NOT NULL , 
     DAY_NUMBER_IN_MONTH NUMBER (2)  NOT NULL , 
     CALENDAR_WEEK_NUMBER NUMBER (2)  NOT NULL , 
     FISCAL_WEEK_NUMBER NUMBER (2)  NOT NULL , 
     WEEK_ENDING_DAY DATE  NOT NULL , 
     WEEK_ENDING_DAY_ID NUMBER  NOT NULL , 
     CALENDAR_MONTH_NUMBER NUMBER (2)  NOT NULL , 
     FISCAL_MONTH_NUMBER NUMBER (2)  NOT NULL , 
     CALENDAR_MONTH_DESC VARCHAR2 (8 BYTE)  NOT NULL , 
     CALENDAR_MONTH_ID NUMBER  NOT NULL , 
     FISCAL_MONTH_DESC VARCHAR2 (8 BYTE)  NOT NULL , 
     FISCAL_MONTH_ID NUMBER  NOT NULL , 
     DAYS_IN_CAL_MONTH NUMBER  NOT NULL , 
     DAYS_IN_FIS_MONTH NUMBER  NOT NULL , 
     END_OF_CAL_MONTH DATE  NOT NULL , 
     END_OF_FIS_MONTH DATE  NOT NULL , 
     CALENDAR_MONTH_NAME VARCHAR2 (9 BYTE)  NOT NULL , 
     FISCAL_MONTH_NAME VARCHAR2 (9 BYTE)  NOT NULL , 
     CALENDAR_QUARTER_DESC CHAR (7 BYTE)  NOT NULL , 
     CALENDAR_QUARTER_ID NUMBER  NOT NULL , 
     FISCAL_QUARTER_DESC CHAR (7 BYTE)  NOT NULL , 
     FISCAL_QUARTER_ID NUMBER  NOT NULL , 
     DAYS_IN_CAL_QUARTER NUMBER  NOT NULL , 
     DAYS_IN_FIS_QUARTER NUMBER  NOT NULL , 
     END_OF_CAL_QUARTER DATE  NOT NULL , 
     END_OF_FIS_QUARTER DATE  NOT NULL , 
     CALENDAR_QUARTER_NUMBER NUMBER (1)  NOT NULL , 
     FISCAL_QUARTER_NUMBER NUMBER (1)  NOT NULL , 
     CALENDAR_YEAR NUMBER (4)  NOT NULL , 
     CALENDAR_YEAR_ID NUMBER  NOT NULL , 
     FISCAL_YEAR NUMBER (4)  NOT NULL , 
     FISCAL_YEAR_ID NUMBER  NOT NULL , 
     DAYS_IN_CAL_YEAR NUMBER  NOT NULL , 
     DAYS_IN_FIS_YEAR NUMBER  NOT NULL , 
     END_OF_CAL_YEAR DATE  NOT NULL , 
     END_OF_FIS_YEAR DATE  NOT NULL 
    ) LOGGING 
;



COMMENT ON COLUMN TIMES.TIME_ID IS 'primary key; day date, finest granularity, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.DAY_NAME IS 'Monday to Sunday, repeating' 
;

COMMENT ON COLUMN TIMES.DAY_NUMBER_IN_WEEK IS '1 to 7, repeating' 
;

COMMENT ON COLUMN TIMES.DAY_NUMBER_IN_MONTH IS '1 to 31, repeating' 
;

COMMENT ON COLUMN TIMES.CALENDAR_WEEK_NUMBER IS '1 to 53, repeating' 
;

COMMENT ON COLUMN TIMES.FISCAL_WEEK_NUMBER IS '1 to 53, repeating' 
;

COMMENT ON COLUMN TIMES.WEEK_ENDING_DAY IS 'date of last day in week, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.CALENDAR_MONTH_NUMBER IS '1 to 12, repeating' 
;

COMMENT ON COLUMN TIMES.FISCAL_MONTH_NUMBER IS '1 to 12, repeating' 
;

COMMENT ON COLUMN TIMES.CALENDAR_MONTH_DESC IS 'e.g. 1998-01, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.FISCAL_MONTH_DESC IS 'e.g. 1998-01, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.DAYS_IN_CAL_MONTH IS 'e.g. 28,31, repeating' 
;

COMMENT ON COLUMN TIMES.DAYS_IN_FIS_MONTH IS 'e.g. 25,32, repeating' 
;

COMMENT ON COLUMN TIMES.END_OF_CAL_MONTH IS 'last day of calendar month' 
;

COMMENT ON COLUMN TIMES.END_OF_FIS_MONTH IS 'last day of fiscal month' 
;

COMMENT ON COLUMN TIMES.CALENDAR_MONTH_NAME IS 'January to December, repeating' 
;

COMMENT ON COLUMN TIMES.FISCAL_MONTH_NAME IS 'January to December, repeating' 
;

COMMENT ON COLUMN TIMES.CALENDAR_QUARTER_DESC IS 'e.g. 1998-Q1, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.FISCAL_QUARTER_DESC IS 'e.g. 1999-Q3, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.DAYS_IN_CAL_QUARTER IS 'e.g. 88,90, repeating' 
;

COMMENT ON COLUMN TIMES.DAYS_IN_FIS_QUARTER IS 'e.g. 88,90, repeating' 
;

COMMENT ON COLUMN TIMES.END_OF_CAL_QUARTER IS 'last day of calendar quarter' 
;

COMMENT ON COLUMN TIMES.END_OF_FIS_QUARTER IS 'last day of fiscal quarter' 
;

COMMENT ON COLUMN TIMES.CALENDAR_QUARTER_NUMBER IS '1 to 4, repeating' 
;

COMMENT ON COLUMN TIMES.FISCAL_QUARTER_NUMBER IS '1 to 4, repeating' 
;

COMMENT ON COLUMN TIMES.CALENDAR_YEAR IS 'e.g. 1999, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.FISCAL_YEAR IS 'e.g. 1999, CORRECT ORDER' 
;

COMMENT ON COLUMN TIMES.DAYS_IN_CAL_YEAR IS '365,366 repeating' 
;

COMMENT ON COLUMN TIMES.DAYS_IN_FIS_YEAR IS 'e.g. 355,364, repeating' 
;

COMMENT ON COLUMN TIMES.END_OF_CAL_YEAR IS 'last day of cal year' 
;

COMMENT ON COLUMN TIMES.END_OF_FIS_YEAR IS 'last day of fiscal year' 
;

ALTER TABLE TIMES 
    ADD CONSTRAINT TIMES_PK PRIMARY KEY ( TIME_ID ) NOVALIDATE ;



CREATE MATERIALIZED VIEW CAL_MONTH_SALES_MV 
    AS SELECT   t.calendar_month_desc 
 ,        sum(s.amount_sold) AS dollars 
 FROM     sales s 
 ,        times t 
 WHERE    s.time_id = t.time_id 
 GROUP BY t.calendar_month_desc 
;



CREATE MATERIALIZED VIEW FWEEK_PSCAT_SALES_MV 
    AS SELECT   t.week_ending_day 
 ,        p.prod_subcategory 
 ,        sum(s.amount_sold) AS dollars 
 ,        s.channel_id 
 ,        s.promo_id 
 FROM     sales s 
 ,        times t 
 ,        products p 
 WHERE    s.time_id = t.time_id 
 AND      s.prod_id = p.prod_id 
 GROUP BY t.week_ending_day 
 ,        p.prod_subcategory 
 ,        s.channel_id 
 ,        s.promo_id 
;


CREATE BITMAP INDEX FW_PSC_S_MV_SUBCAT_BIX ON FWEEK_PSCAT_SALES_MV 
    ( 
     prod_subcategory ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX FW_PSC_S_MV_CHAN_BIX ON FWEEK_PSCAT_SALES_MV 
    ( 
     channel_id ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX FW_PSC_S_MV_PROMO_BIX ON FWEEK_PSCAT_SALES_MV 
    ( 
     promo_id ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

CREATE BITMAP INDEX FW_PSC_S_MV_WD_BIX ON FWEEK_PSCAT_SALES_MV 
    ( 
     week_ending_day ASC 
    ) 
       PCTFREE 10 
    MAXTRANS 255 
    STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT ) 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;



ALTER TABLE COSTS 
    ADD CONSTRAINT COSTS_CHANNEL_FK FOREIGN KEY 
    ( 
     CHANNEL_ID
    ) 
    REFERENCES CHANNELS 
    ( 
     CHANNEL_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE COSTS 
    ADD CONSTRAINT COSTS_PRODUCT_FK FOREIGN KEY 
    ( 
     PROD_ID
    ) 
    REFERENCES PRODUCTS 
    ( 
     PROD_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE COSTS 
    ADD CONSTRAINT COSTS_PROMO_FK FOREIGN KEY 
    ( 
     PROMO_ID
    ) 
    REFERENCES PROMOTIONS 
    ( 
     PROMO_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE COSTS 
    ADD CONSTRAINT COSTS_TIME_FK FOREIGN KEY 
    ( 
     TIME_ID
    ) 
    REFERENCES TIMES 
    ( 
     TIME_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE CUSTOMERS 
    ADD CONSTRAINT CUSTOMERS_COUNTRY_FK FOREIGN KEY 
    ( 
     COUNTRY_ID
    ) 
    REFERENCES COUNTRIES 
    ( 
     COUNTRY_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE SALES 
    ADD CONSTRAINT SALES_CHANNEL_FK FOREIGN KEY 
    ( 
     CHANNEL_ID
    ) 
    REFERENCES CHANNELS 
    ( 
     CHANNEL_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE SALES 
    ADD CONSTRAINT SALES_CUSTOMER_FK FOREIGN KEY 
    ( 
     CUST_ID
    ) 
    REFERENCES CUSTOMERS 
    ( 
     CUST_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE SALES 
    ADD CONSTRAINT SALES_PRODUCT_FK FOREIGN KEY 
    ( 
     PROD_ID
    ) 
    REFERENCES PRODUCTS 
    ( 
     PROD_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE SALES 
    ADD CONSTRAINT SALES_PROMO_FK FOREIGN KEY 
    ( 
     PROMO_ID
    ) 
    REFERENCES PROMOTIONS 
    ( 
     PROMO_ID
    ) 
    NOT DEFERRABLE 
;



ALTER TABLE SALES 
    ADD CONSTRAINT SALES_TIME_FK FOREIGN KEY 
    ( 
     TIME_ID
    ) 
    REFERENCES TIMES 
    ( 
     TIME_ID
    ) 
    NOT DEFERRABLE 
;


CREATE OR REPLACE VIEW PROFITS AS 
 SELECT 
 s.channel_id,
 s.cust_id,
 s.prod_id,
 s.promo_id,
 s.time_id,
 c.unit_cost,
 c.unit_price,
 s.amount_sold,
 s.quantity_sold,
 c.unit_cost * s.quantity_sold TOTAL_COST 
 FROM 
 costs c,sales s 
 WHERE c.prod_id = s.prod_id 
 AND c.time_id = s.time_id 
 AND c.channel_id = s.channel_id 
 AND c.promo_id = s.promo_id ;




CREATE OR REPLACE VIEW QUANTITIES_SOLD AS 
 SELECT "COUNTRIES"."COUNTRY_NAME","PRODUCTS"."PROD_NAME","TIMES"."CALENDAR_YEAR","CUSTOMERS"."CUST_LAST_NAME","SALES"."QUANTITY_SOLD" FROM "COUNTRIES","PRODUCTS","TIMES","CUSTOMERS","SALES" WHERE ( "CUSTOMERS"."COUNTRY_ID" = "COUNTRIES"."COUNTRY_ID" ) AND ( "SALES"."CUST_ID" = "CUSTOMERS"."CUST_ID" ) AND ( "SALES"."PROD_ID" = "PRODUCTS"."PROD_ID" ) AND ( "SALES"."TIME_ID" = "TIMES"."TIME_ID" ) ;




CREATE OR REPLACE VIEW SALES_VIEW AS 
 SELECT country_name country,
 prod_name prod,
 calendar_year year,
 SUM(amount_sold) sale,
 COUNT(amount_sold) cnt 
 FROM sales,times,customers,countries,products 
 WHERE sales.time_id = times.time_id AND 
 sales.prod_id = products.prod_id AND 
 sales.cust_id = customers.cust_id AND 
 customers.country_id = countries.country_id AND 
 ( customers.country_id = 52779 OR 
 customers.country_id = 52776 ) AND 
 ( prod_name = 'Standard Mouse' OR 
 prod_name = 'Mouse Pad' ) 
 GROUP BY country_name,prod_name,calendar_year ;




CREATE DIMENSION CHANNELS_DIM 
    LEVEL CHANNEL_TOTAL IS CHANNELS.CHANNEL_TOTAL_ID 
    LEVEL CHANNEL_CLASS IS CHANNELS.CHANNEL_CLASS_ID 
    LEVEL CHANNEL IS CHANNELS.CHANNEL_ID 
    HIERARCHY CHANNEL_ROLLUP ( 
        CHANNEL  CHILD OF  
        CHANNEL_CLASS  CHILD OF  
        CHANNEL_TOTAL 
    ) 
    ATTRIBUTE Attr1 LEVEL CHANNEL_TOTAL DETERMINES 
        CHANNEL_TOTAL 
    ATTRIBUTE Attr2 LEVEL CHANNEL_CLASS DETERMINES 
        CHANNEL_CLASS 
    ATTRIBUTE Attr3 LEVEL CHANNEL DETERMINES 
        CHANNEL_DESC 
;


CREATE DIMENSION CUSTOMERS_DIM 
    LEVEL STATE IS CUSTOMERS.CUST_STATE_PROVINCE_ID 
    LEVEL COUNTRY IS COUNTRIES.COUNTRY_ID 
    LEVEL GEOG_TOTAL IS COUNTRIES.COUNTRY_TOTAL_ID 
    LEVEL SUBREGION IS COUNTRIES.COUNTRY_SUBREGION_ID 
    LEVEL CUST_TOTAL IS CUSTOMERS.CUST_TOTAL_ID 
    LEVEL REGION IS COUNTRIES.COUNTRY_REGION_ID 
    LEVEL CITY IS CUSTOMERS.CUST_CITY_ID 
    LEVEL CUSTOMER IS CUSTOMERS.CUST_ID 
    HIERARCHY CUST_ROLLUP ( 
        CUSTOMER  CHILD OF  
        CITY  CHILD OF  
        STATE  CHILD OF  
        CUST_TOTAL 
    ) 
    HIERARCHY GEOG_ROLLUP ( 
        CUSTOMER  CHILD OF  
        CITY  CHILD OF  
        STATE  CHILD OF  
        COUNTRY  CHILD OF  
        SUBREGION  CHILD OF  
        REGION  CHILD OF  
        GEOG_TOTAL 
        JOIN KEY CUSTOMERS.COUNTRY_ID REFERENCES COUNTRY 
    ) 
    ATTRIBUTE Attr4 LEVEL STATE DETERMINES 
        CUST_STATE_PROVINCE 
    ATTRIBUTE Attr5 LEVEL COUNTRY DETERMINES ( 
        COUNTRY_NAME , 
        COUNTRY_ISO_CODE 
    ) 
    ATTRIBUTE Attr6 LEVEL GEOG_TOTAL DETERMINES 
        COUNTRY_TOTAL 
    ATTRIBUTE Attr7 LEVEL SUBREGION DETERMINES 
        COUNTRY_SUBREGION 
    ATTRIBUTE Attr8 LEVEL CUST_TOTAL DETERMINES 
        CUST_TOTAL 
    ATTRIBUTE Attr9 LEVEL REGION DETERMINES 
        COUNTRY_REGION 
    ATTRIBUTE Attr10 LEVEL CITY DETERMINES 
        CUST_CITY 
    ATTRIBUTE Attr11 LEVEL CUSTOMER DETERMINES ( 
        CUST_FIRST_NAME , 
        CUST_EMAIL , 
        CUST_MAIN_PHONE_NUMBER , 
        CUST_POSTAL_CODE , 
        CUST_STREET_ADDRESS , 
        CUST_CREDIT_LIMIT , 
        CUST_INCOME_LEVEL , 
        CUST_YEAR_OF_BIRTH , 
        CUST_MARITAL_STATUS , 
        CUST_GENDER , 
        CUST_LAST_NAME 
    ) 
;


CREATE DIMENSION PRODUCTS_DIM 
    LEVEL CATEGORY IS PRODUCTS.PROD_CATEGORY_ID 
    LEVEL SUBCATEGORY IS PRODUCTS.PROD_SUBCATEGORY_ID 
    LEVEL PRODUCT IS PRODUCTS.PROD_ID 
    LEVEL PROD_TOTAL IS PRODUCTS.PROD_TOTAL_ID 
    HIERARCHY PROD_ROLLUP ( 
        PRODUCT  CHILD OF  
        SUBCATEGORY  CHILD OF  
        CATEGORY  CHILD OF  
        PROD_TOTAL 
    ) 
    ATTRIBUTE Attr12 LEVEL CATEGORY DETERMINES ( 
        PROD_CATEGORY , 
        PROD_CATEGORY_DESC 
    ) 
    ATTRIBUTE Attr13 LEVEL SUBCATEGORY DETERMINES ( 
        PROD_SUBCATEGORY , 
        PROD_SUBCATEGORY_DESC 
    ) 
    ATTRIBUTE Attr14 LEVEL PRODUCT DETERMINES ( 
        PROD_NAME , 
        PROD_MIN_PRICE , 
        PROD_LIST_PRICE , 
        PROD_STATUS , 
        PROD_PACK_SIZE , 
        PROD_UNIT_OF_MEASURE , 
        PROD_WEIGHT_CLASS , 
        PROD_DESC 
    ) 
    ATTRIBUTE Attr15 LEVEL PROD_TOTAL DETERMINES 
        PROD_TOTAL 

;


CREATE DIMENSION PROMOTIONS_DIM 
    LEVEL CATEGORY IS PROMOTIONS.PROMO_CATEGORY_ID 
    LEVEL PROMO IS PROMOTIONS.PROMO_ID 
    LEVEL PROMO_TOTAL IS PROMOTIONS.PROMO_TOTAL_ID 
    LEVEL SUBCATEGORY IS PROMOTIONS.PROMO_SUBCATEGORY_ID 
    HIERARCHY PROMO_ROLLUP ( 
        PROMO  CHILD OF  
        SUBCATEGORY  CHILD OF  
        CATEGORY  CHILD OF  
        PROMO_TOTAL 
    ) 
    ATTRIBUTE Attr17 LEVEL PROMO DETERMINES ( 
        PROMO_NAME , 
        PROMO_END_DATE , 
        PROMO_BEGIN_DATE , 
        PROMO_COST 
    ) 
    ATTRIBUTE Attr18 LEVEL PROMO_TOTAL DETERMINES 
        PROMO_TOTAL 
;


CREATE DIMENSION TIMES_DIM 
    LEVEL QUARTER IS TIMES.CALENDAR_QUARTER_ID 
    LEVEL DAY IS TIMES.TIME_ID 
    LEVEL MONTH IS TIMES.CALENDAR_MONTH_ID 
    LEVEL FIS_YEAR IS TIMES.FISCAL_YEAR_ID 
    LEVEL YEAR IS TIMES.CALENDAR_YEAR_ID 
    LEVEL FIS_QUARTER IS TIMES.FISCAL_QUARTER_ID 
    LEVEL FIS_WEEK IS TIMES.WEEK_ENDING_DAY_ID 
    LEVEL FIS_MONTH IS TIMES.FISCAL_MONTH_ID 
    HIERARCHY CAL_ROLLUP ( 
        DAY  CHILD OF  
        MONTH  CHILD OF  
        QUARTER  CHILD OF  
        YEAR 
    ) 
    HIERARCHY FIS_ROLLUP ( 
        DAY  CHILD OF  
        FIS_WEEK  CHILD OF  
        FIS_MONTH  CHILD OF  
        FIS_QUARTER  CHILD OF  
        FIS_YEAR 
    ) 
    ATTRIBUTE Attr20 LEVEL QUARTER DETERMINES ( 
        CALENDAR_QUARTER_DESC , 
        END_OF_CAL_QUARTER , 
        DAYS_IN_CAL_QUARTER , 
        CALENDAR_QUARTER_NUMBER 
    ) 
    ATTRIBUTE Attr21 LEVEL DAY DETERMINES ( 
        DAY_NUMBER_IN_WEEK , 
        CALENDAR_WEEK_NUMBER , 
        DAY_NUMBER_IN_MONTH , 
        DAY_NAME 
    ) 
    ATTRIBUTE Attr22 LEVEL MONTH DETERMINES ( 
        CALENDAR_MONTH_DESC , 
        END_OF_CAL_MONTH , 
        DAYS_IN_CAL_MONTH , 
        CALENDAR_MONTH_NAME , 
        CALENDAR_MONTH_NUMBER 
    ) 
    ATTRIBUTE Attr23 LEVEL FIS_YEAR DETERMINES ( 
        FISCAL_YEAR , 
        END_OF_FIS_YEAR , 
        DAYS_IN_FIS_YEAR 
    ) 
    ATTRIBUTE Attr24 LEVEL YEAR DETERMINES ( 
        CALENDAR_YEAR , 
        END_OF_CAL_YEAR , 
        DAYS_IN_CAL_YEAR 
    ) 
    ATTRIBUTE Attr25 LEVEL FIS_QUARTER DETERMINES ( 
        FISCAL_QUARTER_DESC , 
        END_OF_FIS_QUARTER , 
        DAYS_IN_FIS_QUARTER , 
        FISCAL_QUARTER_NUMBER 
    ) 
    ATTRIBUTE Attr26 LEVEL FIS_WEEK DETERMINES ( 
        WEEK_ENDING_DAY , 
        FISCAL_WEEK_NUMBER 
    ) 
    ATTRIBUTE Attr27 LEVEL FIS_MONTH DETERMINES ( 
        FISCAL_MONTH_DESC , 
        END_OF_FIS_MONTH , 
        DAYS_IN_FIS_MONTH , 
        FISCAL_MONTH_NAME , 
        FISCAL_MONTH_NUMBER 
    ) 
;






















-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            15
-- CREATE INDEX                            19
-- ALTER TABLE                             17
-- CREATE VIEW                              3
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- CREATE STRUCTURED TYPE                   0
-- CREATE COLLECTION TYPE                   0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         5
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 2
-- CREATE SYNONYM                           0
-- CREATE - CREATE USER                              0
-- 
-- DROP - DROP DATABASE                            0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0