236 High-Function Business Intelligence in e-business
DDL of tables
The tables are presented here in alphabetical order. The content of these tables
varied from query to query, and is therefore not shown here.
Example: B-1 AD_CAMP
CREATE TABLE “AD_CAMP” (
“AD_BUDGET” REAL,
“SALES” REAL)
Example: B-2 CAL_AD_CAMP
CREATE TABLE “CAL_AD_CAMP” (
“CITY” VARCHAR(15),
“AD_BUDGET” REAL,
“SALES” REAL)
Example: B-3 BIG_CHARGES
CREATE TABLE "BIG_CHARGES" (
"CUSTID" CHAR(10) NOT NULL,
"CHARGE_AMT" DEC(9,2) NOT NULL)
Example: B-4 CUST
CREATE TABLE "CUST" (
"CUSTID" CHAR(10) NOT NULL,
"MARITAL_STATUS" CHAR(1),
"INCOME_RANGE" INTEGER NOT NULL,
"ZIPCODE" INTEGER,
"RESIDENCE" VARCHAR(5))
Example: B-5 CUST_DATA
CREATE TABLE "CUST_DATA" (
"CUSTID" INTEGER NOT NULL,
"PURCHASES" DEC(9,2) NOT NULL)
Example: B-6 CUSTTRANS
CREATE TABLE "CUSTTRANS" (
"CUSTID" CHAR(10) NOT NULL,
"CHARGE_AMT" DEC(9,2) NOT NULL,
"DATE" DATE NOT NULL)
Example: B-7 EMPLOYEE
CREATE TABLE "EMPLOYEE" (
"EMPNO" CHAR(6) NOT NULL,
"FIRSTNME" VARCHAR(12) NOT NULL,
Appendix B. Tables used in the examples 237
"MIDINIT" CHAR(1) NOT NULL,
"LASTNAME" VARCHAR(15) NOT NULL,
"WORKDEPT" CHAR(3),
"PHONENO” CHAR(4),
"HIREDATE" DATE,
"JOB" CHAR(8),
"EDLEVEL" SMALLINT NOT NULL,
"SEX" CHAR(1),
"BIRTHDATE" DATE,
"SALARY" DEC(9,2),
"BONUS" DEC(9,2),
"COMM" DEC(9,2))
Example: B-8 EVENT
CREATE TABLE "EVENT" (
"EVENT_NAME" CHAR(15),
"EVENT_DATE" DATE,
"ATHLETE" CHAR(8),
"COUNTRY" CHAR(15),
"SCORE" DECIMAL(3,1))
Example: B-9 FACT_TABLE
CREATE TABLE "FACT_TABLE" (
"CITY_ID" INTEGER NOT NULL,
"PRODUCT_KEY" INTEGER NOT NULL,
"TIME_ID" INTEGER NOT NULL,
"SCENARIO_ID" INTEGER NOT NULL,
"TRANSDATE" DATE,
"SALES" INTEGER,
"COGS" INTEGER,
"MARKETING" INTEGER,
"MISC" INTEGER,
"PAYROLL" INTEGER,
"OPENING_INVENTORY" INTEGER,
"ADDITIONS" INTEGER,
"ENDING_INVENTORY" INTEGER)
Example: B-10 FEB_SALES
CREATE TABLE "FEB_SALES" (
"CITY" VARCHAR(15),
"BRANCH_ID" INTEGER,
"SALES" INTEGER)
Example: B-11 LC_PURCHASES
CREATE TABLE “LC_PURCHASES” (
238 High-Function Business Intelligence in e-business
“CARDNO” CHAR(12) NOT NULL,
“COFFEE“ DEC(7,2),
“BEER” DEC(7,2),
“SNACKS” DEC(7,2),
“BREAD” DEC(7,2),
“READY_MEALS” DEC(7,2),
“MILK” DEC(7,2))
Example: B-12 LOC
CREATE TABLE “LOC” (
“LOCID” CHAR(10) NOT NULL,
“CITY” VARCHAR(10), STATE CHAR(2),
“COUNTRY” VARCHAR(10))
Example: B-13 LOOKUP_MARKET
CREATE TABLE "LOOKUP_MARKET" (
"REGION" VARCHAR(50),
"REGION_TYPE_ID" INTEGER,
"STATE" VARCHAR(50),
"STATE_TYPE_ID" INTEGER,
"CITY_ID" INTEGER NOT NULL,
"CITY" VARCHAR(50),
"SIZE_ID" INTEGER,
"POPULATION" INTEGER)
Example: B-14 PRICING
CREATE “TABLE PRICING” (
“STORE” CHAR(15) NOT NULL,
“ITEM” CHAR(10),
“COST“ DEC(7,2),
“PRICE” DEC(7,2))
Example: B-15 PROD
CREATE TABLE "PROD" (
"PRODID" CHAR(10) NOT NULL,
"PROD_TYPE" INTEGER,
"PROFIT" DECIMAL(5,2),
"PROD_NAME" CHAR(10))
Example: B-16 PROD_OWNED
CREATE TABLE "PROD_OWNED" (
"PRODID" CHAR(10),
"PROD_TYPE" INTEGER,
"OPEN_DATE" DATE,
"CUSTID" CHAR(10),
Appendix B. Tables used in the examples 239
"BRANCH_ID" INTEGER,
"CITY" VARCHAR(15))
Example: B-17 SALES
CREATE TABLE "SALES" (
"SALES_DATE" DATE NOT NULL,
"SALES_PERSON" VARCHAR(15) NOT NULL,
"REGION" VARCHAR(15) NOT NULL,
"SALES" INTEGER)
Example: B-18 SALES_DTL
CREATE TABLE "SALES_DTL" (
"CITY" VARCHAR(15),
"BRANCH_ID" INTEGER,
"PRODID" CHAR(10),
"QTY" INTEGER,
"DATE_SOLD" DATE)
Example: B-19 SEEDINGS
CREATE TABLE “SEEDINGS” (
“PLAYER” CHAR(20) NOT NULL,
“W_RANKING” SMALLINT NOT NULL,
“T_1” SMALLINT,
“T_2” SMALLINT,
“T_3” SMALLINT,
“T_4” SMALLINT,
“T_5” SMALLINT)
Example: B-20 STOCKTAB
CREATE TABLE “STOCKTAB” (
“DATE” DATE,
“SYMBOL” CHAR(5),
“CLOSE_PRICE” DECIMAL(8,3))
Example: B-21 SURVEY
CREATE TABLE "SURVEY" (
"CUSTID" INTEGER,
"PROD_NAME" VARCHAR(10),
"CITY" VARCHAR(10))
Example: B-22 SURVEY_MORTG
CREATE TABLE "SURVEY_MORTG" (
"CITY" VARCHAR(10),
240 High-Function Business Intelligence in e-business
"BRANCH" INTEGER,
"MORTG_PREF" INTEGER)
Example: B-23 TRAFFIC_DATA
CREATE TABLE "TRAFFIC_DATA" (
"HITS" INTEGER,
"DAYS" INTEGER)
Example: B-24 T
CREATE TABLE “T” (
“SALES” DEC(9,2),
“AD_BUDGET” DEC(9,2))
Example: B-25 TRANS
CREATE TABLE “TRANS” (
“TRANSID” CHAR(10) NOT NULL,
“ACCTID” CHAR(10) NOT NULL,
“PDATE” DATE NOT NULL,
“STATUS” VARCHAR(15),
“LOCID” CHAR(10) NOT NULL)
Example: B-26 TRANSACTIONS
CREATE TABLE “TRANSACTIONS” (
“STORE” CHAR(15) NOT NULL,
“QUARTER” CHAR(2) NOT NULL,
“ITEM” CHAR(10) NOT NULL,
“SALES” INT NOT NULL)
Example: B-27 TRANSITEM
CREATE TABLE “TRANSITEM” (
“TRANSITEMID” CHAR(10) NOT NULL,
“TRANSID” CHAR(10) NOT NULL,
“AMOUNT” DECIMAL(10,2) NOT NULL,
“PGID” CHAR(10) NOT NULL)
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset