Database Testing

Database systems play an important role in nearly every modern organization. The ultimate objective of database analysis, design, and implementation is to establish an electronic data store corresponding to a user’s conceptual world.

Functionality of the database is a very critical aspect of application’s quality; problems with the database could lead to data loss or security violation, and may put a company at legal risk
depending on the type of data being stored. Applications consist of a database and improving quality of data in an organization is often a daunting task. A database should be evaluated throughout the database development life cycle to prepare a quality database application.
Data in a database may be input from and displayed on a number of different types of Systems. Each of these types of systems has unique system limitations, which may dictate how data should be formatted in your database. A database should be evaluated based on the factors
such as data integrity, consistency, normalization, performance, security and very important - the expectations of its end users.

                                 The database design process is decided by finding the requirements and needs of the end user. Uncertainty about understanding the requirements can be reduced only after significant analysis and discussions with users. Once the user requirements are clear, the
process of behavior implementation consists of the design and construction of a solution domain following the problem domain. Because of the difficulties associated with the changing
requirements, the database developer must attempt to develop a database model which closely matches the perception of the user, and deliver a design that can be implemented, maintained and modified in a cost-effective way. Diagrammatic representation using entity relationship
diagrams, object models, data flow diagrams, allows the information described in a visual format in a meaningful way.

Database testing is one of the most challenging tasks to be done by software testing team.
A Database Tester, by understanding the referential integrity and database security, and by having a good grasp on the various technologies and data formats used to transfer and retrieving the data from the database, can test database to avoid problems. Testing should be included in various phases of database development life cycle. The cycle typically consists of several stages from planning to designing, testing and deployment. In the first phase of database development process, requirements are gathered; checklists can be used as part of the evaluation process for the database specification. After gathering requirement and understanding the need for the database, a preliminary list of the fields of data to be
included in the database should be prepared. We should have complete information about what information is required by the client and what type of fields are required to produce that information. Next determine if the logical data model is complete and correct. Confirm the design with the business community in a design review process. Create a logical Entity Relationship Diagram (ERD) to graphically represent the data store. Determine if the data model is fully documented (entities, attributes, relationships) Attributes have correct data type, length, NULL status, default values. General discussion of business rules to be enforced by database level constraints should be carried out e.g.
• Not null constraints
• Check constraints
• Unique constraints
• Primary key constraints
• Foreign key constraints
Business rules to be enforced by triggers and procedures should be discussed along with the business rules to be enforced by application code. After this the normal forms should be tested with the help of test data. Testing physical database design includes testing table definitions,constraints, triggers and procedures. Black Box testing techniques like Boundary value analysis can be used. We can test the table definition by testing the column definitions and constraints that have been imposed:Database constraints can be checked as follows:
1. Primary Key- Write Test Case to insert duplicate value in Primary Key column.
2. Insert record to violate Referential Integrity Constraint.
3. Delete record to violate Referential Integrity Constraint.
4. Insert NULL value in NOT NULL column.
5. Insert values to violate check constraints by inserting values outside input domains.                    
For relational databases queries are written using SQL. We can test database queries by identifying different situations and data values. SQL conditions can be tested using Decision Condition Coverage:
a. Select statements use conditions in Where Clause and Having Clause for Group By columns.
I. Conditions written using AND logical operator requires (T, T), (T, F), (F, T) outcomes for two operands to be tested.
ii. Conditions written using OR logical operator requires (F, F), (T,
F), (F, T) outcomes to be tested.
Testing requires that every condition affecting the result takes all possible outcomes at least once.
b. Testing SQL statements involving NULL values.
I. Requires Testing conditions with each operand in the condition taking NULL value.
ii. For a Group By Clause, NULL values have to be considered.
c. For sub queries, include test cases to return zero and more rows.
d. SQL statements like Update, Insert, and Delete also need to be tested for conditions.
Apart from testing the table definitions and SQL statements a database tester should test the triggers, procedures and functions. These objects can be unit tested by finding various paths of
execution in the code and functionality can be tested by executing the code - providing required inputs and checking the output generated.

Let’s see how can we design test cases to test table definition i.e. column definitions and constraints. Refer to ‘Item’ table storing details of items in stock. Details of sales orders placed for various items are stored in another table ‘Sales’. The table definitions are as follows:
Table Definitions
To test the constraints, we can design test cases as follows:
ITEM Table
Test Case ID: TCcheck _itemcodePK
Objective: To evaluate Primary key constraint on Item code in item table.
Description: Insert two records with I001 as Item code.
Expected Result: second record should not be saved in database.
Test Case ID: TCcheck _itemcode1
Objective: To evaluate check constraint on Item code in item table.
Description: Insert a record with I001 as Item code.
Expected Result: record should be saved in database.
Test Case ID: TCcheck _itemcode2
Objective: To evaluate check constraint on Item code in item table.
Description: Insert a record with I555 as Item code.
Expected Result: record should be saved in database.
Test Case ID: TCcheck _itemcode3
Objective: To evaluate check constraint on Item code in item table.
Description: Insert a record with invalid Item code as I000.
Expected Result: error message should be displayed.
Test Case ID: TCcheck _itemcode4
Objective: To evaluate check constraint on Item code in item table.
Description: Insert a record with I556 as Item code.
Expected Result: error message should be displayed.
Test Case ID: TCcheck _Description1
Objective: To evaluate NOT NULL constraint on description column in item table.
Description: Insert a record with no value for description column.
Expected Result: error message should be displayed.
Test Case ID: TCcheck _price
Objective: To evaluate check constraint on price column in item table.
Description: Insert a record with price=-10.
Expected Result: error message should be displayed.
Test Case ID: TCcheck _delete
Objective: To evaluate referential integrity constraint on item code column in item table.
Description: insert a record in sales table with item code I001.
Delete record from item table where item code=I001.
Expected Result: error message should be displayed. SALES Table
Test Case ID: TCcheck _itemcode
Objective: To evaluate references constraint on item code column in sales table.
Description: Insert a record with item code not existing in item table.
Expected Result: error message should be displayed.
Test Case ID: TCcheck _orderID
Objective: To evaluate check constraint on Order_ID column in sales table.
Description: Insert a record with Order_ID as 1001.
Expected Result: error message should be displayed. 

Now consider a requirement for displaying details of all the items for which qty ordered is>10. We can find the required details by writing

SQL query:
Select item_code from sales where qty>10
To test this query we can prepare a test sales table having records with qty>10. When executed, this query should return all the matching records. Similarly we can check the query by preparing a table not having any matching records.

Blog posting on google search

ISTQB Examp Paper

ISTQB Examp Paper 1

1 When what is visible to end-users is a deviation from the specific or expected behavior, this is called:

a) an error

b) a fault

c) a failure

d) a defect

e) a mistake

2 Regression testing should be performed:

v) every week

w) after the software has changed

x) as often as possible

y) when the environment has changed

z) when the project manager says

a) v & w are true, x – z are false

b) w, x & y are true, v & z are false

c) w & y are true, v, x & z are false

d) w is true, v, x y and z are false

e) all of the above are true

3 IEEE 829 test plan documentation standard contains all of the

following except:

a) test items

b) test deliverables

c) test tasks

d) test environment

e) test specification

4 Testing should be stopped when:

a) all the planned tests have been run

b) time has run out

c) all faults have been fixed correctly

d) both a) and c)

e) it depends on the risks for the system being tested

5 Order numbers on a stock control system can range between 10000 and99999 inclusive. Which of the following inputs might be a result of designing tests for only valid equivalence classes and valid boundaries:

a) 1000, 5000, 99999

b) 9999, 50000, 100000

c) 10000, 50000, 99999

d) 10000, 99999

e) 9999, 10000, 50000, 99999, 10000

6 Consider the following statements about early test design:

i. early test design can prevent fault multiplication

ii. faults found during early test design are more expensive to fix

iii. early test design can find faults

iv. early test design can cause changes to the requirements

v. early test design takes more effort

a) i, iii & iv are true. Ii & v are false

b) iii is true, I, ii, iv & v are false

c) iii & iv are true. i, ii & v are false

d) i, iii, iv & v are true, ii us false

e) i & iii are true, ii, iv & v are false

7 Non-functional system testing includes:

a) testing to see where the system does not function properly

b) testing quality attributes of the system including performance and usability

c) testing a system feature using only the software required for that action

d) testing a system feature using only the software required for that function

e) testing for functions that should not exist

8 Which of the following is NOT part of configuration management:

a) status accounting of configuration items

b) auditing conformance to ISO9001

c) identification of test versions

d) record of changes to documentation over time

e) controlled library access

9 Which of the following is the main purpose of the integration

strategy for integration testing in the small?

a) to ensure that all of the small modules are tested adequately

b) to ensure that the system interfaces to other systems and networks

c) to specify which modules to combine when and how many at once

d) to ensure that the integration testing can be performed by a small team

e) to specify how the software should be divided into modules

10 What is the purpose of test completion criteria in a test plan:

a) to know when a specific test has finished its execution

b) to ensure that the test case specification is complete

c) to set the criteria used in generating test inputs

d) to know when test planning is complete

e) to plan when to stop testing

11 Consider the following statements

i. an incident may be closed without being fixed

ii. incidents may not be raised against documentation

iii. the final stage of incident tracking is fixing

iv. the incident record does not include information on test

environments

v. incidents should be raised when someone other than the author of the software performs the test

a) ii and v are true, I, iii and iv are false

b) i and v are true, ii, iii and iv are false

c) i, iv and v are true, ii and iii are false

d) i and ii are true, iii, iv and v are false

e) i is true, ii, iii, iv and v are false

12 Given the following code, which is true about the minimum number of test cases required for full statement and branch coverage:

Read P

Read Q

IF P+Q > 100 THEN

Print “Large”

ENDIF

If P > 50 THEN

Print “P Large”

ENDIF

a) 1 test for statement coverage, 3 for branch coverage

b) 1 test for statement coverage, 2 for branch coverage

c) 1 test for statement coverage, 1 for branch coverage

d) 2 tests for statement coverage, 3 for branch coverage

e) 2 tests for statement coverage, 2 for branch coverage

13 Given the following:

Switch PC on

Start “outlook”

IF outlook appears THEN

Send an email

Close outlook

a) 1 test for statement coverage, 1 for branch coverage

b) 1 test for statement coverage, 2 for branch coverage

c) 1 test for statement coverage. 3 for branch coverage

d) 2 tests for statement coverage, 2 for branch coverage

e) 2 tests for statement coverage, 3 for branch coverage

14 Given the following code, which is true:

IF A > B THEN

C = A – B

ELSE

C = A + B

ENDIF

Read D

IF C = D Then

Print “Error”

ENDIF

a) 1 test for statement coverage, 3 for branch coverage

b) 2 tests for statement coverage, 2 for branch coverage

c) 2 tests for statement coverage. 3 for branch coverage

d) 3 tests for statement coverage, 3 for branch coverage

e) 3 tests for statement coverage, 2 for branch coverage

15 Consider the following:

Pick up and read the newspaper

Look at what is on television

If there is a program that you are interested in watching then switch

the the television on and watch the program

Otherwise

Continue reading the newspaper

If there is a crossword in the newspaper then try and complete the

crossword

a) SC = 1 and DC = 1

b) SC = 1 and DC = 2

c) SC = 1 and DC = 3

d) SC = 2 and DC = 2

e) SC = 2 and DC = 3

16 The place to start if you want a (new) test tool is:

a) Attend a tool exhibition

b) Invite a vendor to give a demo

c) Analyse your needs and requirements

d) Find out what your budget would be for the tool

e) Search the internet

17 When a new testing tool is purchased, it should be used first by:

a) A small team to establish the best way to use the tool

b) Everyone who may eventually have some use for the tool

c) The independent testing team

d) The managers to see what projects it should be used in

e) The vendor contractor to write the initial scripts

18 What can static analysis NOT find?

a) The use of a variable before it has been defined

b) Unreachable (“dead”) code

c) Whether the value stored in a variable is correct

d) The re-definition of a variable before it has been used

e) Array bound violations

19 Which of the following is NOT a black box technique:

a) Equivalence partitioning

b) State transition testing

c) LCSAJ

d) Syntax testing

e) Boundary value analysis

20 Beta testing is:

a) Performed by customers at their own site

b) Performed by customers at their software developer’s site

c) Performed by an independent test team

d) Useful to test bespoke software

e) Performed as early as possible in the lifecycle

ANSWERS

1 C

2 C

3 E

4 E

5 C

6 A

7 B

8 B

9 C

10 E

11 B

12 B

13 B

14 B

15 E

16 C

17 B

18 C

19 C

20 A