MP5: DigiSonos & UBC

The B2B database project: integration

Goal

Learning INTEGRATED business data modelling.
Understand how B2B works from a data perspective.

Context

"All right, team, we're ready for phase two!"
Your team manager, E. R. Datopulos, is excited. Phase two is the fun part.
"Each of you has two tasks. First, connect with the two consultants who worked at the same company you did and consolidate your ERDs. Your goal is a single integrated diagram that supports all three business functions."
"I imagine there will be quite a bit of duplication across our individual diagrams," you say.
"Are you the one who took that awesome DM4DM course at McIntire? You're absolutely right! In fact, an important part of your job is to eliminate those duplications while preserving full functionality."
"Consider it done."
Mr. Datopulos gives you a big smile. "Excellent! The second task is to lay the foundation for the B2B system between DigiSonos and UBC. The technical consultants who will build it need a clear view of what data each company will send and receive. Your job is to identify and highlight those data flows."
The six of you nod. "We're 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 version 8.x, not the latest one 20-something, which does not have the functionality you need.
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: why two deadlines?

You may have noticed that there are two deadlines. It is to help you. I understand that you are all busy students and finding time to meet might be challenging. Start planning early and shoot for the 'ideal' deadline. If you do not make it, deliver by the second deadline (still no late penalty). Ideally all subteam and team members should meet in person. If that is not possible, use zoom, or do the task in steps. As a last resort, delegate the arrow drawing task to one representative from UBC and one from DigiSonos, and then individually check their work.

Consult the FAQ.