DB Testing and ETL Testing

A Database Testing is a software testing type that checks the schema, tables, triggers, etc. of the database that is under test. It validates the existence, correctness and completeness of the data table. It may also involve creating complex queries to load/stress test the Database and check its responsiveness. It also checks the data integrity and consistency.

The DB testing is a back-end testing whereas GUI testing is the front-end testing.

GUI Testing is a software testing type that checks the Graphical User Interface (GUI) of the software, before the UI or Application is made public to use.

The purpose of GUI testing is to ensure the functionalities of software applications whether or not it is working as per specifications by checking screens, controls like menu buttons, icons, application response, etc.

Types of DB testing are: -

1.       Structural Testing.

2.       Functional Testing.

3.       Non-Functional Testing.

1.       Structural Testing involves –

a.       Schema testing / Mapping testing: -

-          To check if there is proper mapping between the tables/views/columns of the DB and GUI.

-          To verify the heterogeneous DB environment within overall application mapping.

-          Tools used are: - DB Unit, Microsoft SQL server, etc.

 

b.       DB tables and columns testing: -

-          To check the mapping between DB fields and columns in the back end.

-          Check if any not useful/unmapped DB columns/tables.

-          Check for key constraints and Indexes – primary key and foreign key constraints and their data-types in all the tables.

-          Whether clustered Indexes and non-clustered indexes have been created on the required tables as specified by the business requirement.

 

c.       Stored procedures: -

-          To check the standard conventions of the coding, exceptions and error handling of all the stored procedure of the modules.

-          Checking for conditions/loops applied to the required input data.

-          Executing TRIM () function to remove the unnecessary space characters or other specified characters from leading (start) and trailing (end) of a string.

-           Checking the presence of any manual exception, NULL condition, unused stored procedures.

-          Overall integration of stored procedures and function modules.

-          Tools used are: - LINQ, Stored Procedures Test tool.

 

d.       Trigger Testing involves: -

-          Coding conventions.

-          DML transactions – SELECT, INSERT, UPDATE, DELETE, etc.

-          Check all updates.

e.       DB Server Validation involves: -

-          DB server configuration check.

-          Authorization by only required users to perform different levels of action on the application.

-          It can also cater the needs of maximum number users allowed for the transaction.

2.       Functional DB Testing checks all the fields and allows necessary NULL values, length and size of that field and checks if the similar fields across multiple tables have same name.

a.       Black Box Testing involves checking data integrity and consistency: -

-          If data is logically well organized and stored in tables.

-          If any unnecessary data is present.

-          To check the data updated on UI.

-          TRIM () on DML.

-          Transactions based on business goals whether properly committing and rolling back or not.

-          Checking all conditions applied on multiple heterogeneous DB.

-          Checking all the transactions executed.

b.      White Box Testing involves login and user security: -

-          If the application is preventing unknown user login.

    o   Invalid Username Valid Password.

    o   Invalid Username Invalid Password.

    o   Valid Username Invalid Password.

    o   Valid Username Valid Password.

-          Data security and the level of access.

-          Assignment of different roles/permissions as per eligibility and encrypting sensitive passwords, card details, etc.

3.       Non-Functional Testing involves: -

-          Risk quantification and minimum system equipment requirement.

-          Load Testing: -

o   To check if the running transactions have performance impact on DB.

o   Response time for executing the transactions from multiple remote users.

o   Time taken by DB to fetch specific records.

-          Stress Testing (Fatigue Testing): -

o   To identify the system breakpoint.

o   The point where system fails after loading more data on an application is Breakpoint of a system.

o   For ex:- A CRM application takes max user load of 50,000 users but when the load is 51,000 then the transaction syncs the DB to avoid breakpoint and allow new transactions.

o   Proper planning is required to avoid time and cost based issues.

o   Tools used: - Load-Runner and Win-Runner.

-          Security Testing.

-          Compatibility Testing.

-          Usability Testing.