What are different issues that complicate database testing for web applications?

Web applications interface with sophisticated database management systems and build dynamic content objects created in real time using the data acquired from a database.Errors can and do occur as a consequence of each of these steps. Database testing for web applications is complicated by a variety of factors:

- Tests should be designed in such a way that they should be able to uncover errors made in translating the user's request into a form that is processed by DBMS.
- REMOTE DATABASE TO THE SERVER: Tests should be designed that uncovers errors in communication between web application and remote database.
- RAW DATA FROM DATABASE MUST BE TRANSMITTED TO WEBAPP SERVER: Tests should be designed that will demonstrate the validity of raw data received by the web application server.
- DYNAMIC CONTENT OBJECT TRANSMITTED TO CLIENT IN A FORM THAT IS DISPLAYED TO END USER: Tests should be designed to uncover errors in content object format and there should be test compatibility with client environment configurations.

Testing should ensure that :
- valid information is passed between client and server from interface layer.
- web application processes scripts correctly.
- user data is passed correctly to server.
- queries are passed to data management layer that communicates with database access routines.

User interface Layer - Tested to ensure HTML scripts are constructed properly and
transmitted to server side.
WebApp Layer on server - Ensure that user data are extracted from HTML scripts and transmitted to data transformation layer.
Data transformation function - Tested to ensure that correct SQL is created and passed to data management components.

What is the purpose of load tests?

The purpose of any load test should be clearly understood and documented. A load test usually fits into one of the following categories:
- Quantification of risks :
Determine, through formal testing, the likelihood that system performance will meet the formal stated performance expectations of stakeholders, such as response time requirements under given levels of load. This is traditional quality assurance(QA) type test. The load testing does not mitigate risk directly, but through identification and quantification of risk, presents tuning opportunities and an impetus for remediation that will mitigate risk.

- Determination of minimum configuration : Determine, through formal testing, the minimum configuration that will allow the system to meet the formal stated performance expectations, so that extraneous hardware, software and the associated cost of ownership can be minimized. This is a Business Technology Optimization (BTO) type test.

Basis for determining the business functions/processes to be included in a test

- High Frequency Transactions : The most frequently used transactions have the potential to impact the performance of all of the other transactions if they are not efficient.
- Critical Transactions : The more important transactions that facilitate the core objectives of the system should be included, as failure under load of these transactions has the greatest impact.
- Read Transactions : At least one READ ONLY transaction should be included, so that performance of such transactions can be differentiated from other more complex transactions.
- Update Transactions : At least one update transaction should be included so that performance of such transactions can be differentiated from other transactions.

What are Load Tests - End to End performance tests

Load Tests are end to end performance tests under anticipated production load. The objective such tests are to determine the response times for various time critical transactions and business processes and ensure that they are within documented expectations. Load tests also measures the capability of an application to function correctly under load, by measuring transaction pass/fail/error rates. An important variation of the load test is the network sensitivity test which incorporates WAN segments into a load test as most applications are deployed beyond a single LAN.

Load tests are major tests, requiring substantial input from the business, so that anticipated activity can be accurately simulated in a test environment. If the project has a pilot in production then logs from the pilot can be used to generate 'usage profiles' that can be used as part of the testing process, and can even be used to drive large portions of load test.

Load testing must be executed on today's production size database, and optionally with a projected database. If some database tables will be much larger in some months time, then load testing should also be performed against a projected database. It is important that such tests are repeatable, and give the same results for identical runs. They may need to be executed several times in the first year of wide scale deployment, to ensure that new releases and changes in database size do not push response times beyond prescribed service level agreements.

Entity - Relationship Model

The entity-relationship (ER) data model allows us to describe the data involved in a real-world enterprise in terms of objects and their relationships and is widely used to develop an initial database design. The ER model is important primarily for its role in database design. It provides useful concepts that allow us to move from an informal description of what users want from their database to a more detailed, and precise, description that can be implemented in a DBMS.

ENTITIES, ATTRIBUTES, AND ENTITY SETS
- A database can be modeled as:
o a collection of entities,
o relationship among entities.
- An entity is an object that exists and is distinguishable from other objects.
o Example: specific person, company, event, plant
- Entities have attributes.
o Example: people have names and addresses
- An entity set is a set of entities of the same type that share the same properties.
o Example: set of all persons, companies, trees, holidays.



- Attributes : An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set.
o E.g. Employee = (Name, Address, Age, Salary)



Types of Attributes
- SIMPLE attributes are attributes that are drawn from the atomic value domains.
E.g. Name = {John} ; Age = {23}
- COMPOSITE attributes: Attributes that consist of a hierarchy of attributes.
E.g. Address may consists of “Number”, “Street” and “Suburb” → Address = {59 + ‘Meek Street’ + ‘Kingsford’}
- SINGLE VALUED attributes: Attributes that have only one value for each entity.
E.g. Name, Age for EMPLOYEE
- MULTIVALUED attributes: Attributes that have a set of values for each entity.
E.g. Degrees of a person: ‘ BSc’ , ‘MIT’, ‘PhD’
- DERIVED attributes: Attributes Contain values that are calculated from other attributes.
E.g. Age can be derived from attribute DateOfBirth. In this situation, DateOfBirth might be called Stored Attribute.

- DOMAIN/VALUE SETS : Each simple attribute of an entity type is associated with a value set which specifies the set of values that may be assigned to that attribute for each individual entity.
- KEYS : An important constraint on the entities is the key. An entity type usually has an attribute whose values are distinct for each individual entity in the collection. Such an attribute is called a key attribute.
For example, for the entity SET EMPLOYEE = {EID, Name, Address, Age, Salary}
EID attribute is the unique key for entity set EMPLOYEE as no two employees can have two same EID. Some entity types have more than one key attribute. An entity type may also have no key.

Conceptual Data Models for Database Design

Conceptual modeling is an important phase in designing a successful database application. The database design process consists of a number of steps listed below:

Step 1: Requirements Collection and Analysis
- Prospective users are interviewed to understand and document data requirements
This step results in a concise set of user requirements, which should be detailed and complete.
- The functional requirements should be specified, as well as the data requirements. Functional requirements consist of user operations that will be applied to the database, including retrievals and updates.
- Functional requirements can be documented using diagrams such as sequence diagrams, data flow diagrams, scenarios, etc.



Step 2: Conceptual Design / Data Modeling
- Once the requirements are collected and analyzed, the designers go about creating the conceptual schema.
- Conceptual schema: concise description of data requirements of the users, and includes a detailed description of the entity types, relationships and constraints.
- The concepts do not include implementation details; therefore the end users easily understand them, and they can be used as a communication tool.
- The conceptual schema is used to ensure all user requirements are met, and they do not conflict.

Step 3: Database Design
- Two sub-steps called Database Logical Design which define a database in a data model of a specific DBMS and Database Physical Design which define the internal database storage structure are defined.
- It also defines file organization or indexing techniques.

Step 4: Database Implementation
- Many DBMS systems use an implementation data model, so the conceptual schema is transformed from the high-level data model into the implementation data model.
- This step is called logical design or data model mapping, which results in the implementation data model of the DBMS.

Step 5: Physical Design
- Internal storage structures, indexes, access paths and file organizations are specified.
- Application programs are designed and implemented.