A Data Integration Model, Discussion Question help

Anonymous
timer Asked: Oct 9th, 2016
account_balance_wallet $15

Question Description

I need an 2 page paper Assignment.

Application: A Data Integration Model

Please read the attached document
Thank you


Unformatted Attachment Preview

Application: A Data Integration Model 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 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 LANSING MI 49224 2000 3 522 NELSON MARY 108 PINE ADA MI 49441 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 48 COLLEGE 16 ELM 1817.5 98.75 SALES REPS (ID, NAME(LAST FIRST), ADDRESS, TOTAL COMMISSION, COMMISSION RATE) 3 JONES MARY 123 MAIN 6 SMITH WILLIAM MIGUEL 12 DIAZ GRANT MI 49219 2150 102 RAYMOND ADA MI 49441 4912.5 .07 419 HARPER MI 49224 2150 LANSING .05 .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 12504 05-JUL-11 522 22-AUG-11 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 CX11 BLENDER CZ81 TREADMILL 44 SG 1 299.99 112 HW 3 22.95 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 108 SMITH JON GRANT 124 ROAST CHUCK 101 WILLIAMS AL 215 PETE 48 COLLEGE IRA 914 CHERRY KENT MI 49219 jsmith@xxx.com NULL 2 JONES WILLIAM 555 MAIN MI 48391 NULL 444-555-1211 125.75 2500 15 GRANT MI 49219 15 SECOND ADA sday@xxx.com .05 MI 49441 wjones@xxx.com .07 ORDERS (ORDER NO, ORDER DATE, CUSTOMER, STATUS, SHIPPING DATE) 11223 02-JUN-11 100 1 22-JUN-11 1500 15 825.75 1000 15 COMMISSION RATE) SYLVIA 21.5 2 MI 49034 NULL 444-555-1111 SALES REPS (SALES NO, NAME (LAST FIRST), ADDRESS, E-MAIL, 15 DALY 500.25 1500 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 15 DARTBOARD 125 3 24.95 15 2 12.95 17 1 24 BLENDER LAWN MOWER WAFFLE MAKER 10 3 24.95 2 2 149.99 17 3 39.99 122 3 39.99 6 FRY PAN 8 BIKE 20 1 299.99 TREADMILL 19 2 349.95 18 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. ...

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors