Databases are an essential part of the modern software such as client-server software or web-applications.

Our team has some experience in testing of applications that work with databases. The purpose of this article is to sum up the gathered experience and to share it with those who faced the similar task for the first time.

The article is a small manual with practical ideas that can be useful while writing the test plan for your application. Test cases are gathered with no relation to some definite type of application or DBMS (Database Management System); that is why they do not claim to be taken “as is”. After reading this article, you will be able to adapt them to your concrete product.

Written by:
Anna Skumina,
Junior Tester of Network Testing Team

Applications that Work with Databases
Installation Testing
Functional Testing
Stress Testing

Applications that Work with Databases

As a rule, applications that work with databases are multilevel: client and server parts, and the database itself. The database can be an instance of any existing DBMS on the modern market: MS Access, SQL Server, Oracle, Mysql, Firebird. An application can represent a program code written in one of modern programming languages, such as C, C++, C#, Java for a client-server application and in the script language, such as JavaScript, PHP, Perl, etc. for a web-application.

Installation Testing

The acquaintance of a user with your product begins with the installation, that is why it is important to pay special attention to it.

1. Installation of the application and the database on a single machine

Connection type: local. The following test cases appear:

• To define the location of the database as localhost;
• To define the location of the database by the name of the machine, e.g.,Dell-pc;
• To define the location of the database by the IP address;
• To define the location of the database by the address of the “loopback” 127.0.0.1.

1. Installation of the application and the database on the different network nodes

Connection type: network.

• To define the location of the database by the name of the machine, e.g., Dell-pc;
• To define the location of the database by the IP address.

1. Installation with/without user authentification
2. Installation with the network port:
   • By default;
   • Not by default, free port;
   • Not by default, used port.
3. Installation of the application with the creation of a new database
4. Installation of the application without creation of a new database but using the existing one
5. Updating of the application version – check of the work of the existing database with the updated application
6. Removal of the application together with the database
7. Removal of the application without removal of the database
8. Changing of the database location after the application installation

Here are also some test cases that can be useful for the majority of applications:

1. Installation of the application using the not default path
2. Installation of the application as an Administrator
3. Installation of the application as a Standard user or Guest
4. Installation for all users/for the current user
5. Installation with enabled/disabled UAC
6. Installation over the installed application

Functional Testing

There are three main operations with data: adding, editing, and deleting. We check the correct performance of these operations by the fourth one - selection.

As the article concerns the blackbox testing, the test cases are written with an assumption of work through the user interface. Let’s make one more assumption – the tested application has definite entry fields for adding data to the database tables. In this case, the entry fields will be the instrument for the check of the correct interaction of the application and the database.

Testing of applications that work with the database using entry fields of the client interface is a classic example of the data-driven testing. We will examine only three main test scenarios for each of three operations with data. Then, we will suggest only input data and the expected result for it.

Adding data

1. Open the form of the client interface for data adding.
2. Enter data.
3. Save data.
4. Open the selection form.
5. Perform the selection of the just entered data.

Expected result: Selected data is identical to the entered data.

Editing data

1. Open the form of the client interface for data editing.
2. Edit data.
3. Save data.
4. Open the selection form.
5. Perform the selection of the just edited data.

Expected result: Selected data is identical to the edited data.

Here, we shouldn’t also forget about two additional negative test cases:

Changing data to the incorrect one

Changing data so that it fully duplicates the existing record in the database

Expected result: In both cases, the correct system response will be to warn the user about the error and do not save the incorrect input in the database table.

Deleting data

1. Open the form of the client interface for data deleting
2. Delete data
3. Open the selection form
4. Perform the selection of the just deleted data

Expected result: Deleted data is absent by selection.

Here, the tests on database cleanup are also worth mentioning:

• If the database must be of the fixed size according to specification, we should fill it with data up to the maximum size and check if the size will then increase;
• If the application supports the database cleanup, after the cleanup, we should check its results not only through the client interface, but also open the database tables and make sure that there is no data there.

Input data

Text fields

1. Input of text symbols

Expected result: A record with the entered text in the corresponding field appeared in the corresponding database table.

1. Input of the numbers and letters if it is implied by the specification and the application logic, e.g., if the field contains the proper name - 43Folders

Expected  result: numeric characters should be available for input in the text field. The just entered value is stored in the corresponding table of database. The value can be checked by the corresponding query.

1. Input of text that contains space characters.

Expected result: In the database table, the record is not cut before the space character if there are significant symbols after it.

1. Input of text that begins with/ends on the space character.

Expected result: Space characters are cut at the beginning of the string till the first significant symbol (the same is for the space characters at the end of the string)

1. Input of the identical text in upper and lower cases: “Metropol” and “METROPOL”.

Expected result: The application should return the informative error message and warn the user that data is duplicated.

1. Check of the maximum number of symbols available for the input to the field

Expected result: The application should not give the user an opportunity to enter more symbols than it is specified for this field in specification. The user can be warned about it by the beep sound, warning GUI marker, or in any other way.

1. Input of acceptable special symbols, e.g., @ in the e-mail address field.

Expected result: The string with the acceptable symbol appears in the database table.

1. Check of the input of single and double quotes and also the usage of the apostrophe: Ann's PC.

Expected result: The entered string is stored in the database correctly.

1. Input of non-roman  symbols, e.g. Korean ones.

Expected result: Non-roman symbols are stored in the database in the form they were entered in the field. It can be checked by the corresponding query.

The test examples given below represent a specific input; expected result for each of them is that the entered data must be stored as a string value in the database and must be available by the selection:

1. Input of html tags: <title>Database testing</title>
2. Input of a script fragment: <script>function square(x) {return x*x;}</script>
3. Input of a so-called SQL injection: SELECT * FROM USERS
4. Input of invalid special symbols - ☺☺
5. Input of “null” value

Integer-valued fields

1. Input of an integer number

Expected result: Integer value is stored in the database correctly and can be selected by the corresponding query; after the selection it is represented as the integer number.

1. Input of boundary values of the integer numbers range: 0 and 65535 for the field with positive integer values;  -32767, 32767 for the field that can contain both positive and negative numbers according to the program logic
2. Input of border values: 1) 1, 65534 2)  -32766, 32766

Expected result: Both boundary and border values must be stored and processed as any other value from the range.

1. Input of values beyond the range borders: 1) -4, 50000  2)-50000,50000

Expected result: The user should be informed that the value cannot be added to the database, or such input should be disabled.

1. Input of the fractional number 0,1 and 0.1

Expected result: а) Input of non-number symbols must be blocked; b) An informative error message and the offer to re-fill the field must be returned

1. Input of the empty value

Expected result: If the field is not a key one according to the program logic, such input must be acceptable. The corresponding field is empty in the database table.

1. Input of a space character

Expected result: The space character should be considered as an empty field.

The following set of test examples is a negative one. The expected result is an error message and absence of a new record in the database tables.

1. Input of a space character between two numbers.
2. Input of the text symbols.
3. Input of the special symbols.

Fields with fractional number values

1. Input of a positive fractional number
2. Input of a negative fractional number: a positive test if the field contains, for example, air temperature, and negative if the field contains the human body temperature
3. Input of an integer number (e.g. “1”).

Expected result: The numeric value should be brought to the format with floating point. The result of the corresponding query must be “1.0”

1. Input of a number with the separating point and absent fractional part (e.g. “1.”).

Expected result: Data must be stored and the result of the corresponding query must be “1.0”.

1. Input of a number with the maximum number of symbols after comma.
2. Change the delimiter of fractional numbers on the client side and/or on the database side:

Control Panel -> Region and Language -> Numbers -> Additional settings ->Decimal symbol

Expected result: The entered value with a new delimiter is processed by the database correctly

Negative test cases:

1. Input of a number with two delimiters: 0,52,2
2. Input of values of other data type: text symbols, date, etc.

Expected result (7-8): There should be input verification applied, and as the result, user should be informed that the data he entered are invalid and the insertion to the table of database will not be performed.

Date fields

Expected result for all positive cases: the corresponding query result must contain the just entered value; for all negative test cases: the invalid input must be processed before adding the value(s) to the table of database.

1. Input of a valid date value
2. Input of a boundary value, e.g., 01.01.1920 can be the low bound of the date of birth field and the current date can be the high bound.
3. Input of the 31st of September, April, June, and November
4. Input of the 30th and 31st of February and also the 29th of February for a not leap year
5. Change of the date input format in locale settings on user machine and… what?
6. Usage of a comma instead of a dot when the input mask is absent

In case, when two dates define the time interval:

1. Valid input – the first date is earlier than the second one: from 01.01.2010 to 10.10.2010
2. Invalid input – the first date is later than the second one: from 10.10.2010 to 01.10.2010
3. One of the interval dates is not specified

Address fields

Expected result for the test cases 1-7: The result of the corresponding queries must be equal to the data entered for adding to the table of database.

1. Input of a network address: ftp://192.168.0.1/library
2. Input of a network address using the back slash:  ftp:\\192.168.0.1\library
3. Input of a local address: c:\Windows\system32
4. Input of a local address using the slash: c:/Windows/system32
5. Input of an address that contains a space character: C:\Program Files
6. Input of an Internet address: www.google.com
7. Input of an e-mail address: This e-mail address is being protected from spambots. You need JavaScript enabled to view it

The test cases given below are negative, and the data proposed in them must not be added to the database.

1. Input of text value without @ symbol in the e-mail address field
2. Input of an Internet address with a comma instead of the delimiter www,google,com

User passwords

If the application supposes authorization, it surely supports the creation of user passwords. Here, it is worth mentioning one important test.

1. Add a new user and a password.
2. Open the database table that contains user information.

Expected result: The password is encrypted and stored in the encrypted form. You should open the table of database where the user passwords are stored and make sure that they are stored in the unreadable form.

Stress Testing

In this part of the article, we will represent some tests that will help you to check the recover after failure functionality of your software.

Network

Supposing, the application and the database are installed on the different network nodes. In this case, it is worth checking how the application behaves if the connection is lost during the interaction of the application and the database.

Let’s examine the following test:

1. Start the insertion to the database
2. Interrupt the connection

Expected result: The user must be warned of the reason of the system failure with the message concerning the absence of network connection. After the network problem is solved, the application continues interaction with the database correctly. It depends on the database system architecture, if the data or its part is added to the table. Insertion can be performed as the atomic transaction and as a sequence of smaller transactions. In the first case, the insertion will not be performed at all, in the second case some part of data can be successfully added to the database.

The connection failure can be caused in the following ways (each of the proposed methods should be checked on machine with installed application as well as on the machine with the database):

1. Start the command line. Enter the ipconfig /release command
2. Control Panel ->  Network and Internet -> Network Connections ->  Call the context menu for the active network connection -> Disconnect
3. Unplug the network cable

Port

One more reason to check the system recovery after the failure is the network port, through which the interaction of your application and the database is performed via network. The simplest test is to create a rule for Windows Firewall that will block the incoming and outgoing connection through the specified port. The application should return the maximum informative message about the problem that limits the possibility to interact with the database. After the rule deletion, connection should be restored and the interaction between the database and application should be continued without difficulties.

Database service

One more possibility to check the system stability is to terminate the database service during the system work. Situation is similar to the test with network.

1. Start the prolonged transaction
2. Terminate the database service

Expected result: Transaction is terminated. Message about the failure is returned to the user. After the service start, the system work resumes.

Conclusion

In this article, I try to share all that experience that our team has gained working on various projects concerning tasting of the applications based on the client-server architecture.

The main concept that is valid for all positive functional test cases is the following: the data, which are inserted into the tables of database, can be checked by means of the corresponding queries. The data returned by the query and data entered in the insertion form must be equal.

The main concept concerning he negative test cases is that the insertion of the invalid data  must be blocked by the system.

I hope that using the basic test cases provided in this article you will manage to find  the bottle-necks in the tested system easily.