Banner

 

Data integration

Data integration is typically defined as the standardization of data definitions and structures throughout an organization. Field and code definitions are standard. For example:

The goal of corporate data integration is to increase:

An integrated environment

Table 1 shows example data for a company that has integrated data across the Red and Blue divisions.

Table 1: Data integration

Red Division Blue Division
partnumber
(code for green widget)
27 27
customerid
(code for UPS)
53 53
Definition of salesdate The date the customer signs the order The date the customer signs the order

Exercise

Tables containing data for the Red and Blue divisions are shown below. Write an SQL query to report total unit sales for the corporation.

Blue Division

partnumber unitssold
1 45
2 9
4 7
6 90
7 12
8 23
9 12
16 3

Red Division

partnumber unitssold
1 15
2 25
3 30
4 12
5 56
6 2
8 1
9 345
10 23
15 67

SQL code

CREATE TABLE blue1 (
partnumber INTEGER,
unitssold INTEGER,
PRIMARY KEY(partnumber));

CREATE TABLE red1 (
partnumber INTEGER,
unitssold INTEGER,
PRIMARY KEY(partnumber));

INSERT INTO blue1 VALUES (1,45);
INSERT INTO blue1 VALUES (2,9);
INSERT INTO blue1 VALUES (4,7);
INSERT INTO blue1 VALUES (6,90);
INSERT INTO blue1 VALUES (7,12);
INSERT INTO blue1 VALUES (8,23);
INSERT INTO blue1 VALUES (9,12);
INSERT INTO blue1 VALUES (16,3);

INSERT INTO red1 VALUES (1,15);
INSERT INTO red1 VALUES (2,25);
INSERT INTO red1 VALUES (3,30);
INSERT INTO red1 VALUES (4,12);
INSERT INTO red1 VALUES (5,56);
INSERT INTO red1 VALUES (6,2);
INSERT INTO red1 VALUES (8,1);
INSERT INTO red1 VALUES (9,345);
INSERT INTO red1 VALUES (10,23);
INSERT INTO red1 VALUES (15,67);

A non-integrated environment

Table 1 shows example data for a company that has not integrated data across the Red and Blue divisions.

Red Division Blue Division
partnumber
(code for green widget)
27 10056
customerid
(code for UPS)
53 613
Definition of salesdate The date the customer signs the order The date the customer receives the order

Exercise

Tables containing data for the Red and Blue divisions have been created. For each division, the table lists the partnumber, part description, and units sold. Write an SQL query to report total unit sales for the corporation. Hint: you might want to create a table that matches the codes, where you can, between the two tables.

Blue Division

partnumber partdescription unitssold
1 widget - 5cm 45
2 widget - 10cm 9
4 widget - 15cm 7
6 widget - blue 90
7 widget - green 12
8 widget - yellow 23
9 widget - round 12
16 widget - plain 3

Red Division

partnumber partdescription unitssold
1001 striped widget 15
1002 checked widget 25
1003 polka dot widget 30
1004 plain widget 12
1005 red widget 56
1006 blue widget 2
1008 yellow widget 1
1009 5cm widget 345
1010 10 cm widget 23
1015 triangular widget 67

SQL code

CREATE TABLE blue2 (
partnumber INTEGER,
partdescription VARCHAR(20),
unitssold INTEGER,
PRIMARY KEY(partnumber));

CREATE TABLE red2 (
partnumber INTEGER,
partdescription VARCHAR(20),
unitssold INTEGER,
PRIMARY KEY(partnumber));

INSERT INTO blue2 VALUES (1,'widget - 5cm',45);
INSERT INTO blue2 VALUES (2,'widget - 10cm',9);
INSERT INTO blue2 VALUES (4,'widget - 15cm',7);
INSERT INTO blue2 VALUES (6,'widget - blue',90);
INSERT INTO blue2 VALUES (7,'widget - green',12);
INSERT INTO blue2 VALUES (8,'widget - yellow',23);
INSERT INTO blue2 VALUES (9,'widget - round',12);
INSERT INTO blue2 VALUES (16,'widget - plain',3);

INSERT INTO red2 VALUES (1001,'striped widget',15);
INSERT INTO red2 VALUES (1002,'checked widget',25);
INSERT INTO red2 VALUES (1003,'polka dot widget',30);
INSERT INTO red2 VALUES (1004,'plain widget',12);
INSERT INTO red2 VALUES (1005,'red widget',56);
INSERT INTO red2 VALUES (1006,'blue widget',2);
INSERT INTO red2 VALUES (1008,'yellow widget',1);
INSERT INTO red2 VALUES (1009,'5cm widget',345);
INSERT INTO red2 VALUES (1010,'10cm widget',23);
INSERT INTO red2 VALUES (1015,'triangular widget',67);

Note

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 02-Dec-2022