HH_H_T_ID TRADE_T Not Null PK+ FK (T_) Trade Identifier of the trade that originally created the holding row. This is a Foreign Key to the TRADE table rather than the HOLDING table because the HOLDING row could be deleted. HH_T_ID TRADE_T Not Null PK+ FK (T_) Identifier of the current trade (the one that last inserted, updated or deleted the holding identified by HH_H_T_ID). HH_ BEFORE_QTY S_QTY_T Not Null Quantity of this security held before the modifying On initial insertion, HH_BEFORE_QTY is 0. HH_ AFTER_QTY S_QTY_T Not Null Quantity of this security held after the modifying trade. If the HOLDING row gets deleted by the modifying trade, then HH_AFTER_QTY is 0. HOLDING_SUMMARY The table contains aggregate information about the customer account’s security holdings. Table Prefix: HS_ Column Name Data Type Constraints Relations Description HS_CA_ID IDENT_T Not Null PK+ FK (CA_) Customer account identifier. HS_S_SYMB CHAR(15) Not Null PK+ FK (S_) Symbol for the security held. HS_ QTY S_QTY_T Not Null Total quantity of this security held. Comment: HOLDING_SUMMARY may be implemented as a view on HOLDING, in case the HOLDING Foreign Key references to HOLDING_SUMMARY are automatically met. However, the HOLDING_SUMMARY Foreign Key references to CA_ and S_ must then be adopted and met by HOLDING. WATCH_ITEM The table contains list of securities to watch for a watch list. Prefix: WI_ Column Name Data Type Constraints Relations Description WI_WL_ID IDENT_T Not Null PK+ FK (WL_) list identifier. WI_S_SYMB CHAR(15) Not Null PK+ FK (S_) Symbol of the security to WATCH_LIST The table contains information about the customer who created this watch list. Table Prefix: WL_
TRADE The table contains information about trades. Prefix: T_ Column Name Data Type Constraints Relations Description T_ID TRADE_T Not Null PK Trade identifier. T_DTS DATETIME Not Null Date and time of trade. T_ST_ID CHAR(4) Not Null FK (ST_) Status type identifier; identifies the status of this trade. T_TT_ID CHAR(3) Not Null FK (TT_) Trade type identifier; identifies the type of his trade. T_IS_CASH BOOLEAN Not Null in 0, 1 Is this trade a cash (1) or margin (0) trade? T_S_SYMB CHAR(15) Not Null FK (S_) Security symbol of the security that was traded. T_QTY S_QTY_T Not Null >0 Quantity of securities traded. T_BID_PRICE S_PRICE_T Not Null > 0 The requested unit price. T_CA_ID IDENT_T Not Null FK (CA_) Customer account identifier. T_EXEC_NAME CHAR(49) Not Null Name of the person executing the trade. T_TRADE_PRICE S_PRICE_T Unit price at which the security was traded. T_CHRG VALUE_T Not Null >= 0 Fee charged for placing this trade request. T_COMM VALUE_T Not Null >= 0 Commission earned on this trade; may be zero. T_TAX VALUE_T Not Null >= 0 Amount of tax due on this trade; can be zero. Whether the tax is withheld from the settlement amount depends on the customer account tax status. T_LIFO BOOLEAN Not Null in 0, 1 If this trade is closing an existing position, is it executed against the account holdings of this security (1=LIFO) or against the oldest-to-newest account holdings (0=FIFO). TRADE_HISTORY The contains the history of each trade transaction through the various states. Table Prefix: TH_ Column Name Data Type Constraints Relations Description TH_T_ID TRADE_T Not Null PK+ FK (T_) identifier. This value will be used for the corresponding T_ID in the TRADE and SE_T_ID in the SETTLEMENT table if this trade request results in a trade. oldest
FINANCIAL The table contains information about a company's quarterly financial reports. VGenLoader will load this table with financial information for each company for the Quarters starting 1 January 2000 and ending with the quarter that starts 1 October 2004. Prefix: FI_ Column Name Data Type Constraints Relations Description FI_CO_ID IDENT_T Not Null PK+ FK (CO_) Company identifier. FI_YEAR NUM(4) Not Null PK+ Year of the quarter end. FI_QTR NUM(1) Not Null in 1,2,3,4 PK+ Quarter number that the financial information is for: valid values 1, 2, 3, 4. FI_QTR_START_DATE DATE Not Null date of quarter. FI_REVENUE FIN_AGG_T Not Null Reported revenue for the quarter. FI_NET_EARN FIN_AGG_T Not Null Net earnings reported for the quarter. FI_BASIC_EPS VALUE_T Not Null Basic earnings per share reported for the quarter. FI_DILUT_EPS VALUE_T Not Null Diluted earnings per share reported for the quarter. FI_MARGIN VALUE_T Not Null Profit divided by revenues for the quarter. FI_INVENTORY FIN_AGG_T Not Null Value of inventory on hand at the end of the quarter. FI_ASSETS FIN_AGG_T Not Null Value of total assets at the end of the quarter. FI_LIABILITY FIN_AGG_T Not Null Value of total liabilities at the end of the quarter. FI_OUT_BASIC S_COUNT_T Not Null Average number of common shares outstanding (basic). FI_OUT_DILUT S_COUNT_T Not Null Average number of common shares outstanding (diluted). INDUSTRY The table contains information about industries. Used to categorize which industries a company is in. Table Prefix: IN_ Column Name Data Type Constraints Relations Description IN_ID CHAR(2) Not Null PK Industry identifier. IN_NAME CHAR(50) Not Null Industry name. Examples: "Air Travel", "Air Cargo", "Software", "Consumer Banking", "Merchant Banking", etc. IN_SC_ID CHAR(2) Not Null FK (SC_) Sector identifier of the sector the industry is in.
SECTOR The table contains information about market sectors. Table Prefix: SC_ Column Name Data Type Constraints Relations Description CHAR(2) Not Null PK Sector identifier. SC_NAME CHAR(30) Not Null Sector name. Examples: “Energy”, “Materials”, “Industrials”, “Health Care, etc. SECURITY This table contains information about each security traded on any of the exchanges. Table Prefix: S_ Column Name Data Type Constraints Relations Description S_SYMB CHAR(15) Not Null PK Security symbol used to identify the security on "ticker". S_ISSUE CHAR(6) Not Null Security type. Example: "COMMON", "PERF_A", "PERF_B", etc. S_ST_ID CHAR(4) Not Null FK (ST_) Security status type identifier. Identifies if this security is active or not. S_NAME CHAR(70) Not Null Security name. S_EX_ID CHAR(6) Not Null FK (EX_) Exchange identifier of the exchange the security is traded on. S_CO_ID IDENT_T Not Null FK (CO_) Company identifier of the company this security is issued by. S_NUM_OUT S_COUNT_T Not Null Number of shares outstanding for this security. S_START_DATE DATE Not Null Date security first started trading. S_EXCH_DATE DATE Not Null Date security first started trading on this exchange. S_PE VALUE_T Not Null Current share price to earnings per share ratio. S_52WK_HIGH S_PRICE_T Not Null Security share price 52-week high. S_52WK_HIGH_DATE DATE Not Null Date of security share price 52-week high. S_52WK_LOW S_PRICE_T Not Null Security share price 52-week low. S_52WK_LOW_DATE DATE Not Null Date of security share price 52-week low. S_DIVIDEND VALUE_T Not Null Annual Dividend per share amount. May be zero, is not allowed to be negative.
The following rules are used by VGenLoader to calculate the cardinalities of the Scaling Tables and Growing Tables. The VGen package uses random number generators to set the number of rows for relationships such as securities per account and, as a result, the cardinality of some TPCxV tables can only be approximated. Variable Used Rule ACCOUNT_PERMISSION accounts 60% have just the customer as the executor 38% have the customer and 1 other executor 2% have the customer and 2 other executors Avg. is ~1.42 * accounts ADDRESS customers companies(5,000) + EXCHANGE(4) + customers BROKER customers 0.01 * customers (or 1 broker per 100 customers) CASH_TRANSACTION settled ~0.92 * settled (84% of buys and 100% of sells are cash) COMPANY companies 1 * companies COMPANY_COMPETITOR companies 3 * companies CUSTOMER_ACCOUNT customers 5 * customers CUSTOMER_TAXRATE customers 2 * customers DAILY_MARKET securities securities * 1,305 (5 years of 5-day work weeks with two leap years) FINANCIAL companies companies * 20 quarters (5 years) HOLDING settled ~0.07955 * settled (assumes ITD = 125 and SF = 500) HOLDING_HISTORY settled ~1.3331 * settled (assumes ITD = 125 and SF = 500) HOLDING_SUMMARY accounts ~ 9.9234 * accounts (assumes ITD = 125 and SF = 500) LAST_TRADE securities 1 * securities NEWS_ITEM companies 2 * companies NEWS_XREF companies 2 * companies SECURITY customers 1 * Securities SETTLEMENT settled 1 * settled TRADE customers 7200 * customers = ((ITD * 8 * 3600) / SF) * customers TRADE_HISTORY settled ~((2 rows per market trade) * 0.6) + ((3 rows per limit trade) * 0.4) Average is (2.4 * settled) TRADE_REQUEST 0 WATCH_LIST customers Each customer has one watch list (1 * customers) WATCH_ITEM customers Average=100 items per watch list * customers The following list contains the cardinality of Fixed Tables. Fixed Tables Cardinality Cardinality Formula CHARGE 15 5 trade types * 3 customer tiers COMMISSION_RATE 240 4 rates * 4 exchanges * 5 trade types * 3 customer tiers COMPANY 5,000 5,000 companies
Maximum number of rows to return for Frame 1 TradeLookupFrame1MaxRows 20 MiscConsts.h Maximum number of TRADE rows to return for Frame 2 TradeLookupFrame2MaxRows 20 MiscConsts.h Maximum number of TRADE rows to return for Frame 3 TradeLookupFrame3MaxRows 20 MiscConsts.h Maximum number of TRADE _HISTORY rows to return TradeLookupMaxTradeHistoryRowsReturned 3 MiscConsts.h Trade-Status Maximum number of status rows to return max_trade_status_len 50 TxnHarnessStructs.h Maximum number of TRADE rows to return for Transaction TradeUpdateMaxRows 20 MiscConsts.h Maximum number of rows to return for Frame 1 TradeUpdateFrame1MaxRows 20 MiscConsts.h Maximum number of TRADE rows to return for Frame 2 TradeUpdateFrame2MaxRows 20 MiscConsts.h Maximum number of TRADE rows to return for Frame 3 TradeUpdateFrame3MaxRows 20 MiscConsts.h Maximum number of _HISTORY rows to return TradeUpdateMaxTradeHistoryRowsReturned 3 MiscConsts.h All data exchanges between Frames must be done by the VGenTxnHarness through its use of input and output parameters passed in and out of the Frames. Comment 1: The intent of this clause is to prevent the Frames from using global variables, or other means, for storing and retrieving information across multiple invocations of the same or different Frames in order to avoid work intended to be done during each individual invocation. Comment 2: The Test Sponsor may augment each Frame with code to unpack the input parameters received from the VGenTxnHarness and to pack the output parameters returned to the VGenTxnHarness.
Broker-Volume Transaction Parameters The inputs to the Broker-Volume Transaction are generated by the VGenDriverCE code in CETxnInputGenerator.cpp and the data structures defined in TxnHarnessStructs.h must be used to communicate the input and output parameters. Broker-Volume Interfaces Module/Data Structure CE Input generation GenerateBrokerVolumeInput() Transaction Input/Output Structure TBrokerVolumeTxnInput TBrokerVolumeTxnOutput Frame 1 Input/Output Structure TBrokerVolumeTxnInput TBrokerVolumeFrame1Output Broker-Volume Transaction Parameters: Parameter Direction Description broker_list[ ] IN A list of twenty to forty distinct broker name strings as defined by B_NAME in BROKER table. Names are randomly selected from the broker range, with uniform distribution. The list size is determined by the first null input name in the broker_list array. sector_name IN A randomly selected sector name string as defined in SC_NAME in SECTOR table using uniform distribution. list_len OUT Number of in the list being returned. status OUT Code indicating the execution status for this transaction. volume[ ] OUT A list of numbers, sorted in descending order, representing the sum of all trade request values (TR_QTY * TR_BID_PRICE) in the TRADE_REQUEST table for stocks in a given sector grouped by broker names provided by broker_list. The list size is determined by list_len parameter. Broker-Volume Transaction Database Footprint This Transaction is read-only and makes no changes to the database. The Broker-Volume Database Footprint is as follows: Broker-Volume Database Footprint Table Column Frame 1 BROKER B_NAME Return TRADE_REQUEST TR_BID_PRICE Reference TR_QTY Reference Transaction Control Commit Broker Volume Transaction Frame 1 of 1 The database access methods used in Frame 1 are all Returns. The VGenTxnHarness controls the execution of Frame 1 as follows:
symbol = S_SYMB from SECURITY where S_CO_ID = co_id and S_ISSUE = issue } else { select co_id = S_CO_ID, exch_id = S_EX_ID, s_name = S_NAME from SECURITY where S_SYMB = symbol select co_name = CO_NAME from COMPANY where = co_id } // Get current pricing information for the security select market_price = LT_PRICE from LAST_TRADE where LT_S_SYMB = symbol // Set characteristics based on the type of trade. select type_is_market = TT_IS_MRKT, type_is_sell = TT_IS_SELL from TRADE_TYPE where TT_ID = trade_type_id // If this is a limit-order, then the requested_price was passed in to the frame, // but if this a market-order, then the requested_price needs to be set to the // current market price. if( type_is_market ) then {
type_id = T_TT_ID, symbol = T_S_SYMB, trade_qty = T_QTY, charge = T_CHRG, is_lifo = T_LIFO, trade_is_cash = T_IS_CASH from TRADE where T_ID = trade_id num_found = row_count select type_name = TT_NAME, type_is_sell = TT_IS_SELL, type_is_market = TT_IS_MRKT from TRADE_TYPE where TT_ID = type_id select hs_qty = HS_QTY from HOLDING_SUMMARY HS_CA_ID = acct_id and HS_S_SYMB = symbol if (hs_qty is NULL) then // no prior holdings exist hs_qty = 0 } Trade-Result Transaction Frame 2 of 7 The second Frame is responsible for modifying the customer's holdings to reflect the result of a buy or a sell trade. The database access methods used in Frame 2 are a mixture of References, Modifies, Removes and Adds. The VGenTxnHarness controls the execution of 2 as follows:
broker_id IN Broker ID, which was obtained in Frame 1. comm_amount IN The broker commission amount, computed by the VGen code st_completed_id IN The index ID value into STATUS_TYPE for “Completed” status. trade_dts IN Trade date and time provided by the output of Frame 2. trade_id IN The Trade ID for the trade to be settled passed to the transaction by the Market Exchange Emulator. trade_price IN Trade price provided by the Market Exchange Emulator. status OUT Code indicating the execution status for this frame. Trade-Result_Frame-5 Pseudo-code: Record the trade result and the broker's commission { update TRADE set T_COMM = comm_amount, T_DTS = trade_dts, T_ST_ID = st_completed_id, T_TRADE_PRICE = trade_price where T_ID = trade_id insert into TRADE_HISTORY ( TH_T_ID, TH_DTS, TH_ST_ID ) values ( trade_id, trade_dts, st_completed_id ) update BROKER set B_COMM_TOTAL = B_COMM_TOTAL + comm_amount, B_NUM_TRADES = B_NUM_TRADES + 1 where B_ID = broker_id }
trigger_id IN The Trade ID for the pending trade that has triggered and needs to be to be submitted to the MEE. Trade ID is the primary key of the TRADE table. bid_price OUT Requested bid/ask price for triggered limit trade. num_found OUT Number of rows found to trigger. status OUT Code indicating the execution status for this frame. symbol OUT Security symbol for triggered limit trade. trade_id OUT Trade ID of triggered limit trade. trade_qty OUT Requested share quantity. trade_type_id OUT type of triggered limit trade. Trade-Result_Frame-7 Pseudo-code: Submit the triggered limit trade { declare now_dts DATETIME start transaction get_current_dts(now_dts) select TR_T_ID, TR_BID_PRICE, TR_S_SYMB, TR_TT_ID, TR_QTY from TRADE_REQUEST where TR_T_ID = trigger_id num_found = row_count delete TRADE_REQUEST TR_T_ID = trigger_id update TRADE set T_DTS = now_dts, T_ST_ID = status_submitted where T_ID = trigger_id insert TRADE_HISTORY (TH_T_ID, TH_DTS, TH_ST_ID) values (trigger_id, now_dts, status_submitted)
T_DTS asc num_found = row_count num_updated = 0 // Get extra information for each trade in the trade list. for (i = 0; i < num_found; i++) { // Get settlement information // Will return only one row for each trade select settlement_amount[i] = SE_AMT, settlement_cash_due_date[i] = SE_CASH_DUE_DATE, settlement_cash_type[i] = SE_CASH_TYPE from SETTLEMENT where SE_T_ID = trade_list[i] // get cash information if this is a cash transaction // Will return only one row for each trade that was a cash transaction if (is_cash[i]) then { if (num_updated < max_updates) then { // Modify the CASH_TRANSACTION row for this trade. select ct_name = CT_NAME from CASH_TRANSACTION where CT_T_ID = trade_list[i] if (ct_name like “% shares of %”) then ct_name = type_name[i] + “ “ + quantity[i] + “ Shares of “ + s_name[i] else ct_name = type_name[i] + “ “ + quantity[i] + “ shares of “ + s_name[i] update CASH_TRANSACTION set CT_NAME = ct_name CT_T_ID = trade_list[i] num_updated = num_updated + row_count
DAILY_MARKET DM_DATE Reference * DM_S_SYMB Reference * DM_VOL Reference * Modify * EXCHANGE EX_DESC Reference * Modify * Count(*) Reference * FINANCIAL FI_CO_ID Reference * FI_QTR_START_DATE Reference * Modify * Count(*) Reference * SECURITY S_EXCH_DATE Modify * S_SYMB Reference * NEWS_ITEM NI_DTS Modify * Reference * TAXRATE TX_ID Reference * TX_NAME Reference * Modify * WATCH_ITEM WI_S_SYMB Reference * Modify * WI_WL_ID Reference * Transaction Control Commit Data-Maintenance Transaction Frame 1 of 1 The VGenTxnHarness controls the execution of Frame 1 as follows: { invoke (Data-Maintenance_Frame-1) } Data-Maintenance Frame 1 of 1 Parameters: Parameter Direction Description acct_id IN A single customer is chosen non-uniformly by customer tier, from the range of available customers. A single customer account id, as defined by CA_ID in CUSTOMER_ACCOUNT, is chosen at random, uniformly, from the range of customer account ids for the chosen customer. This input is used when table_name is “ACCOUNT_PERMISSION”, otherwise it is set to 0. c_id IN A number randomly selected from the possible customer identifiers as defined by C_ID in CUSTOMER table using a uniform distribution. This input is always used when table_name is “CUSTOMER”, or “CUSTOMER_TAXRATE”. This input (instead of co_id) is used 67% of the time when table_name is “ADDRESS”. Otherwise this input is set to 0.
// second email address to “@mindspring.com” // or “@earthlink.com”. email2 = NULL len = 0 lenMindspring = strlen(“@mindspring.com) select email2 = C_EMAIL_2 from CUSTOMER where C_ID = c_id len = strlen(email2) if ( ((len – lenMindspring) > 0) and (strcmp(substr(email2,len-lenMindspring, lenMindspring),”@mindspring.com”) == 0) ) { update CUSTOMER set C_EMAIL_2 = substring(C_EMAIL_2, 1, charindex(“@”,C_EMAIL_2) ) + ‘earthlink.com’ where C_ID = c_id } else { /* set to @mindspring.com */ update CUSTOMER set C_EMAIL_2 = substring(C_EMAIL_2, 1, charindex(“@”,C_EMAIL_2) ) + ‘mindspring.com’ where C_ID = c_id } } else if (strcmp(table_name, “CUSTOMER_TAXRATE”) == 0) { // CUSTOMER_TAXRATE // Find the customer’s current country tax rate code. // Calculate cyclically the next tax rate code for the customer’s country. // Update to the new country tax rate code. declare old_tax_rate char(3), new_tax_rate char(3), tax_num int select old_tax_rate = CX_TX_ID from CUSTOMER_TAXRATE where