B
Sample Data Warehousing Schema

This appendix introduces a common schema (Sales History) that is used in this guide. Most of the examples throughout this book use the same, simple star schema. This schema consists of four dimension tables and a single fact table (called sales) partitioned by month. The definitions of these tables follow:

CREATE TABLE times
( 
  time_id                 DATE,
  day_name                VARCHAR2(9)
      CONSTRAINT             tim_day_name_nn            NOT NULL,
  day_number_in_week      NUMBER(1)    
      CONSTRAINT             tim_day_in_week_nn         NOT NULL,
  day_number_in_month     NUMBER(2)
      CONSTRAINT             tim_day_in_month_nn        NOT NULL,
  calendar_week_number    NUMBER(2)
      CONSTRAINT             tim_cal_week_nn            NOT NULL,
  fiscal_week_number      NUMBER(2)
      CONSTRAINT             tim_fis_week_nn            NOT NULL,
  week_ending_day         DATE     
      CONSTRAINT             tim_week_ending_day_nn     NOT NULL,
  calendar_month_number   NUMBER(2)
      CONSTRAINT             tim_cal_month_number_nn    NOT NULL,
  fiscal_month_number     NUMBER(2)
      CONSTRAINT             tim_fis_month_number_nn    NOT NULL,
  calendar_month_desc     VARCHAR2(8)
      CONSTRAINT             tim_cal_month_desc_nn      NOT NULL,
  fiscal_month_desc       VARCHAR2(8)
      CONSTRAINT             tim_fis_month_desc_nn      NOT NULL,
  days_in_cal_month       NUMBER
      CONSTRAINT             tim_days_cal_month_nn      NOT NULL,
  days_in_fis_month       NUMBER
      CONSTRAINT             tim_days_fis_month_nn      NOT NULL,
  end_of_cal_month        DATE
      CONSTRAINT             tim_end_of_cal_month_nn   NOT NULL,
  end_of_fis_month        DATE
      CONSTRAINT             tim_end_of_fis_month_nn   NOT NULL,
  calendar_month_name     VARCHAR2(9)
      CONSTRAINT             tim_cal_month_name_nn     NOT NULL,
  fiscal_month_name       VARCHAR2(9)
      CONSTRAINT             tim_fis_month_name_nn     NOT NULL,
  calendar_quarter_desc   CHAR(7)   
      CONSTRAINT             tim_cal_quarter_desc_nn   NOT NULL,
  fiscal_quarter_desc     CHAR(7)    
      CONSTRAINT             tim_fis_quarter_desc_nn   NOT NULL,
  days_in_cal_quarter     NUMBER
      CONSTRAINT             tim_days_cal_quarter_nn   NOT NULL,
  days_in_fis_quarter     NUMBER
      CONSTRAINT             tim_days_fis_quarter_nn   NOT NULL,
  end_of_cal_quarter      DATE
      CONSTRAINT             tim_end_of_cal_quarter_nn NOT NULL,
  end_of_fis_quarter      DATE
      CONSTRAINT             tim_end_of_fis_quarter_nn NOT NULL,
  calendar_quarter_number NUMBER(1)  
      CONSTRAINT             tim_cal_quarter_number_nn NOT NULL,
  fiscal_quarter_number   NUMBER(1)  
      CONSTRAINT             tim_fis_quarter_number_nn NOT NULL,
  calendar_year           NUMBER(4)  
      CONSTRAINT             tim_cal_year_nn           NOT NULL,
  fiscal_year             NUMBER(4)  
      CONSTRAINT             tim_fis_year_nn           NOT NULL,
  days_in_cal_year        NUMBER
      CONSTRAINT             tim_days_cal_year_nn      NOT NULL,
  days_in_fis_year        NUMBER
      CONSTRAINT             tim_days_fis_year_nn      NOT NULL,
  end_of_cal_year         DATE
      CONSTRAINT             tim_end_of_cal_year_nn    NOT NULL,
  end_of_fis_year         DATE
      CONSTRAINT             tim_end_of_fis_year_nn    NOT NULL
  );
REM creation of dimension table CHANNELS ...
CREATE TABLE channels
( 
   channel_id       CHAR(1),
   channel_desc     VARCHAR2(20)
        CONSTRAINT     chan_desc_nn NOT NULL,
   channel_class    VARCHAR2(20) 
  );

REM creation of dimension table PROMOTIONS ...
CREATE TABLE promotions
(
   promo_id           NUMBER(6),
   promo_name         VARCHAR2(20)
        CONSTRAINT      promo_name_nn       NOT NULL,
   promo_subcategory  VARCHAR2(30)
        CONSTRAINT      promo_subcat_nn     NOT NULL,
   promo_category     VARCHAR2(30)
        CONSTRAINT      promo_cat_nn        NOT NULL,
   promo_cost         NUMBER(10,2)
        CONSTRAINT      promo_cost_nn       NOT NULL,
   promo_begin_date   DATE
        CONSTRAINT      promo_begin_date_nn NOT NULL,
   promo_end_date     DATE
        CONSTRAINT      promo_end_date_nn   NOT NULL
 );

REM creation of dimension table COUNTRIES ...
CREATE TABLE countries 
(
  country_id         CHAR(2),
  country_name       VARCHAR2(40) 
        CONSTRAINT     country_country_name_nn NOT NULL,
  country_subregion  VARCHAR2(30),
  country_region     VARCHAR2(20) 
 ); 

CREATE TABLE customers
(
   cust_id                NUMBER,
   cust_first_name        VARCHAR2(20) 
        CONSTRAINT           customer_fname_nn      NOT NULL,
   cust_last_name         VARCHAR2(40) 
        CONSTRAINT           customer_lname_nn      NOT NULL,
   cust_gender            CHAR(1),
   cust_year_of_birth     NUMBER(4),
   cust_marital_status    VARCHAR2(20),
   cust_street_address    VARCHAR2(40) 
        CONSTRAINT           customer_st_addr_nn    NOT NULL,
   cust_postal_code       VARCHAR2(10) 
        CONSTRAINT           customer_pcode_nn      NOT NULL,
   cust_city              VARCHAR2(30) 
        CONSTRAINT           customer_city_nn       NOT NULL,
   cust_state_province    VARCHAR2(40),
   country_id             CHAR(2) 
        CONSTRAINT           customer_country_id_nn NOT NULL,
   cust_main_phone_number VARCHAR2(25),
   cust_income_level      VARCHAR2(30),
   cust_credit_limit      NUMBER,
   cust_email             VARCHAR2(30)
);

REM creation of dimension table PRODUCTS ...
CREATE TABLE products
(
  prod_id              NUMBER(6),
  prod_name            VARCHAR2(50)   
      CONSTRAINT           products_prod_name_nn     NOT NULL,
  prod_desc            VARCHAR2(4000) 
      CONSTRAINT           products_prod_desc_nn     NOT NULL,
  prod_subcategory     VARCHAR2(50)  
      CONSTRAINT           products_prod_subcat_nn   NOT NULL,
  prod_subcat_desc     VARCHAR2(2000)  
      CONSTRAINT           products_prod_subcatd_nn  NOT NULL,
  prod_category        VARCHAR2(50)  
      CONSTRAINT           products_prod_cat_nn      NOT NULL,
  prod_cat_desc        VARCHAR2(2000)  
      CONSTRAINT           products_prod_catd_nn     NOT NULL,
  prod_weight_class    NUMBER(2),
  prod_unit_of_measure VARCHAR2(20),
  prod_pack_size       VARCHAR2(30),
  supplier_id          NUMBER(6),
  prod_status          VARCHAR2(20)
     CONSTRAINT           products_prod_stat_nn NOT NULL,
  prod_list_price      NUMBER(8,2) 
     CONSTRAINT           products_prod_list_price_nn NOT NULL,
  prod_min_price       NUMBER(8,2) 
     CONSTRAINT           products_prod_min_price_nn NOT NULL
);

REM creation of fact table SALES ...
CREATE TABLE sales
(
  prod_id               NUMBER(6)
      CONSTRAINT           sales_product_nn      NOT NULL,
  cust_id               NUMBER
      CONSTRAINT           sales_customer_nn     NOT NULL,
  time_id              DATE
      CONSTRAINT            sales_time_nn        NOT NULL,
  channel_id           CHAR(1)
      CONSTRAINT            sales_channel_nn     NOT NULL,
  promo_id              NUMBER(6),
  quantity_sold         NUMBER(3)
      CONSTRAINT            sales_quantity_nn    NOT NULL,
  amount                NUMBER(10,2)
      CONSTRAINT            sales_amount_nn      NOT NULL,
  cost                  NUMBER(10,2)
      CONSTRAINT            sales_cost_nn        NOT NULL
)

PARTITION BY RANGE (time_id)
(PARTITION Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
 PARTITION Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
 PARTITION Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
 PARTITION Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
 PARTITION Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
 PARTITION Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
 PARTITION Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
 PARTITION Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
 PARTITION Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
 PARTITION Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
 PARTITION Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
 PARTITION Q4_2000 VALUES LESS THAN (MAXVALUE))
 ;
REM A foreign-key relationship between SALES and PROMOTIONS is 
REM intentionally omitted to demonstrate more sophisticated query
REM rewrite mechanisms

ALTER TABLE sales
ADD ( CONSTRAINT sales_product_fk
      FOREIGN KEY (prod_id) 
      REFERENCES products,
      CONSTRAINT sales_customer_fk 
      FOREIGN KEY (cust_id)
      REFERENCES customers,
      CONSTRAINT sales_time_fk
      FOREIGN KEY (time_id)
      REFERENCES times,
      CONSTRAINT sales_channel_fk
      FOREIGN KEY (channel_id)
      REFERENCES channels
    );
COMMIT;