Database Testing Fundamentals

Database Testing Fundamentals, Introduction to SQL, DBMS, RDBMS, Data, Database, Record, Data Integrity, and SQL & NOSQL Database Testing.

Database Testing Fundamentals

1) What is Data?

Collection of Information or collection of raw facts

2) What is Database?

It is a collection of related data

3) What is DBMS?

Database Management System, It has different types,

i) Hierarchical Model

ii) Network Model

iii) Relational Model

4) What is RDBMS?

Relational Database Management System, It is exclusively used to establish the relation ship between two database objects and it supports;

One to One relation

One to Many

Many to One

Many to Many

5) What are the features of RDBMS?

i) Data is stored in tables

ii) Intersection of Rows and Columns will give only one value

iii) Relation among data is established logically

iv) There are no physical links among data

v) There is no data redundancy

vi) High security for data

vii) It supports all types of data (Ex: Numbers, Characters and data images etc…)

viii) It supports Null values

ix) Supports CODD Rules

x) It supports Integrity constants

xi) Multiple users can access data from any location

6) What is Record?

A Record is nothing but a collection of information or data

7) What is Table?

Database table consists of Rows and Columns for storing set of records

8) What is SQL?

Structured Query Language is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

9) What id DDL?

Data Definition Language, it is a subset of SQL, used to define the database structure.

DDL Commands:

CREATE – to create objects in the database

ALTER – alters the structure of the database

DROP – delete objects from the database

TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed

COMMENT – add comments to the data dictionary

RENAME – rename an object

10) What is DML?

Data Manipulation Language, It is a subset of SQL, used for managing data within schema objects.

DML Commands:

SELECT – retrieve data from the a database

INSERT – insert data into a table

UPDATE – updates existing data within a table

DELETE – deletes all records from a table, the space for the records remain

MERGE – UPSERT operation (insert or update)

CALL – call a PL/SQL or Java subprogram

EXPLAIN PLAN – explain access path to the data

11) What is DCL?

Data Control Language, it is a subset of SQL, used to control the data.

DCL Commands:

GRANT – gives user’s access privileges to database

REVOKE – withdraw access privileges given with the GRANT command

12) What are the important RDBMS that uses SQL?

Important relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

13) What is SQL Server?

SQL Server is a relational database management system (RDBMS), developed by Microsoft.

SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that added several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

The latest version of SQL Server is SQL Server 2012

14) What is Oracle?

The Oracle Database (Oracle RDBMS) is an object-relational database management system (ORDBMS) developed and marketed by Oracle Corporation.

15) What is MySQL?

MySQL is an open-source Relational Database Management System. It is very fast, reliable and flexible Database Management System.

16) What is Database Testing?

Verifying Data Integrity, Data consistency, Data comparisons and Data back-up and recovery operation.

17) What is data integrity?
18) What is data comparison?

Comparing the data, it can be front-end data with back-end or Database table data with excel sheet data etc…

19) What is Database backup and recovery?

Taking the data backup from the database management system to secondary storage devices like CD-ROM, DVD, Tapes, USB drives, etc…

Data recovery from secondary storage devices to the database management system

Using Database backup and recovery data will be secured and increased the performance of the database management system.

20) What are the different stages involved in Database Testing?

In DB testing we need to check for,
1. The field size validation
2. Check constraints.
3. Indexes are done or not (for performance-related issues)
4. Stored procedures.
5. The field size defined in the application is matching with that in the DB.

21) What steps does a tester take for testing Stored Procedures?

The Tester has to go through the requirement, as to why the particular stored procedure is written for? And check whether all the required indexes, joins, updates, deletions are correct comparing with the tables mentions in the Stored Procedure. And also tester has to ensure whether the Stored Procedure follows the standard format like comments, updated by, etc…

22) How to check a trigger is fired or not, while performing Database testing?

It can be verified by querying the common audit log where we can able to see the triggers fired.

23). How to test Data Loading in Database testing?

Using with Query analyzer we can test Data loading,

We have to do the following things while we are involving in Data Load testing.

i. Know about the source data (table(s), columns, data types, and Constraints)
ii. Know about Target data (table(s), columns, data types, and Constraints)
iii. Check the compatibility of Source and Target.
iv. Open the corresponding DTS package in SQL Enterprise Manager and run the DTS package (If we are using SQL Server).
v. compare the column’s data of Source and Target.
vi. Check the number to rows of Source and Target.
vii. Update the data in Source and see the change is reflecting in Target or not.
viii. Check about junk characters and Nulls.

Database Testing Fundamentals

SQL Tutorial for Beginners
SQL Full Course Video
Follow me on social media: