----------------------------------------------------------------
--                                                              
--  File:   Create_Tables_Growing.SQL                           
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
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 growing tables
--
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'CASH_TRANSACTION' )
    DROP TABLE CASH_TRANSACTION
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'HOLDING' )
    DROP TABLE HOLDING
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'HOLDING_HISTORY' )
    DROP TABLE HOLDING_HISTORY
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'HOLDING_SUMMARY' )
    DROP TABLE HOLDING_SUMMARY
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'SETTLEMENT' )
    DROP TABLE SETTLEMENT
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TRADE' )
    DROP TABLE TRADE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TRADE_HISTORY' )
    DROP TABLE TRADE_HISTORY
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TRADE_REQUEST' )
    DROP TABLE TRADE_REQUEST
GO

--
--  Create the TPC-E growing tables
--
CREATE TABLE CASH_TRANSACTION
(
    CT_T_ID     bigint          NOT NULL,
    CT_DTS      datetime,
    CT_AMT      numeric(10,2)   NOT NULL,
    CT_NAME     varchar(100)    NULL
) ON growing_fg
GO

CREATE TABLE HOLDING
(
    H_T_ID      bigint          NOT NULL,
    H_CA_ID     bigint          NOT NULL,
    H_S_SYMB    varchar(15),
    H_DTS       datetime,
    H_PRICE     numeric(8,2)    NOT NULL,
    H_QTY       numeric(6)      NOT NULL,

    CONSTRAINT  CK_H_PRICE
	            CHECK (H_PRICE > 0)

) ON growing_fg
GO

CREATE TABLE HOLDING_HISTORY
(
    HH_H_T_ID       bigint      NOT NULL,
    HH_T_ID         bigint      NOT NULL,
    HH_BEFORE_QTY   numeric(6)  NOT NULL,
    HH_AFTER_QTY    numeric(6)  NOT NULL
) ON growing_fg
GO

CREATE TABLE HOLDING_SUMMARY
(
    HS_CA_ID         bigint         NOT NULL,
    HS_S_SYMB        varchar(15),
    HS_QTY           numeric(6)     NOT NULL
) ON growing_fg
GO

CREATE TABLE SETTLEMENT
(
    SE_T_ID             bigint          NOT NULL,
    SE_CASH_TYPE        varchar(40),
    SE_CASH_DUE_DATE    date,
    SE_AMT              numeric(10,2)   NOT NULL
) ON growing_fg
GO

CREATE TABLE TRADE
(
    T_ID            bigint          NOT NULL,
    T_DTS           datetime,
    T_ST_ID         char(4),
    T_TT_ID         char(3),
    T_IS_CASH       tinyint         NOT NULL,
    T_S_SYMB        varchar(15),
    T_QTY           numeric(6)      NOT NULL,
    T_BID_PRICE     numeric(8,2)    NOT NULL,
    T_CA_ID         bigint          NOT NULL,
    T_EXEC_NAME     varchar(49),
    T_TRADE_PRICE   numeric(8,2)    NULL,
    T_CHRG          numeric(10,2)   NOT NULL,
    T_COMM          numeric(10,2)   NOT NULL,
    T_TAX           numeric(10,2)   NOT NULL,
    T_LIFO          tinyint         NOT NULL,

    CONSTRAINT      CK_T_IS_CASH
	                CHECK (T_IS_CASH in (0, 1)),

    CONSTRAINT      CK_T_LIFO
	                CHECK (T_LIFO in (0, 1)),

    CONSTRAINT      CK_T_BID_PRICE
	                CHECK (T_BID_PRICE > 0),

    CONSTRAINT      CK_T_TAX
                    CHECK (T_TAX >= 0),

    CONSTRAINT      CK_T_COMM
	                CHECK (T_COMM >= 0),

    CONSTRAINT      CK_T_CHRG
	                CHECK (T_CHRG >= 0),

    CONSTRAINT      CK_T_QTY
	                CHECK (T_QTY > 0)

) ON growing_fg
GO

CREATE TABLE TRADE_HISTORY
(
    TH_T_ID     bigint      NOT NULL,
    TH_DTS      datetime,
    TH_ST_ID    char(4)
) ON growing_fg
GO

CREATE TABLE TRADE_REQUEST
(
    TR_T_ID         bigint          NOT NULL,
    TR_TT_ID        char(3),
    TR_S_SYMB       varchar(15),
    TR_QTY          numeric(6)      NOT NULL,
    TR_BID_PRICE    numeric(8,2)    NOT NULL,
    TR_B_ID         bigint          NOT NULL,

    CONSTRAINT      CK_TR_BID_PRICE
	                CHECK (TR_BID_PRICE > 0),

    CONSTRAINT      CK_TR_QTY
	                CHECK (TR_QTY > 0)

) ON growing_fg
GO

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