All About Database Testing Why to Test, How to Test, What to Test Software Testing Help. Database Testing Guide Computer applications are more complex these days with technologies like Android and also with lots of smartphone apps. Thumbnails/access-manage-project-open-issues-templatehok1hqj.PNG' alt='Student Database Software' title='Student Database Software' />Business Software Download Barcode Generator Label Maker Bulk SMS Software Database Converter Keylogger monitoring Data Protection Setup Maker Web Chat for websites. Oracle Database Testing This shall be one of the 3 parts of Oracle Database testing series. Lets start with the Introduction to Oracle Database Testing. All about database testing In this article we will learn all about DB testing. Why to test How to test What to test These are some of the aspects we will cover. The more complex the front ends, the more intricate the back ends become. So it is all the more important to learn about DB testing and be able to validate databases effectively to ensure security and quality databases. In this article, you will learn all about Database Testing. Why test How to test What to test. Why do we test a database Below, we will see why the following aspects of a DB should be validated 1 Data Mapping In software systems, data often travels back and forth from the UI user interface to the backend DB and vice versa. So these are some aspects to watch for Check whether the fields in the UIfrontend forms are mapped consistently with the corresponding fields in the DB table. 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 whether the invoked action in itself is successful or not. Schaum`S Study Guides here. ACID properties validation Atomicity, Consistency, Isolation and Durability. Every transaction a DB performs has to adhere to these four properties. Atomicity means that a transaction either fails or passes. This means that even if a single part of a 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 DBIsolation If there are multiple transactions and they are executed all at once, the resultstate 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. Data integrity This means that following any of the CRUD operations, the updated and most recent valuesstatus 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. Renaissance Place ID What is it Renaissance Place ID is a unique identifier that provides a shortcut to your Renaissance Place site. Where can I use itAustin, Texasbased software developers, Vertex creates Webbased software applications, mobile apps, and database solutions for business. This is simple student information system project. Wher you can do following things 1. Add Records 2. List Records 3. Modify Records. Business rule conformity More complexity in 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 Select. Select from lt tablename where lt condition Apart from Select, SQL has 3 important types of commands DDL Data definition language. DML Data manipulation language. 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. Data Manipulation language. Includes statements to add, update and delete records. Data control language Deals with giving authorization to users for manipulation and access to the data. Student Database Software' title='Student Database Software' />Grant and Revoke are the two statements used. Grant syntax Grant selectupdate. On lt table name To lt user id. Revoke syntax Revokeselectupdateon lt table name fromlt user id. What to test different components. Transactions When testing transactions it is important to make sure that they satisfy the ACID properties. These are the statements commonly used BEGIN TRANSACTION TRANSACTIONEND TRANSACTION TRANSACTIONThe 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 lt tables which involve the transactions 2 Database schema A database schema is nothing more than a formal definition of the 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 DESClt table name to validate the schema. Regular expressions for validating the names of the individual fields and their values. Tools like Schema. Crawler. 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 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 during 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 insertdeleteupdate 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. The second way to test this is to directly load the data that would invoke the trigger and see if it works as intended. Stored Procedures Stored procedures are more or less similar to user defined functions. These can be invoked by Call ProcedureExecute 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. Field constraints 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 a value other than the default value of the field from the front end and see if it results in an error. At any given time during the school. Star Series site. ESPED has kept up with the demands and provided fast connection times and processing speeds. Beth Poss. Instructional Technologist, Northside Independent School District.