Description

The Banking Demo provides a real-world example of how GenRocket can be used to quickly generate custom synthetic test data and populate database tables to meet specific testing needs. In this solutions article, we will quickly and easily solve some test data challenges using GenRocket Test Data Cases on a simulated bank database.

It is extremely difficult to create test data for testing a complex relational data model using spreadsheets, which is why testers often attempt to use production data; but, production data is insecure and unpredictable when trying to solve a particular test data challenge. But, with GenRocket's synthetically generated test data combined with GenRocket's Test Data Cases, you can quickly and easily overcome these test data challenges.

Prerequisites

A few steps are necessary before you will be able to complete this demo: 

1. You will need to install MySQL on your system. A PDF document has been provided at the bottom of this article for installing MySQL on a Windows machine.
   
   
2. Create a Database with a name bank_demo.
   
   
3. Import the database dump to create tables using the attached bank_demo.sql file. Follow the steps provided here to import the database dump.
   
   
4. You need a JDBC configuration (e.g., config.properties) file that contains database connection-related information such as URL, driver, user, password, etc.
   
   

Example JDBC Configuration File

Below is an example of a JDBC configuration file defined to connect to a MySQL database called bank_demo.  

driver=com.mysql.jdbc.Driver
user=root
password=MySQL password
url=jdbc:mysql://localhost:3306/bank_demo?useServerPrepStmts=false&rewriteBatchedStatements=true
batchCount=1000

Article Attachments

The following attachments have been included at the bottom of this article to help with setting up the Banking Demo:

• How To Install MySQL Sever.pdf - This PDF provides steps for installing MySQL on a Windows 7 machine and can be used to assist with the install on any Windows machine. The steps may vary per operating system version.
  
  
• JDBC Configuration File (config.properties) - This attachment can be used to set up the required Database Connection Properties File for GenRocket Runtime.
  
  
• Database Dump (Bank_Demo.sql) - This attachment will need to be used to perform the database dump, which creates the required Table Schema for the Bank_Demo database.
  
  
• ER Diagram (BankDemo ER Diagram.png) - This attachment provides the ER Diagram used for the Banking Demo and is shown in the solutions video below. 

Solutions Video

The following video can be used to learn more about how GenRocket can help better manage test data and overcome your test data challenges: 

GenRocket Bank Solutions Demo with Test Data Cases from Hycel B Taylor III on Vimeo.

Summary

So, let's take a look at how this can be done. 

• Within the GenRocket Web platform, we imported the description of each table within the database and modified a few of the Generators that were auto-assigned to each Attribute, as we have done here, on the customer.
  
  
• For some Domains, we also defined their relationships to each other to establish and guarantee referential integrity during test data generation.
  
  
• Within GenRocket's self-serve Test Data Cases platform, you can define your own Test Data Case suites and within a suite, define multiple Test Data Categories for your test data cases.
  
  
• We have defined a suite called BankingTables and within the suite, we have defined three categories, negative, load, and integration.
  
  

ER Diagram

Defining GenRocket Test Data Cases

Defining load Test Data Cases is quick because you only need to choose a Domain whose loopCount you want to modify for the load. 

Note: Please see Test Data Cases Overview for more information about creating Test Data Cases.

Defining negative cases is easy because you can control how the negative test data is generated. In this case, we linked four Generators to a Test Data Case Domain to create a negative SSN. The Generators generate the following data: 

• gen1 - A valid SSN
• gen2 - Disruptive Characters
• gen3 - Places Disruptive Characters within the SSN
• gen4 - Selects a Negative SSN every fourth iteration
  
  

The fourth Generator (gen4) determines if a valid SSN or negative SSN will be used for the data record. 

• caseTrue - Negative SSN
• caseFalse - Valid SSN
  
  

Note: These Generators are assigned and linked within the Test Data Case Domain for the ssn Attribute. To learn more, please see this knowledge base article: How to Add Attributes to a Case Domain.

GenRocket even provides instructions for executing the Test Data Case on any Scenario. You only need to download the Test Data Case Suite and then run it with the Scenario using the provided command line.

Note: To learn more about downloading and running Test Data Cases (TDCs) with Scenarios or Scenario Chains, please see this knowledge base article: How to Download and Run a Test Data Cases.

Delete All Data from the Database

Before we run our first Test Data Case, we want to delete all data from the database to start with a clean slate. Once cleared, we can go to a command line and run our Test Data Cases on any Scenario. 

The following data will need to be deleted for the Banking Demo.

delete from card_product;
delete from customer_account;
delete from transaction;
delete from account; 
delete from customer;
delete from branch;
delete from account_type;
delete from account_level;
delete from transaction_type;
delete from card_type;

Test 1: Run The Test Data Case with the Customer Scenario

Now, we can go to a command line and run our Test Data Case on any Scenario. For this example, we will run the Test Data Case on the CustomerScenario, because we only need to populate the customer table with negative test data.

genrocket -tdc BankingTables.gtdc:Negative.badSSNs -r CustomerScenario.grs

And sure enough, we have generated 100 customers, and every fourth customer has a Negative SSN.

Attaining this negative pattern from production data would be next to impossible. Now, we can empty the database again and move onto our next test.

Sample Output - Customer Table

Test 2: Populate Type Tables

Here's where we get to run some integration Test Data Cases. The first integration case we want to run is for populating the different bank type tables. 

It's necessary to populate the type tables because many other integration tests depend on them for referential integrity.

We will run this Test Data Case on the TypeTablesScenarioChain, which contains Scenarios to populate all type tables. You will need to download the Scenario Chain and then run the command line below: 

genrocket -tdc BankingTables.gtdc:Integration.typeTables -r TypeTablesScenarioChain.grs

Sample Output

In only a few seconds the type tables are loaded, not only with test data, but test data with very specific key values, allowing complete control of test data in subsequent Scenario runs that depend on these values.

This, again, may be difficult to do with production data and time-consuming with spreadsheets compared to the speed of defining an integration Test Data Case and generating the data with GenRocket.

Account_Level

Account_Type

Card_Type

Transaction_Type

Test 3: Populate the Interrelated Account Table with Specified Amounts of Test Data

Now that we have populated the type tables, we can use either the mediumSetup or smallSetup integration Test Data Case (TDC) to populate the interelated accout table with specific amounts of test data.

So, let's use the smallSetup TDC and also add to it a Test Data Rule (TDR) to control the value of the account balance generated based on the assigned account level.

Here's where we get to utilize the full power of Test Data Cases because we can define and add multiple Test Data Rules to any Test Data Case to generate exactly the complex data we need.

The Test Data Rule (TDR) Set is displayed below. This Test Data Rule Set defines the generated balance based on the defined conditions.

This is the kind of test data the you cannot easily define in a spreadsheet nor pull from production data.

In this integration TDC, we want to generate 100 customers, 3 Branches each having 10 accounts and a total of 30 CustomerAccounts, 30 CardProducts and 30 Transactions generated.

This time, we will run the TDC on the AccountTableScenarioChain, which contains Scenarios to populate all related account tables. This Scenario Chain will need to be downloaded before running the command line below:

genrocket -tdc BankingTables.gtdc:Integration.smallSetup -r AccountTablesScenarioChain.grs

Sample Output

Unlike the complications of using spreadsheets or the use of production data, right away we can see that we have indeed generated 3 branches, with each branch having 10 accounts and each account having a balance within its specified parameters based on its account_level.

We can also see that we have generated 30 CardProducts, 30 CustomerAccounts and 30 Transactions.

Branch Records

Account Records

Card Products

Customer Accounts

Transactions

Conclusion

In conclusion, where spreadsheets and production data inedibly fail, GenRocket's synthetic test data generation along with its TDCs allow you to quickly and easily generate the test data you need to meet your complex test data challenges.