Information Integration

We now start a new topic: 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.
The integrated database may be either physical, for example, stored in a data warehouse; or virtual, a "mediator" may be queried and it in turn queries the various sources.
In the case of systems with mediators, special kinds of query optimizations are needed. Further the kinds of optimizations vary by the kind of mediator.
There are two main kinds of mediators:
1. global-as-view where the data at the integrated database is defined by how it is constructed (imperatively) from the sources. Here we might be interested in capability based optimization.
2. local-as-view where the content of the sources is defined in terms of the schema that the integrated database supports. A schema might be a datalog predicate which is defined declarations in terms of datalog predicates of source tables. Here the optimizer has to be able to figure out how to compose the answer to a query from defined views.
Different data sources might refer to the same entity. So another task that is needed by an information integration solution is to resolve discrepancies such as misspelt names, out-of-date addresses, etc. that different sources have. This task is called entity resolution.

Use Cases of Information Integration

Databases are often created independently, but are later asked to work together. For example, different police stations might have their own database of case files, but the state later mandates that they share certain kinds of data.
The use of databases evolves, so we cannot design a database to support every possible future use. For example, Facebook was originally developed using a Mysql database, which does not look anything like their current set-up. Facebook still might want to access old data from the original database and schema from time-to-time, but make it appear as if from fresh modern source.
As another example, a university might have started automating different functions at different times. For example,
- The registrar at the university created a database of courses and records. This was used to build a transcript app.
- At some different time, the bursar built a different database to manage tuition.
- The HR department asynchronously made a database for employees.
- The grants office made a separate one for funding, etc.
- The university might want it to be the case that the registrar does not record grades for students who don't pay their fees.
- This is not possible without information integration.
One more example is the case of corporate mergers: For example, when American and US Airways merged, American stored bookings in Sabre, which dates to the 1960s, US Airways, was using a more modern booking system from EDS. In turn, US Airways had earlier merged with America West using EDS, and had switched its system from Sabre to what American West was using.

The Heterogeneity Problem

If information sources were developed independently they can differ in many ways, even if they are intended to store the same data. This is called the Heterogeneity Problem.
- The book has an example of Aardvark Automobile Co which has 1000 dealers, each of which has its own database of its stock.
- Aardvark wants to create an integrated database so that dealers can locate a particular model that a different dealer might have in the case that the local dealer doesn't have it.
Heterogeneity can come in several different forms:
- Communication Heterogeneity. One system might be accessed through one protocol say HTTP, but another might use CORBA RPC calls, and another might use FTP.
- Query-Language Heterogeneity. This might involve different dialects of SQL at different sites. Or maybe some site uses Excel and another XQuery. Maybe one even has a legacy site that uses QUEL.
- Schema Heterogeneity. The database schema at the different sites might be different and so the queries needed to get the same data need to change. For example, one site might have a single table CARS, another dealer might have two tables AUTOS and OPTIONS.
- Data Type Heterogeneity. Serial numbers might be stored as a CHAR in one site as an INTEGER at another, etc.
- Value Heterogeneity. One site might use Black to mean a black car, another site might use the code BL.
- Semantic Heterogeneity. Terms might differ from site to site. One site might use CARS to refer to both cars and trucks while another site only puts cars in the CARS table.

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?

A typical distributed database might involve a shared memory architecture where the network speed is fast as compared to disk I/O.
One of the steps in computing a distributed join using the semi-join algorithm from class involved doing the two phase commit protocol.
One way to avoid lock bottlenecks in the distributed setting is for each transaction to have a lock coordinator and to use primary copy locking to handle replication issues.

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 applications 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.

Information Integration - OLAP

Outline