Introduction to Data Testing

Let’s begin with an analogy to understand what Data Testing is. When you go to the movies, what you see on the screen is the final outcome of a lot of ‘behind the scene’ activities. In the prep stage, many unseen aspects have to be taken care of, for the movie to be a success. There is the script, sets, costumes, makeup, lights, camera etc. which have to be spot-on. The performance of the cast has to be flawless, and the actions of the on-set crew have to be perfectly executed. Every back-end activity has to match what the directors and producers have envisioned for the viewers to enjoy on screen. Furthermore necessary permissions have to be in place, and safety and security have to be ensured. Most of these activities do not get the media glare, but they are vital to ensure that the movie gets good ratings and viewership.

Similarly, when we speak of apps, what the end user sees on screen is the final outcome of a lot of checks and balances that have been done in terms of development and testing. Software personnel may tend to pay more attention to the onscreen Graphical User Interface (GUI) aspects, because this is what the end user sees. However, the ‘behind the screen’ activity is also extremely important to ensure superior viewer experience. While GUI testing relates to front-end testing, the ‘behind the screen’ or back-end testing is what Data Testing/Database Testing/DB Testing is all about.

Database Testing can be defined as software testing that validates the veracity of data values and information received and stored in the database. It validates the schema, tables, triggers, keys and indexes, database server, data duplication etc. Data Testing also validates data integrity and consistency. Furthermore Data Testing checks load or stress testing of the database. In short, Data Testing is done to prevent data loss, save information correctly, and prevent unauthorised information access. Data Testing is known as back-end testing as it checks areas and processes that are largely not visible to the user e.g. internal processes and storage like Assembly; DBMS like Oracle, SQL Server, MYSQL, etc.

With this brief introduction, let’s now understand the various types of Database Testing, based on the function and structure of the database. This can be classified as follows:

• Structural Testing
• Functional Testing
• Non-functional Testing

Structural testing is done to authenticate the elements that are used to store data in the data repository, as well as to validate the database servers. These elements are not directly accessible to end users for modification. Structural DB Testing deals with Table and Column Testing, Schema Testing, Keys and Indexes Testing, Stored Procedures & Views Testing, and Trigger Testing. If you are a tester aspiring to perfect Structural Database Testing, you must be adept at framing SQL queries.

Let’s proceed to briefly understand each type of Structural Testing.

• Database Tables and Columns Testing verifies compatibility between back-end and front-end mapping of fields, columns, data-type, field lengths; authenticates that the length and naming convention of the database fields are as desired; checks the existence of unused/unmapped database tables/columns; and verifies that the database fields permit proper input of required information by the user.
• Schema Testing validates the database’s schema formats and ensures compatibility between back-end mapping formats of tables, views, and columns, vis-à-vis front-end mapping formats of the user interface. It also involves verification of unmapped tables, views and columns; as well as verification of whether heterogeneous databases are in line with the overall app mapping.
• Keys and Indexes Testing ensures that the Primary and Foreign key restrictions have been created in the required tables; checks validity of foreign keys; ensures that the data type of primary and relevant foreign keys are the same in the two tables; checks that naming convention of keys and indexes are proper; verifies proper size and length of fields and indexes; ensures that the clustered and non-clustered indexes have been created for the tables as required by the business.
• Stored Procedures Testing checks coding conventions, exception and error handling for stored procedures of all modules of the app; verifies whether the developers have covered all conditions by applying relevant input data; verifies the TRIM operation for fetching data from the tables; manual execution of stored procedures; proper updating of table fields; setting off of the relevant triggers; validation of unused stored procedures, as well as successful execution of all stored procedures and functions when the database is blank; and also validates proper integration of the stored procedure modules.
• Trigger Testing checks that the triggers-related coding conventions have been followed; execution of triggers is proper for the relevant DML transactions; that data gets properly updated on execution of triggers; and also validates the ‘update, insert and delete’ trigger functionalities.
• Database Server Validations verifies that the database server configuration supports the maximum user transaction volumes of the business; as well as ensures that accessibility is permissible as per recommended authorisation levels.

To fall back on our movie analogy, Structural Testing can be roughly equated to ensuring that the ‘behind the scenes’ arrangements in terms of script, sets, costumes, makeup, lighting etc. are in line with the story desired to be projected on screen. In other words, the back-end structures should match and support the desired onscreen displays.

This testing is done to ensure that the app’s functionality in the user’s hand is as expected and that the actions performed by the end users work exactly as required. Database Functionality Testing involves checking of data integrity and consistency; and login and user security. The essentials in this type of testing includes checking whether the field is mandatory while allowing NULL values; ensuring that the field lengths are long enough for user’s data entry; that nomenclature of similar fields is the same in all tables; and also mandates checking on the presence of computed fields in the database. Functionality testing is a two way testing that verifies that an operation done at the database level has appropriate field validations at the UI level; and that operations at the UI level are validated at the back-end. In short, it validates field-mapping from the users’ perspective.

Let’s review the various tests that fall under the umbrella of Functional Testing.

• Data Integrity and Consistency Validation is done to ensure the following: Data is logically structured; data stored in the tables meets business specifications; unnecessary data is weeded out; UI data inputs are properly stored; required TRIM operations have been performed on UI updated data, and also on the data prior to inserting in the database; all transaction are accessible and display correct results; the data is either properly committed or else rolled back as per user’s action; and that every transaction is functioning as mandated.
• Login and User Security Testing involves restriction of app access in case of erroneous username or password; restriction in access as per desired security levels; permission of user access to only those operations specified for users by the business; smooth access of users to data required for their transactions; encryption of all sensitive and confidential data like passwords, bank account/credit card/debit card numbers etc.

Revisiting our movie analogy, this would translate into ensuring that the actors’ performances are in line with each other as well matches the script. Also that the story unfolds logically to make sense to the movie buff. Furthermore the movie editing is to be perfectly done and unnecessary scenes must be deleted. It would also equate to the movie aspects related to measures taken to ensure that only authorised people are on the sets, and given access to only those areas that relate to their field of work.

This includes Load Testing, Stress Testing, Security Testing, Usability Testing, Compatibility Testing etc. Through load testing, risks in terms of system response time requirements are quantified, so that necessary steps can be taken to eliminate them. It also helps determine the minimum system equipment and configuration that are required for proper performance of the app.

• Load Testing needs to necessarily ensure right configurations for the following: Frequently used transactions; transactions that are deemed important to the core objectives of the system; a minimum of one non-editing user transaction; a minimum of one editable transaction; ideal response time during large user traffic; and effective time for retrieving records.
• Database Stress Testing involves subjecting the database to a heavy load so as to determine its failure point. A word of caution here: Proper planning is required to avoid unnecessary use of resources, as this can be costly in terms of time and money.

With this it is curtains down for this blog! It is hoped that you’ve received good insight into Data Testing, which is just one part of the vast field of Software Testing. Given the pace at which every business is going digital, there is a very attractive career for those interested in this booming field.

If you would like to know more about this promising career option, visit www.mypathglow.com. Sign up for PathGlow’s Full Stack Software Testing Course and get industry-ready in just 3 months. You benefit from our ISTQB pattern syllabus, training by industry experts, real work simulations, and use of cutting edge tools and methodologies.