How many ways we can parameterize data in QTP?

Parameterization is one of the important provisions we have in quick test professional which has enabled the passing of the values to the tests very simple. This parameterization feature enables one to pass multiple values at a time to a test.

And what more? 

The process of parameterization has proven to be a great helping hand while carrying out the data driven testing. Data driven testing is the kind of testing that involves working with multiple sets of data on the same tests. 

The quick test professional comes with various ways for carrying out the process of parameterization:

1. Parameterization via loop statements
2. Parameterization via data table
3. Dynamic test data submission
4. Obtaining test data via front end objects
5. Getting test data directly from the spread sheets, flat files or we can say external files.
6. Getting test data directly from the oracle, MSaccess or we can say data bases.

Now we shall discuss the above 6 different ways of parameterizing the tests in detail.

1. Parameterization via loop statements: 

In this method the sequential numbers or logical numbers can be passed via the loop statements however you cannot pass strings to the tests.

2. Parameterization via data table: 

One data table or spread sheet is provided along every test in quick test professional. The provided data table along with the test can be used very well for the data driven testing. Furthermore the following 3 purposes are served by the data table:

a)   To import the test data from external spread sheets: for doing this open the data table and place the pointer. Then right click and select the import from file option. Then you need to enter the path of the spread sheet to be imported from and hit ok. Later connect to the test.

b)   To import the test data from the external flat files: for doing this open the data table and place the pointer. Then right click and select the import from file option. Then you need to browse the path of the file to imported and press ok. Later connect to the test.

c)   To import the test data from the data bases: for doing this the user needs to first create a DSN of the data base i.e., the data source name by making use of the SQL commands. This can be done by creating a test data base and saving the created data base with .mdb extension in msaccess. Next step involves creation of a data table and filling it up with data. The last step is to create a DSN and you can start importing the data.

    3. Dynamic test data submission: 

   This also involves the use of the loop statements however the data has to be entered again and again by the user.

    4. Obtaining test data via front end objects

   5. Getting test data directly from the spread sheets, flat files or we can say external files.

   6. Getting test data directly from the oracle, MSaccess or we can say data bases.

There is one more method for parameterizing the data apart from those mentioned above and is also less followed. The method makes use of the dictionary object for the purpose of parameterization. There are several types of parameterization namely:

1. Data table
2. Environment variable
3. Random number
4. Test and action

The data table consists of the following parameters:

1. Input parameter
2. Out put parameter
3. Random number
4. Environment variable. 

What databases can Test Director reside on?

The test director is another popular software test management tool developed by the HP. The test director or TD as we call in short form has proved to be a very effective tool in the management of all the tests that are performed over a particular software system or application. 

What is the use of Test Director?

- The whole burden of the organization of the software testing process is taken up by the test director itself. 

- It helps in directing the process of software testing in the same way as a movie director takes up the responsibility for directing the shoot of a movie. 

- There have been many cases in which the test director has considerably helped in the creation of the test cases.

- This software test management tool is categorized under the category of web based test management tools since it relies heavily on the web service for the management of the tests. 

- The working of the test director takes place around a centralized data base i.e., a centralized data base form its center of operation. 

- The test director helps in the management of the following aspects:

       1.   Specifications documentation

       2.   Requirements documentation

       3.   Test plans

       4.   Test cases

       5.   Defect tracking

       6.   Defect managing and so on.

- The original developer of the test director is the mercury interactive which is said to be taken under the HP. 

- It would not be wrong to say that the test director is actually a more of a central repository where all the information and reports related to the management of the bugs is stored and processed. 

- In some of the cases, the test director has even helped in the execution of the pre automated test scripts with the help of a special environment known as the test director environment.

- The purpose of the test director lies in the following tasks:

       1.   Creation of the test plans

       2.   Preparation of the test cases

       3.   Execution of the test cases.

       4.   Generation of the bug reports

       5.   Maintenance of the test scripts etc.

In this article we shall also see what all are the platforms that are supported by the test director.

The test director seems to be supporting almost all the platforms that call for a need for high levels of collaboration as well as communication among the testing teams that are distributed and where an efficient global testing process is required. 

Types of Databases on which TD reside

- Since the test director is a web based site administrator, it can reside only up on the data bases which are web based. 

- There is also one more reason behind this fact which is that the test director needs to monitor all the connected users, licenses and test director server information and this information it needs to store in such a data base that it can access from any computer system and therefore it uses a web based data base.

- Another point that can be given is that now most of the projects that are being done using the test director are grouped by the domain which consists of other related test director projects. 

- Such a domain can help in much efficient organization and management of your project! But to access it a centralized data base is required.

- There is one more thing that the test director comes with a feature called collaboration module via which one test director user can chat with another test director user and this also requires a web based centralized data base. 

What are different characteristics of Scalability Testing?

Scalability can be essentially defined as the ability of a software application, network, process or program to effective and gracefully handle the increasing workload and effectively and easily carry out the specified tasks assigned properly. Throughput is the best example for this ability of a software application.

- Scalability as such is very difficult to define without practical examples.
- Therefore, scalability is defined based on some dimensions.
- Scalability is very much needed in communication areas like in a network, in software applications, in handling huge databases and it is also a very important aspect in routers and networking.
- Software applications and systems having the property of scalability are called scalable software systems or applications.
- They improve throughput to surprising extent after addition of new hardware devices. Such systems are commonly known as scalable systems.
- Similarly if a design, network, systems protocol, program or algorithm is suitable and efficient enough and works well when applied to greater conditions and problems in which the input data is in large amount or the problem or situation has got several nodes, they are said to be efficiently scalable.

If, during the process of increasing the quantity of input data the program fails, the program is not said to scale. Scalability is so much needed in the field of information technology. Scalability can be measured in several dimensions. Scalability testing deals with testing of these dimensions only.

The kinds of scalability testing have been discussed in detail below:

- Functional scalability testing:
In this testing new functionalities which are added to the software application or the program to enhance and improve its overall working are tested.

- Geographic scalability testing:
This testing tests the ability of the software system or the application to maintain its performance and throughput, and usefulness irrespective of distributing of working nodes in some geographical pattern.

- Administrative scalability testing:
This testing deals with the increment of working nodes in software, so that a single difficult task is divided among smaller units making it much easier to accomplish.

- Load scalability testing:
This testing can be defined as the testing of the ability of a divided program to divide further and unite again to take light and heavy workload accordingly.

There are several examples available for scalability today. Few have been listed below:

- Routing table of the routing protocol which increases in extent with respect to the increase in the extent of network.
- DBMS (data base management system) is scalable in the sense that more and more data can be uploaded to it by adding new required devices.
- Online transaction processing system can also be stated as scalable as one can upgrade it and more transactions can be done easily at one time.
- Domain name system is a distributed system and works effectively even when the hosting is on the level of World Wide Web. It is scalable.

Scaling is done basically in two ways. These two ways have been discussed below:

- Scaling out or scaling horizontally: This method of involves addition of several nodes or work stations to an already divided or distributed software application. This method has led to the development of technologies namely batch processing management and remote maintenance which were not available before the discovery of this technology.

Scaling up or scaling vertically:
Scaling up or scaling vertically can be defined as the addition of hardware or software resources to any single node of the system. These resources can either be CPUs or memory devices. This method of scaling has led to a tremendous improvement in virtualization technology.

What are Relational Database Management Systems? What is the need to test RDBMS?

Relational Database Management Systems cntains critical data which is updated by many applicatins and many end users. They provide important functionality as well. It is very important to test the functionality of relational database management systems. A regression suite should be made available and testing should be done on a regular basis.

The need to test a relational database management system is necessary because data is a very important asset and all the critical business related functionality is implemented in relational database management system. Generally, data professionals used to control changes and do formal testing but this approach did not proved to be efficient so because of the current approaches not being very efficient, there arises the need for relational database management system testing. Also, testing is a way to provide feedback that could be used to identify defects.

RDBMS testing should test the following :
- Black box testing at the interface which includes data values being persisted, data values being retrieved and stored functions and procedures.
- White box or clear box testing tests within the database. It includes scaffolding code which supports refactoring, unit tests, existence tests for database schema elements, view definitions, referential integrity rules, data invariants involving coloumns, data invariants for single column.

A test-first development(TFD) approach is used by agile software developers where a test is written before writing enough production code to fulfill that test.
- Add a test.
- Run the tests
- If it passes, again add a test otherwise make a little change and again run the tests.
- If it fails, again make a little change and run the tests.
- If it passes, the development stops.
- Once the test passes, again start over.

Test Driven Development(TDD) combines test first development and refactoring. When a new feature is implemented, the question whether this is the best design hwich enables the developer to add this feature is asked. If the answer is yes, then do the work and proceed. If the answer is no, design is refactored and then test first driven (TFD) is done.

What are different types of architectural pattern domains?

Architectural patterns define a specific approach for handling some behavioral characteristics of the system. A software architecture may have a number of architectural patterns that address issues such as concurrency, persistence and distribution.

CONCURRENCY
To promote parallelism, applications must handle multiple tasks and there are different ways to handle concurrency by an application and each way can be presented by a different architectural pattern.
- One approach is to use an operating system process management pattern that provides built-in operating system features allowing components to execute concurrently.
- Another approach is to define a task scheduler at the application level.

PERSISTENCE
Data persists if it survives past the execution of the process that created it. Persistent data can be read or modified by other processes at a later time as they are stored in a database or file. Persistence can be achieved through two architectural patterns:
- database management system pattern that applies the storage and retrieval capability of a database management system to the application architecture.
- application level persistence pattern that builds persistence features into application architecture.

DISTRIBUTION
The distribution problem addresses in a manner in which systems or components communicate with one another in a distributed environment. The most common architectural pattern established to address distribution problem is the broker pattern. Broker acts as middle man between client and server component.

How is data designed at architectural and component level?

Data Design at Architectural Level

Data design translates data objects defined during analysis model into data structures at the software component level and, when necessary,a database architecture at the application level.
There are small and large businesses that contains lot of data. There are dozens of databases that serve many applications comprising of lots of data. The aim is to extract useful information from data environment especially when the information desired is cross functional.
Techniques like data mining is used to extract useful information from raw data. However, data mining becomes difficult because f some factors:
- Existence of multiple databases.
- Different structures.
- Degree of detail contained with databases.Alternative solution is concept of data warehousing which adds an additional layer to data architecture. Data warehouse encompasses all data used by a business. A data warehouse is a large, independent database that serve the set of applications required by a business. Data warehouse is a separate data environment.

Data Design at Component Level

It focuses on representation of data structures that are directly accessed by one or more software components. Set of principles applicable to data design are:
- Systematic analysis principles applied to function and behavior should also be applied to data.
- All data structures and operations to be performed on each should be identified.
- The content of each data object should be defined through a mechanism that should be established.
- Low level data design decisions should be deferred until late in design process.
- A library of data structures and operations that are applied to them should be developed.
- The representation of data structure should only be known to those modules that can directly use the data contained within the structure.
- Software design and programming language should support the specification and realization of abstract data types.

What is Long Session Soak Testing ?

When an application is used for long periods of time each day, the above approach should be modified, because the soak test driver is not logins and transactions per day, but transactions per active user for each user each day. This type of situation occurs in internal systems, such as ERP and CRM systems, where user logins and stay logged in for many hours, executing a number of business transactions during that time. A soak test for such a system should emulate multiple days of activity in a compacted time frame rather than just pump multiple days worth of transactions through the system.

Long session soak tests should run with realistic user concurrency, but the focus should be on the number of transactions processed. VUGen scripts used in long session soak testing may need to be more sophisticated than short session scripts, as they must be capable of running a long series of business transactions over a prolonged period of time.

The duration of most soak tests is often determined by the available time in the test lab. There are many applications that require extremely long soak tests. Any application that must run, uninterrupted for extended periods of time, may need a soak test to cover all of the activity for a period of time that is agreed to by the stakeholders. Most systems have a regular maintenance window, and the time between such windows is usually a key driver for determining the scope of soak test.

Overview of Soak testing.

Soak testing is running a system at high levels of load for prolonged periods of time. A soak test would normally execute several times more transactions in an entire day than would be expected in a busy day, to identify any performance problems that appear after a large number of transactions have been executed. Also, it is possible that a system may stop working after a certain number of transactions have been processed due to memory leaks or other defects. Soak tests provide an opportunity to identify such defects, whereas load tests and stress tests may not find such problems due to their relatively short duration.A soak test would run for as long as possible, given the limitations of the testing situation. For example, weekends are often an opportune time for a soak test.

There are some typical problems identified during soak tests are:
- Serious memory leaks that would eventually result in memory crisis.
- Failure to close connections between tiers of a multi-tiered system under some circumstances which could stall some or all modules of the system.
- Failure to close database cursors under some conditions which would eventually result in the entire system stalling.
- Gradual degradation of response time of some functions as internal data structures becomes less efficient during a long test.

Apart from monitoring response time, it is also important to measure CPU usage and available memory. If a server process needs to be available for the application to operate, it is often worthwhile to record its memory usage at the start and end of the soak test. It is also important to monitor internal memory usages of facilities such as Java virtual machines, if applicable.

Object Query Language (OQL)

Object Query Language (OQL) is a query language standard for object-oriented databases modelled after SQL. OQL was developed by the Object Data Management Group (ODMG). Because of its overall complexity no vendor has ever fully implemented the complete OQL. OQL has influenced the design of some of the newer query languages like JDOQL and EJB QL, but they can't be considered as different flavours of OQL, and should be treated separately.

The key Differences Between OQL and SQL :
- OQL supports object referencing within tables. Objects can be nested within objects.
- Not all SQL keywords are supported within OQL. Keywords that are not relevant to Netcool/Precision IP have been removed from the syntax.
- OQL can perform mathematical computations within OQL statements.

General Rules of OQL :
- All complete statements must be terminated by a semi-colon.
- A list of entries in OQL is usually separated by commas but not terminated by a comma.
- Strings of text are enclosed by matching quotation marks.

OQL was designed to be object-oriented. Queries are specified using objects and their attributes (data-members). Similarly, queries return sets of objects. The complex relationships in an object model can be easily navigated, using the same class-member paradigm, used by object-oriented programming languages. This can often lead to increased performance over SQL, where resource-consuming join processes are necessary to capture relationships. Another big advantage is, that table names and column names are not necessary in the query strings, because queries are formulated using class names and attribute names and no mapping knowledge is necessary in the application.
OQL may be used as an embedded language or as a standalone query language. Both of these are supported by OpenAccess. As an embedded language, OQL queries can be used directly in your application programs. Programs can embed OQL queries, and receive results in the native data types of the programming language being used. OQL statements are simply text strings, which means that the standard string representation of your programming language is used to express the query.

Simple query :
The following example illustrates how one might retrieve the CPU-speed of all PCs with more than 64MB of RAM from a fictional PC database:
SELECT pc.cpuspeed
FROM PCs pc
WHERE pc.ram > 64

Query with grouping and aggregation :
The following example illustrates how one might retrieve the average amount of RAM on a PC, grouped by manufacturer:
SELECT manufacturer, AVG(SELECT part.pc.ram FROM partition part)
FROM PCs pc
GROUP BY manufacturer: pc.manufacturer
Note the use of the keyword partition, as opposed to aggregation in traditional SQL.

Introduction to Data Binding

Data binding is the process that establishes a connection between the application UI and business logic. If the binding has the correct settings and the data provides the proper notifications, then, when the data changes its value, the elements that are bound to the data reflect changes automatically. Data binding can also mean that if an outer representation of the data in an element changes, then the underlying data can be automatically updated to reflect the change. A typical use of data binding is to place server or local configuration data into forms or other UI controls.

Basic Data Binding Concepts :
Data binding is based on a component architecture that consists of four major pieces : the data source object (DSO), data consumers, the binding agent, and the table repetition agent. Data source objects provide the data to a page, data-consuming HTML elements display the data, and the agents ensure that both the provider and the consumer are synchronized.

Direction of the Data Flow :
The data flow of a binding can go from the binding target to the binding source and/or from the binding source to the binding target.
- One Way binding causes changes to the source property to automatically update the target property, but changes to the target property are not propagated back to the source property. This type of binding is appropriate if the control being bound is implicitly read-only.
- Two Way binding causes changes to either the source property or the target property to automatically update the other. This type of binding is appropriate for editable forms or other fully-interactive UI scenarios. Most properties default to One Way binding, but some dependency properties default to Two Way binding.
- OneWayToSource is the reverse of OneWay binding; it updates the source property when the target property changes. One example scenario is if you only need to re-evaluate the source value from the UI.

Data Source Objects
To bind data to the elements of an HTML page in Windows Internet Explorer, a DSO must be present on that page. DSOs implement an open specification that leaves it up to the DSO developer to determine the following:
- How the data is transmitted to the page. A DSO can use any transport protocol it chooses. This might be a standard Internet protocol, such as HTTP or simple file I/O. A DSO also determines whether the transmission occurs synchronously or asynchronously. Asynchronous transmission is preferred, because it provides the most immediate interactivity to the user.
- How the data set is specified. A DSO might require an Open Database Connectivity (ODBC) connection string and an Structured Query Language (SQL) statement, or it might accept a simple URL.
- How the data is manipulated through scripts. Since the DSO maintains the data on the client, it also manages how the data is sorted and filtered.
- Whether updates are allowed.

Data Consumers
Data consumers are elements on the HTML page that are capable of rendering the data supplied by a DSO. Elements include many of those intrinsic to HTML, as well as custom objects implemented as Java applets or Microsoft ActiveX Controls.
A DSO typically exposes this functionality through an object model that is accessible to scripts.

Binding Agents
The binding and repetition agents are implemented by MSHTML.dll, the HTML viewer for Internet Explorer, and they work completely behind the scenes. When a page is first loaded, the binding agent finds the DSOs and the data consumers among those elements on the page. Once the binding agent recognizes all DSOs and data consumers, it maintains the synchronization of the data that flows between them.

Introduction to Database Concurrency

DATABASE CONCURRENCY: - Database concurrency is the particular situation when a single database is being accessed by multiple programs. Databases, by design in most cases are shared resources, but in this case, they are shared across multiple applications.
Database concurrency controls ensure that transactions occur in an ordered fashion. The main job of these controls is to protect transactions issued by different users/applications from the effects of each other. They must preserve the four characteristics of database transactions: atomicity, isolation, consistency and durability. Concurrency control is one of the main issues in the study of real time database systems. In addition to satisfying consistency requirements as in traditional database systems, a real time transaction processing system must also satisfy timing constraints.

Conflicts between transactions can be detected in two ways.
Pessimistic method detects conflicts before making access to the data object. When a transaction requests access to some data item, the concurrency control manager will examine this request and will determine whether to grant the request or not.
Optimistic schemes are designed to get rid of the locking overhead. They are optimistic in the sense that they take into account the explicit assumption that conflicts among transactions are rare events. The task of concurrency control is deferred until the end of transaction when some checking for potential conflicts has to take place and will be resolved accordingly, taking into consideration the amount of progress that has been done and the nature of conflict with transactions.
When concurrency control detects a conflict among some concurrent transactions accessing the same object, a conflict resolution mechanism needs to be put on. Concurrency control manager decides which transaction (victim) to penalize (the lock holder or the requester) and chooses an appropriate action and suitable timing. Two possible actions are most used: Blocking (wait) and abort (restart). In pessimistic concurrency control either blocking or abort can be used to resolve the conflict. However, in optimistic concurrency control only aborting is appropriate since conflict has been detected after the transaction has accessed the data object and performed some computation.

OPTIMISTIC CONCURRENCY CONTROL : The basic idea of an optimistic concurrency control mechanism is that the execution of a transaction consists of three phases: read, validation and write phases. For all optimistic concurrency control (OCC) schemes a conflict is detected after the data object has been accessed. In the OCC, conflict detection and resolution are both done at the certification time when a transaction completes its execution; it requests the concurrency control manager to validate all its accessed data objects. If it has not yet been marked for abort, it enters the commit phase where it writes all its updates to the database.

Introduction to Database Encryption

Encryption can provide strong security for data, but is that enough ? Data in a database can be accessed by many systems, but developing a database encryption strategy must take many factors into consideration. Where should the encryption be performed, for example — in the database, or in the application where the data originates? Who should have access to the encryption keys? How much data must be encrypted to provide security? What’s an acceptable trade-off between data security and application performance?
Data encryption is a process of converting stored or transmitted data to a coded form in order to prevent it from being read by unauthorized person. It is an application of a specific algorithm to alter the appearance of data, making it incomprehensible to those who are not authorized to see the information.
There are 2 types of encryption algorithm: -
- Secret key or Symmetric key algorithm: -In this encryption algorithm, a single secret or private key is shared between the sender and receiver. The sender encrypts this using this key and receiver decrypts it using the same key. It is highly assumed that no one else knows the key.
- Public key or Asymmetric key algorithm: - In this algorithm, every sender and receiver has a pair of keys. One is made public to the network and called public key and the other is kept private to that node called private key. The pair is such made that if the data is encrypted with one of the keys in the pair, it can only be decrypted with other key in the pair. When a sender has to send, it encrypts the data with receiver’s public key & the receiver decrypts it with its private key.

Advice on how to overcome some of the challenges in database encryption:
- Regulatory drivers : Advanced security through database encryption is required across many different sectors, and increasingly to comply with regulatory mandates.
One approach that can help companies address the encryption challenges associated with regulation is the defense-in-depth principle which advocates many layers to strong security – ranging from physical security and access controls to rights assignment and network security, including firewalls and, crucially, encryption of both data at rest and in transit.
- Overcoming key management issues
It is important that database encryption be accompanied by key management; however, statistics show that this is also the main barrier to database encryption. It is well-recognized that key use should be restricted and that key backup is extremely important. An additional best practice rule of encryption is that the encrypted key should never be stored alongside the data it was used to encrypt. Placing encryption keys within the HSM enforces this policy.
- Separation of duties and dual control
Many organizations pay close attention to separation of duties and dual control, which is required to pass audits to show that there are internal controls protecting against rogue administrators or unauthorized employees and is often required by the various regulatory requirements discussed above.

Database Locking - Control concurrent access of the database

Locking is a procedure used to control concurrent access of the data. A lock may deny access to other transaction in order to prevent incorrect results (if multiple people are updating the same data).

Locks can be of two types: -
• Exclusive lock or Write lock
• Shared lock or Read lock

- Exclusive lock: Provides exclusive use of a data item to one transaction. Transaction has to be made exclusive to modify the value of data in a table. If the transaction has obtained an exclusive lock, then no other transaction can access the data item until the lock is released, including being able to read the data.

- Shared locks: Provides a read permission to the transaction. Any number of transactions can make shared lock & read the data item. This helps when the database is being read for multiple transactions, and putting a shared lock can be bad for business logic.

Basic rules for locking: -
• If a transaction has a read lock on the data item, it can read the item but cannot update it.
• If a transaction has a read lock, other transaction can obtain a read lock on the data item, but no write lock.
• If a transaction has a write lock, it can both read and update the data item.
• If a transaction has a write lock, then others can’t obtain either a read lock or a write lock on the data item.

TWO PHASE LOCKING (2PL):-One way to handle the concurrency control is 2PL mechanism.
Every transaction is divided into two phases: -
• A growing phase
• A shrinking phase or Contracting phase
In the growing phase, the transaction acquires all locks needed but can’t release any locks. The number of locks increases from zero to maximum for a transaction.
In the contracting phase, the number of locks held decreases from maximum to zero.
The transaction can acquire the locks, proceed with the execution & during the course of execution acquire additional locks as needed. But it never releases any lock until it has reached a stage where no new locks are required anymore.

2PL are of the following types: -
• Basic two-phase locking
• Conservative two-phase locking
• Strict two-phase locking
• Rigorous two-phase locking.

Lock Time-out :
Locks are held for the length of time needed to protect the resource at the level requested. If a connection attempts to acquire a lock that conflicts with a lock held by another connection, the connection attempting to acquire the lock is blocked until:
· The conflicting lock is freed and the connection acquires the lock it requested.
· The time-out interval for the connection expires.

Database Integrity

Database Integrity is the preservation of data correctly & implies the process of keeping the dbase away from accidental deletion or alteration.
There are following types of integrity constraints:-
• Entity integrity constraints
• Referential integrity constraints
• Domain integrity constraints

DATABASE SECURITY: - Database security is a measurement of confidence that the integrity of a system and its data will be preserved.
Database security is assigned to address the following issues:-
• Privacy of data elements
• Preserving policies of organization
• System related security level
• Maintaining integrity of the database

Data integrity can be compromised in a number of ways:
- Human errors when data is entered.
- Errors that occur when data is transmitted from one computer to another.
- Software bugs or viruses.
- Hardware malfunctions, such as disk crashes.
- Natural disasters, such as fires and floods.

There are many ways to minimize these threats to data integrity. These include:
- Backing up data regularly.
- Controlling access to data via security mechanisms.
- Designing user interfaces that prevent the input of invalid data.
- Using error detection and correction software when transmitting data.

* Declarative Ease
Define integrity constraints using SQL statements. For these reasons, declarative integrity constraints are preferable to application code and database triggers. The declarative approach is also better than using stored procedures, because the stored procedure solution to data integrity controls data access, but integrity constraints do not eliminate the flexibility of ad hoc data access.
* Centralized Rules
Integrity constraints are defined for tables (not an application) and are stored in the data dictionary. Any data entered by any application must adhere to the same integrity constraints associated with the table.
* Maximum Application Development Productivity
If a business rule enforced by an integrity constraint changes, then the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint.
* Superior Performance
The semantics of integrity constraint declarations are clearly defined, and performance optimizations are implemented for each specific declarative rule.
* Flexibility for Data Loads and Identification of Integrity Violations
You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking.
* The Performance Cost of Integrity Constraints
The advantages of enforcing data integrity rules come with some loss in performance.

Overview of Database Security

Database security is the set of systems, processes, and procedures that protect a database from unintended activity. Unintended activity can be categorized as authenticated misuse, malicious attacks or inadvertent mistakes made by authorized individuals or processes. Database security is also a specialty within the broader discipline of computer security. The database is the entity where all the data is stored, so protecting it from unauthorized access and change is extremely critical.
Traditionally databases have been protected from external connections by firewalls or routers on the network perimeter with the database environment existing on the internal network opposed to being located within a demilitarized zone.
Database security can begin with the process of creation and publishing of appropriate security standards for the database environment. The standards may include specific controls for the various relevant database platforms; a set of best practices that cross over the platforms; and linkages of the standards to higher level polices and governmental regulations.
One of the easiest steps to take is regarding passwords. Default or weak passwords are still often used by enterprises to protect an online asset as important as a database, but it's a problem that's easy to fix. The remedy is enforcing a strong password policy; that is, passwords must be changed regularly and be at least 10 digits long and contain both alphanumeric characters and symbols.
SQL Injection attacks pose tremendous risks to web applications that depend upon a database back-end to generate dynamic content. In this type of attack, hackers manipulate a web application in an attempt to inject their own SQL commands into those issued by the database. To prevent this type of attack, it is essential to ensure that all user-supplied data is validated before letting it anywhere near your scripts, data access routines and SQL queries, and preferably use parametrized queries. Another reason to validate and clean data received from users is to prevent cross-site scripting (XSS) attacks, which can be used to compromise a database connected to a Web server.
A database security program should include the regular review of permissions granted to individually owned accounts and accounts used by automated processes. The accounts used by automated processes should have appropriate controls around password storage such as sufficient encryption and access controls to reduce the risk of compromise.
The software used for the database, for the middle layers and for all other layers should be updated regularly with patches, updates and fixes. Falling behind in this task is pretty painful if you end up exposing holes in the software to attackers (and attackers know that a number of companies do not upgrade their systems on an immediate basis).

Structured Query Language (SQL)

The structured query language (SQL) is the language used to query and manipulate information within a SQL Server database. SQL is actually an ISO and ANSI standardised language. However, a lot of RDBMS software use their own proprietary extensions within their own Transact-SQL (T-SQL) variant of SQL.

The basic building block of the structured query language is the SQL statement. Using statements, information in a database can be manipulated and queried.
* CREATE - a data structure.
* SELECT - read one or more rows from a table.
* INSERT - one or more rows into a table.
* DELETE - one or more rows from a table.
* UPDATE - change the column values in a row.
* DROP - a data structure.

Language Structure :
SQL is a keyword based language. Each statement begins with a unique keyword. SQL statements consist of clauses which begin with a keyword. SQL syntax is not case sensitive.
The other lexical elements of SQL statements are:
* names -- names of database elements: tables, columns, views, users, schemas; names must begin with a letter (a - z) and may contain digits (0 - 9) and underscore (_)
* literals -- quoted strings, numeric values, date time values.
* delimiters -- + - , ( ) = < > <= >= <> . * / || ? ;
Basic database objects (tables, views) can optionally be qualified by schema name. A dot -- ".", separates qualifiers: schema-name . table-name
Column names can be qualified by table name with optional schema qualification.

Syntax of Simple SELECT : SELECT column FROM tablename
- Using "Where"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000;
- Compound Conditions
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY < 40000 OR BENEFITS < 10000;
- Using "IN"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff');
- Using "Between"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000;
- Using "LIKE"
SELECT EMPLOYEEIDNO
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'L%';

Introduction to Relational Databases

Relational databases are probably the most common type of database used for general-purpose tasks. In a relational database, information is grouped according to its type, generally in tables (see below). For example, in a database designed to hold fleet information you may include a table of employees and a table of vehicles.
- In addition to separating information according to its data structure, a relational database allows relationships to be created. A relationship defines a possible link between data types; the actual linkage of data is dependent upon the information held.
- Relational databases use the concept of normalization. Normalization is a design technique that minimizes the duplication of information. It also reduces the risk of errors. By using relationships, the duplication required can be lessened or eliminated completely.
A Relational model is the basis for any relational database management system (RDBMS). A relational model has mainly three components:
- A collection of objects or relations.
- Operators that act on the objects or relations.
- Data integrity methods.

Elements of a Relational Database Schema :
There are several key elements to a relational database. Each of these forms a part of the database's schema. The schema is the logical data model that determines the information that may be stored in the database and how it is to be arranged. To design a database we need three things:
- Table : A table is one of the most important ingredient to design the database. It is also known as a relation, and is a two dimensional structure used to hold related information. A database consists of one or more tables.
- Rows : A table contains rows. Rows are collection of instance of one thing.
- Columns : A table contains the columns. Columns contains all the information of a single type. Each column in a table is a category of information referred to as a field.
- Indexes : One of the greatest benefits of holding information in a database is the ability to quickly retrieve it. When querying a database, it is possible to apply criteria to ask for a specific set of rows.
- Keys : A primary key is a single column, or group of several columns (compound key), that can be used to uniquely identify rows in a table. Each table in a database may have a single primary key. Once defined, no two rows in the table may contain matching data in the primary key columns. Foreign keys are used when defining relationships between tables. A foreign key is a single column, or group of columns, in a table that reference the primary key in another table. This creates a link between the two tables.
- Constraints : Constraints are rules that are applied to the information in a database. These are usually used to enforce business rules upon the tabular data.
- Views : Views provide the useful concept of virtual tables. A view gathers specific information from one or more sources and presents it in the format of a single table. The information may be filtered within the view to remove unnecessary information.
- Stored Procedures : A stored procedure is a predefined set of statements that can be executed when required. Stored procedures provide the main means of creating programs within SQL Server databases.

Domain and Integrity Constraints :
* Domain Constraints
o limit the range of domain values of an attribute
o specify uniqueness and `nullness' of an attribute
o specify a default value for an attribute when no value is provided.
* Entity Integrity
o every tuple is uniquely identified by a unique non-null attribute, the primary key.
* Referential Integrity
o rows in different tables are correctly related by valid key values (`foreign' keys refer to primary keys).

Database Normalization

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a thorough investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated. An analysis of this data and its corresponding relationships is advantageous because it can result both in a substantial improvement in the speed in which the tables are queried, and in decreasing the chance that the database integrity could be compromised due to tedious maintenance procedures.

Why Normalize?
- To reduce redundancy : One obvious drawback of data repetition is that it consumes more space and resources than is necessary. Redundancy introduces the possibility for error.
- Unforeseen Scalability Issues : as a database grows in size, initial design decisions will continue to play a greater role in the speed of and resources allocated to this database.

NORMAL FORMS :
The process towards database normalization progressing through a series of steps, typically known as Normal Forms.
- First Normal Form (1NF): A relation is in 1NF if and only if all underlying domains contain scalar values only. 1NF is often refered to the atomic rule. In a database, this means that each column should only be designed to hold one and only one piece of information.
- Second Normal Form (2NF): It further addresses the concept of removing duplicative data:
* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.
- Third Normal Form (3NF): It goes one large step further:
* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.
- Fourth Normal Form (4NF): It has one additional requirement:
* Meet all the requirements of the third normal form.
* A relation is in 4NF if it has no multi-valued dependencies.

Temporal Database Concepts

A temporal database is a database with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language. More specifically the temporal aspects usually include valid-time and transaction-time. These attributes go together to form bitemporal data.
* Valid time denotes the time period during which a fact is true with respect to the real world.
* Transaction time is the time period during which a fact is stored in the database.
* Bitemporal data combines both Valid and Transaction Time.

- Temporal DBMS manages time-referenced data, and times are associated with database entities.
- Modeled reality.
- Database entities.
- Fact: any logical statement than can meaningfully be assigned a truth value, i.e., that is either true or false.
- Valid Time (vt).
- Valid time is the collected times when the fact is true.
- Possibly spanning the past, present & future.
- Every fact has a valid time.
- Transaction Time (tt).
- The time that a fact is current in the database.
- Maybe associated with any database entity, not only with facts.
- TT of an entity has a duration: from insertion to deletion.
- Deletion is pure logical operation.
- Time domain may be discrete or continuous.
- Typically assume that time domain is finite and discrete in database.
- Assume that time is totally ordered.
- Uniqueness of “NOW”.
- The current time is ever-increasing.
- All activities is happed at the current time.
- Current time separates the past from the future.
- “NOW” <> “HERE”.
- Time cannot be reused!
- A challenge to temporal database management.

Most applications of database technology are temporal in nature:
- Financial applications : portfolio management, accounting & banking.
- Record-keeping applications : personnel, medical record and inventory management.
- Scheduling applications : airline, car, hotel reservations and project management.
- Scientific applications : weather monitoring.

Database Recovery Techniques - Overview

The main goal of recovery is to ensure the atomicity property of a transaction. If a transaction fails before completing its execution, the recovery mechanism has to make sure that the transaction has no lasting effects on the database. The different approaches to recovery :
- Deferred Update : These techniques postpone any actual updating of the database on disk until a transaction reaches its commit point. The transaction force writes the log to disk before recording the updates in the database. This approach, when used with certain concurrency control methods, is designed never to require transaction rollback, and recovery simply consists of redoing the operations of transactions committed after the last checkpoint from the log.
Disadvantage : Too much buffer space may be needed, since updates are kept in the buffers and are not applied to disk until a transaction commits.
Deferred update can lead to a recovery algorithm known as NO-UNDO/REDO. Immediate update techniques may apply changes to the database on disk before the transaction reaches a successful conclusion. Any changes applied to the database must be first recorded in the log and force-written to disk so that these operations can be undone if necessary.
- Another algorithm, known as UNDO/NO-REDO, can also be developed for immediate update if all transaction actions are recorded in the database before commit.
- There is another technique, called shadow paging technique, which keeps track of old database pages by using a shadow directory. This technique, which is classified as NO UNDO/NO-REDO, does not require a log in single-user systems but still needs the log for multiuser systems.
- ARIES, a specific recovery scheme is used in some of IBM's relational database products.
- Two-phase commit protocol is used for recovery from failures involving multi-database transactions.
- Recovery from catastrophic failures is typically done by backing up the database and the log to tape. The log can then be backed up more frequently than the database, and the backup log can be used to redo operations starting from the last database backup.