------------------------------------------------------------------
--                                                              --
--  File:   Create_Tables_Scaling_Flat.SQL                      --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1022      --
--          Copyright Microsoft  2008, 2009, 2010               --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO

---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_OFF ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

--
--  Remove all existing TPC-E scaling tables
--
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'ACCOUNT_PERMISSION' )
    DROP TABLE ACCOUNT_PERMISSION
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'ADDRESS' )
    DROP TABLE ADDRESS
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'BROKER' )
    DROP TABLE BROKER
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'COMPANY' )
    DROP TABLE COMPANY
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'COMPANY_COMPETITOR' )
    DROP TABLE COMPANY_COMPETITOR
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'CUSTOMER' )
    DROP TABLE CUSTOMER
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'CUSTOMER_ACCOUNT' )
    DROP TABLE CUSTOMER_ACCOUNT
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'CUSTOMER_TAXRATE' )
    DROP TABLE CUSTOMER_TAXRATE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'DAILY_MARKET' )
    DROP TABLE DAILY_MARKET
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'FINANCIAL' )
    DROP TABLE FINANCIAL
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'LAST_TRADE' )
    DROP TABLE LAST_TRADE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'NEWS_ITEM_TEMP' )
    DROP TABLE NEWS_ITEM_TEMP
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'NEWS_ITEM' )
    DROP TABLE NEWS_ITEM
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'NEWS_XREF' )
    DROP TABLE NEWS_XREF
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'SECURITY' )
    DROP TABLE SECURITY
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'WATCH_ITEM' )
    DROP TABLE WATCH_ITEM
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'WATCH_LIST' )
    DROP TABLE WATCH_LIST
GO

--
--  Create the TPC-E scaling tables
--
CREATE TABLE ACCOUNT_PERMISSION
(
    AP_CA_ID    IDENT_T         NOT NULL,
    AP_ACL      char(4),
    AP_TAX_ID   varchar(20),
    AP_L_NAME   varchar(25),
    AP_F_NAME   varchar(20)
) ON tpce_fg
GO

CREATE TABLE ADDRESS
(
    AD_ID       IDENT_T         NOT NULL,
    AD_LINE1    varchar(80)     NULL,
    AD_LINE2    varchar(80)     NULL    DEFAULT '',
    AD_ZC_CODE  varchar(12),
    AD_CTRY     varchar(80)     NULL
) ON tpce_fg
GO

CREATE TABLE BROKER
(
    B_ID            IDENT_T         NOT NULL,
    B_ST_ID         char(4),
    B_NAME          varchar(49),
    B_NUM_TRADES    numeric(9,0),
    B_COMM_TOTAL    BALANCE_T       NOT NULL
) ON tpce_fg
GO

CREATE TABLE COMPANY
(
    CO_ID           IDENT_T         NOT NULL,
    CO_ST_ID        char(4),
    CO_NAME         varchar(60),
    CO_IN_ID        char(2),
    CO_SP_RATE      char(4),
    CO_CEO          varchar(46),
    CO_AD_ID        IDENT_T         NOT NULL,
    CO_DESC         varchar(150),
    CO_OPEN_DATE    date
) ON tpce_fg
GO

CREATE TABLE COMPANY_COMPETITOR
(
    CP_CO_ID        IDENT_T         NOT NULL,
    CP_COMP_CO_ID   IDENT_T         NOT NULL,
    CP_IN_ID        char(2)
) ON tpce_fg
GO

CREATE TABLE CUSTOMER
(
    C_ID        IDENT_T         NOT NULL,
    C_TAX_ID    varchar(20),
    C_ST_ID     char(4),
    C_L_NAME    varchar(25),
    C_F_NAME    varchar(20),
    C_M_NAME    char(1)         NULL,
    C_GNDR      char(1)         NULL,
    C_TIER      tinyint,
    C_DOB       date,
    C_AD_ID     IDENT_T         NOT NULL,
    C_CTRY_1    char(3)         NULL,
    C_AREA_1    char(3)         NULL,
    C_LOCAL_1   varchar(10)     NULL,
    C_EXT_1     varchar(5)      NULL    DEFAULT '',
    C_CTRY_2    char(3)         NULL,
    C_AREA_2    char(3)         NULL,
    C_LOCAL_2   varchar(10)     NULL,
    C_EXT_2     varchar(5)      NULL    DEFAULT '',
    C_CTRY_3    char(3)         NULL,
    C_AREA_3    char(3)         NULL,
    C_LOCAL_3   varchar(10)     NULL,
    C_EXT_3     varchar(5)      NULL    DEFAULT '',
    C_EMAIL_1   varchar(50)     NULL,
    C_EMAIL_2   varchar(50)     NULL,

    CONSTRAINT  CK_C_TIER
	            CHECK (C_TIER in (1,2,3)),
    
    CONSTRAINT  CK_C_GNDR
	            CHECK (C_GNDR in ('M', 'F'))

) ON tpce_fg
GO

CREATE TABLE CUSTOMER_ACCOUNT
(
    CA_ID       IDENT_T         NOT NULL,
    CA_B_ID     IDENT_T         NOT NULL,
    CA_C_ID     IDENT_T         NOT NULL,
    CA_NAME     varchar(50)     NULL,
    CA_TAX_ST   tinyint,
    CA_BAL      BALANCE_T       NOT NULL,

    CONSTRAINT  CK_CA_TAX_ST
	            CHECK (CA_TAX_ST in (0,1,2))

) ON tpce_fg
GO

CREATE TABLE CUSTOMER_TAXRATE
(
    CX_TX_ID    char(4),
    CX_C_ID     IDENT_T         NOT NULL
) ON tpce_fg
GO

CREATE TABLE DAILY_MARKET
(
    DM_DATE     date,
    DM_S_SYMB   varchar(15),
    DM_CLOSE    S_PRICE_T       NOT NULL,
    DM_HIGH     S_PRICE_T       NOT NULL,
    DM_LOW      S_PRICE_T       NOT NULL,
    DM_VOL      S_COUNT_T       NOT NULL
) ON tpce_fg
GO

CREATE TABLE FINANCIAL
(
    FI_CO_ID            IDENT_T         NOT NULL,
    FI_YEAR             smallint,
    FI_QTR              tinyint,
    FI_QTR_START_DATE   date,
    FI_REVENUE          FIN_AGG_T       NOT NULL,
    FI_NET_EARN         FIN_AGG_T       NOT NULL,
    FI_BASIC_EPS        VALUE_T         NOT NULL,
    FI_DILUT_EPS        VALUE_T         NOT NULL,
    FI_MARGIN           VALUE_T         NOT NULL,
    FI_INVENTORY        FIN_AGG_T       NOT NULL,
    FI_ASSETS           FIN_AGG_T       NOT NULL,
    FI_LIABILITY        FIN_AGG_T       NOT NULL,
    FI_OUT_BASIC        S_COUNT_T       NOT NULL,
    FI_OUT_DILUT        S_COUNT_T       NOT NULL,

    CONSTRAINT          CK_FINANCIAL_QTR
	                    CHECK (FI_QTR in (1,2,3,4))

) ON tpce_fg
GO

CREATE TABLE LAST_TRADE
(
    LT_S_SYMB       varchar(15),
    LT_DTS          datetime,
    LT_PRICE        S_PRICE_T         NOT NULL,
    LT_OPEN_PRICE   S_PRICE_T         NOT NULL,
    LT_VOL          S_COUNT_T         NOT NULL
) ON tpce_fg
GO

CREATE TABLE NEWS_ITEM
(
    NI_ID       IDENT_T         NOT NULL,
    NI_HEADLINE char(80),
    NI_SUMMARY  char(255),
    NI_ITEM     BLOB            NOT NULL,
    NI_DTS      datetime,
    NI_SOURCE   varchar(30),
    NI_AUTHOR   varchar(30)     NULL
) ON tpce_fg
GO

---
--- The NEWS_ITEM_TEMP table is used to facilitate
--- the conversion of the NI_ITEM flat file data, 
--- which is character and the NI_ITEM column that
--- is defined as a BLOB/varbinary(max) in the
--- TPC-E specification
---
CREATE TABLE NEWS_ITEM_TEMP
(
    NI_ID       IDENT_T         NOT NULL,
    NI_HEADLINE char(80),
    NI_SUMMARY  char(255),
    NI_ITEM     varchar(max)    NOT NULL,
    NI_DTS      datetime,
    NI_SOURCE   varchar(30),
    NI_AUTHOR   varchar(30)     NULL
) ON tpce_fg
GO

CREATE TABLE NEWS_XREF
(
    NX_NI_ID    IDENT_T         NOT NULL,
    NX_CO_ID    IDENT_T         NOT NULL
) ON tpce_fg
GO

CREATE TABLE SECURITY
(
    S_SYMB              varchar(15),
    S_ISSUE             char(6),
    S_ST_ID             char(4),
    S_NAME              varchar(70),
    S_EX_ID             char(6),
    S_CO_ID             IDENT_T         NOT NULL,
    S_NUM_OUT           S_COUNT_T       NOT NULL,
    S_START_DATE        date,
    S_EXCH_DATE         date,
    S_PE                VALUE_T         NOT NULL,
    S_52WK_HIGH         S_PRICE_T       NOT NULL,
    S_52WK_HIGH_DATE    date,
    S_52WK_LOW          S_PRICE_T       NOT NULL,
    S_52WK_LOW_DATE     date            NOT NULL,
    S_DIVIDEND          VALUE_T         NOT NULL,
    S_YIELD             numeric(5,2)
) ON tpce_fg
GO

CREATE TABLE WATCH_ITEM
(
    WI_WL_ID    IDENT_T             NOT NULL,
    WI_S_SYMB   varchar(15)
) ON tpce_fg
GO

CREATE TABLE WATCH_LIST
(
    WL_ID       IDENT_T             NOT NULL,
    WL_C_ID     IDENT_T             NOT NULL
) ON tpce_fg
GO

SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO
