In our today’s high-tech world, it’s impossible to store information on paper, tables, or bookshelves. Too much paper will be spent on doing this.

So this type of data storage has been replaced by special data centers that have capacious hard disks, and such a thing as a database has been introduced. A big amount of information can be stored, be processed, and be logically structured for a long time there.

A database is a special structure used for storing information, managing, and exchanging a huge amount of data. They are used in different fields of IT communities: from simple web stores to Space Control Centers.

A special combination of applications and systematic tools (DBMS) has been created for creating, using, and managing databases.

DBMS is a special combination of programs that are used for developing, displaying, filling, and updating the information.

The most popular DBMS are Oracle, PostgreSQL, MongoDB, MySQL, and Microsoft SQL Server.

[highlight dark=”no”]Any system that is being implemented in software should be thoroughly tested, to minimize the errors during its future usage.[/highlight]

Information is transferred from a user to an internal DB and vice versa in various software.

Testing DB functionality entails performance testing, in particular, that is connected with DB and also, testing reliability and structural integrity of information.

Popular components of DB testing

Further, we’ll talk about several features that should be taken into account while performing database testing.

1. Information displaying: a tester should check if the fields in a user interface form are the same as the ones inside DB. This information is commonly mentioned in a specification.
2. Record updating in a table: every time a certain action is executed in an external software interface, the same action should be called in an internal interface. To test record updating in a table, a tester should use a special CRUD C (Create) mnemonic — when a user saves any action, the INSERT operation is used, R (Read) — when a user can review or search for the saved operation, the SELECT operation is used, U (Update) — when a user edits an existing record, the Update operation is automatically performed in a database, D (Delete) — when the one removes a record from DB, the Delete operation is performed;
3. The ACID operation properties testing: any operation that is executed in DB, should completely meet 4 requirements: atomicity, consistency, resistance, and isolation;
4. Data safety and consistency: all information and also, its modification should be displayed in the same way and in a clear way for a user. You should verify that a system уйгфддн displays last modifications in tables in every part they are displayed. This is required to ensure that they are the same in every part of the system;
5. Meeting the business requirements: DBs are used not only to store information but also to manage software development. The big technical complexity of DB entails using very complex components like relational limitations, triggers, saved processes. In other words, testers need to make up corresponding SQL requests to test these complex system objects.

Main attributes of DB testing

• Transactions — a certain sequence of several actions with DB, where all manipulations are performed successfully or no action is executed. A transaction should be atomic and be executed as a whole;
• DB scheme — these are structures of information management, table’s description and column description (their types, thresholds values, connections between the tables);
• A trigger is the process of a certain type, that can be automatically executed after taking certain actions on information modification. For example, adding or editing the information in a certain column. Triggers are used to validate information consistency and implement complex business logic.
• A process— a set of parameters or operators that manage transactions inside a database.

Main types of DB testing

DB functionality testing can be divided into 3 types, according to parameters and a structure of DB:

1. Structural testing of DB is based on testing tables, columns, schemes of DB and also, testing saved processes or triggers;
2. Functional testing of DB — it’s testing DB’s functionality from a common user’s side. We can distinguish 2 types of tests at once — white and black box tests;
3. Non-functional DB testing — it’s testing DB performance, from load tests, risk tests to stress tests and also, analysis of minimum system’s requirements.

DB testing entails testing the functionality of an application and such DBs as tables, columns, with the help of system tools.

You can create very simple requests for DB scheme testing with the help of the Microsoft SQL Server tool.

Testing tables and columns implies the following types of testing:

• Testing data types in a DB by field value in software;
• Testing information field’s length in a database by data type’s length in software;
• Testing invisible tables and columns in a database, that are present in software;
• Testing if DB table names and column names meet the established business requirements;
• Testing if keys and indexes in a database completely meet the established business requirements.

Testing stored processes encompasses testing stored processes and analyzing the results of the executed command output.

Testing stored processes is performed with the help of the following programs: SP test tool and LINQ.

You should follow the following consistent steps while testing:

• Check a process name, parameter names and type names;
• Test an input signal (if zero lines are displayed or only few records are displayed, and if the function of a stored process works);
• Test the process of transmitting the output request to verify that necessary information is saved;
• Validate error displaying in case of any incorrect request’s usage.

To perform trigger testing, a tester should perform the following steps:

• Test the correctness of alphabetic value of a trigger and all its updates;
• Launch the trigger with the already updated information;
• Test the functions of updating, deleting and pasting triggers;
• Launch the trigger with incorrect data and check every bug you find;
• Cancel the launched transactions if there is a failure.

[highlight dark=”no”]Performance testing is a crucial stage of interacting with a database.[/highlight]

Its main aim is an accurate definition of correct software functionality at loads. Nobody will like the system that can’t process a small request in 5 minutes.

To perform such testing, you should:

• Measure accurate time that is needed for the task’s execution at minimum, average, and maximum system loads.
• Analyze a number of users that can simultaneously interact with software;
• Analyze a performance process at different system loads.

The most important attributes to measure a performance level are:

• Amount of operations during a selected time range;
• Amount of transactions during a chosen period;
• Amount of lines, that have been read inside a table during 1 transaction;
• An average amount of operations inside a selection.

Stress testing gives the possibility to see to what extent a tested system works under stress and also, helps to analyze what’s its ability to rapidly recover or to return to its usual state when stress (load) has ended.

Testing DB through an interface

Commonly, such a process of testing is executed by validating its “user friendliness”. It’s testing if the button names are the same as the programmed actions, links, redirects, that were technically added to an interface, and validating the input values.

After executing the following actions in correct order, we can find the bugs by using database testing with the help of a graphical interface:

1. Create requests through a graphical interface and execute them;
2. Find the records in a cell, edit it and try to save it;
3. Delete the current cell with data;
4. Repeat all these test steps with correct and incorrect data and analyze the database’s reaction.

[highlight dark=”no”]DB is quite an important part of any software, so it’s very important to test its performance beforehand, by testing the correctness and security of information input.[/highlight]

DB tests require good concentration, specific competences, professional skills of creating SQL requests, knowledge of a DB structure, and practical skills.

DB testing allows decreasing the risks that are somehow connected with the information consistency, the ability of architecture to react to load before the software will be completely ready to use.