Answered! Some consultants believe it is inefficient to maintain multiple databases for the two companies whose merger was…

Some consultants believe it is inefficient to maintain multiple databases for the two companies whose merger was presented in the Discussion. They recommend a single data source as the best solution for data integration. Therefore, all the data from both companies will be merged into a single set of tables, and the reports will be generated from this merged database.

The tables and data from the Unit 2 Application are shown below.

CUSTOMERS (ID, NAME(LAST FIRST), ADDRESS, CURRENT BALANCE, CREDIT LIMIT, SALES REP ID)

124

ADAMS

SALLY

481

OAK

LANSING

MI 49224

818.75

1000

3

256

SAMUELS

ANN

215

PETE

GRANT

MI 49219

21.5

1500

6

311

CHARLES

DON

48

COLLEGE

IRA

MI 49034

825.75

1000

12

315

DANIELS

TOM

914

CHERRY

KENT

MI 48391

770.75

750

6

405

WILLIAMS

AL

519

WATSON

GRANT

MI 49219

402.75

1500

12

412

ADAMS

SALLY

16

ELM

LANSING

MI 49224

1817.5

2000

3

522

NELSON

MARY

108

PINE

ADA

MI 49441

98.75

1500

12

567

DINH

TRAN

808

RIDGE

HARPER

MI 48421

402.4

750

6

587

GALVEZ

MARA

512

PINE

ADA

MI 49441

114.6

1000

6

622

MARTIN

DAN

419

CHIP

GRANT

MI 49219

1045.75

1000

3

SALES REPS (ID, NAME(LAST FIRST), ADDRESS, TOTAL COMMISSION, COMMISSION RATE)

3

JONES

MARY

123

MAIN

GRANT

MI 49219

2150

.05

6

SMITH

WILLIAM

102

RAYMOND

ADA

MI 49441

4912.5

.07

12

DIAZ

MIGUEL

419

HARPER

LANSING

MI 49224

2150

.05

ORDERS(ID, ORDER DATE, CUSTOMER, SHIPPING DATE)

12489  02-JUL-11  124   22-JUL-11

12491  02-JUL-11  311   22-JUL-11

12494  04-JUL-11  315   12-JUL-11

12495  04-JUL-11  256   22-AUG-11

12498  05-JUL-11  522

12500  05-JUL-11  124   22-AUG-11

12504  05-JUL-11  522

ORDER LINES (ORDER ID, PART ID, NUMBER ORDERED, QUOTED PRICE)

12489  AX12   11

21.95

12491  BT04    1

149.99

12491  BZ66    1

399.99

12494  CB03    4

279.99

12495  CX11    2

22.95

12498  AZ52    2

12.95

12500  BT04    1

149.99

12504  CZ81    2

325.99

PARTS (PART ID, PART DESCRIPTION, UNITS ON HAND, CLASS, WAREHOUSE NUMBER, UNIT PRICE)

AX12   IRON

104   HW   3

24.95

AZ52   DARTBOARD

20   SG   2

12.95

BH22   CORNPOPPER

95   HW   3

24.95

BT04   GAS GRILL

11   AP   2

149.99

BZ66   WASHER

52   AP   3

39.99

CA14   GRIDDLE

78   HW   3

39.99

CB03   BIKE

44   SG   1

299.99

CX11   BLENDER

112   HW   3

22.95

CZ81   TREADMILL

68   SG   2

349.95

The tables and data for the company you are merging with are shown below.

CUSTOMER (CUST NO, NAME (LAST FIRST), ADDRESS, E-MAIL, PRIMARY PHONE, CURRENT BALANCE, CREDIT LIMIT, SALES REP ID)

100

ADAMS

SALLY

481

OAK

LANSING

MI 49224

NULL 444-555-1212

500.25

1500

2

108

SMITH

JON

215

PETE

GRANT

MI 49219

jsmith@xxx.com NULL

21.5

1500

15

124

ROAST

CHUCK

48

COLLEGE

IRA

MI 49034

NULL 444-555-1111

825.75

1000

15

101

WILLIAMS

AL

914

CHERRY

KENT

MI 48391

NULL 444-555-1211

125.75

2500

15

SALES REPS (SALES NO, NAME (LAST FIRST), ADDRESS, E-MAIL, COMMISSION RATE)

15

DALY

SYLVIA

555

MAIN

GRANT

MI 49219

sday@xxx.com

.05

2

JONES

WILLIAM

15

SECOND

ADA

MI 49441

wjones@xxx.com

.07

ORDERS (ORDER NO, ORDER DATE, CUSTOMER, STATUS, SHIPPING DATE)

11223  02-JUN-11  100   1   22-JUN-11

11224  02-AUG-11  100   1   15-AUG-11

11225  04-AUG-11  101   3   01-SEP-11

11226  04-JUL-11  124   2   22-JUL-11

ORDER LINES (ORDER NO,PROD ID)

11223  15   2

11223  24   1

11223  17   3

11224   6   1

11226   6   2

11226  15   1

11225   6   2

PRODUCTS (PROD ID, PRODUCT NAME, DESCRIPTION, QTY ON HAND, WAREHOUSE NUMBER, UNIT PRICE)

12   MIXER

125   3

24.95

15   DARTBOARD

15   2

12.95

17   BLENDER

10   3

24.95

1   LAWN MOWER

2   2

149.99

24   WAFFLE MAKER

17   3

39.99

6   FRY PAN

122   3

39.99

8   BIKE

20   1

299.99

18   TREADMILL

19   2

349.95

Design a UML model for the new company enterprise. Provide a mapping of the original fields to the fields in your diagram to ensure no data is lost in the transition. All tables must have primary keys and foreign keys. Capture screenshots of the tables.

Create the new database using MySQL and transfer the existing data into the new tables. Clean the data and remove duplicate records as they are transferred to the new tables. Do not destroy the existing data in the original database in case you need to go back and retrieve the original data. Capture screenshots of the data cleaning process.

As you are designing and merging the data, consider the following:

·         There may be errors in some of the data that will result in inconsistencies and duplicate records. Make reasonable assumptions as to which records are duplicates, and list all assumptions and business rules in your UML model. Define and justify what constitutes a duplicate customer.

·         The same customer may have a different credit limit for each store. When the stores merge, decide what the customer’s credit limit will be.

·         A sales representative’s commission is 5%.

·         Consolidate the current balance due for each customer from each store.

·         Determine and justify what constitutes the same part/product. Consider storing the inventory to create uniqueness in the parts/products table.

After you create the tables and load the data into them, generate the following reports by creating screenshots or cutting and pasting the results into a Word document:

Need to prepare Only Bold letter portion…