The Data Integration Problem & Design Method
Why heterogeneous sources cannot just be glued together, the two ways to integrate them (materialize vs leave-in-place), and the five-step method that turns a pile of independent schemas into one queryable global schema — the spine of every Part II integration exercise.
How TIS is examined (2025/26)
The exam is written and closed-book, in two parts that may be taken in different calls of the same academic year: Part I — 3 open theory questions, 15 pts, pass ≥ 7.5; Part II — design exercises, 17 pts, pass ≥ 8.5. Part II is always a design exercise, and in 7 of the 16 past sessions in our bank that exercise was a data-integration design — this chapter and the next teach it end to end. (A separate pass/fail Communication Assignment sits outside the study guide.)
01 · Motivation
Modern information systems & the data pipeline
The hard part of analytics is almost never the algorithm. In a real application the analysis code is under 5% of the lines; the other 95% is getting data in, clean, and reconciled — and that upstream work is exactly what this course is about.
Information systems used to live inside one enterprise, on one relational database. The Web era scaled the number of sources up by orders of magnitude and made most of them unstructured — free text, images, sensor streams, user-generated content. Today data feeds ML systems and LLMs, people create data fragments by using web services, and users act as both sensors and actuators. The question the course opens with is blunt: how do we control this mass of data?
The organising idea is the data pipeline — the workflow that turns raw sources into a consumable “data product”. Data integration and data warehousing are the two heavyweight stages in the middle.
1 · Ingestion
Collect from databases, APIs, files, sensors — every source with its own model and interface.
2 · Transformation
Clean, standardise, engineer features. This is where data quality and integration live.
3 · Storage
Land the reconciled data in a central repository — a data warehouse or a data lake.
4 · Analysis & modelling
Query, aggregate (OLAP), mine, or train — the visible 5%.
5 · Deployment
Ship the model or report into production and keep it fed.
Knowledge distillation
The course frames the pipeline as a climb from data → information → knowledge → wisdom. Integration and warehousing are how raw data becomes information you can trust enough to decide on.
02 · Setup
The integration problem
The data integration problem is: combining data coming from different sources to give the user a unified view — which means detecting correspondences between similar concepts across sources and solving the conflicts between them.
The course frames how it is done as three steps, a spine that recurs in almost every later lecture:
- Schema reconciliation — mapping the data structure, when the sources have one (the subject of this chapter and the next).
- Record linkage (entity resolution) — matching records that describe the same real-world entity across sources (Chapter 3).
- Data fusion — reconciling non-identical content once records are matched (Chapter 3).
Why is that hard? Because the sources are autonomous, and autonomy breeds heterogeneity. A source you did not design makes its own choices about three things:
Design autonomy
How it represents the world — its schema, names, data model. Two sources model the same reality differently.
Communication autonomy
How you query it — its query language and interface.
Execution autonomy
When and how it runs — its availability, ordering, algorithms.
Those autonomies generate a whole ladder of heterogeneity you must bridge:
- Technological — same model, different systems (Oracle vs DB2 vs Sybase).
- Model — different data models (relational vs object vs graph vs semi-structured XML/JSON).
- Language — different query languages (SQL vs QUEL).
- Schema (semantic) — different structures and conceptual choices for the same domain.
- Instance (semantic) — different values for the same fact, from errors or differing knowledge.
The four V's, read as integration difficulties
The Volume / Velocity / Variety / Veracity of big data map straight onto integration: not only is each source huge, the number of sources is in the millions (volume); many are very dynamic (velocity); they are deeply heterogeneous at schema and instance level (variety); and they are of widely differing quality — “1 in 3 business leaders do not trust the information they use to decide” (veracity).
The classic within-enterprise motivation makes the payoff concrete: a company’s PURCHASES, PRODUCTION and LOGISTICS databases share a lot of data. Left as a bag of separate DBs you get redundancy — wasted storage and, far worse, inconsistency when one copy is updated and the other is not. The correct answer is a single integrated schema where each datum appears once, with each function getting its own view onto it. Views are the conceptual tool we will use throughout: a view is a named query that expresses a transformation between schemas.
Not the same as a distributed database
A distributed database is integrated a priori: one organisation designs it centrally, then splits it (fragmentation, allocation, replication) over nodes with homogeneous technology. Data integration starts from sources that were designed independently and are heterogeneous. Distribution is a design-time convenience; integration is a reconciliation problem.
03 · Exam-hot
Pervasive data management
Pervasive data management — asked in 5 of 16 sessions
“Pervasive data management” (a.k.a. data management in pervasive systems) is the single most repeated Part I theory question in our bank — 2016-02-10, 2017-02-15, 2017-09-08, 2018-01-19, 2018-09-07. Graders want (1) a definition — managing data across many autonomous, heterogeneous, dynamic, distributed sources — and (2) the main problems that must be solved. Trap: listing only “lots of data”; the marks are for the heterogeneity + autonomy + dynamicity problems, not volume alone.
Pervasive (ubiquitous) computing scatters data across sensors, smart devices, web services and user-generated streams. Managing it is the integration problem taken to its extreme: the sources are not a fixed, known set of databases but a transient, dynamic, initially unknown population. The problems to solve are the ones the whole course circles back to:
- Source discovery & selection — finding which of millions of sources are relevant.
- Schema & semantic heterogeneity — reconciling models, structures and meanings you did not design.
- Data quality / veracity — coverage, accuracy, timeliness all differ per source; some cannot be trusted.
- Dynamicity & mobility — sources appear, disappear and move; answers must stay fresh.
- Incompleteness & uncertainty — you rarely have the whole picture, and rarely with certainty.
- Interaction with the real world — users are themselves sensors and actuators.
Because the source set is open and short-lived, pervasive settings push toward doing integration with as little up-front effort as possible — the pay-as-you-go idea we return to at the end of the chapter.
04 · Exam-hot
Materialized vs virtual integration
There are exactly two ways to give a user one view over many sources: copy the data into a new store, or leave it where it is and reformulate every query. The choice is a recurring exam question and it decides the whole architecture.
Materialized
Data are extracted, transformed and loaded (ETL) into a new physical repository — the classic case is a data warehouse. Queries run locally on the copy. Refreshed periodically (nightly, weekly), building up a history.
Virtual
Data stay at the sources. A query on the global schema is reformulated into queries in each source’s language, and the partial answers are combined. Enterprise Information Integration (EII) systems work this way. (Data exchange — materialising one source into a target — is a now-unfashionable relative.)
Both architectures have the same boxes, so the difference is not what exists but when each arrow fires — every query, or only at refresh:
The trade-off is freshness vs performance and history:
| Dimension | Materialized (warehouse) | Virtual (mediator) |
|---|---|---|
| Freshness | Stale between refreshes | Always live |
| Query speed | Fast — local, pre-cleaned | Depends on source availability/speed |
| History | Keeps time series | Only what the sources still hold |
| Source load | None at query time | Every query hits the sources |
| Cleaning/transform | Once, in ETL | At every query |
Which does a warehouse use, and why — asked in 3 of 16 sessions
“Materialized vs virtual, and which is used for data warehousing and why” appears in 2017-07-03, 2018-01-19 and 2018-07-09. The expected answer: data warehousing is materialized, because OLAP workloads need heavy aggregation over long histories with predictable performance, tolerate periodic (e.g. nightly) refresh, and do the cleaning once in ETL rather than at every query. The mirror-image trap is “virtual is always better because it is fresh” — freshness is irrelevant for multi-year trend analysis, where scan performance is what matters.
When you *must* go virtual
Tanca’s own example: an e-commerce site selling hotel rooms across 100+ reservation systems. Copy the availability data and it is out of date instantly — you might sell a room already sold. When the integrated data must be continuously up to date, virtual integration is the only correct choice.
05 · Architecture
Mediators, wrappers & the general framework
A virtual integration system is the machine that makes “leave the data in place” work. It must:
- support access to different sources,
- know what each source contains,
- integrate them under one global (mediated) schema,
- accept queries in the language of the global schema,
- rewrite those queries and distribute them to the sources,
- combine the answers into the final result.
Two roles do this work, and the exam will expect you to keep them apart (they get a chapter of their own next):
Wrapper
Sits on one source. Translates that source’s data model and query interface into the common model — exposing a web page, an XML store or a legacy DB as if it were relations. It knows nothing about the other sources.
Mediator
Holds the global schema and the mappings. Receives a global-schema query, decomposes and rewrites it into sub-queries for the wrapped sources, and reconciles the answers. Mediators can be stacked — a mediator over other mediators.
Put together, they are the machine — one wrapper per source translating it into the common model, one mediator above them holding the global schema and the only component that can see across sources:
The general framework (Fernandez, EDBT Summer School 2017)
An application talks to a middleware layer; below it each source is a data layer with its own data manager, queried via and returning answers ; mediators apply transformations and can recurse over sub-mediators. The whole point is that a query part touching a single source is pushed down and evaluated there, while any part that spans sources (e.g. a join across two databases) can only be evaluated at the global level — and finding good strategies for that decomposition is the core engineering challenge.
06 · The exercise
Designing an integration step by step
When the sources share a data model (start with the relational case), integration follows a fixed recipe. This recipe is the Part II integration exercise — learn it as a procedure you can run under exam pressure.
1 · Source schema identification
Identify each source’s schema, when it has one. (Some sources — web pages, files — do not, and need a wrapper to expose one.)
2 · Reverse engineering
Turn each source’s logical schema back into a conceptual (ER) schema. You reason about integration at the conceptual level, not over raw tables.
3 · Integration & restructuring
The heart of the exercise, in three moves: (a) related-concept identification (which entities across sources mean the same thing), (b) conflict analysis & resolution, (c) build the integrated conceptual schema.
4 · Conceptual → logical translation
Translate the integrated conceptual schema into a global logical (relational) schema.
5 · Mapping to the sources
Define, for each global relation, the view relating it to the source schemas (the GAV/LAV mappings of the next chapter). After this, the system can answer queries on the global schema.
Step 3(b) is where the marks are, so know the conflict taxonomy cold. When two related concepts disagree, the disagreement is one of:
Name conflicts
Homonyms — same name, different meaning (price = production price vs sale price).
Synonyms — different names, same thing (Department vs Division).
Type conflicts
Different types for one attribute (gender as M/F vs 0/1 vs implicit in the SSN), or different
abstractions of one real-world concept (attribute vs entity vs generalization).
Data-semantics conflicts
Same attribute, different units or granularity — euros vs dollars, kg vs pounds, °C vs °F, grams vs kilos.
Structure conflicts
The same thing modelled with a different structure — an attribute in one source, a full entity in another; a generalization here, two flat entities there.
Cardinality conflicts
A relationship that is 1:1 in one source and 1:N (or N:M) in another.
Key conflicts
Different identifiers for the same entity (Code vs Line+Code, CustomerId vs SSN).
Resolve toward minimal information loss
The exam text almost always says “integrate losing the least possible information”. That single rule
resolves most conflicts: keep the more general cardinality (N:M over 1:N), keep single-source
attributes as optional, generate a fresh global key when the source keys clash (KeyGen(id, source)), and unify units by converting (e.g. miles → km). Dropping a concept because only one source
has it is the classic way to lose marks.
Knowing the six kinds is not the same as telling them apart on a concrete pair of fragments, which is what the paper actually asks. Run the conflicts the lecture’s own example throws up:
Conflict analysis — classify and resolve
Two robot databases, PoliRobots and UniRobots, are being merged into UniPoliRobots. For each pair of corresponding elements, say which kind of conflict it is — then read the resolution that loses the least information.
Pick the kind of conflict. Hover a button for what that kind means.
{active, retired} against {0, 1}. Both are "the values disagree", but one is a unit inside a shared domain (semantics) and the other is the domain itself (type). That is the line graders are checking.KeyGen a fresh key when the identifiers clash. Dropping a concept because only one source has it is the standard way to lose marks.Running example — UniPoliRobots
The lecture threads one example through all five steps: two robot databases, PoliRobots and
UniRobots, merged into UniPoliRobots. You reverse-engineer each, spot that a “robot” in one is
a “robot” in the other, resolve the key clash by generating a common RobotID, and end with a single
global schema plus the views that map it back to the two sources.
Here is a full worked instance of the exercise — reverse engineering, the conflict table, the integrated schema, and the GAV mapping + query rewriting (the mapping half belongs to the next chapter, but see the shape of the whole thing now):
07 · Lightweight
Mashups & pay-as-you-go integration
Not every integration task deserves a mediated schema. Many are transient — you need to combine a few sources to answer a question asked once or twice, quickly, by someone without technical expertise (think disaster response: corroborate field reports and push them to the public fast). This is lightweight integration, and its problems are locating relevant sources, judging their quality, and understanding their semantics with minimal setup.
A mashup is an application that integrates two or more components at any application
layer — data, logic, or presentation — possibly making them talk to each other. The textbook
example, housingmaps.com, synchronises housing listings from Craigslist with Google Maps: neither
component alone offers the combined view, so integration is the added value.
Data mashup
Fetch data from several sources, process, return one integrated result set.
Logic mashup
Combine functionality published by logic/data components.
UI mashup
Combine the components’ native user interfaces into one, possibly synchronised.
Hybrid
Integration at more than one layer at once.
Positioned against traditional integration, mashups sit at the presentation layer and target non-mission-critical applications — the “long tail” of very specific, ad-hoc, situational data needs. Pay-as-you-go data management takes the same spirit to the extreme: skip the initial setup of a mediated schema and source descriptions entirely, and offer useful services over a heterogeneous collection with almost no up-front effort, improving the integration incrementally as you go.
Lightweight integration / mashups — asked in 2 of 16 sessions
Mashups / lightweight integration appears in 2016-02-10 and 2018-06-22. The expected answer defines a mashup (integration at data/logic/presentation layers), says when it fits (transient, non-mission-critical, long-tail, low-setup needs) and gives the pros/cons versus heavyweight integration. Trap: presenting a mashup as a replacement for a mediated integration system — it complements it, at the cheap-and-transient end.
Load-bearing ideas
- Integration = one unified view over autonomous sources. Autonomy (design, communication, execution) breeds heterogeneity (technological, model, language, schema, instance).
- Two strategies. Materialized (ETL into a warehouse — fast, historical, stale between refreshes) vs virtual (mediator rewrites queries to live sources — fresh, but source-dependent). Warehouses are materialized; continuously-up-to-date needs are virtual.
- Architecture. Wrappers adapt one source to the common model; mediators hold the global schema and rewrite/combine queries, and can stack.
- The design recipe. Identify → reverse-engineer → integrate (related concepts, conflict resolution) → translate to logical → map to sources. Resolve conflicts toward minimal information loss.
- Lightweight end. Mashups and pay-as-you-go handle transient, long-tail, low-setup integration.
- Exam radar. Re-derive before the exam: materialized vs virtual + why warehouses materialize; the conflict taxonomy and how minimal-information-loss resolves each; the five-step method as a procedure; and crisp definitions of pervasive data management and mashups.