Database Testing guidelines

S. No	Guide Lines	Examples
1	Validate the connectivity of the DB	Check whether the user is able to connect to the database
2	Validating the data that is getting stored in the table	If user is giving some inputs through GUI then check for the data that is getting stored in the tables
3	Field type validation for each input	Suppose if user enters some alphabets in the place of pin code, In GUI we will get error message but what happens at the table level
4	Validating the integrity of the data that is getting stored in the table	Assume that user is giving his bank details and it is supposed to be stored in bank_details table and if he gives his personal details it is supposed to be stored in personal_details table. Now when try to get the all details in a report, check how the data in the two tables are integrated.
5	Validating the Data corruption	Check directly in the table for data corruption – means that what ever the data that we feed the system should be saved in the proper readable format
6	Validating the Data Encryption and decryption	For some sensitive data, while saving the records in table it should be encrypted and while displayed in the GUI it should be decrypted Eg: Password, IPIN, QPIN etc
7	Validating the users (with appropriate role) who can access the DB directly	Generally end users are not allowed to access the DB. Login as end user and check whether you can access the DB.
8	Validating the users (with appropriate role) who can change the Data in the DB directly	Even if users has read permission on DB not all the users will have the write permission, Check for the update, delete operations on DB directly with proper and improper permissions
9	Lack of data integrity across multiple tables in the DB	When some user details are getting stored in the multiple table then the data across table should be integrated with primary key (which can’t be null or duplicated) or else it will create problem in accessing the data.
10	Validate the data that is displayed in the user viewable report	Genarally the report will fetch data from multiple tables. In this case check the data in the report. We can also check whether the sensitive data are displayed in the report and in some pages there will be some option like display my personal information to others, check the data that is getting displayed based on the selection.
11	Validate the store procedure that is getting the data for the report	Assume that the report is getting generated by executing the SP, then if possible take the SP break it down to small queries and we can check the SP
12	Depending on the application size try to get the health of the DB Server (Not all client would be expecting this but this can used by our company to get more work)	In windows we can monitor the health of DB by perfmon and in Linux we have command like Vmstat. For any DB input output operation is very important. We should also track the CPU, memory utilizations along with locks, dead loacks etc.
13	Performance of the DB if the number of records in the reports are huge	Check the performance of the DB when the number of records are huge – means after getting the request how much time does the DB takes to respond to that query.
14	Finally, make sure that test cases are there are for certain basic operation like create, update, retrieve & deleting	This is called CURD testing. Any user should be allowed to this operation from UI level (again based on permission and type of application).
S.No	Advanced points during DB Testing
1	Before we start testing the DB we would need enough realistic data which should be similar to production environment (as much as possible). Dummy test data that we are using will not catch all bugs
2	Dummy DB should be created and by adding, deleting the records we could easily know the relation between the tables
3	Some basic adjustments for DB tuning like primary heap size, secondary heap size, query logs etc should be analyzed so that we can have better idea about the testing
4	Testing indexes – Before index what is the time taken and after indexing what is the time taken
5	Validating the need of SP – SP will store the data in the temp table before it gives data to the user, Some kind of validation should be there to decide the need of store procedure
6	Testing SP – Testing the store procedure could be break the SP in to smaller queries and execute one by one.
7	Error Handling of SP can be tested – Pass parameters which is not accepteable or pass parameters which give the unexpected parameter during the execution of SP
8	Get the health report of the DB like CPU utlization, Memory utilization
9	Get the query logs which can say give you the details about the query which has taken more than the expected seconds
10	Get the Input output report of the DB which plays the vital role in the DB tuning