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
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.