Database testing

Database Testing 

Computer applications are more complex these days with technologies like android and also with lots of smart phone apps. The more complex the front ends, the back ends are even more intricate.  So, it is all the more important to learn about DB testing and be able to validate the databases effectively to ensure secure and quality databases.

In this article you will learn all about Database Testing. Why to test- How to test- What to test : These are some of the aspects we will cover.

Why do we test a database?

Below, we will briefly see why the following aspects of a DB should be validated:

1) Data Mapping: In the software systems, data often travels back and forth from the UI (user interface) to the backend DB and vice versa. So following are the aspects to look for:

• To check whether the fields in the UI/Front end forms and mapped consistently with the corresponding DB table (and also the fields within).  Typically this mapping information is defined in the requirements documents.
• Whenever a certain action is performed in the front end of an application, a corresponding CRUD (Create, Retrieve, Update and delete) action gets invoked at the back end. A tester will have to check if the right action is invoked and the invoked action in itself is successful or not.

2) ACID properties validation:  atomicity, consistency, isolation and durability. Every transaction a DB performs has to adhere to these four properties. (image credit)

• Atomicity means that a transaction either fails or passes. This means that even if a single part of transaction fails- it means that the entire transaction has failed. Usually this is called the “all-or nothing” rule.
• Consistency: A transaction will always result in a valid state of the DB
• Isolation: If there are multiple transactions and they are executed all at once, the result/state of the DB should be the same as if they were executed one after the other.
• Durability: Once a transaction is done and committed, no external factors like power loss or crash should be able to change it

3) Data integrity:

This means that following any of the CRUD operations, the updated and most recent values/Status of shared data should appear on all the forms and screens. A value should not be updated on one screen and display an older value on another one. So devise your DB test cases in a way to include checking the data in all the places it appears to see if it is consistently the same.

4) Business rule conformity:  More complex databases means more complicated components like relational constraints, triggers, stored procedures, etc. So testers will have to come up with appropriate SQL queries in order to validate these complex objects.

How to test – Database Testing Process

The general test process for DB testing is not very different from any other application. The following are the steps:

Step #1) Prepare the environment
Step #2) Run a test
Step #3) Check test result
Step #4) Validate according to the expected results
Step #5) Report the findings to the respective stakeholders

Usually SQL queries are used to develop the tests. The most commonly used command is the “Select”.

Select * from <tablename> where <condition>

Apart from select, SQL has 3 important types of commands:

1. DDL : Data definition language
2. DML: Data manipulation language
3. DCL: data control language

Let us see the syntax for the most commonly used statements.

Data Definition language: Uses CREATE, ALTER, RENAME, DROP and TRUNCATE to handle tables (and indexes).

• Create table: Create table<tablename> (field1 datatype(field size) ,……………..fieldn datatype(field size))
• Delete entire table: Drop table <tablename>. – this command cannot be rolled back

Data Manipulation language: Includes statements to add, update and delete records.

• To insert a row into a DB: INSERT INTO <table name> (field1, field2, field3)  VALUES  (‘val1’, ‘val2’…’valn’);
• Delete specific row/rows from a table: DELETE FROM TABLENAME WHERE <required condition>.
• Update rows: UPDATE <tablename> SET field1 = ‘updated value’ WHERE field2 = ‘N’;

Data control language: Deals with giving the authorization to users for manipulation and access to the data. Grant and Revoke are the two statements used.

Grant syntax:
Grant select/update
On <table name>
To <user id1, user id2…useridn>;

Revoke syntax:
Revokeselect/update
on <table name>
from<user id1, user id2…useridn>;

What to test – different components

1) Transactions:

When testing transactions it is important to make sure that they satisfy the ACID properties.

The following are the statements commonly used:

• BEGIN TRANSACTION TRANSACTION#
• END TRANSACTION TRANSACTION#

Rollback statement ensures that the database lies in a consistent state.

• ROLLBACK TRANSACTION#

After these statements are executed, use a select to make sure if the changes have been reflected.

• SELECT * FROM TABLENAME <tables which involve the transactions>

2) Database schema:

Database schema is nothing but a formal definition of the how the data is going to be organized into a DB. To test it:

Identify the requirements based on which the database operates. Sample requirements:

• Primary keys to be created before any other fields are created.
• • Foreign keys should be completely indexed for easy retrieval and searching.
  • Field names starting or ending with certain characters.
  • Fields with a constraint that certain values can or cannot be inserted.
• Use one of the following ways according to the relevance:
  • SQL Query DESC<table name> to validate the schema.
  • Regular expressions for validating the names of the individual fields and their values
  • Tools like SchemaCrawler

3) Trigger:

When a certain event takes places on a certain table, a piece of code (a trigger) can be auto-instructed to be executed.

For example, a new student joined a school. The student is taking 2 classes; math and science. The student is added to the “student table”.  A trigger could be adding the student to the corresponding subject tables once he is added to the student table.

The common method to test is to execute SQL query embedded in the trigger independently first and record the result. Follow this up with executing the trigger as a whole. Compare the results.

These are tested during both the black box and white box testing phases.

• White box testing:  Stubs and drivers are to insert or update or delete data that would result in the trigger being invoked. The basic idea is to just test the DB alone even before the integration with the front end (UI) is made.
• Black box testing:

a) Since the UI and DB integration is now available; we can insert/delete/update data from the front end in a way that the trigger gets invoked. Following that select statements can be used to retrieve the DB data to see if the trigger was successful in performing the intended operation.

b) Second way to test this is to directly load the data that would invoke the trigger and see if it works as intended.

4) Stored Procedures:

Stored procedures are more or less similar to user defined functions. These can be invoked by a call procedure/execute procedure statements and the output is usually in the form of result sets.

These are stored in the RDBMS and are available for applications.

These are also tested during:

• White box testing: Stubs are used to invoke the stored procedures and then the results are validated against the expected values.
• Black box testing: Perform an operation from the frontend(UI) of the application and check for the execution of the stored procedure and its results.

5. Field constraints – Default value, unique value and foreign key:

• Perform a front end operation which overruns the database object condition
• Validate the results with a SQL Query.

Checking the default value for a certain field is quite simple. It is a part of business rule validation. You can do it manually or you can use tools like QTP to do so. Manually, you can perform an action that will add a value other than the default value into the field from the front end and see if it results in an error.

The following is a sample VBScript code:

1	<i>Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

2	<i>Set newregexp = new RegExp</i>

3	<i>newregexp.Pattern = “<Default value as required by the business requirements>”</i>

4	<i>newregexp.Ignorecase = True</i>

5	<i>newregexp.Global = True</i>

6	<i>VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)</i>

7	<i>End Function</i>

8	<i>Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

The result to the above code is true if the default value exists or false if it doesn’t.

Checking the unique value can be done exactly the way we did for the default values. Try entering values from the UI that will violate this rule and see if an error gets displayed.

Automation VB script code can be:

1	<i>Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

2	<i>Set newregexp = new RegExp</i>

3	<i>newregexp.Pattern = “<Unique value as required by the business requirements>”</i>

4	<i>newregexp.Ignorecase = True</i>

5	<i>newregexp.Global = True</i>

6	<i>VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)</i>

7	<i>End Function</i>

8	<i>Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

For the foreign key constraint validation use data loads that directly input data that violates the constraint and see if the application restricts the same or not. Along with the back end data load, perform the front end UI operations too in a way that are going to violate the constraints and see if the relevant error is displayed.

Database Testing DML command

DML command

Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled back.

---

1) INSERT command

Insert command is used to insert data into a table. Following is its general syntax,

INSERT into table-name values(data1,data2,..)

Lets see an example,

Consider a table Student with following fields.

S_id	S_Name	age

INSERT into Student values(101,'Adam',15);

The above command will insert a record into Student table.

S_id	S_Name	age
101	Adam	15

---

Example to Insert NULL value to a column

Both the statements below will insert NULL value into age column of the Student table.

INSERT into Student(id,name) values(102,'Alex');

Or,

INSERT into Student values(102,'Alex',null);

The above command will insert only two column value other column is set to null.

S_id	S_Name	age
101	Adam	15
102	Alex

---

Example to Insert Default value to a column

INSERT into Student values(103,'Chris',default)

S_id	S_Name	age
101	Adam	15
102	Alex
103	chris	14

Suppose the age column of student table has default value of 14.

Also, if you run the below query, it will insert default value into the age column, whatever the default value may be.

INSERT into Student values(103,'Chris')

---

2) UPDATE command

Update command is used to update a row of a table. Following is its general syntax,

UPDATE table-name set column-name = value where condition;

Lets see an example,

update Student set age=18 where s_id=102;

S_id	S_Name	age
101	Adam	15
102	Alex	18
103	chris	14

---

Example to Update multiple columns

UPDATE Student set s_name='Abhi',age=17 where s_id=103; 

The above command will update two columns of a record.

S_id	S_Name	age
101	Adam	15
102	Alex	18
103	Abhi	17

---

3) Delete command

Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row. Following is its general syntax,

DELETE from table-name;

---

Example to Delete all Records from a Table

DELETE from Student;

The above command will delete all the records from Student table.

---

Example to Delete a particular Record from a Table

Consider the following Student table

S_id	S_Name	age
101	Adam	15
102	Alex	18
103	Abhi	17

DELETE from Student where s_id=103;

The above command will delete the record where s_id is 103 from Student table.

S_id	S_Name	age
101	Adam	15
102	Alex	18

Database Testing DDL Commands

create command

create is a DDL command used to create a table or a database.

---

Creating a Database

To create a database in RDBMS, create command is uses. Following is the Syntax,

create database database-name;

---

Example for Creating Database

create database Test;

The above command will create a database named Test.

---

Creating a Table

create command is also used to create a table. We can specify names and datatypes of various columns along.Following is the Syntax,

create table table-name
{
 column-name1 datatype1,
 column-name2 datatype2,
 column-name3 datatype3,
 column-name4 datatype4
};

create table command will tell the database system to create a new table with given table name and column information.

---

Example for creating Table

create table Student(id int, name varchar, age int);

The above command will create a new table Student in database system with 3 columns, namely id, name and age.

alter command

alter command is used for alteration of table structures. There are various uses of alter command, such as,

• to add a column to existing table
• to rename any existing column
• to change datatype of any column or to modify its size.
• alter is also used to drop a column.

---

To Add Column to existing Table

Using alter command we can add a column to an existing table. Following is the Syntax,

alter table table-name add(column-name datatype);

Here is an Example for this,

alter table Student add(address char); 

The above command will add a new column address to the Student table

---

To Add Multiple Column to existing Table

Using alter command we can even add multiple columns to an existing table. Following is the Syntax,

alter table table-name add(column-name1 datatype1, column-name2 datatype2, column-name3 datatype3);

Here is an Example for this,

alter table Student add(father-name varchar(60), mother-name varchar(60), dob date); 

The above command will add three new columns to the Student table

---

To Add column with Default Value

alter command can add a new column to an existing table with default values. Following is the Syntax,

alter table table-name add(column-name1 datatype1 default data);

Here is an Example for this,

alter table Student add(dob date default '1-Jan-99'); 

The above command will add a new column with default value to the Student table

---

To Modify an existing Column

alter command is used to modify data type of an existing column . Following is the Syntax,

alter table table-name modify(column-name datatype);

Here is an Example for this,

alter table Student modify(address varchar(30)); 

The above command will modify address column of the Student table

---

To Rename a column

Using alter command you can rename an existing column. Following is the Syntax,

alter table table-name rename old-column-name to column-name;

Here is an Example for this,

alter table Student rename address to Location; 

The above command will rename address column to Location.

---

To Drop a Column

alter command is also used to drop columns also. Following is the Syntax,

alter table table-name drop(column-name);

Here is an Example for this,

alter table Student drop(address); 

The above command will drop address column from the Student table

SQL queries to Truncate, Drop or Rename a Table

truncate command

truncate command removes all records from a table. But this command will not destroy the table's structure. When we apply truncate command on a table its Primary key is initialized. Following is its Syntax,

truncate table table-name

Here is an Example explaining it.

truncate table Student;

The above query will delete all the records of Student table.

truncate command is different from delete command. delete command will delete all the rows from a table whereas truncate command re-initializes a table(like a newly created table).

For eg. If you have a table with 10 rows and an auto_increment primary key, if you use delete command to delete all the rows, it will delete all the rows, but will not initialize the primary key, hence if you will insert any row after using delete command, the auto_increment primary key will start from 11. But in case of truncatecommand, primary key is re-initialized.

---

drop command

drop query completely removes a table from database. This command will also destroy the table structure. Following is its Syntax,

drop table table-name

Here is an Example explaining it.

drop table Student;

The above query will delete the Student table completely. It can also be used on Databases. For Example, to drop a database,

 drop database Test;

The above query will drop a database named Test from the system.

---

rename query

rename command is used to rename a table. Following is its Syntax,

rename table old-table-name to new-table-name

Here is an Example explaining it.

rename table Student to Student-record;

The above query will rename Student table to Student-record.