Data Base Testing

 

Most applications are data-driven in some way in today’s environments. The complex systems we deal with have many different layers. Testing experience has found that we cannot effectively test the application without being aware of these layers and what issues and bugs they can introduce into the system.  A data-based application has a data access layer which is the code written to manipulate and perform actions on the database.  The databases and data stores used by the application present a multi-layered system in and of itself.

What is DB Testing: Database testing is all about testing the joins, views, imports and exports , testing the procedures, checking locks, indexing etc. It’s not about testing the data in the database. Usually database testing is performed by DBA.

Database testing involves some in depth knowledge of the given application and requires more defined plan of approach to test the data. Key issues include:

A) Data Integrity

B) Data Validity

C) Data Manipulation and updates

Tester must be aware of the database design concepts and implementation rules. Data bas testing basically include the following.

A) Data validity testing.

B) Data Integrality testing

C) Performance related to data base.

D) Testing of Procedure, triggers and functions.

Doing data validity testing we should be good in SQL queries. For data integrity testing you should know about referential integrity and different constraint. For performance related things you should have idea about the table structure and design. For testing Procedure triggers and functions we should be able to understand the same.

Why testing at the data layers are important:

• Today’s complex software systems access heterogeneous data from a variety of backend databases.

• Corporate acquisitions make multiple database backend the norm

• Focus is on functionality, reliability, recoverability, capacity planning and scalability, performance, data accuracy

• The intricate mix of client-server and Web enabled database applications are difficult to test productively.

• All data, especially production data, should be QA'd with periodic sanity checks. Is the data “reliable”, “reasonable”, “Consistent” and “accurate”.

Complexity of database interaction: Database testing is a process working with data that's stored in the database. Database testing, basically, includes the following:-

• Data Accuracy & Validity (Field size validation)
• Data Integrity (Check constraints, insert, delete, update)
  Database Objects (Stored Procedures, Views, Tables)
  Data Migration (Import, Export)
• Data Transaction Consistency and Concurrency (States & Locks)
• Performance related to database (Indices, number of triggers and procedures)
• Security related to database - Data Accessing ( unauthorized access

SQL: SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, update, delete) and database creation.

SQL statement

SELECT * FROM Individual ORDER BY LastName

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

 

Result

IndividualId	FirstName	LastName	UserName
3	Homer	Brown	notsofamous
1	Fred	Flinstone	freddo
5	Homer	Gain	noplacelike
4	Ozzy	Ozzbourne	sabbath
2	Homer	Simpson	homey