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. Your team manager 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 the two companies will build two separate platforms to manage most of their business operations (selling, accounts payable, receiving, etc.). Later they will connect them to automate their B2B commerce. We will advise on data modeling and integration. Your job will be to model the Database for one of their business function. Make sure you are up to speed with MySQL Workbench."

"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 concept. 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

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: must use MySqlWorkbench
Format: a printout in a single sheet of paper to be handed in class
The ERD must include:

• Tables/entities
• Primary keys (with the 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 with the "crow's foot" notation

Do not show indexes and data types.
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.