MP5: DigiSonos & UBC

The B2B database project: integration

Goal

Learning data modelling  / design business database.
Understand how B2B works from a data perspective.

Context

"Alright Team, we are ready for phase two"
 Your team manager, Erdon Datopulos, is excited.  Phase two is the fun part.
"Each one of you has two tasks: first, connect with the two consultants who worked at the same company as you did and consolidate your ERDs.  Your goal is to create a single integrated diagram that supports all three business functions."
"I imagine that there will be quite a bit of duplication among our individual diagrams..."
"Are you the one who took that awesome DM4DM course at McIntire? You are so right!  In fact, an important part of your job is to eliminate all those duplications while preserving full functionality."
"Consider it done."
"Excellent! The second task is to create the foundations for the B2B system between DigiSonos and UBC.  The technical consultants that will build the B2B systems needs a clear view of what data each company will send and receive. Your job is to identify and highlight those data flows."
"We are on it, boss!"

Task ONE (Sub-team of three): Integrating the functions

You are a member of a team of three people working for the same company. Create an ERD for the whole company by consolidating your individual diagrams.

DigiSonos: Purchasing, Receiving, Accounts Payable

UBC:  Sales, Shipping, Accounts Receivable

Tool: must use MySqlWorkbench
Format: a printout in a single sheet of paper
The ERD must include:

• Tables/entities
• Primary keys (with WB "key" symbols)
• Foreign keys, shown both as relationships (lines) and also as red diamonds/keys
• Attributes
• Relationships, labelled with one/two words to clarify their meaning
• Upper and lower cardinalities the "crow's foot" notation

Do not show indexes and data types.
Avoid drawing too many crossing lines.
Avoid using fonts that when printed are too small to read.
Remember to keep a copy for yourself.

This ERD does not need to be handed in. It is input for

Task TWO (Team of Six): Mapping Data Flows Between UBC and DigiSonos

Get together and create a single ERD that shows how data moves between UBC and DigiSonos throughout the B2B process. The diagram above gives you a simplified idea—your job is to flesh it out using the full ERDs for both companies.
This is a low-tech exercise. Print out the ERD for UBC and for DigiSonos. Use a marker to highlight the specific data fields that are exchanged at each step. For example, the first step is UBC sending product and pricing info to DigiSonos. Highlight the relevant fields in UBC’s ERD, then highlight where that data lands in DigiSonos’s ERD. Draw one or more arrows with an highlighter to show the direction of the data flow(s) and label them “1” to mark the first step.
Next, DigiSonos places an order. Don’t worry about how they decide what to buy—just focus on the data they send to UBC. Highlight the fields in DigiSonos’s ERD that are transmitted, and where they go in UBC’s ERD. Again, draw one or more arrows and label them “2”.
Continue this process for each step in the overall process, numbering the arrows sequentially: 3, 4, 5 and so on.
If multiple entities are involved in a single data exchange (like ‘order’ and ‘order line item’), give all related arrows the same number to show the data move together.
As you work through this, you might notice missing or mismatched data. Maybe DigiSonos sends something UBC doesn’t have a place to store, or UBC expects something DigiSonos isn’t sending. In those cases, you’ll need to revise the ERDs and the arrows to make sure both systems can handle the data properly.

You can do this by taping together printed ERDs and drawing the flows by hand, or by combining the two ERDs in MySQL Workbench (see the tip below), printing the larger diagram, and then drawing the flows.

Criteria

Your ERD must accurately represent all the business functions described in the write-ups, include all the ERD items and requirements listed above, and the numbered arrows representing the stepped data flows.

Tips for success: analysis paralysis

The descriptions for the assignment are somewhat vague. You will experience some discomfort because of that. That feeling is actually a common experience in real-world data modeling. You will have to think hard about what data are needed to perform the various tasks that need to be done.  As a general recommendation, do not overdo. Avoid analysis paralysis! Be reasonable in your assumptions and keep your model simple. Handle the problems that are described in the text for the six functions. Do not handle all the conceivable problems of the two companies.

Tip: easier drawing of the arrows

Suppose that DigiSonos is on your left ERD and UBC on the right (as in the figure).  You will make your task much easier if you move the entities that either send or receive data on the side of the ERD facing the other company.  For instance, the DigiSonos "order"  could be moved to the right of the DigiSonos ERD, thus "facing" the UBC ERD. Still do not cause too many lines to cross.

Tip: Copy/Pasting Tables between Diagrams

If you try to copy/paste tables from multiple diagrams into one file you will find that MySQLWorkbench won’t let you open multiple model files at once.  However, you can open up one model (*.mwb) and then open up a second diagram into it by selecting File > Include Model. When you do this, the second ERD will appear as a new option in the ‘Model Overview’ section. From there, you can open multiple diagrams up and copy/paste tables between them.

Tip: plan your meetings early

You are all busy students! Finding common meeting times might be challenging. For this reason I gave a little more time to complete MP5. Start planning early. As a last resort, delegate the arrow drawing task to a representative from UBC and one from DigiSonos, and then individually check their work.

Consult the FAQ.