Explore topic-wise InterviewSolutions in .

The need for correct data has grown exponentially in the software development field. It is very crucial to use the right data, perform the right set of OPERATIONS on it and show the results to the right set of people. Due to this, database testing helps to validate the correctness, integrity and truthfulness of the data. In this article, we have seen the most commonly asked questions in database testing that deals with RDBMS database systems to both freshers and experienced people.

Useful RESOURCES:

DBMS Interview

SQL Interview

MYSQL Interview

We can do it EASILY by USING the SELECT and EXCEPT keywords as SHOWN below:

No. A table can allow one COMPOSITE primary KEY that CONSISTS of TWO or more columns. But it does not allow for multiple primary KEYS.

We can test using the following methods:

• At compilation time, CHECK if there are any compile-time errors.
• Add proper comments and LOGGERS that WOULD give you hints about the stages completed and help you narrow down the REGION of error.

We can test the tables and COLUMNS in the databases by applying the following test cases:

• Are the database fields mapped correctly and is compatible with the REQUIRED mappings in the front-end or BACKEND requirements?
• Are the fields following proper naming conventions and have LENGTHS and sizes as per the requirements?
• Check if there are any unused or unmapped tables and columns.
• Check if the table design and the referential integrities and constraints on the columns are applied effectively and are scalable to different requirements.

Following are some good practices that need to be followed while performing database testing:

• Validate every single PIECE of data that includes metadata and functional data based on the requirement specifications.
• Test data should be verified and created after consultation with the development team and only then the VALIDATION needs to be done.
• Output data needs to be validated by using both AUTOMATED and manual processes.
• Perform boundary value analysis, graphing techniques, equivalence partitioning techniques to get the required test data inputs.
• The referential integrity of the data in the tables also needs to be carefully evaluated.
• The selection of default values in the table needs to be validated too to check for consistency of data.
• Check if appropriate logging events have been tracked and added to the database.
• Are the jobs scheduled to run are executed on time?
• Ensure that timely backup of the test database is taken to ensure test cases do not fail.
• Ensure that the test server is periodically refreshed with close to real-time data so that testing can be as accurate as POSSIBLE.

• Significant overhead can be involved to determine the STATE of transactions.
  • Solution: Plan the process that is efficient in both TIME and cost. A fine balance needs to be present while managing the quality of the database testing and the application project duration.
• Post the clean up of old test data, testers need to come up with a design of new data.
  • Solution: Plan a methodology and design test cases that can easily take up different data sets without much effort.
• To validate the SQL queries, there is a need for an SQL GENERATOR that can HELP to validate the data and can easily help in handling different test cases. This maintenance, if not done correctly, can result in messy and unmanageable scenarios.
  • Solution: Overall process of testing should be well planned and the queries should be well maintained in a way that continuous UPDATES are supported.

Whenever there are different modules in the application that are using the same DATA and also perform different operations on them, it is very much necessary to have the latest data to be shown everywhere. The most recent value of the data should be used by the systems. This is called Data Integrity. Following image shows 5 characteristics of data following data integrity:

We can apply the following test cases for TESTING out the integrity of the data:

• Are all the triggers working FINE to update the records in the reference tables?
• Are there any invalid data in the columns of each database?
• INSERT wrong data into the tables and see how the insertion behaves.
• What would happen if you try to insert the child record before inserting the parent record in the MAIN table?
• Is there any failure if you are trying to delete records referenced by another table?
• Are the replicated servers in sync? The data should not be out of sync in two or more replicated servers as it would defy the intent of replication.

Consider a simple SQL CODE below:

We will test for the ACID properties for two columns X and Y. There is also a constraint added on the table that the sum of values in columns X and Y should always be 50.

• Atomicity: Here, we test that the transactions done on the table is either successful or failed. No records should be updated if the transactions fail.
• Consistency: Here, we test that the values in columns X and Y are updated correctly by following the constraint that the sum of these two values is always 50. The insertion or updation should not be allowed if the sum is not equal to 50.
• Isolation: Here, in the presence of multiple transactions, we need to test they are happening in isolation.
• Durability: Here, the test cases should consider that if a transaction has been committed, it should remain even after the incidents of power losses, crashes and errors. If we are using sharded or DISTRIBUTED database applications, RIGOROUS testing needs to be done to ENSURE the data is not lost.

Transactions require the ACID properties to be satisfied. To achieve that, we use the below statements:

The Rollback command ENSURES that the database changes are ROLLED back to the previous stable commit and ensures consistency. Once the transactions are executed, we can make use of SELECT queries to RUN on the tables where the transaction took place and validate the data.

Data load testing requires the testers to know about source databases and the destination databases and their objects like tables, columns, design, constraints etc. While performing tests, we need to validate the compatibility of the source and destination databases using the DTS package in SQL Enterprise Manager. We can follow the below steps for the same:

• OPEN the DTS package and run it.
• Compare the column data of the source and destination tables.
• Check for the NUMBER of rows in source and destination tables.
• Post updating data in source tables, check if the same changes are reflected in the destination database.
• Check about the data QUALITY WHETHER the destination data received any junk characters and NULL values.
• Check for the maximum processable VOLUME on the servers and evaluate the response times and speed of data load from the source to destination.

SQL Constraints are used for setting the rules for the records in the database table. If any constraints are not satisfied, then the action can be aborted. The constraints are defined when we create the database objects. It can also be ALTERED by using the ALTER commands. SQL has 6 major constraints, they are:

1. NOT NULL: This constraint is used for specifying that a column cannot have NULL values.
2. UNIQUE: This constraint specifies that each column has a unique value, meaning the values are not repeated.
3. PRIMARY KEY: This constraint also combines NOT NULL and UNIQUE constraints and indicates that one or more combinations having this key is used for uniquely IDENTIFYING a record in the database table.
4. FOREIGN KEY: It is used for ensuring the referential integrity of that record in the database table. It matches the value of a column in one table with the value defined as the primary key in the other table.
5. CHECK: It is used for ensuring whether the column values fulfil given specified conditions.
6. DEFAULT: This constraint is used for adding default values to the column whenever needed. If the user specifies any value in the DEFAULT constraint, then at the time of record creation, if we do not specify values to that column, the default value will be SAVED in the table.

Firstly, when migration happens, we need to be aware of all the changes and enhancements are done in the new SQL server. Based on this information, design your test case SUITE by considering the FOLLOWING points:

• What is the data type used?
• What is the length of the fields used in the server?
• Are the database objects CREATED as EXPECTED in the new SQL server?
• Perform stress testing on the new SQL server to identify any risks ASSOCIATED with the migrated environment.

For testing the database triggers and procedures, it is required to KNOW the input parameters to these functionalities and know what would be the expected output for these. The EXEC statement can be used for determining the behaviour of the TABLES when the procedures or triggers are run.

You can also achieve this by creating SQL unit tests for checking the database objects modified due to the EXECUTION of the triggers or procedures. The SQL unit tests follow the 3 rules as shown below:

The test case should check whether the objects exist in the database or not. It should check or validate what the normal outputs would be for success scenarios. It should also check for the behaviour of the system under the influence of NEGATIVE test cases.

This testing involves testing database structures that DEPEND on schema, tables, triggers, functions, procedures and also the servers USED for databases. Following are the types of structural testing:

• Schema Testing: Here, the exact schema NAME should map between both front-end and backend. The schema validation is very important because in some cases, the schema of the tables would be different from the actual business requirement and the front-end applications. This also involves verifying unmapped tables or views or columns.
• Tables Testing: This testing involves testing table names and testing columns. The names mapped to frontend and backend should be the same and the datatype and sizes of the columns should be as per the requirements specified by the business. It also involves testing constraints applied to the tables and columns. Furthermore, testing of indexes based on the property of clustered or non-clustered and their functionality should also be tested accordingly.
• Procedure and Function Testing: Here, the testers have to test the procedures and functions available in the database and validate for the list of points below:
  • Did the team follow the business requirements correctly?
  • Is the code following good practices and proper NAMING conventions?
  • Are the parameters for input and output of these as per the expected requirements?
  • Are the exceptions handled?
  • Are the procedures and functions inserting data to required tables properly?
  • Are the procedures and functions updating/modifying data in the required tables properly?
• Trigger testing: The testing rules are similar to the procedure or functional testing. In addition to those rules, we have to check if the triggers are GETTING triggered/executed at required instants.
• Database Server Testing: Test whether the database configurations, RAM, capacity of processors, storage capacity etc based on the business requirements.

The process to perform database testing is very similar to the steps that we follow for testing any other applications. FOLLOWING are the steps that are followed:

1. PREPARE the TEST environment and test scripts
2. Run the script
3. Check for the test results
4. Validate the results against the expected results.
5. Report the validation findings to the DEVELOPERS and the stakeholders.

Database testing ensures that the data in the database that is received from various sources are correct and as per the business requirements or not. It helps to analyse the risks associated with incorrect data, PROTECT from data losses due to incorrect transactions and also properly check for unauthorized access to the data. In most of the applications, the User Interface is GIVEN the most importance while testing to validate the correctness of the data. But in cases where there is a lack of UI or view, it is very much essential to test the quality of the INFORMATION in the database.

Consider the example of a banking application where lots of users use it to perform transactions. While testing the database, it is very much important to NOTE the following points:

• Does the application store the relevant transaction information and display the data correctly to the correct user?
• Ensure that information is not lost during the transaction process.
• Ensure that the system doesn’t store partially performed transactions or ABORTED transactions that are not saved.
• Ensure that only authorized users have access to the relevant information.

To perform these validations, it is very much needed to possess the art of data testing or database testing.