Data Integration and Transformation

Data Integration and Transformation

PART A — DATA INTEGRATION

1. Definition

Data integration combines data from multiple databases, files, sensors and web services into a coherent data store, resolving conflicts so the combined data is consistent and usable for mining.

2. The Three Integration Problems

(a) Schema integration & entity identification
The same real-world entity may have different names/keys in different sources:
CRM.cust_id = ERP.customer_no = BILLING.party_code.
Solutions: metadata-based entity matching, value-pattern matching, global surrogate keys, integration schema / mediated schema.

(b) Redundancy detection
An attribute is redundant if it can be derived from another attribute in the same or another table (e.g. total = qty × price, birth_date vs age).

• Numeric attributes → correlation coefficient $r_{A,B}$:

$r = 0$ → independent; $|r| \ge 0.8$ (typical cut-off) → strongly correlated → one attribute may be redundant.

• Nominal (categorical) attributes → chi-square test:

Example — gender vs preferred reading:

	Fiction	Non-fiction	Row total
Male	250	200	450
Female	50	1000	1050
Col total	300	1200	N = 1500

Expected: $e_{11}=\frac{450\times300}{1500}=90$, $e_{12}=360$, $e_{21}=210$, $e_{22}=840$

Large $\chi^2$ → gender and reading preference are strongly correlated (not independent).

(c) Tuple duplication & value conflicts

• Same customer entered twice in two systems → detect by matching rules, merge with survivorship.
• Conflicting units/scales — kg vs pound, ₹ vs $, DD/MM/YY vs YYYY-MM-DD: convert to a single standard.

PART B — DATA TRANSFORMATION

3. Purpose

Convert data into forms suitable for the mining algorithm: smoothing, aggregation, generalisation, normalisation and attribute construction.

4. Smoothing

Remove noise from data using binning, regression or clustering (see Data Cleaning).

5. Attribute Construction (Feature Construction)

Create new attributes that are more informative:
area = length × width, BMI = weight / height², profit = revenue − cost, recency = today − last_purchase.

6. Aggregation

Summarise data at a coarser grain — daily sales → monthly, per-store → per-region. Reduces data volume and stabilises variance.

7. Normalisation

Needed when attributes have very different ranges and algorithms use distance (k-NN, k-means, neural nets).

Method	Formula	Example ($v = 73$, $\min=12$, $\max=98$, $\bar A=54$, $\sigma=16$)
Min–max to [0,1]	$v'=\dfrac{v-\min_A}{\max_A-\min_A}$	$(73-12)/(98-12)=0.709$
Z-score	$v'=\dfrac{v-\bar A}{\sigma_A}$	$(73-54)/16 = 1.19$
Decimal scaling	$v'=\dfrac{v}{10^{j}}$, $j$ makes $\max|v'|<1$	$v=725$, $j=3$ → $0.725$

8. Discretisation & Concept Hierarchy

Replace continuous values by intervals or higher-level concepts: age = 27 → young; (0, 20] (20, 40] (40, 60]. (Detailed under Data Discretization and Concept Hierarchy Generation.)

9. Encoding & Generalisation

• One-hot encoding for nominal attributes; label encoding for ordinal.
• Generalise low-level values: street → city → state → country; day → month → quarter → year.

10. Integration-Transformation Pipeline

 Source A (Oracle, kg, DD-MM-YYYY) --\
                                      +--> [ entity match ] -> [ redundancy
 Source B (CSV, pounds, MM/DD/YYYY) --/       (global keys)      removal,
                                                                  unit/format
 Source C (Web API, JSON) ------------+                           conversion ]
                                                                        |
                                        [ aggregate ] -> [ normalise ] -> MINING DATA

11. Exam Points

• Explain data integration problems and solutions with chi-square / correlation example — 10 marks.
• Normalisation numerical (min-max, z-score, decimal scaling) — 5 marks (frequent).
• Short notes: attribute construction, aggregation, smoothing — 3-5 marks.
• Why normalisation matters for distance-based algorithms — 3 marks.