When testing Transactions it is important to make sure that they satisfy the ACID properties.
These are the statements commonly used:
- BEGIN TRANSACTION TRANSACTION#
- END TRANSACTION TRANSACTION#
The Rollback statement ensures that the database remains in a consistent state.
After these statements are executed, use a Select to make sure the changes have been reflected.
- SELECT * FROM TABLENAME <tables which involve the transactions>
#2) Database Schemas
A Database Schema is nothing more than a formal definition of how the data is going to be organized inside 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 search.
- 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 methods 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
When a certain event takes place 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 add the student to the corresponding subject tables once he is added to the student table.
The common method to test is to execute the 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 in both the Black-box and White-box testing phases.
- White box testing: Stubs and Drivers are used 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) The 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 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 front end (UI) of the application and check for the execution of the stored procedure and its results.
#5) Field Constraints
The Default value, Unique value, and Foreign key:
- Perform a front-end operation which exercises the Database object condition
- Validate the results with a SQL Query.
Checking the default value for a certain field is quite simple. It is part of business rule validation. You can do it manually or you can use tools like QTP. Manually, you can perform an action that will add value other than the default value of the field from the front end and see if it results in an error.
The following is a sample VBScript code: