Appendix A

connect system/manager

-- Temporary Tablespace
CREATE TEMPORARY TABLESPACE easy_temp
TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\easy_temp.f'
SIZE 10m REUSE AUTOEXTEND ON NEXT 16k ;

-- Tablespace to store Materialized Views
CREATE TABLESPACE mview
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\easy_mview.f'
SIZE 6m REUSE AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- Tablespace for Dimensions
CREATE TABLESPACE easy_dim
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\dimensions.f'
SIZE 5m REUSE AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- Tablespace for the INDEXES
CREATE TABLESPACE easy_idx
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\index.f'
SIZE 5m REUSE AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- Default Tablespace
CREATE TABLESPACE easydw_default
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\
easydw_default.f'
SIZE 5m REUSE AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- create the 3 month tablespaces for the fact partitions
CREATE TABLESPACE purchases_jan2003
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\
PURCHASESJAN2003.f'
SIZE 5m REUSE AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- create the 3 month tablespaces for the fact indexes
CREATE TABLESPACE purchases_jan2003_idx
datafile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\
PURCHASESJAN2003_IDX.f'
SIZE 3m REUSE AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- create a user called EASYDW
-- this will be the schema where the objects will reside
connect system/manager

CREATE USER easydw IDENTIFIED BY easydw
DEFAULT TABLESPACE easydw_default
TEMPORARY TABLESPACE temp
PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT unlimited tablespace TO easydw ;
GRANT dba TO easydw ;
GRANT create session TO easydw;

-- now create the tables
CONNECT easydw/easydw

-- CUSTOMER Dimension
CREATE TABLE easydw.customer
(customer_id varchar2(10),
 city varchar2(15),
 state varchar2(10),
 postal_code varchar2(10),
 gender varchar2(1),
 region varchar2(15),
 country varchar2(20),
 tax_rate number,
 occupation varchar2(15))
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

ALTER TABLE customer
ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id)
USING INDEX
PCTFREE 5
TABLESPACE indx
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

-- PRODUCT Dimension
CREATE TABLE easydw.product
(product_id varchar2(8),
 product_name varchar2(30),
 category varchar2(4),
 cost_price number (6,2)
 constraint cost_price_not_null NOT NULL,
 sell_price number (6,2)
 constraint sell_price_not_null NOT NULL,
 weight number (6,2),
 shipping_charge number (5,2)
 constraint shipping_charge_not_null NOT NULL,
 manufacturer varchar2(20),
 supplier varchar2(10))
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
ALTER TABLE product
ADD CONSTRAINT pk_product PRIMARY KEY (product_id)
USING INDEX
PCTFREE 5 TABLESPACE easy_idx
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

-- TIME Dimension
CREATE TABLE easydw.time
(time_key date,
 month number (6,0),
 month_name varchar2(10),
 quarter number (6,0),
 year number (4,0),
 day_number number (3,0),
 day_of_the_week varchar2(9),
 week_number number (2,0) )
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

ALTER TABLE time
ADD CONSTRAINT pk_time PRIMARY KEY (time_key)
USING INDEX
PCTFREE 5 TABLESPACE easy_idx
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

-- TODAYS_SPECIAL_OFFERS Dimension
CREATE TABLE easydw.todays_special_offers
(product_id varchar2(8),
 offer_date date,
 special_price number (6,2),
 offer_price number (6,2))
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

ALTER TABLE todays_special_offers
ADD CONSTRAINT pk_specials PRIMARY KEY
 (offer_date,product_id )
USING INDEX
PCTFREE 5 TABLESPACE easy_idx
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

-- Fact Table PURCHASES
CREATE TABLE easydw.purchases
(product_id varchar2(8)
  CONSTRAINT not_null_product_id NOT NULL
  CONSTRAINT fk_product_id
  REFERENCES product(product_id),
 time_key date
  CONSTRAINT not_null_time NOT NULL
  CONSTRAINT fk_time
  REFERENCES time(time_key),
 customer_id varchar2(10) 
  CONSTRAINT not_null_customer_id NOT NULL
  CONSTRAINT fk_customer_id
  REFERENCES customer(customer_id),
 ship_date date,
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1)
  CONSTRAINT special_offer
  CHECK (today_special_offer IN ('Y','N')) )
PARTITION BY RANGE (time_key )
(
 PARTITION purchases_jan2002
   VALUES LESS THAN (TO_DATE('01-02-2002', 'DD-MM-YYYY'))
   PCTFREE 0 PCTUSED 99
   STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
   TABLESPACE purchases_jan2002 ,
 PARTITION purchases_feb2002
   VALUES LESS THAN (TO_DATE('01-03-2002', 'DD-MM-YYYY'))
   PCTFREE 0 PCTUSED 99
   STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
   TABLESPACE purchases_feb2002 ,
 PARTITION purchases_mar2002
   VALUES LESS THAN (TO_DATE('01-04-2002', 'DD-MM-YYYY'))
   PCTFREE 0 PCTUSED 99
   STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
   TABLESPACE purchases_mar2002 );

-- Now create the indexes
-- Partition on the Time Key Local prefixed index
CREATE BITMAP INDEX easydw.purchase_time_index
  ON purchases (time_key ) LOCAL ;

CREATE BITMAP INDEX easydw.purchase_product_index
  ON purchases (product_id )
 LOCAL
 PCTFREE 5 TABLESPACE indx
 STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;

CREATE INDEX easydw.purchase_customer_index
  ON purchases (customer_id )
 LOCAL
 PCTFREE 5 TABLESPACE indx
 STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;

CREATE BITMAP INDEX easydw.purchase_special_index
  ON purchases (today_special_offer )
 LOCAL
 PCTFREE 5 TABLESPACE indx
 STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;


connect system/manager

-- Add privileges
GRANT SELECT ANY TABLE TO easydw;
GRANT EXECUTE ANY PROCEDURE TO easydw;

-- Add privileges for summary management
GRANT CREATE ANY DIMENSION TO easydw;
GRANT ALTER ANY DIMENSION TO easydw;
GRANT DROP ANY DIMENSION TO easydw;
GRANT CREATE ANY MATERIALIZED VIEW TO easydw;
GRANT ALTER ANY MATERIALIZED VIEW TO easydw;
GRANT DROP ANY MATERIALIZED VIEW TO easydw;
GRANT QUERY REWRITE TO easydw;
GRANT GLOBAL QUERY REWRITE TO easydw;


-- Now Analyze the Tables and Indexes
EXECUTE dbms_stats.gather_table_stats('EASYDW','CUSTOMER');
EXECUTE dbms_stats.gather_table_stats('EASYDW','TODAYS_SPECIAL_OFFERS');
EXECUTE dbms_stats.gather_table_stats('EASYDW','PRODUCT');
EXECUTE dbms_stats.gather_index_stats('EASYDW','PURCHASE_CUSTOMER_INDEX');