EER to Relational Mapping using MySQL Workbench

In today's lab, you will practice using dbdiagram to create a Relational model from an EER model. Work alone or in a pair.

The instructions below follow the mapping categories documented in the slides.

Use the Conference Review EER model below to create a relational model (data requirements). The model is in the same style you were required to use in GP2.

Like all design processes, the process of creating a relational model is cyclical, with refinements at each step. However, transforming an EER model into a relational model follows well-defined rules, with just a few decisions needed.

Getting Started

From the front page of the dbdiagram.io website, you can start a new diagram by clicking the Create your diagram button of the Go To App button. Both choices will open the tool with a new sample diagram (users and posts). You should sign in using the Sign in button in the top right corner. Using the menu under the dbdiagram icon in the top left corner, open a New Diagram.

You may start a new diagram by writing DBML in the left hand side of the screen or by importing a model from an existing database. This activity will walk you through creating parts of the Conference Review System (CRS) relational model by writing DBML.

Category 6: Specialization Hierarchies

Although specialization hierarchies are covered last (category 6) in the slides, the CRS model requires these decisions to be made first, because a significant number of the entities in the model are involved in a specialization hierarchy (person, author, reviewer). The are several options for handling specialization hierarchies in a relational model:

• Each sub-class can become a table with no table for the super-class.
  • Each sub-class table will have all attributes of the super-class.
  • A poor choice for overlapping specialization hierarchies.
• Each sub-class can become a table and the super-class can also become a table.
  • The super-class table will have a 1-to-1 identifying relationship with each sub-class table.
• All classes in the hierarchy can be combined into one table, including all attributes, using boolean or type columns to differentiate between the subclasses.
  • Only if the number of specific sub-class attributes is very small.
• A combination of these options.

As an overlapping type hierarchy, the possible choices for the CRS hierarchy are to combine all 3 entities into one table, have just a PERSON and REVIEWER table (because AUTHOR has no specific attributes), or give each entity its own table. For this activity, we will make the second choice: PERSON and REVIEWER, where REVIEWER has a 1-to-1 identifying relationship with PERSON. As you type the DBML, the tables will appear.

Category 1: Regular and Weak Entities

Regular and weak entities become tables in a relational model.

In order to demonstrate the rest of the categories, we will map 3 or the 4 remaining entities to tables: AFFILIATION, PAPER and REVIEW. Create the tables using DBML, assigning reasonable data types.

Attributes of a regular entity

• The key of the entity becomes the primary key of the table.
• Simple attributes become columns/fields of the table.
• Each of a composite attribute's parts becomes its own column.
• A decision is made about whether or not to make a derived attribute a column of the table.
• Multi-valued attributes are handled in a later step.

In the AFFILIATION table, we created a new PK, org_id, rather than use name.

Attributes of a weak entity

• Attributes of a weak entity follow the same rules, except
• The primary key of a weak entity is handled in a later step.

Category 2: Non-Identifying Relationships

Non-identifying relationships are relationships that do not involve weak entities. Relationship attributes must be created as columns in the appropriate table after the connection is made.

Using dbdiagram, you must create the FKs as required before creating the reference.

1-to-N relationships

• The primary key (PK) from the table on the 1-side of the relationship becomes a foreign key (FK) in the table on the N-side
• Any attributes on the relationship move into the table on the N-side

For the 1-N non-identifying relationship between PERSON and PAPER (contact_author_of), create the FK in the PAPER table, and then create the reference.

1-to-1 relationships

• The PK from either table can become a FK in the other table.
  • Make the decision based on the context.
• Any attributes on the relationship move into the table with the FK.

Category 3: N-to-M relationships

• Create a new table and make it the N-side of 1-N relationships from each of the original tables.
• The PK’s from both sides become FK’s in the new table, then the combination of the FK’s become the PK of the new table.
• Any attributes on the relationship go into the new table.

Create the new joining table between PERSON and AFFILIATION. Make the 2 PK attributes and the 2 relationship attributes. Then create the 2 reference lines.

Category 4 Identifying Relationships

Identifying relationships involve a weak entity or a sub-class.

1-1 identifying relationships

• The PK from identifying side becomes a FK and the PK of the weak (or sub-class) table.
• There is never a partial key in a 1-1 identifying relationship.
• Any attributes on the relationship move into the table with the FK.
• Another option is to merge the 2 tables.

Create the email attribute in the REVIEWER table and establish the 1-to-1 relationship according to the rules.

1-N identifying relationships

• The PK from the table on the 1-side of the relationship becomes a FK in the table on the N-side (the weak entity side).
• Any attributes on the relationship move into the table on the N-side.
• In the weak-side (N-side) table, the combination of the new FK and the partial key from the weak entity become the primary key of the table.

REVIEW is a weak entity identified by PAPER and REVIEWER. It does not have a partial key. You must create the two PK attributes and then add the two 1-to-N references. As can be seen in the image below, it is not always easy to make the diagram readable.

Category 5: Multi-valued Attributes

• A multi-valued attribute requires a new table which contains:
  • The PK of the table corresponding to the entity with the multi-valued attribute.
  • A column for the multi-valued attribute.
  • The combination of all the columns becomes the PK of the new table.

Create the new table for a reviewer's topics of expertise, where the combination of reviewer and topic must be unique.

Complete the model

Add the remaining entity, M-to-N relationship, 1-to-N relationship and multi-valued attribute to the model. Submit a file containing the DBML that creates your model to Canvas. Put the name of the person you worked with in a comment (DBML uses C-language style comments).