DATABASE TESTING

First of all let us quickly understand as to what are the Relational Datatbases

Relational databases are tabular databases that are used to store target related data that can be easily reorganized and queried. They are used in many applications by millions of end users.

What are the key aspects of Database Testing?

1) Testing of the actual data.

2) Database integrity.

3) Functionality testing of database application.

The users may access, update, delete or append to the database. The modified database should be error free. To make the database error free and to deliver the quality product, regression testing of the database must be done. Regression testing involves retesting of database again and again to ensure that it is free of all errors. It is a relatively new idea in the data community. Agile software developers take this approach to the application code.

Why should we test a RDBMS?

Extensive testing of RDBMS is done due to the following reasons:

1) Quality data is an important asset: According to a survey on the importance of quality data following conclusions were drawn:

a) 95.7% of people believed that data is a corporate asset.

b) 4.3% believed that data is not a corporate asset.

c) Out of the 95.7%, 40.3% had a test suite for data validation.

d) 31.6% discussed the importance of data.

2) Target related business functions are implemented in RDBMS: RDBMS should focus on mission-critical business functionality.

3) Present approaches of RDBMS testing are inefficient: Presently we develop a database by setting up database, write code to access the database, run code, and do SELECT operation to find the query results. Although visual inspection is a good start, it may help us to find problems but not prevent them.

4) Testing provides a concrete test suite to regression test an RDBMS: Database regression testing is the act of running the database test suite on a regular basis. This includes testing of actual data, database integrity, ensuring that database is not corrupted, schemas are correct as well as the functionality testing of database applications.

5) Verification of all modifications: Making changes to the database, may result in some serious errors like missing-data and regression testing may help us in detecting such missing - data.

What should be Tested in Relational Database?

Here we will be discussing both Black Box and White Box testing approaches on relational databases.

Black Box Testing involves the following:

1) I/O Validation: Regression testing will help us in validating incoming data-values; outgoing data -values from queues, stored- functions and views.

2) Error Handling: Regression testing of an RDBMS allows us to test quasi-nulls that is, empty strings are not allowed.

3) Validating Table Structure: We can validate the relationships between the rows in different tables. This is known as referential integrity. For example: if a row in an employee table references a row within the position table then that row should actually exist.

4) Testing interaction between SQL and other components such as scripts: Regression testing allows testing of interfaces between SQL and scripts by techniques such as parameter passing.

5) Testing of stored data: Data stored in the form of tables can be tested extensively by regression testing.

6) Testing of modified data: Updating the tables might introduce new errors which can be easily detected by regression testing.

White Box testing involves the following:

1) Testing of the entire structure of stored procedures and functions: Entire schema can be tested by regression testing. We can refactor our database tables into structures, which are more performant. The process of refactoring here means a small change to a database schema, which improves its design without changing its semantics. It is an evolutionary improvement of our database schema, which will support three things:

a) New needs of our customers.

b) Evolutionary software development.

c) Fix legacy database design problems.

2) Testing various stimulations: Regression testing allows unit testing of stored procedures, functions and triggers. The idea is that the testis automatically run via a test framework and success or failure is indicated via a Boolean flag.

3) Testing all views: Regression testing allows an extensive testing of all the three views viz, conceptual, logical and physical.

4) Testing of all data constraints: Regression testing allows testing of all data constraints like null values, handling single quote in a string field, handling comma in an integer field, handling wrong data types, large size value, large size string etc.

5) Improving the quality of data: Data quality may range from syntactic mistakes to undetectable dirty data. Data quality involves four C's i.e., Correctness, Completeness, Comprehension and Consistency.

Correctness of Database: Regression testing provides a correct database by removing the following errors

a) Incorrect manipulation through the use of views.

b) Incorrect joins performed using non-key attributes.

c) Integrity constraints incorrectly used.

d) CHECK, UNIQUE and NULL constraints, which cause problems with data insertion, updations and deletions.

When should we test a Database?

Testing of databases involves initial testing of database and database refactoring. This strategy can be applied concurrently to both the application code and the database schema. Testing of databases is done not only during the release but also during the development.

New software developers follow Test-First Approach wherein a test case is first written and then code is written which will fulfill this test.

A) Step by step approach is as under:

Step - 1: A test is added for just enough code to fail.

Step - 2: Tests are then run to make sure that the new tests does in fact fail.

Step - 3: Functional code is then updated so that it passes the new tests.

Step - 4: Tests are run again.

Step - 5: If tests fail, update functional code again and retest.

Step - 6: Once the tests pass, next step is start again.

B) Test First Approach: TFA is also known as initial testing of database.

Test Driven Development: TDD is a progressive approach. It comprises TFA and refactoring (Regression testing).

This can be expressed in form of a equation:

TDD = TFA + Refactoring

The process of refactoring here means a small change to a database schema, which improves its design without changing its semantics. It is an evolutionary improvement of our database schema, which will further support following three things:

1) New needs of our customers.

2) Evolutionary software development.

3) Fix legacy database design problem.

What are the typical steps involved in Testing Databases?

Database testing primarily involves two key activities like:

A) Organising Sandboxes

B) Developing Test Cases

Now let us discuss these two key activities in detail

A) Key Activity - 1: Organising Sandboxes: Database testing involves the need of a copy of databases which are called sandboxes. These sandboxes are of following three types

1) Functionality Sandbox: In this we check the new functionality of database and refactor the existing functionality. Then we pass the tested sandbox to the next stage, which is integrated sandbox.

2) Integrated Sandbox: In this we integrate all the sandboxes and then test the system.

3) QA sandbox: After the system is tested, sandboxes are sent for acceptance testing. This will ensure the quality of the database.

B) Key Activity - 2: Development of test cases: The step by step procedure for the development of test cases is as under:

Step - 1:
Setting up of the test cases: Set up the database to a known state.

The sources of test data are

1) External test data.

2) Test scripts.

3) Test data with known values.

4) Real world data.

Step - 2: Running the test cases : The test cases are then run. The running of the database test cases is analogous to usual development testing.

Traditional Approach of Test Case Execution:

Test cases are executed on the browser side. Inputs are entered on web-input forms and data i submitted to the back-end database via the web browser interface. The results sent back to the browser are then validated against expected values.

Advantages of Traditional Approach: It is simple and no programming skill is required. It not only addresses thi functionality of stored procedures, rules, triggers and data integrity but also the functionality of application as a whole.

Disadvantages of Traditional Approach:

1) Sometimes the results sent to the browser after test case execution do no necessarily indicate that the data itself is properly written to a record in the table.

2) When erroneous results are sent back to the browser after the execution of test cases, it doesn't necessarily mean that the error is a database error.

3) A crucial danger with database testing and with regression testing in specific is coupling between tests. If we put the database in to a known state, run several tests against that known states, before setting it, then those tests are potentially coupled to one another.

Advanced Approach of Test Case Execution:

First of all we need to do a schematic preparation for Database Testing, which involves:

Generate a list of database tables, stored procedures, triggers, defaults, rules and so on. This will help us to have a good handle on the scope of testing required for database testing.

Thereafter we can follow the following points:

1. Generate data schemata for tables. Analyzing the schema will help us determine the following:

# Can a certain field value be Null?

# What are the allowed or disallowed values?

# What are the constraints?

# Is the value dependent upon values in another table?

# Will the values of this field be in the look-up table?

# What are user defined data types?

# What are primary key and foreign key relationships among tables?

2. At a high level, analyze how the stored procedures, triggers, defaults and rules work. This will help us determine the following:

# What is the primary function of each stored procedure and trigger? Does it read data and produce outputs, write data or both?

# What are the accepted parameters?

# What are the return values?

# When is the stored procedure called and by whom?

# When is a trigger fired?

3. Determine what the configuration management process is. That is how the new tables, stored procedures, triggers and such are integrated.

Step - 3: Checking the results: Actual database test results and expected database test results are compared in this step as described in the following example.

CREATE FUNCTION f_is leapyear (@ ai_year small int)

RETURNS small int

AS

BEGIN

-if year is illegal (null or -ve ), return -1

IF (@ ai_year IS NULL) or

(@ ai_year <=0) RETURN -1

IF (((@ ai_year % ) = 0) AND

((ai_year % 100) <> 0)) OR

((ai_year % 400) = 0)

RETURN 1 -leap year

RETURN 0 - Not a leap year

END

Following test cases are derived for the above piece of code:

Test_id	Year (Year to Test)	Expected Result	Observed Result	Match
1	-1	-1	-1	Yes
2	-400	-1	-1	Yes
3	100	0	0	Yes
4	1000	0	0	Yes
5	1800	0	0	Yes
6	1900	0	0	Yes
7	2010	0	0	Yes
8	400	1	1	Yes
9	1600	1	1	Yes
10	2000	1	1	Yes
11	2400	1	1	Yes
12	4	1	1	Yes
13	1204	1	1	Yes
14	1996	1	1	Yes
15	2004	1	1	Yes

Who is best suited for Testing the Database?

Key individuals responsible for doing database testing are application developers and agile database administrators. They will typically pair together and will perform pair testing which is an extension of pair programming. Pair database testing has following advantages. Firstly testing becomes a real time interaction. Secondly, discussions are involved throughout.

The database testers are also responsible for procuring database-testing tools for the organization.

Some of the dataset testing CASE tools are described below:

Category of Testing	Meaning	Examples
UNIT TESTING TOOLS	Tools, which enable us to regression test our database.	DBUnit, SQL Unit.
LOAD TESTING TOOLS	These tools will test whether our system will be able to stand high conditions of load.	Mercury Interactive, Rational Suite, Test Studio.
TEST DATA GENERATOR	They help to generate large amounts of data for stress and load testing.	Data Factory, Turbo Data.

Q.1: How can we unit test our databases?

We can test stored procedures by executing SQL statements one at a time against known results. Then the results can be validated with expected results. This is similar to unit testing.

<<<<<< =================== >>>>>>

Q.2: What are certain points that are to be kept in mind during database testing?

Following four important points need to be kept in mind.

(1) I/O validations and error handling must be done outside of the stored procedures.

(2) Do thorough analysis to design black-box test cases that produce problematic inputs that would break the constraints.

(3) Testing the interaction between SQL and other components like scripts.

(4) Understanding how to use database tools to execute SQL statements can improve our ability to analyze web-based errors like it helps us to determine whether an error is in the stored procedure code, the data itself or in the components outside the database.

<<<<<< =================== >>>>>>

Q.3: What sort of tests may be carried out during database testing?

(1) SQL databases may not be able to accept special characters (like $, @, &) as valid inputs.

(2) Data sent to the database server may be corrupted due to packet losses caused by slow connections.

(3) Proper implementation of database rollback logic. Otherwise, it causes data corruption. Design your test cases to exercise those critical areas.

(4) Check for complete loading of tables in the database.

(5) Check for proper error handling.

(6) Check that your server does not run out of disk space.

<<<<<< =================== >>>>>>

Q.4: What are milestone tests?

Milestone tests are performed prior to each development milestone. They are scheduled according to the milestone plan.

<<<<<< =================== >>>>>>

Q.5: What sort of errors are handled at the client side and at the server side?

Simple errors such as invalid inputs should be handled at the client side. Handling error conditions can be done at the server side.

Web testing

Non Functional Testing of Web Applications

Non Functional or White Box Testing of Web Applications invove either or all of the following seven types of testing

1) Configuration Testing: This type of test includes

2) Usability Testing

3) Performance Testing

4) Scalability Testing

5) Security Testing

6) Recoverability Testing

7) Reliability Testing

Let us discuss each types of these testings in detail

1) Configuration Testing: This type of test includes

a) The operating system platforms used.

b) The type of network connection.

c) Internet service provider type.

d) Browser used (including version).

The real work for this type of test is ensuring that the requirements and assumptions are understood by the development team, and that test environments with those choices are put in place to properly test it.

2) Usability Testing:

For usability testing, there are standards and guidelines that have been established throughout the industry. The end-users can blindly accept these sites since the standards are being followed. But the designer shouldn't completely rely on these standards.

While following these standards and guidelines during the making of the website, he should also consider the learnability, understandability, and operability features so that the user can easily use the website.

3) Performance Testing: Performance testing involves testing a program for timely responses.

The time needed to complete an action is usually benchmarked, or compared, against either the time to perform a similar action in a previous version of the same program or against the time to perform the identical action in a similar program. The time to open a new file in one application would be compared against the time to open a new file in previous versions of that same application, as well as the time to open a new file in the competing application. When conducting performance testing, also consider the file size.

In this testing the designer should also consider the loading time of the web page during more transactions. For example: a web page loads in less than eight seconds, or can be as complex as requiring the system to handle 10,000 transactions per minute, while still being able to load a web page within eight seconds.

Another variant of performance testing is load testing. Load testing for a web application can be thought of as multi-user performance testing, where you want to test for performance slow-downs that occur as additional users use the application. The key difference in conducting performance testing of a web application versus a desktop application is that the web application has many physical points where slow-downs can occur. The bottlenecks may be at the web server, the application server, or at the database server, and pinpointing their root causes can be extremely difficult.

We can create performance test cases by following steps:

a) Identify the software processes that directly influence the overall performance of the system.

b) For each of the identified processes, identify only the essential input parameters that influence system performance.

c) Create usage scenarios by determining realistic values for the parameters based on past use. Include both average and heavy workload scenarios. Determine the window of observation at this time.

d) If there is no historical data to base the parameter values on, use estimates based on requirements, an earlier version, or similar systems.

e) If there is a parameter where the estimated values form a range, select values that are likely to reveal useful information about the performance of the system. Each value should be made into a separate test case.

Performance testing can be done through the "window" of the browser, or directly on the server. If done on the server, some of the performance time that the browser takes is not accounted for.

4) Scalability Testing:

The term "scalability" can be defined as a web application's ability to sustain its required number of simultaneous users and/or transactions, while maintaining adequate response times to its end users.

When testing scalability, configuration of the server under test is critical. All logging levels, server timeouts, etc. need to be configured. In an ideal situation, all of the configuration files should be simply copied from test environment to the production environment, with only minor changes to the global variables.

In order to test scalability, the web traffic loads must be determined to know what the threshold requirement for scalability should be. To do this, use existing traffic levels if there is an existing website, or choose a representative algorithm (exponential, constant, Poisson) to simulate how the user "load" enters the system.

5) Security Testing:

Probably the most critical criterion for a web application is that of security. The need to regulate access to information, to verify user identities, and to encrypt confidential information is of paramount importance. Credit card information, medical information, financial information, and corporate information must all be protected from persons ranging from the casual visitor to the determined cracker. There are many layers of security, from password-based security to digital certificates, each of which has its pros and cons.

We can create security test cases by following steps:

a) The web server should be setup so that unauthorized users cannot browse directories and the log files in which all data from the website stores.

b) Early in the project, encourage developers to use the POST command wherever possible because the POST command is used for large data.

c) When testing, check URLs to ensure that there are no "information leaks" due to sensitive information being placed in the URL while using a GET command.

d) A cookie is a text file that is placed on a website visitor's system that identifies the user's "identity." The cookie is retrieved when the user revisits the site at a later time. Cookies can be controlled by the user, regarding whether they want to allow them or not. If the user does not accept cookies, will the site still work?

e) Is sensitive information stored in the cookie? If multiple people use a workstation, the second person may be able to read the sensitive information saved from the first person's visit. Information in a cookie should be encoded or encrypted.

6) Recoverability Testing:

Website should have backup or redundant server to which the traffic is rerouted when the primary server fails. And the rerouting mechanism for the data must be tested. If a user finds your service unavailable for an excessive period of time, the userwill switch over or browse the competitor's website. If the site can't recover quickly then inform the user when the site will be available and functional.

7) Reliability Testing:

Reliability testing is done to evaluate the product's ability to perform its required functions and give response under stated conditions for a specified period of time.

For example: A web application is trusted by users who use an online banking web application (service) to complete all of their banking transactions. One would hope that the results are consistent and up to date and according to the user's requirements.

Functional or Black Box Testing of Web Applications

Web Browser-Page Testing:
This type of test covers the objects and code that executes within the browser, but does not execute the server-based components. For example, JavaScript and VB Script code within HTML that does rollovers, and other special effects. This type of test also includes field validations that are done at the HTML level. Additionally, browser-page tests include Java applets that implement screen functionality or graphical output.

For web browser testing we can create test cases using following guidelines:

1) If all mandatory fields on the form are not filled then it will display a message on pressing a submit button.

2) It will not show the complete information about sensitive data like full credit card number, social security number (SSN) etc.

3) Hidden passwords.

4) Login by the user is must for accessing the sensitive information.
5) It should check the limits of all the fields given in the form.

Transaction Testing:
In this testing, test cases are designed to confirm that information entered by the user at the web page level makes it to the database, in the proper way, and that when database calls are made from the web page, the proper data is returned to the user.
Conclusion:
For trouble-free operation of a website we must follow both non-functional and functional testing methods. With these methods one can test the performance, security, reliability, user interfaces etc. which are the critical issues related to any website.

Essential Elements of Testing Web Applications

Today everyone depends upon websites for business, education and trading purpose. Websites are related to the internet. It is believed that no work is possible without internet today. There are so many types of users connected to the websites who need different type of information. So, websites should respond according to the user requirements. At the same time, the correct behaviour of sites has become crucial to the success of businesses and organizations and thus should be tested thoroughly and frequently.

Here we are discussing various methods to test a website. However, testing a website is not an easy job since we have to test not only the client-side but also the server-side. With this approach we can completely test a website with minimum number of errors.

Introduction to Web Testing:
The client end of the system is represented by a browser, which connects to the website server via the Internet.The centerpiece of all web applications is a relational database which stores dynamic contents. A transaction server controls the interactions between the database and other servers (often called "application servers"). The administration function handles data updates and database administration.

Broad Architecture of Web Applications:

According to the above Architecture of Web Applications, It is evident that we need to conduct the following tests to ensure the suitability of web applications.

1) What are the expected loads on the server and what kind of performance is required under such loads. This may include web server response time, database query response times.

2) What kind of browsers will be used?

3) What kinds of connection speeds will they have?

4) Are they intra-organization (thus with likely high connection speeds and similar browsers) or Internet-wide (thus with a wide variety of connection speeds and browser types)?

5) What kind of performance is expected on the client side (e.g., how fast should pages appear, how fast should animations, applets, etc. load and run)?

There are many possible terms for the web application development life cycle including the spiral life cycle or some form of iterative life cycle. A more cynical way to describe the most commonly observed approach is to describe it as the unstructured development similar to the early days of software development before software engineering techniques were introduced. The "maintenance phase" often fills the role of adding missed features and fixing problems.

We need to have ready answers to the following questions:

1) Will down time for server and content maintenance / upgrades be allowed? How much?

2) What kinds of security (firewalls, encryptions, passwords, etc.) will be required and what is it expected to do? How can it be tested?

3) How reliable the Internet connections are? And how does that affect backup system or redundant connection requirements and testing?

4) What processes will be required to manage updates to the website's content, and what are the requirements for maintaining, tracking, and controlling page content, graphics, links, etc.?

5) Will there be any standards or requirements for page appearance and/or graphics throughout a site or parts of a site?

6) How will internal and external links be validated and updated? How often?

7) How many times the user login and do they require testing?

8) How are CGI programs, Applets, Javascripts, ActiveX components, etc. to be maintained, tracked, controlled and tested?

How to compare Web Application & Normal Software Project

Broad Comparison among typical Web Application Project & Conventional Software Project has been drawn in the form of short questions or issues, which need to be resolved.

	Web Application Project	Conventional Software Project
1	Gathering user requirements: # What services are we going to offer our customers? # What is the best user interface and navigation to reach the most important pageswith a minimum of clicks? # What are the cunenttrends and hot technologies? This is typically based on discussions, notes and ideas.	Gathering user requirements: # What are we going to build? # How does it compare to products currently available? This is typically supported by a detailed requirements specifications.
2	Planning: # We need to get this product out now. This is purely driven by available time window and resources.	Planning: # How long will it take our available resources to build this product? # How will we test this product? This typically involves experience-based estimation and planning.
3	Analysis and Design: # How should the site look? # What kinds of logos and graphics will we use? # How do we develop a 'brand' for our site? # Who is our 'typical' customer? # How can we make it usable? # What technologies will we use? This involves short, iterative cycles of design moving parallel with implementation activities.	Analysis and Design: # What technologies should we use? # Any designpatterns we should follow? # What kind of architecture is most suitable? This is mostly based on well known technologies and design methods. This is generally complete before the implementation starts.
4	Implementation: Let us put in the framework and hang some of the key features. We can then show it as a demo or pilot site to our customers. This is Iterative prototyping with transition of prototype to a website.	Implementation: Let us decide on the sequence of building blocks that will optimize our integration of a series of builds. This involves sequential development of design components.
5	Integration: This phase typically does not exist. It is a point in time when prototyping stops and the site goes live.	Integration: # "How does the product begin to take shape, as the constituent pieces are bolted together? # Are we meeting our requirements? # Are we creating what we set out to create in the first place? This involves assembly of components to build the specified system.
6	Testing: # Do you think - It's just a website - the designer will test it as he develops it, right? # How do you test a website? # Make sure that all the links work properly? This involves testing of implied features based on a general idea of desired functionality.	Testing: # Have we tested the product in a reproducible and consistent manner? # Have we achieved complete test coverage? # Have all serious defects been resolved in some manner? This involves systematic testing of functionality against specifications.
7	Release: # "Go live NOW! We can always add the rest of the features later!" This involves transfer of the development site to the live server.	Release: # Have we met our acceptance criteria? # Is theproduct stable? # Has QA authorized the product for release? # Have we implemented version controlmethods to ensure we can always retrieve the source code for this release? This involves building a release candidate and burning it to CD.
8	Maintenance: # We just publish new stuff when it's ready. # We can make changes on the fly, since there's no installation required. # Any changes should be transparent to our users. This is an Integral part of the extended development life cycle for web apps. Average timeframe for the above can be around 4 months	Maintenance: # What features can we add for a future release? # What bug fixes? # How do we deal with defects reported by the end-user? This involves periodic updates based on feature enhancements and user feedback. Average timeframe for the above can be one to three years

Q. 1: Consider a Web Server Supporting 10,000 concurrent users who request documents from a pool of 10 different HTML documents (with an average size of 2K each) every 3.5 minutes. Calcualte the bandwidth requirement for handling this throughput?

Throughput = [10,000x(2x1024x8)} / (3.5 x 60) = 780,190 bps
or
= [10000 x (2 KB x 1024 bytes/KB x 8 bits/byte)] / (3.5 minx sec/min)

Therefore The Bandwidth (B.W) = 780,190 bps

<<<<<< =================== >>>>>>

Q. 2: Explain any of the load / performance testing tool for web application?

Tool type: Web-load simulator used for the performance analysis.

Input: Stimulated user requests.

Output: Various performance and analytical reports.

Primary user: Tester.

Secondary user: Developer.

Technology Principle: This tool enables us to simulate thousands of users accessing the website, in addition to other E-commerce and E-business activities. Virtual load can also simulate various versions of web browsers and network bandwidth. The simulated load is applied to the server whereas the performance data is collected and plotted in several useful report formats for further analysis.

Q. 3: What are the points of differences among Inspection & Walkthrough in context of Web Application?

	Inspection	Walkthrough
1	It is a five-step process that is well-formalized.	It has fewer steps than inspection and is a less formal process.
2	It uses checklists for locating errors.	It does not use a checklist.
3	It is used to analyze the quality of the process.	It is used to improve the quality of product.
4	This process takes longer time.	It does not take longer time.
5	It focuses on training of junior staff.	It focuses on finding defects.

Q. 4: How test cases may be developed for website testing?

Generated loads may be designed to interact with servers via a web browser user interface (WBUI).Consideration must be given to the types of requests that are sent to the server under test by the load generator and the resources available to the load generator.

Q. 5: A web-based project has various attributes to be used as metric. List some of them.

Some measurable attributes of a web-based projects are

(a) Number of static web pages used in a web-project. By 'static', we mean no databases.

(b) Number of dynamic web pages used in a web-project. By 'Dynamic', we mean, 'databases' are also connected to the web-pages.

(c) Word count metric which counts the total number of words on a page.