Building Integrated Databases
Team Graded Pledged Assignment
Goals
Learn about the design of integrated databases.
Learn a professional-level DB tool.
Prep Work
For this exercise you must use MySqlWorkBench. This is a video on how to use the tool. The last few minutes describe the difference between identifying and non-identifying relationship, which often confuse beginners and expert alike.
Here I provide more tips for accessing MySqlWorkBench and using it effectively.
Task
This exercise is done in teams of three.
Look at the columns in the picture. Students numbered 1, 4 and 5 work for Digisonos and will do the exercise together.
Students numbered 2, 3 and 6 work for UBC and will do the exercise together.
Begin by producing an integrated ER diagram for the company you work for (either DigiSonos or UBC). 'Integrated' means that it
eliminates duplication of data inside each company.
Only model your company, not like in the picture, where you see both companies. The picture explains at a high level the
architecture of this B2B data exchange.
1) Model all three functions of your assigned company as a single DB using MySQLWorkbench (if you are a team of two, model only
the functions you have)
2) Print out the ERD
3) Use a highlighter to highlight which attributes in each table need to be communicated to the other company during the various
steps of the process. Careful! Not everything needs to be communicated to the other company.
4) Use a highlighter of a different color to highlight data that you need to receive from the other company.
5) Show that the data goes out or comes in with a arrow in the same highlight color.
5) Number the arrows so that a reader can see what happens first and what happens later. What is the first set of data that you receive from or send to the other company? Give it a (1). What is the next set of data? That is a (2)... If data in different tables moves at the same time (e.g., in 'order' and 'order line item'), it is a single set, so give it the same number.
The picture below shows a fragment of the exercise for Digisonos (left) and UBC (right) as an example. You need to do only
your company, not both as in the picture. Do not assume that the attributes or keys in the picture are correct or complete.
This is just an example to show how to highlight the attributes and draw the arrows to/from the other company, which you do not
need to model.
If data move internally to your company, do not highlight it, nor draw arrows.
Deliverables
Hand-in one paper copy of your work in class. One copy per team of three.
The ER diagram must be produced with the tool covered in class (MySQLWorkBench). Here I provide a few tips for accessing it and using it effectively.
It is fine if the ERD is larger the one printed page. Print out all the pages and tape them together.
Make sure it is readable without a magnifying lens.
Use the notations demonstrated in class to provide:
- Tables
- Primary keys
- Foreign keys, shown both as relationships (lines) and as attributes (with "FK" or some other symbol)
- Attributes
- Relationships, optionally labelled with a verb if that clarifies their meaning
- Upper and lower cardinalities
- Numbered lines showing how highlighted data sets moves from one company to the other.
Make backup copies of all deliverables. Do not forget to write down your team name/number, and the names of all your team members.
Criteria
- The produced ERD must follow the notation demonstrated in in class and portray accurately the business processes described in the various functions write-ups (e.g., "shipping") as one integrated business.This means that there should not be duplicated data (e.g.,only one"Order table")
- Avoid too many crossing lines.
- Avoid using fonts that, when printed, are too small to read.
- To keep the model simple, do not show data types and indexes. Some tools will show them by default. Turn that option off as shown in the intro video.
go back