--SYS·Î Á¢¼Ó
ALTER SYSTEM FLUSH SHARED_POOL;

DROP USER TLO CASCADE;

CREATE USER TLO
  IDENTIFIED BY "transformer"
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;  
  GRANT DBA TO TLO;
  GRANT EXECUTE ANY PROGRAM TO TLO;
  GRANT SELECT ANY DICTIONARY TO TLO;
  GRANT SELECT_CATALOG_ROLE TO TLO;  
  GRANT EXP_FULL_DATABASE TO TLO;
  GRANT IMP_FULL_DATABASE TO TLO;

CREATE TABLE TLO.REGION
(
  REGION_ID    NUMBER CONSTRAINT REGION_ID_NN   NOT NULL,
  REGION_NAME  VARCHAR2(25 BYTE)
)
;


CREATE TABLE TLO.COUNTRY
(
  COUNTRY_ID    CHAR(2 BYTE) CONSTRAINT COUNTRY_ID_NN NOT NULL,
  COUNTRY_NAME  VARCHAR2(40 BYTE),
  REGION_ID     NUMBER, 
  CONSTRAINT COUNTRY_C_ID_PK
 PRIMARY KEY
 (COUNTRY_ID)
)
ORGANIZATION INDEX
;


CREATE TABLE TLO.LOCATION
(
  LOCATION_ID     NUMBER(4),
  STREET_ADDRESS  VARCHAR2(40 BYTE),
  POSTAL_CODE     VARCHAR2(12 BYTE),
  CITY            VARCHAR2(30 BYTE) CONSTRAINT LOC_CITY_NN NOT NULL,
  STATE_PROVINCE  VARCHAR2(25 BYTE),
  COUNTRY_ID      CHAR(2 BYTE)
)
;


CREATE TABLE TLO.DEPARTMENT
(
  DEPARTMENT_ID    NUMBER(4),
  DEPARTMENT_NAME  VARCHAR2(30 BYTE) CONSTRAINT DEPT_NAME_NN NOT NULL,
  MANAGER_ID       NUMBER(6),
  LOCATION_ID      NUMBER(4)
)
;


CREATE TABLE TLO.JOB
(
  JOB_ID      VARCHAR2(10 BYTE),
  JOB_TITLE   VARCHAR2(35 BYTE) CONSTRAINT JOB_TITLE_NN NOT NULL,
  MIN_SALARY  NUMBER(6),
  MAX_SALARY  NUMBER(6)
)
;


CREATE TABLE TLO.EMPLOYEE
(
  EMPLOYEE_ID     NUMBER(6),
  FIRST_NAME      VARCHAR2(20 BYTE),
  LAST_NAME       VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
  EMAIL           VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
  PHONE_NUMBER    VARCHAR2(20 BYTE),
  HIRE_DATE       DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
  JOB_ID          VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
  SALARY          NUMBER(8,2),
  COMMISSION_PCT  NUMBER(2,2),
  MANAGER_ID      NUMBER(6),
  DEPARTMENT_ID   NUMBER(4)
)
;


CREATE TABLE TLO.JOB_HISTORY
(
  EMPLOYEE_ID    NUMBER(6) CONSTRAINT JHIST_EMPLOYEE_NN NOT NULL,
  START_DATE     DATE CONSTRAINT JHIST_START_DATE_NN NOT NULL,
  END_DATE       DATE CONSTRAINT JHIST_END_DATE_NN NOT NULL,
  JOB_ID         VARCHAR2(10 BYTE) CONSTRAINT JHIST_JOB_NN NOT NULL,
  DEPARTMENT_ID  NUMBER(4)
)
;


CREATE INDEX TLO.JHIST_DEPARTMENT_IX ON TLO.JOB_HISTORY
(DEPARTMENT_ID)
;


CREATE INDEX TLO.JHIST_EMPLOYEE_IX ON TLO.JOB_HISTORY
(EMPLOYEE_ID)
;


CREATE INDEX TLO.JHIST_JOB_IX ON TLO.JOB_HISTORY
(JOB_ID)
;


CREATE UNIQUE INDEX TLO.JHIST_EMP_ID_ST_DATE_PK ON TLO.JOB_HISTORY
(EMPLOYEE_ID, START_DATE)
;


CREATE INDEX TLO.EMP_NAME_IX ON TLO.EMPLOYEE
(LAST_NAME, FIRST_NAME)
;


CREATE INDEX TLO.EMP_MANAGER_IX ON TLO.EMPLOYEE
(MANAGER_ID)
;


CREATE INDEX TLO.EMP_JOB_IX ON TLO.EMPLOYEE
(JOB_ID)
;


CREATE INDEX TLO.EMP_DEPARTMENT_IX ON TLO.EMPLOYEE
(DEPARTMENT_ID)
;


CREATE UNIQUE INDEX TLO.EMP_EMP_ID_PK ON TLO.EMPLOYEE
(EMPLOYEE_ID)
;


CREATE UNIQUE INDEX TLO.EMP_EMAIL_UK ON TLO.EMPLOYEE
(EMAIL)
;


CREATE INDEX TLO.DEPT_LOCATION_IX ON TLO.DEPARTMENT
(LOCATION_ID)
;


CREATE UNIQUE INDEX TLO.DEPT_ID_PK ON TLO.DEPARTMENT
(DEPARTMENT_ID)
;


CREATE INDEX TLO.LOC_COUNTRY_IX ON TLO.LOCATION
(COUNTRY_ID)
;


CREATE INDEX TLO.LOC_STATE_PROVINCE_IX ON TLO.LOCATION
(STATE_PROVINCE)
;


CREATE INDEX TLO.LOC_CITY_IX ON TLO.LOCATION
(CITY)
;


CREATE UNIQUE INDEX TLO.LOC_ID_PK ON TLO.LOCATION
(LOCATION_ID)
;


CREATE UNIQUE INDEX TLO.REG_ID_PK ON TLO.REGION
(REGION_ID)
;


ALTER TABLE TLO.REGION ADD (
  CONSTRAINT REG_ID_PK
 PRIMARY KEY
 (REGION_ID));

ALTER TABLE TLO.LOCATION ADD (
  CONSTRAINT LOC_ID_PK
 PRIMARY KEY
 (LOCATION_ID));

ALTER TABLE TLO.DEPARTMENT ADD (
  CONSTRAINT DEPT_ID_PK
 PRIMARY KEY
 (DEPARTMENT_ID));

ALTER TABLE TLO.JOB ADD (
  CONSTRAINT JOB_ID_PK
 PRIMARY KEY
 (JOB_ID));

ALTER TABLE TLO.EMPLOYEE ADD (
   CONSTRAINT EMP_EMP_ID_PK
 PRIMARY KEY
 (EMPLOYEE_ID));

ALTER TABLE TLO.JOB_HISTORY ADD (
   CONSTRAINT JHIST_EMP_ID_ST_DATE_PK
 PRIMARY KEY
 (EMPLOYEE_ID, START_DATE));


CREATE TABLE TLO.SALES
(
  PROD_ID        NUMBER                         NOT NULL,
  CUST_ID        NUMBER                         NOT NULL,
  TIME_ID        DATE                           NOT NULL,
  CHANNEL_ID     NUMBER                         NOT NULL,
  PROMO_ID       NUMBER                         NOT NULL,
  QUANTITY_SOLD  NUMBER(10,2)                   NOT NULL,
  AMOUNT_SOLD    NUMBER(10,2)                   NOT NULL
)
PARTITION BY RANGE (TIME_ID)
(  
  PARTITION SALES_1995    VALUES LESS THAN (TO_DATE('1996-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_1996    VALUES LESS THAN (TO_DATE('1997-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_H1_1997 VALUES LESS THAN (TO_DATE('1997-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_H2_1997 VALUES LESS THAN (TO_DATE('1998-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('1998-04-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('1998-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('1998-10-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('1999-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('1999-04-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('1999-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('1999-10-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('2000-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('2000-04-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('2000-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('2000-10-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q4_2000 VALUES LESS THAN (TO_DATE('2001-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q1_2001 VALUES LESS THAN (TO_DATE('2001-04-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q2_2001 VALUES LESS THAN (TO_DATE('2001-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q3_2001 VALUES LESS THAN (TO_DATE('2001-10-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q4_2001 VALUES LESS THAN (TO_DATE('2002-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q1_2002 VALUES LESS THAN (TO_DATE('2002-04-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q2_2002 VALUES LESS THAN (TO_DATE('2002-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q3_2002 VALUES LESS THAN (TO_DATE('2002-10-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q4_2002 VALUES LESS THAN (TO_DATE('2003-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q1_2003 VALUES LESS THAN (TO_DATE('2003-04-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q2_2003 VALUES LESS THAN (TO_DATE('2003-07-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q3_2003 VALUES LESS THAN (TO_DATE('2003-10-0100:00:00', 'YYYY-MM-DDHH24:MI:SS')),  
  PARTITION SALES_Q4_2003 VALUES LESS THAN (TO_DATE('2004-01-0100:00:00', 'YYYY-MM-DDHH24:MI:SS'))
);


CREATE BITMAP INDEX TLO.SALES_CHANNEL_BIX ON TLO.SALES
(CHANNEL_ID)
  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_Q2_2002,  
  PARTITION SALES_Q3_2002,  
  PARTITION SALES_Q4_2002,  
  PARTITION SALES_Q1_2003,  
  PARTITION SALES_Q2_2003,  
  PARTITION SALES_Q3_2003,  
  PARTITION SALES_Q4_2003
);


CREATE BITMAP INDEX TLO.SALES_CUST_BIX ON TLO.SALES
(CUST_ID)
  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_Q2_2002,  
  PARTITION SALES_Q3_2002,  
  PARTITION SALES_Q4_2002,  
  PARTITION SALES_Q1_2003,  
  PARTITION SALES_Q2_2003,  
  PARTITION SALES_Q3_2003,  
  PARTITION SALES_Q4_2003
);


CREATE BITMAP INDEX TLO.SALES_PROD_BIX ON TLO.SALES
(PROD_ID)
  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_Q2_2002,  
  PARTITION SALES_Q3_2002,  
  PARTITION SALES_Q4_2002,  
  PARTITION SALES_Q1_2003,  
  PARTITION SALES_Q2_2003,  
  PARTITION SALES_Q3_2003,  
  PARTITION SALES_Q4_2003
);


CREATE BITMAP INDEX TLO.SALES_PROMO_BIX ON TLO.SALES
(PROMO_ID)
  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_Q2_2002,  
  PARTITION SALES_Q3_2002,  
  PARTITION SALES_Q4_2002,  
  PARTITION SALES_Q1_2003,  
  PARTITION SALES_Q2_2003,  
  PARTITION SALES_Q3_2003,  
  PARTITION SALES_Q4_2003
);


CREATE BITMAP INDEX TLO.SALES_TIME_BIX ON TLO.SALES
(TIME_ID)
  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_Q2_2002,  
  PARTITION SALES_Q3_2002,  
  PARTITION SALES_Q4_2002,  
  PARTITION SALES_Q1_2003,  
  PARTITION SALES_Q2_2003,  
  PARTITION SALES_Q3_2003,  
  PARTITION SALES_Q4_2003
);


CREATE TABLE TLO.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
)
;


CREATE TABLE TLO.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)
)
;


CREATE TABLE TLO.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
)
;


CREATE TABLE TLO.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
)
;


CREATE TABLE TLO.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)
)
;


CREATE TABLE TLO.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)
)
;


CREATE BITMAP INDEX TLO.CUSTOMERS_YOB_BIX ON TLO.CUSTOMERS
(CUST_YEAR_OF_BIRTH)
;


CREATE BITMAP INDEX TLO.CUSTOMERS_MARITAL_BIX ON TLO.CUSTOMERS
(CUST_MARITAL_STATUS)
;


CREATE BITMAP INDEX TLO.CUSTOMERS_GENDER_BIX ON TLO.CUSTOMERS
(CUST_GENDER)
;


CREATE BITMAP INDEX TLO.PRODUCTS_PROD_STATUS_BIX ON TLO.PRODUCTS
(PROD_STATUS)
;


CREATE UNIQUE INDEX TLO.COUNTRY_PK ON TLO.COUNTRIES
(COUNTRY_ID)
;


CREATE UNIQUE INDEX TLO.CUSTOMERS_PK ON TLO.CUSTOMERS
(CUST_ID)
;


CREATE UNIQUE INDEX TLO.PROMO_PK ON TLO.PROMOTIONS
(PROMO_ID)
;


CREATE UNIQUE INDEX TLO.CHANNELS_PK ON TLO.CHANNELS
(CHANNEL_ID)
;


CREATE INDEX TLO.PRODUCTS_PROD_CAT_IX ON TLO.PRODUCTS
(PROD_CATEGORY)
;


CREATE INDEX TLO.PRODUCTS_PROD_SUBCAT_IX ON TLO.PRODUCTS
(PROD_SUBCATEGORY)
;


CREATE UNIQUE INDEX TLO.PRODUCTS_PK ON TLO.PRODUCTS
(PROD_ID)
;


CREATE UNIQUE INDEX TLO.TIMES_PK ON TLO.TIMES
(TIME_ID)
;


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

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

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

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

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

ALTER TABLE TLO.COUNTRIES ADD (
  CONSTRAINT COUNTRY_PK
 PRIMARY KEY
 (COUNTRY_ID));
 
 
INSERT INTO TLO.REGION SELECT * FROM HR.REGIONS ; 
COMMIT;
INSERT INTO TLO.LOCATION SELECT * FROM HR.LOCATIONS ; 
COMMIT;
INSERT INTO TLO.DEPARTMENT SELECT * FROM HR.DEPARTMENTS ; 
COMMIT;
INSERT INTO TLO.JOB SELECT * FROM HR.JOBS ; 
COMMIT;
INSERT INTO TLO.EMPLOYEE SELECT * FROM HR.EMPLOYEES ; 
COMMIT;
INSERT INTO TLO.JOB_HISTORY SELECT * FROM HR.JOB_HISTORY ; 
COMMIT;
INSERT INTO TLO.TIMES SELECT * FROM SH.TIMES ; 
COMMIT;
INSERT INTO TLO.PRODUCTS SELECT * FROM SH.PRODUCTS ; 
COMMIT;
INSERT INTO TLO.CHANNELS SELECT * FROM SH.CHANNELS ; 
COMMIT;
INSERT INTO TLO.PROMOTIONS SELECT * FROM SH.PROMOTIONS ; 
COMMIT;
INSERT INTO TLO.CUSTOMERS SELECT * FROM SH.CUSTOMERS ; 
COMMIT;
INSERT INTO TLO.COUNTRIES SELECT * FROM SH.COUNTRIES ; 
COMMIT;
INSERT INTO TLO.COUNTRY SELECT * FROM HR.COUNTRIES ; 
COMMIT;
INSERT INTO TLO.SALES SELECT * FROM SH.SALES ; 
COMMIT;

exec dbms_stats.gather_schema_stats('TLO',  estimate_percent=>100, method_opt => 'for all indexed columns size 254', cascade=>true);

 

--ALTER TABLE TLO.SALES ADD (
--  CONSTRAINT SALES_PROMO_FK 
-- FOREIGN KEY (PROMO_ID) 
-- REFERENCES TLO.PROMOTIONS (PROMO_ID));
--
--ALTER TABLE TLO.SALES ADD (
--  CONSTRAINT SALES_CUSTOMER_FK 
-- FOREIGN KEY (CUST_ID) 
-- REFERENCES TLO.CUSTOMERS (CUST_ID));
--
--ALTER TABLE TLO.SALES ADD (
--  CONSTRAINT SALES_PRODUCT_FK 
-- FOREIGN KEY (PROD_ID) 
-- REFERENCES TLO.PRODUCTS (PROD_ID));
--
--ALTER TABLE TLO.SALES ADD (
--  CONSTRAINT SALES_TIME_FK 
-- FOREIGN KEY (TIME_ID) 
-- REFERENCES TLO.TIMES (TIME_ID));
--
--ALTER TABLE TLO.SALES ADD (
--  CONSTRAINT SALES_CHANNEL_FK 
-- FOREIGN KEY (CHANNEL_ID) 
-- REFERENCES TLO.CHANNELS (CHANNEL_ID));