In general, we are using client-server architecture for most of our software designs (backend testing tutorial). In that case, data provided in the front end gets stored in backend and during the retrial , it fetches current data that is stored. So common question may arise, why need back end testing? read tutorial.
Database is a collection of interconnected files on server storing information(may not be same data always). Database testing is a layered approach consisting user interface(UI),business layers,data access layers,database layer etc.
Data is considered a collection of relations-
- Relational schema
- Relational states
- During transactions, servers may go to the error state. Simple front end validation can not dig and find the error behind it. The front end may give faulty message of confirmation about data save but in the background, it might not have saved.
- It provides a clear message to the performance tuning team during performance testing. Also leads to an activity called query optimization on which turns to be an important aspect in providing a response. It is not black-box testing though.
- Many server processes are monitored by another process. It may crash, may restart. In that time system may go to unresponsive state. Until we dig into those details, we can not see them happening and can not find the route cause of them.
- This activity makes sure that the data integrity like saying the front end is designed to accept the predefined number of characters for fields.
input type='text' Size='50'name='someName'
But assume the database is designed to hold only 30 characters. In that case, data may be lost. Testing data on boundaries will reveal the fact about data loss.
- Many times it is seen, the client is catching (maybe the client’s browser) user input and submitting the same to the server. Using proper database testing ensures that there is no caching effect happening. (No data corruption).
- The data storage logic can be tested via backend testing.
- A good back end testing minimizes the load on front end testing.
- When the database is used in a distributed model. Many threads or applications may try to update the database. We need to ensure that the thread-safety or concurrency about the database. A strongly designed database can remove all these deadlock related issues.
Backend testing (follow tutorial) is more of Server-side testing or database testing.
Aspects of Database testing
- Does the database perform all operations correctly?
- Is concurrent access handled and managed correctly?
- Is the system fault-tolerant?
- Overall does the system is behaving as intended?
Traditional Vs Database program
Traditional program has functions and they are imperative in nature. Whereas the database program has also functions but they are declarative in nature.
The Databases may be:
- SQL Server
backend testing tutorial
The could of
- Structured data
- Unstructured data
Database testing mainly includes validation of:
- data or Database tables
- Database server validation
- Data duplication
In back end testing GUI component is optional. We can directly pass data by service, GUI or as a request via browsers. The data sending can be done using XML or JSON or we can use SQL queries. After the data have been pushed, the same can be verified using the following:
- SQL Queries
- Log files
What are the types of Database testing?
Database testing can be divided into four major parts:
- Structured data testing
- Unstructured data testing
- Functional testing
- Non-functional testing
Every transaction we perform on the database must follow the ACID principle.
A-Atomicity– Transactions are nothing or all. If a single part of the transaction fails, it means the entire transaction has failed or if all parts of the transaction pass, then it will pass.
C-Consistency– Only valid data is saved.
I-Isolation– Transactions do not affect each other.
D-Durability– written data will not be lost.
How to test ACID?
- Prepare the environment having transactions.
- Run a test.
- Check for the positive scenario.
- Validate the result.
- Report the findings.
- For negative test make one sub transaction failed.
- Validate the result.
- Report the findings.
Structured data testing and unstructured data testing
Structured data testing or unstructured data testing involves the validation of the elements inside data repository(storage for data) which can not be manipulated directly by the user.
Schema Testing is a type of testing that ensures that the mapping of schema(s) are correct for front end and back end.
- Validation of the various schema towards associated with the database.Many a times mapping with back end and front end fail.
- Un-mapped tables/ views/columns need to be double verified.
- Heterogeneous databases need to be in sync with the overall application.
ANT with DBUnit is very good for testing of mappings.SQL server allows users to query and check the schema of databases.
Database table/Column testing
- To check if the front end and back end mappings of the fields and columns are compatible and proper.
- Validate the length and naming convention of database fields are proper and they are as per requirement specification (datatypes and filed length).
- Validation of un-mapped or unused database tables/columns or derived column.
- Validate if the user is able to provide desired user inputs as described by the business.
Keys and Indexes testing
Keys and Indexes testing consists of the followings:
- Check for the primary key and composite key.
- Check for foreign keys
- What constraints have been designed in the table?
- Check if the reference for foreign keys is valid. (Maybe the other tables are not valid or it is not indexed yet).
- Check the primary key datatype is same for both the table (When used as a foreign key).
- Confirm proper naming convention is followed.
- Check for the size and length of the required fields and indexes.
- Confirm cluster index and non-cluster index for a given table.
Shared procedures testing involves the below :
- Manually look for the coding standards.
- Exceptions and error handling in database level.
- Check for coverage (all possible conditions, loops, user data etc).
- Use of train operation while fetching data.
- Manual execution should give the desired output to the end-user with the desired resource.
- Manual execution should update/modify the tables.
- Invocation of triggers should be proper by stored procedures.
- Validation of unused shared procedures. (finding out zombie procedures).
- validation of Null usage as key business rules.
- Stored procedures/functions need to be executed successfully when the database is blank.
- Validation of overall integration of the stored procedure modules.
Trigger testing covers the following validations:
- If the coding conventions are followed.
- If the trigger can execute the data manipulation(DM)operations.
- The update/rollback mechanism.
DB Server Validation
DB Server Validation check the below points
- Validate the database server configuration as per business needs.
- Checks for authorization levels of actions.
- Check the load on the server or allowed user transactions.
Functional DB Testing
Functional DB Testing covers the followings:
- Checking of Null values
- Length of each field (is size sufficient?)
- The similar name for similar fields across tables.
- Fields and their correctness
- Field level validations.
Data Integrity and Consistency
Data Integrity and Consistency checks the followings to be validated:
- If the data is logically organized
- The correctness of data.
- The presence of unnecessary data.
- If the trim operation is performed when reading from front end application.
- The transaction is permitted as per business rules.
- If the commit is happening after every transaction successfully.
- The rollback mechanism is working fine.
- If all the transactions are happening as per requirement design procedures.
What are popular tools to test database testing?
- Data Factory
- Data Generator
- Turbo Data
- Test Data Generation
What is the prerequisite of Data testing?
Test engineers need to have strong command on queries, database servers, Preferably they should be from ETIL domain.
This type of testing mainly deals with the testable items hidden behind the server or hidden from users.
Advantages of Data testing?
Below are the advantages of data testing
- Full control over test coverage and depth.
- This can stop early bugs.
What is popular Schema testing tool?
SchemaCrawler is a popular free schema testing tool.
Sample Login and User Security
If there is a sample login to the application to be tested. Below are few points to test from Database testing point of view:
- Validate if the application is behaving correctly for the below-written combinations.
- Validate upon successful login the user can perform operations specific to his/her login(Role testing).
- Validate if the data is secured from unauthorized access.
- Validate if different user role is created with different permission level.
- Validate all user has specific roles and permissions as per business requirements.
- Validate if the sensitive data like password, pin, credit card information is stored encrypted format.
Non Functional Testing:
- Load testing – validate if the most frequently used user transaction is having good performance. There should not be any slowness in this area.
- Validate the non-editing user transaction with complex user’s transaction along with an editing user transaction.
- Validate the frequent transaction and most important transaction should be included during load testing as they may have greater impact.
- Validate the optimum response time under a specific virtual user load.
Popular load testing tools are:
Stress Testing: Validate the overhead amount to determine the state of the database transactions so that no time and cost based issue appears.
Role of Testdata in database performance testing
The test data needs to be in place in order to test the application properly. This will help to avoid data corruption. XML is a very nice communication medium while testing database data.
- Will the live data suffice for database testing?
No, the live dump of data is not sufficient for testing for various conditions. It may not reflect a sufficiently wide variety of situations. It might be difficult to find an area of interest. Less data also may violate privacy and security inconsistency.
- SQL generator is needed to transform SQL validators in order to ensure the SQL queries are at per for handling the database test cases. This query alternation is an important part of the overall testing process.
- Database setup could be very costly, the strategy should be in place between quality and overall project schedule duration.
How to generate Synthetic data?
DB State :
DB State is a collection of relational states, each of which is a subset of certain product of some domains.
Generating domain elements and gluing them together is not enough since constraints must be honored. It needs a lot of patience to generate interesting data that obey integrity constraints. You can use schema and user-supplied info to generate synthetic data.
DeepDive into SQL testing
SQL is mainly categorized into three basic parts:
- DDL(Data Definition Language)-Create/Alter/Rename/Drop/Truncate
- DML(Data Manipulation Language)- Add/Update/Delete
- DCL(Data Control Language)- Grant/Revoke
Every transaction should follow the ACID principle (given before).
Testing triggers can be of two ways
- Whitebox-Placing stub and drivers
- Blackbox way
Stored Procedure Testing
Stored procedures are the same as user-defined functions. These can be invoked by a call procedure or execute procedure statements, the output is usually in the form of the result set.
They can be tested by the following methods:
- White box testing using stub and drives
- Black box testing using front end.
We test the Field constraints for mainly default unique values and foreign key.
- Perform a front end operation that overruns the database object condition
- Validate the result with a SQL query.
Quick start guide to database testing
- Start afresh test
- Rebuild the database
- Create source data
- Maintain a source data file in the form of XML,flat-file, etc
- Create and maintain a test data creation link.
- Carry out your testing.
Some important Database testing tools:
- Data privacy tools(information privacy)Like- IBM Option Data Privacy tool
- Load testing – Simulate high usage loads on database just to check if the architecture is for production Like- Empirix, Radview, WebPerformance
- Test data generator – Test data generator can be useful when a large data is needed. Like- Data Factory, Datatect, DTM Data Generator, Turbo Data
- Test data Management- All test data should be well managed. Like- IBM Optim Test Data Management tools
- Unit Testing tools: AnyDBTest,DBFit,DBUnit,nDBUnit,SQLUnit,TSQLUnit
Common challenges in Database testing with solutions
- Insufficient testing skills can be overcome by pairing with someone with good testing tool exposure.
- Insufficient unit tests for the existing database – as it is a very new concept, we need to start writing fresh test cases.
- Insufficient database testing can occur due to insufficient test data which can be resolved by involving the Data Management team.