----------------------------------------------------------------
--                                                              
--  File:   Create_Tables_Fixed.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 fixed tables
--
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'CHARGE' )
    DROP TABLE CHARGE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'COMMISSION_RATE' )
    DROP TABLE COMMISSION_RATE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'EXCHANGE' )
    DROP TABLE EXCHANGE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'INDUSTRY' )
    DROP TABLE INDUSTRY
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'SECTOR' )
    DROP TABLE SECTOR
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'STATUS_TYPE' )
    DROP TABLE STATUS_TYPE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TAXRATE' )
    DROP TABLE TAXRATE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TRADE_TYPE' )
    DROP TABLE TRADE_TYPE
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'ZIP_CODE' )
    DROP TABLE ZIP_CODE
GO

--
--  Create the fixed TPC-E tables
--
CREATE TABLE CHARGE
(
    CH_TT_ID    char(3),
    CH_C_TIER   tinyint,
    CH_CHRG     numeric(10,2)   NOT NULL,

    CONSTRAINT  CK_CH_CHRG
                CHECK (CH_CHRG >= 0),

    CONSTRAINT  CK_CH_C_TIER
                CHECK (CH_C_TIER in (1,2,3))

) ON fixed_fg
GO

CREATE TABLE COMMISSION_RATE
(
    CR_C_TIER       tinyint,
    CR_TT_ID        char(3),
    CR_EX_ID        char(6),
    CR_FROM_QTY     numeric(6)      NOT NULL,
    CR_TO_QTY       numeric(6)      NOT NULL,
    CR_RATE         numeric(5,2),

    CONSTRAINT      CK_CR_C_TIER
	                CHECK (CR_C_TIER in (1,2,3)),

    CONSTRAINT      CK_CR_FROM_QTY
	                CHECK (CR_FROM_QTY >= 0),

    CONSTRAINT      CK_CR_TO_QTY
	                CHECK (CR_TO_QTY > CR_FROM_QTY),

    CONSTRAINT      CK_CR_RATE
	                CHECK (CR_RATE >= 0)

) ON fixed_fg
GO

CREATE TABLE EXCHANGE
(
    EX_ID       char(6),
    EX_NAME     char(100),
    EX_NUM_SYMB numeric(6),
    EX_OPEN     smallint,
    EX_CLOSE    smallint,
    EX_DESC     char(150)   NULL,
    EX_AD_ID    bigint      NOT NULL
) ON fixed_fg
GO

CREATE TABLE INDUSTRY
(
    IN_ID    char(2),
    IN_NAME  char(50),
    IN_SC_ID char(2)
) ON fixed_fg
GO

CREATE TABLE SECTOR
(
    SC_ID   char(2),
    SC_NAME char(30)
) ON fixed_fg
GO

CREATE TABLE STATUS_TYPE
(
    ST_ID   char(4),
    ST_NAME char(10)
) ON fixed_fg
GO

CREATE TABLE TAXRATE
(
    TX_ID       char(4),
    TX_NAME     char(50),
    TX_RATE     numeric(6,5),

    CONSTRAINT  CK_TX_RATE
	            CHECK (TX_RATE >= 0)

) ON fixed_fg
GO

CREATE TABLE TRADE_TYPE
(
    TT_ID       char(3),
    TT_NAME     char(12),
    TT_IS_SELL  tinyint    NOT NULL,
    TT_IS_MRKT  tinyint    NOT NULL,

    CONSTRAINT  CK_TRADE_TYPE_IS_SELL
	            CHECK (TT_IS_SELL in (0, 1)),

    CONSTRAINT  CK_TRADE_TYPE_IS_MRKT
	            CHECK (TT_IS_MRKT in (0, 1))

) ON fixed_fg
GO

CREATE TABLE ZIP_CODE
(
    ZC_CODE     varchar(12),
    ZC_TOWN     varchar(80),
    ZC_DIV      varchar(80)
) ON fixed_fg
GO

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