-- Database Creation
--
-- Easy Shopping Example

set echo on
connect system/manager@orcl1;

-- Create Tablespace for Undo and Temporary area and Rollback segments
drop tablespace undo including contents cascade constraints;
create tablespace undo
  datafile 'd:\ora815\oradata\oradata\orcl\easy_undo.f'
  size 8m reuse autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

drop tablespace temp including contents cascade constraints;
create tablespace temp
  datafile 'd:\ora815\oradata\orcl\easy_temp.f'
  size 10m reuse autoextend on
  default storage (initial 16k next 16k maxextents unlimited pctincrease 0)
temporary;

drop tablespace rbs including contents cascade constraints;
create tablespace rbs
  datafile 'd:\ora815\oradata\orcl\easy_rbs.f'
  size 4m reuse autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

-- create 2 rollback segments

alter rollback segment r1  offline ;
alter rollback segment r2  offline ;

drop rollback segment r1;
drop rollback segment r2 ;

create public rollback segment r1
 tablespace rbs
 storage (initial 64k next 64k maxextents unlimited);
alter rollback segment r1 online;

create public rollback segment r2
 tablespace rbs
 storage (initial 64k next 64k maxextents unlimited);
alter rollback segment r2 online;

-- Tablespace to store Summary data
drop tablespace summary including contents cascade constraints;
create tablespace summary
  datafile 'd:\ora815\oradata\orcl\easy_summ.f'
  size 6m reuse autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

-- Tablespace for DIMENSIONS
drop tablespace dimensions including contents cascade constraints;
create tablespace dimensions
  datafile 'd:\ora815\oradata\orcl\DIMENSIONS.D' size 5m reuse autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

-- create tablespace for the INDEXES
drop tablespace indx including contents cascade constraints;
create tablespace indx
  datafile 'd:\ora815\oradata\orcl\indx.f'
  size 5m reuse autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

-- create the default tablespace
drop tablespace easydw_default including contents cascade constraints;
create tablespace easydw_default
  datafile 'd:\ora815\oradata\orcl\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
drop tablespace purchases_jan99 including contents cascade constraints;
create tablespace purchases_jan99
  datafile 'd:\ora815\oradata\orcl\PURCHASESJAN99.f' size 5m reuse
autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

drop tablespace purchases_feb99 including contents cascade constraints;
create tablespace purchases_feb99
  datafile 'd:\ora815\oradata\orcl\PURCHASESFEB99.f' size 5m reuse
autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

drop tablespace purchases_mar99 including contents cascade constraints;
create tablespace purchases_mar99
  datafile 'd:\ora815\oradata\orcl\PURCHASESMAR99.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

drop tablespace purchases_jan99_idx including contents cascade constraints;
create tablespace purchases_jan99_idx
  datafile 'd:\ora815\oradata\orcl\PURCHASESJAN99_IDX.f' size 3M reuse
autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

drop tablespace purchases_feb99_idx including contents cascade constraints;
create tablespace purchases_feb99_idx
  datafile 'd:\ora815\oradata\orcl\PURCHASESFEB99_IDX.f' size 3M reuse
autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

drop tablespace purchases_mar99_idx including contents cascade constraints;
create tablespace purchases_mar99_idx
  datafile 'd:\ora815\oradata\orcl\PURCHASESMAR99_IDX.f' size 1M reuse
autoextend on
  default storage (initial 16k next 16k pctincrease 0 maxextents unlimited);

-- Change extents on system tablespace
ALTER TABLESPACE "SYSTEM"
       DEFAULT STORAGE (MAXEXTENTS UNLIMITED);

-- Give the system tablespace another file
ALTER TABLESPACE "SYSTEM"
   add datafile 'd:\ora815\oradata\orcl\system3.f' size 5m reuse autoextend
on;

ALTER DATABASE easydw
   ADD LOGFILE ( 'd:\ora815\oradata\orcl\redo.log' ) SIZE 20M REUSE;

-- create a user called EASYDW
--    this will be the schema where the objects will reside

drop user easydw cascade;
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 ;

-- 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 query rewrite to easydw;
grant global query rewrite to easydw;

disconnect;

-- now create the database
connect easydw/easydw@orcl1;

-- CUSTOMER Dimension
drop TABLE easydw.customer CASCADE CONSTRAINTS;

CREATE TABLE easydw.customer
(customer_id            varchar2(10),
 town                           varchar2(10),
 county                 varchar2(10),
 postal_code            varchar2(10),
 dob                            date,
 country                        varchar2(20),
 occupation                     varchar2(10))
pctfree 0 pctused 99
tablespace dimensions
storage (initial 16k next 16k pctincrease 0) ;

CREATE INDEX  easydw.customer_pk_index ON customer
  (customer_id)
  pctfree 5
  tablespace indx
  storage (initial 16k next 16k pctincrease 0) ;

ALTER TABLE customer
  ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id);

-- PRODUCT Dimension
drop TABLE easydw.product CASCADE CONSTRAINTS;

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 (4,2),
 shipping_charge                number (5,2)
   constraint shipping_charge_not_null  NOT NULL,
 manufacturer           varchar2(20),
 supplier                       varchar2(10))
pctfree 0 pctused 99
tablespace dimensions
storage (initial 16k next 16k pctincrease 0) ;

CREATE INDEX  easydw.product_pk_index ON product
  (product_id)
  pctfree 5  tablespace indx
  storage (initial 16k next 16k pctincrease 0) ;

ALTER TABLE product
  ADD CONSTRAINT pk_product PRIMARY KEY (product_id);


-- TIME Dimension
drop TABLE easydw.time CASCADE CONSTRAINTS;

CREATE TABLE easydw.time
(time_key               date,
 day                            number (2,0),
 month                  number (2,0),
 quarter                        number (2,0),
 year                           number (4,0),
 day_number                     number (3,0),
 day_of_the_week                varchar2(8),
 week_number            number (2,0),
 public_holiday         varchar2(1)
   constraint public_holiday
    CHECK (public_holiday IN ('Y','N'))   )
pctfree 0 pctused 99
tablespace dimensions
storage (initial 16k next 16k pctincrease 0) ;

CREATE INDEX  easydw.time_pk_index ON time
  (time_key)
  pctfree 5  tablespace indx
  storage (initial 16k next 16k pctincrease 0) ;

ALTER TABLE time
  ADD CONSTRAINT pk_time PRIMARY KEY (time_key);

-- TODAYS_SPECIAL_OFFERS Dimension
drop TABLE easydw.todays_special_offers CASCADE CONSTRAINTS;

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 dimensions
storage (initial 16k next 16k pctincrease 0) ;

CREATE INDEX  easydw.tso_pk_index ON todays_special_offers
  (offer_date, product_id)
  pctfree 5  tablespace indx
  storage (initial 16k next 16k pctincrease 0) ;

ALTER TABLE todays_special_offers
  ADD CONSTRAINT pk_specials PRIMARY KEY (offer_date,product_id );

-- Fact Table  PURCHASES

drop TABLE easydw.purchases CASCADE CONSTRAINTS;

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),
 purchase_date          date,
 purchase_time          number(4,0),
 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_jan99
          values less than (TO_DATE('31-01-1999', 'DD-MM-YYYY'))
          pctfree 0  pctused 99
          storage (initial 64k next 16k pctincrease 0)
          tablespace purchases_jan99 ,
        partition purchases_feb99
          values less than (TO_DATE('28-02-1999', 'DD-MM-YYYY'))
          pctfree 0  pctused 99
          storage (initial 64k next 16k pctincrease 0)
          tablespace purchases_feb99 ,
partition purchases_mar99
          values less than (TO_DATE('31-03-1999', 'DD-MM-YYYY'))
          pctfree 0  pctused 99
          storage (initial 64k next 16k pctincrease 0)
          tablespace purchases_mar99 );

--------------------------------------------------------------------------------------
-- Now put some data in the database

-- TIME Dimension
INSERT INTO time VALUES ( '01-JAN-1999',1,1,1,1999,1,'Sunday',1,'Y');
INSERT INTO time VALUES ( '02-JAN-1999',2,1,1,1999,2,'Monday',1,'Y');
INSERT INTO time VALUES ( '01-FEB-1999',1,2,1,1999,32,'Sunday',5,'N');
INSERT INTO time VALUES ( '02-FEB-1999',2,2,1,1999,33,'Monday',5,'N');
INSERT INTO time VALUES ( '01-MAR-1999',1,3,1,1999,60,'Monday',9,'N');
INSERT INTO time VALUES ( '02-MAR-1999',2,3,1,1999,61,'Tuesday',9,'N');
INSERT INTO time VALUES ( '01-APR-1999',1,4,2,1999,92,'Thursday',13,'N');

COMMIT;

------------------------

-- Customer
INSERT INTO customer VALUES
 ('AB123456', 'Eastleigh', 'Hants','SO531LD','16-FEB-1959' ,
'UK','CONSULTANT');

INSERT INTO customer VALUES
 ('AB123457', 'Soton', 'Hants','SO6 1gh','23-AUG-1921'   ,
'UK','HOUSEWIFE');

-- Customer
INSERT INTO customer VALUES
 ('AB123458', 'Chandlers', 'Hants','SO53 1TZ','23-AUG-1921'  ,
'UK','HOMEMAKER');

INSERT INTO customer VALUES ('AB123459', 'Soton', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123460', 'Soton2', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123461', 'Soton3', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123462', 'Soton4', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123463', 'Soton5', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123464', 'Soton6', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123465', 'Soton7', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123466', 'Soton8', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123467', 'Soton9', 'Hants','SO1
1TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES ('AB123477', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123478', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123479', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123480', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123481', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123482', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123483', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123484', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123485', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123486', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123487', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123488', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123489', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123490', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123491', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123492', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123493', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123494', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123495', 'London', 'London','W1
1QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES ('AB123496', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123497', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123498', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123499', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123500', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123501', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123502', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123503', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123504', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123505', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123506', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123507', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123508', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES ('AB123509', 'Liverpool', 'Mersey','LV1
1QT','17-JUN-1963' ,'UK','FITTER');

COMMIT;

----------------------------

-- PRODUCT

INSERT INTO product VALUES
  ('SP1000', 'Digital  Camera','ELEC', 45.67, 67.23, 15.00, 4.50,
'Ricoh','Ricoh' );

INSERT INTO product VALUES ('SP1001', 'APS  Camera','ELEC', 24.67, 36.23,
5.00, 4.50, 'Ricoh','Ricoh' );
INSERT INTO product VALUES ('SP1010', 'Camera','ELEC', 35.67, 47.89, 5.00,
4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1011', 'Camera','ELEC', 35.67, 47.89, 5.00,
4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1012', 'Camera','ELEC', 35.67, 47.89, 5.00,
4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1013', 'Camera SX1','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1014', 'Camera SX2','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1015', 'Camera SX3','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1016', 'Camera SX4','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1017', 'Camera SX5','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1018', 'Camera SX6','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1019', 'Camera SX7','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1020', 'Camera SX8','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1021', 'Camera SX9','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1022', 'Camera SX45','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1023', 'Telescope1','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1024', 'Telescope2','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1025', 'Telescope3','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1026', 'Telescope4','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1027', 'Telescope5','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1028', 'Telescope6','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1029', 'Telescope7','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1030', 'Telescope8','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1031', 'Telescope9','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1032', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1033', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1034', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1035', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1036', 'Computer LX1','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1037', 'Computer LX2','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1038', 'Computer LX3','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1039', 'Computer LX4','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1040', 'Computer LX5','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1041', 'Computer LX6','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1042', 'Computer LX7','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1043', 'Computer LX8','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1044', 'Computer LX9','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1045', 'Computer SX1','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1046', 'Computer SX2','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1047', 'Computer SX3','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1048', 'Computer SX4','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1049', 'Computer SX5','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1050', 'Computer SX6','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1051', 'Computer SX7','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1052', 'Computer SX8','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1053', 'Computer SX9','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1054', 'Computer TX1','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1055', 'Computer TX2','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1056', 'Computer TX3','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1057', 'Computer TX4','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1058', 'Computer TX5','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1059', 'Computer TX6','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1060', 'Computer TX7','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1061', 'Computer TX8','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1062', 'Lamp LX1','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1063', 'Lamp LX2','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1064', 'Lamp LX3','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1065', 'Lamp LX4','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1066', 'Lamp LX5','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1067', 'Lamp LX6','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1068', 'Lamp LX7','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1069', 'Lamp LX8','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1070', 'Lamp LX9','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1071', 'Lamp SX1','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1072', 'Lamp SX2','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1073', 'Lamp SX3','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1074', 'Lamp SX4','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1075', 'Lamp SX5','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1076', 'Lamp SX6','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1077', 'Lamp SX7','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1078', 'Lamp SX8','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1080', 'Lamp SX9','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1081', 'Lamp TX1','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1082', 'Lamp TX2','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1083', 'Lamp TX3','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1084', 'Lamp TX4','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1085', 'Lamp TX5','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1086', 'Lamp TX6','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1087', 'Lamp TX7','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1088', 'Lamp TX8','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1089', 'Lamp TX9','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1090', 'Lamp TX0','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1092', 'Kitchen LX1','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1093', 'Kitchen LX2','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1094', 'Kitchen LX3','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1095', 'Kitchen LX4','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1096', 'Kitchen LX5','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1097', 'Kitchen LX6','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1098', 'Kitchen LX7','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1099', 'Kitchen LX8','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1100', 'Kitchen LX9','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1101', 'Kitchen SX1','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1102', 'Kitchen SX2','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1103', 'Kitchen SX3','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1104', 'Kitchen SX4','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1105', 'Kitchen SX5','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1106', 'Kitchen SX6','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1107', 'Kitchen SX7','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1111', 'Kitchen SX8','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1110', 'Kitchen SX9','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1111', 'Kitchen TX1','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1112', 'Kitchen TX2','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1113', 'Kitchen TX3','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1114', 'Kitchen TX4','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1115', 'Kitchen TX5','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1116', 'Kitchen TX6','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1117', 'Kitchen TX7','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1118', 'Kitchen TX8','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1119', 'Kitchen TX9','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1200', 'Kitchen TX0','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
commit;
INSERT INTO product VALUES ('SP1212', 'Mop LX1','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1213', 'Mop LX2','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1214', 'Mop LX3','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1215', 'Mop LX4','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1216', 'Mop LX5','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1217', 'Mop LX6','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1218', 'Mop LX7','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1219', 'Mop LX8','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1220', 'Mop LX9','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1221', 'Mop SX1','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1222', 'Mop SX2','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1223', 'Mop SX3','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1224', 'Mop SX4','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1225', 'Mop SX5','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1226', 'Mop SX6','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1227', 'Mop SX7','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1231', 'Mop SX8','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1122', 'Mop SX9','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1231', 'Mop TX1','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1232', 'Mop TX2','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1233', 'Mop TX3','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1234', 'Mop TX4','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1235', 'Mop TX5','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1236', 'Mop TX6','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1237', 'Mop TX7','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1238', 'Mop TX8','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1239', 'Mop TX9','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
commit;
INSERT INTO product VALUES ('SP1242', 'CD LX1','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1243', 'CD LX2','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1244', 'CD LX3','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1245', 'CD LX4','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1246', 'CD LX5','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1247', 'CD LX6','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1248', 'CD LX7','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1249', 'CD LX8','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1250', 'CD LX9','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1251', 'CD SX1','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1252', 'CD SX2','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1253', 'CD SX3','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1254', 'CD SX4','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1255', 'CD SX5','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1256', 'CD SX6','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1257', 'CD SX7','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1258', 'CD SX8','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1260', 'CD SX9','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1261', 'CD TX1','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1262', 'CD TX2','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1263', 'CD TX3','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1264', 'CD TX4','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1265', 'CD TX5','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1266', 'CD TX6','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1267', 'CD TX7','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1268', 'CD TX8','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1269', 'CD TX9','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
commit;

----------------------------

-- TODAYS_SPECIAL_OFFERS

INSERT INTO todays_special_offers  VALUES ('SP1001' , '1-JAN-1999', 28.01,
36.23);
INSERT INTO todays_special_offers  VALUES ('SP1032' , '2-JAN-1999', 125.67,
156.45);
INSERT INTO todays_special_offers  VALUES ('SP1057' , '1-FEB-1999', 901.23,
1267.89);
INSERT INTO todays_special_offers  VALUES ('SP1200' , '2-FEB-1999', 59.78,
72.87);
INSERT INTO todays_special_offers  VALUES ('SP1239' , '1-MAR-1999', 11.34,
15.67);
INSERT INTO todays_special_offers  VALUES ('SP1269' , '2-MAR-1999', 10.56,
15.67);
COMMIT;

-----------------------------

-- PURCHASES

INSERT INTO purchases VALUES ( 'SP1001','1-JAN-1999',
'AB123456','1-JAN-1999',   0024,28.01, 4.50, 'Y');

INSERT INTO purchases VALUES ( 'SP1001','1-JAN-1999',
'AB123457','1-JAN-1999',   1024,28.01, 4.50, 'Y');

INSERT INTO purchases VALUES ( 'SP1001','2-JAN-1999',
'AB123457','2-JAN-1999',   0024,28.01, 4.50, 'N');

INSERT INTO purchases VALUES ( 'SP1001','2-JAN-1999',
'AB123457','2-JAN-1999',   1024,28.01, 4.50, 'N');

commit;

INSERT INTO purchases VALUES ( 'SP1061','1-FEB-1999',
'AB123456','1-JAN-1999',   0024,28.01, 4.50, 'Y');
INSERT INTO purchases VALUES ( 'SP1062','1-FEB-1999',
'AB123457','1-JAN-1999',   1024,28.01, 4.50, 'Y');
INSERT INTO purchases VALUES ( 'SP1063','2-FEB-1999',
'AB123457','2-JAN-1999',   0024,28.01, 4.50, 'N');
INSERT INTO purchases VALUES ( 'SP1064','2-FEB-1999',
'AB123457','2-JAN-1999',   1024,28.01, 4.50, 'N');
commit;

------------------------------------------------

-- Now load up lots of purchases

DECLARE
   p_id      varchar2(8);
   s_price   number(6,2);
   ship      number(5,2);
   c_id      varchar2(10);
   buyat     NUMBER := 1001;
  CURSOR all_buys IS
    SELECT  product_id, sell_price, shipping_charge  FROM product;
  CURSOR all_customers  IS SELECT customer_id FROM customer;
  BEGIN
  OPEN  all_buys;
  LOOP
    FETCH all_buys INTO p_id, s_price, ship;
  OPEN all_customers;
   LOOP
      FETCH all_customers INTO c_id;
      INSERT INTO PURCHASES VALUES (p_id, '1-JAN-1999',c_id, '1-JAN-1999',
buyat , s_price, ship, 'N');
      buyat:= buyat +1;
  EXIT WHEN all_customers%NOTFOUND;
   END LOOP;
   CLOSE all_customers;
   buyat:= 1001;
  EXIT WHEN all_buys%NOTFOUND;
  END LOOP;
  CLOSE all_buys;
  END;
/

commit;

-- Now load up lots of purchases

DECLARE
   p_id      varchar2(8);
   s_price   number(6,2);
   ship      number(5,2);
   c_id      varchar2(10);
   buyat     NUMBER := 1001;
  CURSOR all_buys IS
    SELECT  product_id, sell_price, shipping_charge  FROM product;
  CURSOR all_customers  IS SELECT customer_id FROM customer;
  BEGIN
  OPEN  all_buys;
  LOOP
    FETCH all_buys INTO p_id, s_price, ship;
  OPEN all_customers;
   LOOP
      FETCH all_customers INTO c_id;
      INSERT INTO PURCHASES VALUES (p_id, '2-FEB-1999',c_id, '1-JAN-1999',
buyat , s_price, ship, 'N');
      buyat:= buyat +1;
  EXIT WHEN all_customers%NOTFOUND;
   END LOOP;
   CLOSE all_customers;
   buyat:= 1001;
  EXIT WHEN all_buys%NOTFOUND;
  END LOOP;
  CLOSE all_buys;
  END;
/

commit;

-- -- Now create the indexes

--- Partition on the Time Key   Local prefixed index
drop index easydw.purchase_time_index;
CREATE bitmap INDEX easydw.purchase_time_index ON purchases
  (time_key )
local
 (partition indexJan99 tablespace purchases_jan99_idx,
  partition indexFeb99 tablespace purchases_feb99_idx,
  partition indexMar99 tablespace purchases_mar99  );

drop index easydw.purchase_product_index;
CREATE INDEX easydw.purchase_product_index ON purchases
  (product_id )
  local
  pctfree 5   tablespace indx
  storage (initial 64k next 64k pctincrease 0) ;

drop index easydw.purchase_customer_index;
CREATE INDEX easydw.purchase_customer_index ON purchases
  (customer_id )
  local
  pctfree 5   tablespace indx
  storage (initial 64k next 64k pctincrease 0) ;

drop index easydw.purchase_special_index;
CREATE INDEX easydw.purchase_special_index ON purchases
  (today_special_offer )
  local
  pctfree 5   tablespace indx
  storage (initial 64k next 64k pctincrease 0) ;

-- Now Analyze the Tables and Indexes
ANALYZE TABLE customer COMPUTE STATISTICS;
ANALYZE TABLE todays_special_offers COMPUTE STATISTICS;
ANALYZE TABLE product COMPUTE STATISTICS;
ANALYZE TABLE time COMPUTE STATISTICS;
ANALYZE TABLE purchases COMPUTE STATISTICS;

------------------------------------------------

CREATE DIMENSION customer
  LEVEL customer IS customer.customer_id
  LEVEL town     IS customer.town
  LEVEL region   IS customer.county
HIERARCHY  customer_zone
  ( customer CHILD OF
    town     CHILD OF
    region ) ;

CREATE DIMENSION product
  LEVEL product  IS product.product_id
  LEVEL category IS product.category
  HIERARCHY products
   (product CHILD OF category)
  ATTRIBUTE product DETERMINES product.product_name;

CREATE DIMENSION time
LEVEL time       IS time.time_key
LEVEL month          IS time.month
LEVEL quarter        IS time.quarter
LEVEL year           IS time.year
HIERARCHY calendar_rollup    (
  time  CHILD OF
  month      CHILD OF
  quarter    CHILD OF
  year               );

commit;

CREATE MATERIALIZED VIEW customer_sum
 TABLESPACE summary
 BUILD IMMEDIATE
 REFRESH COMPLETE
 ENABLE QUERY REWRITE
 AS
SELECT  SUM(purchase_price), county
  FROM PURCHASES pv, CUSTOMER ct
   WHERE pv.customer_id = ct.customer_id
    GROUP BY county;

CREATE MATERIALIZED VIEW product_sum
 TABLESPACE summary
 BUILD IMMEDIATE
 REFRESH COMPLETE
 ENABLE QUERY REWRITE
 AS
SELECT SUM (purchase_price), purchase_date
  FROM purchases
     GROUP BY purchase_date;