Sample warehouse schema
The Informix® Warehouse Accelerator examples use this sample warehouse schema.
SQL statements
The following SQL statements create the tables, indexes, and key constraints for the sample warehouse schema.
CREATE TABLE DAILY_FORECAST (
PERKEY INTEGER NOT NULL ,
STOREKEY INTEGER NOT NULL ,
PRODKEY INTEGER NOT NULL ,
QUANTITY_FORECAST INTEGER ,
EXTENDED_PRICE_FORECAST DECIMAL(16,2) ,
EXTENDED_COST_FORECAST DECIMAL(16,2) );
CREATE TABLE DAILY_SALES (
PERKEY INTEGER NOT NULL ,
STOREKEY INTEGER NOT NULL ,
CUSTKEY INTEGER NOT NULL ,
PRODKEY INTEGER NOT NULL ,
PROMOKEY INTEGER NOT NULL ,
QUANTITY_SOLD INTEGER ,
EXTENDED_PRICE DECIMAL(16,2) ,
EXTENDED_COST DECIMAL(16,2) ,
SHELF_LOCATION INTEGER ,
SHELF_NUMBER INTEGER ,
START_SHELF_DATE INTEGER ,
SHELF_HEIGHT INTEGER ,
SHELF_WIDTH INTEGER ,
SHELF_DEPTH INTEGER ,
SHELF_COST DECIMAL(16,2) ,
SHELF_COST_PCT_OF_SALE DECIMAL(16,2) ,
BIN_NUMBER INTEGER ,
PRODUCT_PER_BIN INTEGER ,
START_BIN_DATE INTEGER ,
BIN_HEIGHT INTEGER ,
BIN_WIDTH INTEGER ,
BIN_DEPTH INTEGER ,
BIN_COST DECIMAL(16,2) ,
BIN_COST_PCT_OF_SALE DECIMAL(16,2) ,
TRANS_NUMBER INTEGER ,
HANDLING_CHARGE INTEGER ,
UPC INTEGER ,
SHIPPING INTEGER ,
TAX INTEGER ,
PERCENT_DISCOUNT INTEGER ,
TOTAL_DISPLAY_COST DECIMAL(16,2) ,
TOTAL_DISCOUNT DECIMAL(16,2) ) ;
CREATE TABLE CUSTOMER (
CUSTKEY INTEGER NOT NULL ,
NAME CHAR(30) ,
ADDRESS CHAR(40) ,
C_CITY CHAR(20) ,
C_STATE CHAR(5) ,
ZIP CHAR(5) ,
PHONE CHAR(10) ,
AGE_LEVEL SMALLINT ,
AGE_LEVEL_DESC CHAR(20) ,
INCOME_LEVEL SMALLINT ,
INCOME_LEVEL_DESC CHAR(20) ,
MARITAL_STATUS CHAR(1) ,
GENDER CHAR(1) ,
DISCOUNT DECIMAL(16,2) ) ;
ALTER TABLE CUSTOMER
ADD CONSTRAINT PRIMARY KEY
( CUSTKEY );
CREATE TABLE PERIOD (
PERKEY INTEGER NOT NULL ,
CALENDAR_DATE DATE ,
DAY_OF_WEEK SMALLINT ,
WEEK SMALLINT ,
PERIOD SMALLINT ,
YEAR SMALLINT ,
HOLIDAY_FLAG CHAR(1) ,
WEEK_ENDING_DATE DATE ,
MONTH CHAR(3) ) ;
ALTER TABLE PERIOD
ADD CONSTRAINT PRIMARY KEY
( PERKEY );
CREATE UNIQUE INDEX PERX1 ON PERIOD
( CALENDAR_DATE ASC,
PERKEY ASC );
CREATE UNIQUE INDEX PERX2 ON PERIOD
( WEEK_ENDING_DATE ASC,
PERKEY ASC );
CREATE TABLE PRODUCT (
PRODKEY INTEGER NOT NULL ,
UPC_NUMBER CHAR(11) NOT NULL ,
PACKAGE_TYPE CHAR(20) ,
FLAVOR CHAR(20) ,
FORM CHAR(20) ,
CATEGORY INTEGER ,
SUB_CATEGORY INTEGER ,
CASE_PACK INTEGER ,
PACKAGE_SIZE CHAR(6) ,
ITEM_DESC CHAR(30) ,
P_PRICE DECIMAL(16,2) ,
CATEGORY_DESC CHAR(30) ,
P_COST DECIMAL(16,2) ,
SUB_CATEGORY_DESC CHAR(70) ) ;
ALTER TABLE PRODUCT
ADD CONSTRAINT PRIMARY KEY
( PRODKEY );
CREATE UNIQUE INDEX PRODX2 ON PRODUCT
( CATEGORY ASC,
PRODKEY ASC );
CREATE UNIQUE INDEX PRODX3 ON PRODUCT
( CATEGORY_DESC ASC,
PRODKEY ASC );
CREATE TABLE PROMOTION (
PROMOKEY INTEGER NOT NULL ,
PROMOTYPE INTEGER ,
PROMODESC CHAR(30) ,
PROMOVALUE DECIMAL(16,2) ,
PROMOVALUE2 DECIMAL(16,2) ,
PROMO_COST DECIMAL(16,2) ) ;
ALTER TABLE PROMOTION
ADD CONSTRAINT PRIMARY KEY
( PROMOKEY );
CREATE UNIQUE INDEX PROMOX1 ON PROMOTION
( PROMODESC ASC,
PROMOKEY ASC);
CREATE TABLE STORE (
STOREKEY INTEGER NOT NULL ,
STORE_NUMBER CHAR(2) ,
CITY CHAR(20) ,
STATE CHAR(5) ,
DISTRICT CHAR(14) ,
REGION CHAR(10) ) ;
ALTER TABLE STORE
ADD CONSTRAINT PRIMARY KEY
( STOREKEY );
CREATE INDEX DFX1 ON DAILY_FORECAST ( PERKEY ASC);
CREATE INDEX DFX2 ON DAILY_FORECAST ( STOREKEY ASC);
CREATE INDEX DFX3 ON DAILY_FORECAST ( PRODKEY ASC);
CREATE INDEX DSX1 ON DAILY_SALES ( PERKEY ASC);
CREATE INDEX DSX2 ON DAILY_SALES ( STOREKEY ASC);
CREATE INDEX DSX3 ON DAILY_SALES ( CUSTKEY ASC);
CREATE INDEX DSX4 ON DAILY_SALES ( PRODKEY ASC);
CREATE INDEX DSX5 ON DAILY_SALES ( PROMOKEY ASC);
ALTER TABLE daily_sales ADD CONSTRAINT FOREIGN KEY (perkey)
references period(perkey);
ALTER TABLE daily_sales ADD CONSTRAINT FOREIGN KEY (prodkey)
references product(prodkey);
ALTER TABLE daily_sales ADD CONSTRAINT FOREIGN KEY
(storekey) references store(storekey);
ALTER TABLE daily_sales ADD CONSTRAINT FOREIGN KEY (custkey)
references customer(custkey);
ALTER TABLE daily_sales ADD CONSTRAINT FOREIGN KEY (promokey)
references promotion(promokey);
ALTER TABLE daily_forecast ADD CONSTRAINT FOREIGN KEY (perkey)
references period(perkey);
ALTER TABLE daily_forecast ADD CONSTRAINT FOREIGN KEY (prodkey)
references product(prodkey);
ALTER TABLE daily_forecast ADD CONSTRAINT FOREIGN KEY (storekey)
references store(storekey);
update statistics medium;