MP4: DigiSonos & UBC

The B2B database project

Goal

Learning to model business data.

Context

"I have your next assignment!"
You are a consultant with CapTech, a consulting company based in Richmond with a national presence. Your team manager, E. R. Datopulos, has in his hands a voluminous contract.
"This has just been signed: we are engaged with two clients: DigiSonos and UBC. We will help them streamline their B2B operations. The project will have two phases: first we will model the business functions of the two companies (selling, accounts payable, receiving, etc.). Then we will design two separate platforms (one per company) to manage most of their business operations and we will connect them to automate their B2B commerce. We will advise on data modeling and integration. Your initial job will be to model the data for one of their business function. Make sure you are up to speed with MySQL Workbench because we will standardize on it."

This is your first assignment with a B2B project. You are both excited and a bit apprehensive for the challenge ahead.
"I am on it, boss!"

Prep work

Watch the MySQLWorkbench video.  Pay special attention to the last five minutes, where the concepts of identifying/non-identifying relationships are introduced with an example. Make sure you understand these concepts. They are a source of frequent errors for beginners.

There are copies of the Workbench installed in the McIntire labs. If you prefer to download a copy on your computer, the community edition is free and available for Windows and MacOS. Do not download the MySQL RDBMS.  Only the Workbench.

Task

MP4 is a pleadged, individual assignment. In MS Teams you will be assigned to one of the functions below.  Find which one, then create an ER diagram for that specific function.

DigiSonos: Purchasing,   Receiving,   Accounts Payable

UBC:  Sales,   Shipping,   Accounts Receivable

Tool: you must use MySqlWorkbench
Format: a readable printout in a single sheet of paper to be handed in class.
The ERD must include:

• Tables/entities
• Primary keys (with the mySQLWB "key" symbols)
• Foreign keys, shown both as relationships (lines) and also as red diamonds/red keys
• Attributes
• Relationships, labelled with one/two words to clarify their meaning
• Upper and lower cardinalities with the "crow's foot" notation
• Avoid too many crossing lines.
• Avoid using fonts that when printed are too small to read.
• Remember to write your name and the function that you are modeling (e.g., 'Liam Xiong, Shipping').

Keep a copy for yourself: you will need it for MP5.

Criteria

Your ERD must accurately represent the business function described in the write-up assigned to you and include all the items and requirements listed above.

Tips for success

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 in your assigned function.  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 your function. Do not handle all the conceivable problems of the company.

Consult the FAQ.