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;