Tutorial Database Testing using SQL

DataBase Management System (DBMS)

The software used for the management, maintenance and retrieval of the data stored in a database is called DBMS.

A database management system frees the programmer from the need to worry about the organization and location of data. The primary goal of a dbms is to provide an environment which is both convenient and efficient to use in retrieving and storing information.

A Database Storage Hierarchy

Database

A collection of data files integrated and organised into a simple comprehensive file system, which is arranged to minimize duplication of data and to provide convenient access to information within that system to satisfy a wide variety of user needs.

Data files

A file is a number of related records that are treated as a unit.

Ex: a collection of all employee records for one company would be an employee file.

Records

A collection of related items of data is treated as a unit.

Ex: an employee record would be collection of all fields of one employee.

Record is sometimes referred as tuple.

Field

Individual element of data is called field.

Ex: bank cheque consists of following field cheque no, date, payee, numeric amt, signature, bank, etc.

Field is sometimes referred as data item.

Basic DBMS Terms

Relation

A relationship is an association among several entities

Ex: a cusst_acct relationship associates a customer with each account that she or he has.

Redundancy

If same piece of information is stored in database for number of times the database is said to be redundant. We should check our database should not be redundant as it wastes make our disk space, reduced efficiency of database, require more processing time, and their chances of inconsistency due to it in our database.

Ex: if we have two tables emp_details (contains details of employee) and payroll (contains payment details to employee), than if we include details of employee in payroll table, than it is said to be redundancy as same piece of information is repeated.

Inconsistency

Inconsistency is various copies of the same data may no longer agree. Inconsistency occurs due to redundancy, so redundancy should be reduced. Though we cannot eliminate the redundancy, but we can reduce it up to certain level.

Ex: if we have details of employee stored in emp_details and payroll table than while updating information we should check that both tables are updated or not, if we update the address of one employee in emp_details and same details is not updated in payroll table, than database is said to be in inconsistent state.

Propagating updates

Propagating updates ensures users that any change made to record of one files, automatically made to other files. This process is known as propagating updates, where the term “updates” is used to cover all the operations of creation, deletion and modification.

We can avoid inconsistency by using propagating update technique.

Instances

The collection of information stored in the database at a particular moment in time is called instances of the database.

Schemas

The overall design of the database is called the database schemas. Schemas are changed infrequently, if at all.

Users

There are four different types of database system users.

Application programmers

A person who prepares application program are called application programmer. Application programs operate on the data in all the usual ways: retrieving information, creating new information, deleting or changing existing information.

Sophisticated users

Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. Each such query is submitted to a query processor whose function is to take a dml statement and break it down into instructions that the database manager understands.

Specialized users

Some sophisticated users write specialized database application that does not fit into the traditional data processing framework. Among these applications are computer-aided design systems, knowledge-base and expert systems, systems that store data with complex data types

Ex: For graphics and audio data.

End users

Unsophisticated users interact with the system by invoking one of the permanent application programs that have been written previously. Thus they are persons who use the information generated by a computer based system. Retrieval is the most common function for this class of user.

Keys concept in DBMS

A key is a single attribute or combination of two or more attributes of an entity set that is used to identify one or more instances of the set.

Primary key

A primary key is a field that uniquely identifies each record in a table. As it uniquely identifies each entity, it cannot contain null value and duplicate value.

Ex: consider the customer table, which has field: customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.

Super key

If we add additional attributes to a primary key, the resulting combination would still uniquely identify an instance of the entity set. Such augmented keys are called superkey.

A primary key is therefore a minimal superkey.

Candidate key

A nominee’s for primary key field are known as candidate key.

Ex: from above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.

Alternate key

A candidate key that is not the primary key is called an alternate key.

Ex: in above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.

Composite key

Creating more than one primary key are jointly known as composite key.

Ex: in above example, if customer_number and customer_socialsecurity_number are made primary key then they will be jointly known as composite key.

Foreign key

Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.

Ex: consider two tables emp(contains employees description) and emp_edu(contains details of employee’s education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.

Normalization

It is important to understand the concept of normalization before switch on to the creation of table and its manipulation.

• Normalization is the process of grouping data into logical related groups.
• Normalization is the process of reducing the redundancy of data in a relational database.
• A database that is not normalized may include data that is contained in one or more different tables for no apparent reason. This could be bad for security reasons, disks space usage, speed of queries, efficiency of database updates, and may be most importantly, data integrity. A database before normalization is one that has not been broken down logically into smaller, more manageable tables.

Benefits of normalization:

Normalization provides numerous benefits to a database.

Some of the major benefits include wider the overall database organization.

1. The reduction of redundant data.
2. Data inconsistency can be avoided.
3. A much more flexible database design.
4. A better handle on database security.

The demand for "all round" testers, i.e. being able to test the system’s functionality through traditional testing methods and being able to show some technical knowledge are growing.
Basics of Database testing contain the following:

How to connect to the database?

Ability to write simple queries to retrieve data and manipulate the data using DML operations.
Functional flow should be very well known!
Good knowledge on table level, column level constraints, ability to understand and execute complex queries related to joins is added advantage.

INTRODUCTION TO DATABASE TESTING

• Why back end testing is so important
• Characteristics of back end testing
• Back end testing phases
• Back end test methods

STRUCTURAL BACK END TESTS

1. Database schema tests
1. Databases and devices
2. Tables, columns, column types, defaults, and rules
3. Keys and Indexes
2. Stored procedure tests
1. Individual procedure tests
2. Integration tests of procedures
3. Trigger tests
1. Update triggers
2. Insert triggers
3. Delete triggers
4. Integration tests of SQL server
5. Server setup scripts
6. Common bugs

FUNCTIONAL BACK END TESTS

1. Dividing back end based on functionality
2. Checking data integrity and consistency
3. Login and user security
4. Stress Testing
5. Test back end via front end
6. Benchmark testing
7. Common bugs

TESTING THE NIGHTLY DOWNLOADING AND DISTRIBUTION JOBS

1. Batch jobs
2. Data downloading
3. Data conversion
4. Data distribution
5. Nightly time window
6. Common bugs

TESTING THE INTERFACES TO TRANSACTION APIS

1. APIs' queries to back end
2. Outputs of back end to APIs
3. Common bugs

OTHER DATABASE TESTING ISSUES

1. Test tips
2. Test tools
3. Useful queries

What are the difference between DDL, DML and DCL commands?

1. DDL is Data Definition Language statements. Some examples:

1. CREATE - to create objects in the database
2. ALTER - alters the structure of the database
3. DROP - delete objects from the database
4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
5. COMMENT - add comments to the data dictionary
6. GRANT - gives user's access privileges to database
7. REVOKE - withdraw access privileges given with the GRANT command

1. DML is Data Manipulation Language statements. Some examples:

1. SELECT - retrieve data from the a database
2. INSERT - insert data into a table
3. UPDATE - updates existing data within a table
4. DELETE - deletes all records from a table, the space for the records remain
5. CALL - call a PL/SQL or Java subprogram
6. EXPLAIN PLAN - explain access path to data
7. LOCK TABLE - control concurrency.

1. DCL is Data Control Language statements. Some examples:

1. COMMIT - save work done
2. SAVEPOINT - identify a point in a transaction to which you can later roll back
3. ROLLBACK - restore database to original since the last COMMIT
4. SET TRANSACTION - Change transaction options like what rollback segment to use

After reading part 1, now you are aware of –

1. Why database testing is necessary?
2. Differences between backend testing and front end testing
3. Backend testing phases / Database Testing Phases
4. Backend test methodology / Database Testing methodology
5. Basics of SQL

Now let’s put more focus on SQL Statements –

1. Section 1
1. Basics of the SELECT Statement
2. Conditional Selection
3. Relational Operators
4. Compound Conditions
5. IN & BETWEEN
6. Using LIKE
2. Section 2
1. Joins
2. Keys
3. Performing a Join
4. Eliminating Duplicates
5. Aliases & In/Sub-queries
3. Section 3
1. Aggregate Functions
2. Views
3. Creating New Tables
4. Altering Tables
5. Adding Data
6. Deleting Data
7. Updating Data
4. Section 4
1. Indexes
2. GROUP BY & HAVING
3. More Sub-queries
4. EXISTS & ALL
5. UNION & Outer Joins
6. Embedded SQL
7. Common SQL Questions
8. Nonstandard SQL
9. Syntax Summary