Introduction

Last Wednesday, we began talking about information integration.
Information integration is the process of taking several databases or information sources and making the data in these sources available to application users as if they were a single database.
We said we can either physically integrate the data sources, as in a data warehouse, or virtually integrate the data sources using a mediator. A mediator can queried and it in turn queries the different sources.
We gave several use cases for information integration: handling independent data creation (police station and university examples) and handling old data when databases evolve (Facebook DBMS needs eveloving, American and US Airways merger).
We looked at the heterogeneity problem. This problem involves the ways that information sources that were developed independently may differ even if they are used to store the same data.
We begin today by look at some common ways of performing Information Integration.

Three Information Integration Techniques

Below are three common techniques for integrating data source, we will go into them in more detail after the quiz.

Federated Databases. Here we have multiple independent data sources, but can use one source to call on the others to supply info.
Warehousing. Copies of data from several sources are stored in a single database called a warehouse. This data has usually been processed to a common schema, filtered, and aggregated. The warehouse is usually updated periodically (say once a day at off peak times).
Mediation. This is a software component that supports a virtual database, which the user may query. It stores no data itself, but instead, queries its sources and synthesizes from them a response.

Quiz

Which of the following statements is true?

The distributed commit protocol we learned last week is called three phase commit.
A lock coordinator is a centralized site that has a lock table for all logical elements in a distributed database.
Combining primary copy locking and using a lock coordinator largely solves the problem of handling distributed locking without bottlenecks even if elements are replicated.

Federated Database Systems

A simple way to connect several databases, DB1, ..., DBn is for every database `i` and `j` to have a connection. (See image above for n=4 case.)
This leads to quadratically `(n\cdot n-1)` many connections that must be written for each DB to understand the sources on the other DBs.
For simple communication this may still be feasible.
For example, Aardvark Automobile Co dealers might want to share their inventory, but each dealer only needs the inventory of other, close-by dealers, not all dealers across the country.
Dealer 1 might have table NeededCars(model, color, autoTrans) of cars it needs.
A connector to Dealer 2 would involve a SQL query to the two tables Dealer 2 uses: Autos(serial, model, color) and Options(serial, option).
A connector Dealer 3, on the other hand, might just be a single SQL query to a table Cars(serialNo, model, color, autoTrans).

Data Warehouses

In a data warehouse, data from several sources are extracted, combined, and inserted into a single global schema.
Once the data is in the warehouse, it can be queried by a user in exactly the same way as any database.
Two common approaches to constructing a warehouse are:
1. Close the warehouse periodically, reconstruct the warehouse from the current data sources. This is perhaps the easiest and most common approach, and might be done once a night.
2. Update the warehouse periodically based on changes since the last update. This is called incremental updating. This typically involves communicating less data. The drawback is computing the change in the data can be complex, especially if there are several sources.

Warehouses Example

Suppose we wanted to make a warehouse of the data that was stored at Dealer 2 and Dealer 3 from the two slides ago.

The warehouse might have the schema:

AutosWarehouse(serialNo, Model, color, autoTrans, dealer)

Notice we added an extra dealer column to say at what dealer the car was.

The extractor and combiner for Dealer 3 might look like:

INSERT INTO AutosWarehouse(serialNo, Model, autoTrans, dealer)
SELECT serialNo, model. color, autoTrans, "Dealer  3"
FROM Cars;

We would also need to write an extractor and combiner for Dealer 2.
This is just a simple example, a typical extractor and combiner might do filtering, compute joins, etc.

Mediators

Recall a mediator supports a virtual view or collection of views integrating several data sources.
The image above shows the flow of a query and the results in the case of a mediator for two data sources.
Notice the query is issued to the mediator by some user.
It is then modified by the mediator into queries to wrappers for each of the data sources.
This in turn queries these sources and returns results, which are then propagated back up the tree and combined into what the mediator returns.

Mediator Example

Suppose we tried to implement the data warehouse of Dealer 2 and 3 from before as a mediator.

We might try to have the mediator maintain a view:

AutosMediator(serialNo, Model, color, autoTrans, dealer)

If the user tries to perform query:
```
SELECT serialNo, model
FROM AutosMediator
WHERE color = 'red';
```
for Dealer 2 which has the tables Autos(serial, model, color) and Options(serial, option), the mediator would send the query to the Dealer 2's wrapper which would compute the query:
```
SELECT serial, model
FROM Autos
WHERE color = 'red';
```
On the other hand, the same query sent to Dealer 3 would result in the query:
```
SELECT serialNo, model
FROM Cars
WHERE color = 'red';
```
Once the results from the two queries had been computed, the union of the results would be output by the mediator to the user.

Mediator Wrappers - Templates

Notice the queries needed to perform a source extractor for a data warehouse are typically fixed and can be predefined.
Also, the communication mechanism between a source and a wrapper tends to be simpler: open a connection, run predefined query, pass info to warehouse, close connection.
On the other hand, a mediator wrapper needs to be able to accept a variety of queries and to be able to translate any of the them to the terms of the source.
One way to build these queries in a systematic way is to use template queries: ` T => S`.
These consist of an example query `T` which might be given to the mediator, and a source query `S` which should be sent to a particular source for this case. For example,
```
SELECT *
FROM AutosMediator
WHERE  color = '$c'
 =>
SELECT serialNo, model, color, autoTrans, 'dealer3'
FROM Cars
WHERE color = '$c'
```
Notice that $c is a variable which is bound when the template is matched. It might take a value like red, blue, etc.
Often DB vendors have wrapper generator software which can build a wrapper from the kind of connection the mediator supports, the kind of connection the data source supports (via a driver), and a sequence of such templates.

Mediator Wrappers - Filters

It is often difficult to some up with a set of templates which will work in all circumstances of a query to a mediator.
Instead, we might come up with a set of templates such that for any query `q` to the mediator there is some template which returns a superset of the columns and rows desired by `q`.
The wrapper can then determine the template which is the closest to `q` and apply a filter wrapper to select for the particular rows and columns asked for by `q`.

Global as View Optimization

Earlier in the semester, we were looking at cost-based plan selection for a single database.
Optimization is not so easy in the case of mediators.
For example, even if we know a data source has the data we want, we may not be able to get that data in a form suitable for answering a query. For example, Twitter knows all of Donald Trump's followers on Twitter, but its API wouldn't let us access this complete list in one go.
Often to determine how to carry out a query, a mediator needs to know the capabilities of each of its sources.
This capabilities specification for a source might consist of a sequence of adornments for the attributes it provides that tell us how it can be queried.
For example, an f (free) attribute is one that can be specified or not in a query to a source, a b (bound) attribute is one that must be specified and should be selected with a particular value, etc.
Given a set of data source capabilities specifications, a mediator often uses a capabilities based optimizer to determine if there exists a plan that can answer a user's query.
Then given this and information about the performance abilities of the data sources as costs, it can then try to choose between plans satisfying the query based on cost.
The book gives an example of a greedy, capabilities based plan generating algorithm called the chain algorithm.
Our approach to mediation has been the global as view approach, we have skipped the local-as-view approach which is more closely related to logic programming.

Online Analytical Processing

OLAP (Online Analytical processing) is the examination of data in a database for patterns or trends.
It generally involves complex queries (called decision support queries) that use one or more aggregations.
These queries might only return a small number of rows in their results, but touch every row in a table or collection tables.
On the other hand, the transactions we have mainly been considering this semester only touch a few rows at a time. The study of these short transactions is called OLTP (Online Transaction Processing).
OLAP applications often take place in a data warehouse which is either a separate copy of a master database or which is a collection of materialized views calculated from several data sources.
Having a separate, periodically updated data warehouse, allows the OLAP system to run long queries which might have difficulty running in the presence of shorter transactions.

Example OLAP Application

Aardvark Automobile Co might build a data warehouse to analyze the sales of its cars.

The warehouse might use the schema:

Sales(serialNo, date, dealer, price)
Autos(serialNo, model, color)
Dealers(name, city, state, phone)

An example decision support query might examines sales on or after April 1, 2006 to see how the average price per vehicle varies by state:
```
SELECT state, AVG(price)
FROM Sales, Dealers
WHERE Sales.dealer = Dealers.name
AND date >= '2006-1-04';
```
This this query touches much of the database.
A typical OLTP query might be "find the price at which the auto with serial number 123 was sold." This query only touches one row.

Multi Dimensional View of OLAP data

Car dealer example of multidimensional data

Many OLAP application make use of a fact table, a central relation or collection of data.
A fact table represents events or objects of interest, for example, sales.
We can think of this fact table as being arranged in multidimensional space as a "cube" or "hyper-cube".
For example, in the above picture, each point represents a sale and its position in the cube is determined by which dealer, what date, and what kind of car.
The data space above is often referred to as the raw data cube.
To facilitate decision support queries, this cube is often augmented to make a formal data cube by adding:
1. Aggregates of the data in all subsets of dimensions, as well as the data itself.
2. Points in the formal cube may represent an initial aggregation of points in the raw-data cube. For example, instead of the "car" dimension representing each individual car, that dimension might be aggregated by model only.

Approaches to OLAP

The distinction between raw-data cube and formal data cube shows up in the two common approaches to making system that support OLAP:
1. ROLAP, or Relational OLAP. In this approach data is stored in relations with a specialized structure called a "star schema". One of these relations is a "fact table" which contains the raw, unaggregated data corresponding to the raw-data cube. Other relations give info about the values along each dimension.
2. MOLAP, or Multidimensional OLAP. In this set-up, a specialized structure corresponding to the formal data cube is used to hold the data, including aggregates. Nonrelational operators may be implemented to support queries on this structure.

More Information Integration - OLAP

Outline