/* Formatted on 2007/11/22 12:30 (Formatter Plus v4.8.8) */
SET SERVEROUT ON;

spool create_sample_star_schema.log;

/* Drop the Sample Tables */
DROP TABLE batting_fact;
DROP TABLE game_dim;
DROP SEQUENCE game_dim_seq;
DROP DIMENSION game_dim;
DROP TABLE team_dim;
DROP SEQUENCE team_dim_seq;
DROP DIMENSION team_dim;
DROP TABLE player_dim;
DROP SEQUENCE player_dim_seq;
DROP DIMENSION player_dim;
purge recyclebin;

/* Create the Sample Dimension Tables and Dimensions */

CREATE TABLE game_dim
(game_id NUMBER(30) NOT NULL,
 game_no NUMBER(3) NOT NULL,
 game_day DATE NOT NULL ,
 game_month VARCHAR2(15) NOT NULL,
 game_season NUMBER(4) NOT NULL);

CREATE SEQUENCE game_dim_seq;

CREATE DIMENSION game_dim
   LEVEL game         IS game_dim.game_no
   LEVEL game_day     IS game_dim.game_day
   LEVEL game_month   IS game_dim.game_month
   LEVEL game_season  IS game_dim.game_season
   HIERARCHY game_rollup    (
             game          CHILD OF
             game_day      CHILD OF
             game_month    CHILD OF
             game_season);

DECLARE
   lv_start_date   DATE := TO_DATE ('01-APR-0001', 'DD-MON-YYYY');
   lv_tmp_date     DATE;
BEGIN
   FOR j IN 1 .. 9998
   LOOP
      lv_tmp_date := lv_start_date;

      FOR i IN 1 .. 365
      LOOP
         INSERT INTO game_dim
              VALUES (game_dim_seq.NEXTVAL, i, TRUNC (lv_tmp_date),
                      TRIM (TO_CHAR (lv_tmp_date, 'Month')),
                      TO_CHAR (lv_tmp_date, 'YYYY'));

         COMMIT;
         lv_tmp_date := TRUNC (lv_tmp_date) + 1;
      END LOOP;

      lv_start_date := TRUNC (lv_start_date) + 365;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;
/

CREATE UNIQUE INDEX game_dim_pk ON game_dim(game_id);
ALTER TABLE game_dim ADD CONSTRAINT game_dim_pk PRIMARY KEY (game_id)  RELY ENABLE NOVALIDATE;
CREATE BITMAP INDEX game_dim_game_no_bidx ON game_dim(game_no);
CREATE BITMAP INDEX game_dim_game_day_bidx ON game_dim(game_day);
CREATE BITMAP INDEX game_dim_game_month_bidx ON game_dim(game_month);
CREATE BITMAP INDEX game_dim_game_season_bidx ON game_dim(game_season);

CREATE TABLE team_dim
(team_id NUMBER(30) NOT NULL,
 team_name VARCHAR2(20) NOT NULL,
 team_division VARCHAR2(20) NOT NULL ,
 team_league VARCHAR2(20) NOT NULL);

CREATE SEQUENCE team_dim_seq;

CREATE DIMENSION team_dim
   LEVEL team              IS team_dim.team_name
   LEVEL team_division     IS team_dim.team_division
   LEVEL team_league       IS team_dim.team_league
   HIERARCHY team_rollup    (
             team               CHILD OF
             team_division      CHILD OF
             team_league     );

INSERT INTO team_dim
     VALUES (team_dim_seq.NEXTVAL, 'CHICAGO CUBS', 'MIDWEST', 'NATIONAL');
INSERT INTO team_dim
     VALUES (team_dim_seq.NEXTVAL, 'HOUSTON ASTROS', 'MIDWEST', 'NATIONAL');
INSERT INTO team_dim
     VALUES (team_dim_seq.NEXTVAL, 'ST. LOUIS CARDINALS', 'MIDWEST',
             'NATIONAL');
INSERT INTO team_dim
     VALUES (team_dim_seq.NEXTVAL, 'BOSTON RED SOX', 'EAST', 'AMERICAN');
INSERT INTO team_dim
     VALUES (team_dim_seq.NEXTVAL, 'NEW YORK', 'EAST', 'AMERICAN');
COMMIT ;

CREATE UNIQUE INDEX team_dim_pk ON team_dim(team_id);
CREATE UNIQUE INDEX team_dim_uk ON team_dim(team_name,team_division,team_league);
ALTER TABLE team_dim ADD CONSTRAINT team_dim_pk PRIMARY KEY (team_id) RELY ENABLE NOVALIDATE;
ALTER TABLE team_dim ADD CONSTRAINT team_dim_uk UNIQUE (team_name,team_division,team_league) USING INDEX;
CREATE BITMAP INDEX team_dim_team_name_bidx ON team_dim(team_name);
CREATE BITMAP INDEX team_dim_team_division_bidx ON team_dim(team_division);
CREATE BITMAP INDEX team_dim_team_league_bidx ON team_dim(team_league);

CREATE TABLE player_dim
(player_id NUMBER(30) NOT NULL,
 player_fname VARCHAR2(30) NOT NULL,
 player_lname VARCHAR2 (30) NOT NULL ,
 player_name VARCHAR2(65) NOT NULL,
 player_bday DATE NOT NULL,
 player_start_date DATE NOT NULL,
 player_retire_date DATE);

CREATE SEQUENCE player_dim_seq;

CREATE DIMENSION player_dim
   LEVEL player            IS player_dim.player_id
ATTRIBUTE player DETERMINES
    (player_dim.player_fname,
     player_dim.player_lname,
     player_dim.player_name,
     player_dim.player_bday,
     player_dim.player_start_date,
     player_dim.player_retire_date);

INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Ryne', 'Sandberg', 'Ryne Sandberg',
             TO_DATE ('23-JAN-1945', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1982', 'DD-MON-YYYY'),
             TO_DATE ('23-AUG-2001', 'DD-MON-YYYY'));
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'James', 'Colestock', 'James Colestock',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Jim', 'Colestock', 'Jim Colestock',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));    
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Jaime', 'Colestock', 'Jaime Colestock',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY')); 
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Rob', 'Colestock', 'Rob Colestock',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));    
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Larry', 'Colestock', 'Larry Colestock',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));   
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Bob', 'Colestock', 'Bob Colestock',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
     VALUES (player_dim_seq.NEXTVAL, 'Bob', 'Dobalina', 'Bob Dobalina',
             TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
             TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
             TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));                                                               
COMMIT ;

CREATE UNIQUE INDEX player_dim_pk ON player_dim(player_id);
CREATE UNIQUE INDEX player_dim_uk ON player_dim(player_name,player_bday, player_start_date, player_retire_date);
ALTER TABLE player_dim ADD CONSTRAINT player_dim_pk PRIMARY KEY (player_id) RELY ENABLE NOVALIDATE;
ALTER TABLE player_dim ADD CONSTRAINT player_dim_uk UNIQUE (player_name,player_bday, player_start_date, player_retire_date) USING INDEX;
CREATE BITMAP INDEX player_dim_player_fname_bidx ON player_dim(player_fname);
CREATE BITMAP INDEX player_dim_player_lname_bidx ON player_dim(player_lname);
CREATE BITMAP INDEX player_dim_player_name_bidx ON player_dim(player_name);
CREATE BITMAP INDEX player_dim_player_bday_bidx ON player_dim(player_bday);
CREATE BITMAP INDEX player_dim_player_sdate_bidx ON player_dim(player_start_date);
CREATE BITMAP INDEX player_dim_player_rdate_bidx ON player_dim(player_retire_date);

/* Create the Sample Fact Table */

CREATE TABLE batting_fact
(
  game_id        NUMBER(30)                     NOT NULL,
  player_id      NUMBER(30)                     NOT NULL,
  team_id        NUMBER(30)                     NOT NULL,
  at_bats        NUMBER(2)                      DEFAULT 0 NOT NULL,
  walks          NUMBER(2)                      DEFAULT 0 NOT NULL,
  strike_outs    NUMBER(2)                      DEFAULT 0 NOT NULL,
  "1B"             NUMBER(2)                      DEFAULT 0 NOT NULL,
  "2B"             NUMBER(2)                      DEFAULT 0 NOT NULL,
  "3B"             NUMBER(2)                      DEFAULT 0 NOT NULL,
  hrs            NUMBER(2)                      DEFAULT 0 NOT NULL
);

INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 5, 1, 1, 1, 1, 1, 0
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Ryne Sandberg';
COMMIT ;

INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 4, 0, 0, 1, 1, 1, 1
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'James Colestock';
COMMIT ;

INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 4, 0, 0, 1, 1, 1, 1
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Jim Colestock';
COMMIT ;

INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 3, 0, 0, 0, 1, 1, 1
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'BOSTON RED SOX' AND pd.player_name = 'Jaime Colestock';
COMMIT ;
INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 2, 0, 0, 0, 0, 1, 1
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'BOSTON RED SOX' AND pd.player_name = 'Rob Colestock';
COMMIT ;
INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 2, 0, 0, 0, 0, 1, 1
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Larry Colestock';
COMMIT ;
INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 3, 0, 0, 0, 1, 1, 1
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'HOUSTON ASTROS' AND pd.player_name = 'Bob Colestock';
COMMIT ;
INSERT INTO batting_fact
   SELECT gd.game_id, pd.player_id, td.team_id, 5, 0, 0, 0, 1, 1, 3
     FROM game_dim gd, team_dim td, player_dim pd
    WHERE td.team_name = 'HOUSTON ASTROS' AND pd.player_name = 'Bob Dobalina';
COMMIT ;

CREATE UNIQUE INDEX batting_fact_pk ON batting_fact(game_id,player_id);
ALTER TABLE batting_fact ADD CONSTRAINT batting_fact_pk PRIMARY KEY (game_id,player_id) RELY ENABLE NOVALIDATE;
CREATE BITMAP INDEX batting_fact_gmid_fk_bidx ON batting_fact(game_id);
CREATE BITMAP INDEX batting_fact_plyrid_fk_bidx ON batting_fact(player_id);
CREATE BITMAP INDEX batting_fact_teamid_fk_bidx ON batting_fact(team_id);
CREATE BITMAP INDEX batting_fact_at_bats_bidx ON batting_fact(at_bats);
CREATE BITMAP INDEX batting_fact_walks_bidx ON batting_fact(walks);
CREATE BITMAP INDEX batting_fact_so_bidx ON batting_fact(strike_outs);
CREATE BITMAP INDEX batting_fact_1b_bidx ON batting_fact("1B");
CREATE BITMAP INDEX batting_fact_2b_bidx ON batting_fact("2B");
CREATE BITMAP INDEX batting_fact_3b_bidx ON batting_fact("3B");
CREATE BITMAP INDEX batting_fact_hrs_bidx ON batting_fact(hrs);

ALTER TABLE batting_fact ADD (
  CONSTRAINT batting_fact_gmid_fk
 FOREIGN KEY (game_id)
REFERENCES game_dim (game_id) RELY DISABLE NOVALIDATE);

ALTER TABLE batting_fact ADD (
  CONSTRAINT batting_fact_plyrid_fk
 FOREIGN KEY (player_id)
REFERENCES player_dim (player_id) RELY DISABLE NOVALIDATE);

ALTER TABLE batting_fact ADD (
  CONSTRAINT batting_fact_teamid_fk
 FOREIGN KEY (team_id)
REFERENCES team_dim (team_id) RELY DISABLE NOVALIDATE);

/* Analyze the Tables */
EXEC dbms_stats.gather_schema_stats(ownname=>'SH',options=>'GATHER EMPTY',estimate_percent=>.001, degree=>2, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 100');

/* Query the sizes of the tables */

select segment_name, round(sum(bytes)/1024/1024) "MB" from user_segments 
where segment_name like ('BATTING%')
or  segment_name like ('GAME%')
or  segment_name like ('PLAYER%')
or  segment_name like ('TEAM%')
group by segment_name
order by 2 desc;

spool off;