----------------------------------------------------------------
--                                                              
--  File:   RI_Tests.SQL                                        
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
--  Demonstrates the referential integrity is in force after    
--  TPC-E database load                                         
--                                                              
----------------------------------------------------------------
USE tpce
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT   ' '
SELECT  'Referential Integrity Verification Starting...',
        CONVERT(CHAR(30), GETDATE(), 21)

DECLARE @num_custs      bigint,
	    @cust_id	    bigint,
        @acct_id        bigint,
        @broker_id      bigint,
        @acct_name      varchar(30),
        @tax_st         tinyint,
        @balance        numeric(12,2),
        @check_cust     tinyint

--- set the default values
SELECT  @acct_name  = 'Auditor Retirement Fund',
        @tax_st     = 0,
        @balance    = 1000000,
        @check_cust = 1

--- get the number of customers in the database
SELECT  @num_custs      = COUNT(*) FROM CUSTOMER

PRINT   ' '
PRINT   'Generating a random Customer ID for this test.'
WHILE @check_cust = 1
BEGIN
    SELECT  @check_cust As 'Customer Check Flag'

    --- select a random customer ID for use in the test
    SET     @cust_id        = CONVERT(bigint, RAND() * @num_custs) + 4300000001

    --- get a customer account ID for the selected customer ID
    SELECT  TOP 1
            @acct_id    = MAX(CA_ID) + 1
    FROM    CUSTOMER_ACCOUNT
    WHERE   CA_C_ID     = @cust_id

    --- check the customer account selected
    SELECT  @check_cust = @acct_id % 10
END

PRINT   ' '
SELECT  'Generated Customer ID:',
        @cust_id As 'Customer ID'

PRINT   ' '
SELECT  'Randomly selected Customer Account ID for the generated Customer ID:',
        @acct_id As 'Customer Account ID'

--- Prove the generated customer ID is valid
PRINT   ' '
PRINT   'Row from CUSTOMER to prove C_ID does exist:'
SELECT  C_ID,
        C_F_NAME,
        C_L_NAME
FROM    CUSTOMER
WHERE   C_ID    = @cust_id

--- get the largest broker id in the database and increment it by one to
--- make sure it is not a valid broker id
SELECT  @broker_id  = MAX(B_ID) + 1
FROM    BROKER

PRINT   ' '
SELECT  'Generated Broker ID:',
        @broker_id As 'Broker ID'

PRINT   'Row from BROKER to prove B_ID does not exist:'
SELECT  B_ID,
        B_NAME
FROM    BROKER
WHERE   B_ID = @broker_id
PRINT   ' '

--- try to insert a row into CUSTOMER ACCOUNT
--- where the CA_ID is new and the B_ID is 
--- not found in the BROKER table
PRINT   '=================================================================='
PRINT   'Attempting to insert a row into CUSTOMER_ACCOUNT where the '
PRINT   'CA_ID is new and the B_ID does not exist in the BROKER table.'
PRINT   ' '
PRINT   'Values to be inserted:'
SELECT  @acct_id As 'Customer Account ID',
        @broker_id As 'Broker ID',
        @cust_id As 'Customer ID',
        @acct_name As 'Customer Account Name',
        @tax_st As 'Customer Tax Status',
        @balance As 'Customer Account Balance'
PRINT   '=================================================================='
INSERT INTO CUSTOMER_ACCOUNT (  CA_ID,
                                CA_B_ID,
                                CA_C_ID,
                                CA_NAME,
                                CA_TAX_ST,
                                CA_BAL)
                      VALUES (  @acct_id,
                                @broker_id,
                                @cust_id,
                                @acct_name,
                                @tax_st,
                                @balance)
PRINT   ' '

--- next try to delete the SECTOR table where the 
--- SC_ID is still in use by the INDUSTRY table
PRINT   '=================================================================='
PRINT   'Attempting to delete the SECTOR table where the' 
PRINT   'SC_ID is still in use by the INDUSTRY table.'
PRINT   '=================================================================='
DELETE  SECTOR
WHERE   SC_ID   = 'EN'
PRINT   ' '

SELECT  'Referential Integrity Verification Done.',
        CONVERT(CHAR(30), GETDATE(), 21)
GO
