Answered! Question: The UML Entity Relationship at the bottom of this question is it correct based of the parts hight……

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.

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:

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 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) UNITPRICE

Expert 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.

  • CREATE TABLE CUSTOMERS( CUSTNO INT(5) PRIMARY KEY,LASTNAME VARCHAR(10),FIRSTNAME VARCHAR(10),ADDRESS VARCHAR(20),EMAIL VARCHAR(20),PRIMARYPHONE INT(11),CURRENTBALANCE DECIMAL(5,2),CREDITLIMTI INT(5),SALESREPID INT(5) FORIGN KEY REFERENCES SALESREPS(SALESREPID));
  • CREATE TABLE SALESREPS(SALESREPID INT(5) PRIMARY KEY,LASTNAME VARCHAR(10),FIRSTNAME VARCHAR(10),ADDRESS VARCHAR(20),EMAIL VARCHAR(20),TOTALCOMMISSION INT(2),TOTALCOMMISSION INT(2));
  • CREATE TABLE ORDERS (ORDERNO INT(4) PRIMARY KEY,ORDERDATE DATE,CUSTNO INT(5) FOREIGN KEY REFERENCES CUSTOMERS(CUSTNO),STATUS VARCHAR(20),SHIPPINGDATE DATE);
  • CREATE TABLE ORDERLINES(ORDERNO INT(5) PRIMARY KEY,PARTNO INT(5) FOREIGN KEY REFERENCES PARTS(PARTID),PRODID INT(5) FOREIGN KEY REFERENCES PRODUCTS(PRODID),NUMBERORDERED INT(5),QUOTEDPRICE INT(5));
  • CREATE TABLE PARTS(PARTID INT(5) PRIMARY KEY,PARTDESCRIPTION VARCHAR(30),UNITSONHAND INT(3),CLASS CHAR(1),WAREHOUSENUMBER INT(5) FOREIGN KEY REFERENCES PRODUCTS(WAREHOUSENUMBER),UNITPRICE INT(4));
  • CREATE TABLE PRODUCTS(PRODID INT(5) PRIMARY KEY,PRODUCTNAME VARCHAR(20),DESCRIPTION VARCHAR(30),QTYONHAND INT(4),WAREHOUSENO INT(5) PRIMARY KEY,UNITPRICE INT(4));

To retrieve the repors with the required information execute the following queries/

  • SELECT LASTNAME,FIRSTNAME,ADDRESS,CREDTLIMIT,BALANCE,SALESREPID FROM CUSTOMERS.
  • SELECT O.ORDERNO,O.ORDERDATE,O.SHIPPINGDATE,OL.PARTID,OL.QUOTEDPRUCE,OLNUMBERORDERED,(NUMBER*QUOTEPRICE) AS COST FROM THE ORDERS O,ORDERLINES OL WHERE O.ORDERNO=OL.ORDERNO AND ORDERDATE>=DATE_SUB(NOW(),INTERVAL 10 YEAR) GROUPBY O.CUSTNO;
  • SELECT PARTID,PARTDESCRIPTION,UNITSONHAND,UNITPRICE FROM PARTS GROUP BY WAREHOUSENUMBER;

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.

Still stressed from student homework?
Get quality assistance from academic writers!