The UML Entity Relationship at the bottom of this question is it correct based o
ID: 3845829 • Letter: T
Question
The UML Entity Relationship at the bottom of this question is it correct based of the parts hightlighted?
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.
124
481
818.75
1000
3
256
215
21.5
1500
6
311
48
825.75
1000
12
315
914
770.75
750
6
405
519
402.75
1500
12
412
16
1817.5
2000
3
522
108
98.75
1500
12
567
808
402.4
750
6
587
512
114.6
1000
6
622
419
1045.75
1000
3
3
123
2150
.05
6
102
4912.5
.07
12
419
2150
.05
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
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
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.
100
481
NULL 444-555-1212
500.25
1500
2
108
215
jsmith@xxx.com NULL
21.5
1500
15
124
48
NULL 444-555-1111
825.75
1000
15
101
914
NULL 444-555-1211
125.75
2500
15
15
555
sday@xxx.com
.05
2
15
wjones@xxx.com
.07
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
11223 15 2
11223 24 1
11223 17 3
11224 6 1
11226 6 2
11226 15 1
11225 6 2
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:
List each sales representative with their customers’ last names, first names, addresses, credit limits, and balances.
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.
A part list including part description, vendor, quantity on hand, and unit price, grouped by warehouse.
(This part is important). I see warehousenumber is the UML as a foreign key, but not associated with a primary key.)
CUSTOMERS (ID, NAME(LAST FIRST), ADDRESS, CURRENT BALANCE, CREDIT LIMIT, SALES REP ID)124
ADAMS SALLY481
OAK LANSING MI 49224818.75
1000
3
256
SAMUELS ANN215
PETE GRANT MI 4921921.5
1500
6
311
CHARLES DON48
COLLEGE IRA MI 49034825.75
1000
12
315
DANIELS TOM914
CHERRY KENT MI 48391770.75
750
6
405
WILLIAMS AL519
WATSON GRANT MI 49219402.75
1500
12
412
ADAMS SALLY16
ELM LANSING MI 492241817.5
2000
3
522
NELSON MARY108
PINE ADA MI 4944198.75
1500
12
567
DINH TRAN808
RIDGE HARPER MI 48421402.4
750
6
587
GALVEZ MARA512
PINE ADA MI 49441114.6
1000
6
622
MARTIN DAN419
CHIP GRANT MI 492191045.75
1000
3
CUSTOMERS CUSTNO (PK) LASTNAME FIRSTNAME ADDRESS EMAIL PRIMARY PHONE CURRENTBALANCE CREDIT LIMIT Process 1..N ORDERS ORDERNO (PK) ORDER DATE CUSTNO (FK) STATUS SHIPPINGDATE PARTS PARTID (PK) PARTDESCRIPTION UNITSONHAND CLASS WAREHOUSENUMBER (FK) UNIT PRICE 1..N 1..N 1.N SALESREPS Interacts With SALESREPID (PK) LASTNAME 1..1 FIRSTNAME ADDRESS E-MAIL TOTALCOMMISSION COMMISSION RATE ORDERLINES Goes In ORDERNO (PK) 1.N PARTID (FK) PRODID (FK) NUMBERORDERED 1.1 QUOTED PRICE 1..1 Contains Contains 1..N PRODUCTS PRODID (PK) PRODUCTNAME DESCRIPTION QTYONHAND WAREHOUSE NO (FK) UNITPRICEExplanation / Answer
As per the descripton and the key points and the databases given the UML ER diagram which is shown last is perfectly apt and correct based upon the scenario except a small change. In products table make the warehouse number as primary key and draw the relationship(Many to 1) between parts and products entities.
Based on the above ER Diagram I am giving you the MY SQL Table Definitions inorder to create the tables.
To retrieve the repors with the required information execute the following queries/
For the Lastquestion include the warehourse number as primary key in product table as primary key and create the relationship between the parts and product table. Because the product may have been created with many parts. And also the manufacturer which manufactures the parts may design and manufacture product also.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.