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 customers credit limit will be.

A sales representatives 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...