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 / MI49224 / 818.75 / 1000 / 3
256 / SAMUELS / ANN / 215 / PETE / GRANT / MI49219 / 21.5 / 1500 / 6
311 / CHARLES / DON / 48 / COLLEGE / IRA / MI49034 / 825.75 / 1000 / 12
315 / DANIELS / TOM / 914 / CHERRY / KENT / MI48391 / 770.75 / 750 / 6
405 / WILLIAMS / AL / 519 / WATSON / GRANT / MI49219 / 402.75 / 1500 / 12
412 / ADAMS / SALLY / 16 / ELM / LANSING / MI49224 / 1817.5 / 2000 / 3
522 / NELSON / MARY / 108 / PINE / ADA / MI49441 / 98.75 / 1500 / 12
567 / DINH / TRAN / 808 / RIDGE / HARPER / MI48421 / 402.4 / 750 / 6
587 / GALVEZ / MARA / 512 / PINE / ADA / MI49441 / 114.6 / 1000 / 6
622 / MARTIN / DAN / 419 / CHIP / GRANT / MI49219 / 1045.75 / 1000 / 3
SALES REPS (ID, NAME(LAST FIRST), ADDRESS, TOTAL COMMISSION, COMMISSION RATE)
3 / JONES / MARY / 123 / MAIN / GRANT / MI49219 / 2150 / .05
6 / SMITH / WILLIAM / 102 / RAYMOND / ADA / MI49441 / 4912.5 / .07
12 / DIAZ / MIGUEL / 419 / HARPER / LANSING / MI49224 / 2150 / .05
ORDERS(ID, ORDER DATE, CUSTOMER, SHIPPING DATE)
1248902-JUL-1112422-JUL-11
1249102-JUL-1131122-JUL-11
1249404-JUL-1131512-JUL-11
1249504-JUL-1125622-AUG-11
1249805-JUL-11522
1250005-JUL-1112422-AUG-11
1250405-JUL-11522
ORDER LINES (ORDER ID, PART ID, NUMBER ORDERED, QUOTED PRICE)
12489AX1211 / 21.95
12491BT041 / 149.99
12491BZ661 / 399.99
12494CB034 / 279.99
12495CX112 / 22.95
12498AZ522 / 12.95
12500BT041 / 149.99
12504CZ812 / 325.99
PARTS (PART ID, PART DESCRIPTION, UNITS ON HAND, CLASS, WAREHOUSE NUMBER, UNIT PRICE)
AX12IRON / 104HW3 / 24.95
AZ52DARTBOARD / 20SG2 / 12.95
BH22CORNPOPPER / 95HW3 / 24.95
BT04GAS GRILL / 11AP2 / 149.99
BZ66WASHER / 52AP3 / 39.99
CA14GRIDDLE / 78HW3 / 39.99
CB03BIKE / 44SG1 / 299.99
CX11BLENDER / 112HW3 / 22.95
CZ81TREADMILL / 68SG2 / 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 / MI49224 / NULL444-555-1212 / 500.25 / 1500 / 2
108 / SMITH / JON / 215 / PETE / GRANT / MI49219 / ULL / 21.5 / 1500 / 15
124 / ROAST / CHUCK / 48 / COLLEGE / IRA / MI49034 / NULL444-555-1111 / 825.75 / 1000 / 15
101 / WILLIAMS / AL / 914 / CHERRY / KENT / MI48391 / NULL444-555-1211 / 125.75 / 2500 / 15
SALES REPS (SALES NO, NAME (LAST FIRST), ADDRESS, E-MAIL, COMMISSION RATE)
15 / DALY / SYLVIA / 555 / MAIN / GRANT / MI49219 / / .05
2 / JONES / WILLIAM / 15 / SECOND / ADA / MI49441 / / .07
ORDERS (ORDER NO, ORDER DATE, CUSTOMER, STATUS, SHIPPING DATE)
1122302-JUN-11100122-JUN-11
1122402-AUG-11100115-AUG-11
1122504-AUG-11101301-SEP-11
1122604-JUL-11124222-JUL-11
ORDER LINES (ORDER NO,PROD ID)
11223152
11223241
11223173
1122461
1122662
11226151
1122562
PRODUCTS (PROD ID, PRODUCT NAME, DESCRIPTION, QTY ON HAND, WAREHOUSE NUMBER, UNIT PRICE)
12MIXER / 1253 / 24.95
15DARTBOARD / 152 / 12.95
17BLENDER / 103 / 24.95
1LAWN MOWER / 22 / 149.99
24WAFFLE MAKER / 173 / 39.99
6FRY PAN / 1223 / 39.99
8BIKE / 201 / 299.99
18TREADMILL / 192 / 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:

1.  List each sales representative with their customers’ last names, first names, addresses, credit limits, and balances.

2.  Show the orders placed over the past 10 years, grouped by customer. Include the order number, order date, ship date, part numbers for ordered parts, quote price, number ordered, and cost for the ordered amount of parts.

3.  A part list including part description, vendor, quantity on hand, and unit price, grouped by warehouse.