EER to Relational Mapping using MySQL Workbench

In today's lab, you will practice using MySQL Workbench to create a Relational model from an EER model.

The instructions below follow the five 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 Chen style, except that the relationship connectors use the Crow's foot style.

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 Workbench home screen, open the Model panel, and click the plus sign to create a new model. This will open a MySQL Model tab. In the EER Diagrams pane on that tab, choose Add Diagram to open an EER Diagram tab.

Category 1: Regular and Weak Entities

Regular and weak entities become tables in a relational model.

Use the New Table tool to create a table for each of the regular entities in your model. Do not include any entities that are part of a specialization hierarchy (no subclass or superclass entities).

Clicking on a newly-created table opens the Table pane at the bottom of the screen, where the table's name and other information can be entered.

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.

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.

Adding attributes to a table in Workbench

The Table pane has a Columns tab, where columns, their data types, features, and default values can be defined. Notice the checkboxes for column features like primary key, not null, unique, and auto-increment. Notice how the primary key column is denoted in the diagram.

Category 2: Non-Identifying Relationships

Non-identifying relationships are relationships that do not involve weak entities. They are depicted with dashes lines in Workbench. Workbench implements most of the required actions automatically when you connect tables using the relationship connectors. Relationship attributes must be created as columns in the appropriate table after the connection is made.

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

Click the connector tool, then click the table on the N-side first, followed by the table on the 1-side. Workbench draws the connector, and it automatically creates the FK in the table on the N-side. Notice how the foreign key column is denoted in the diagram.

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.

These relationships work exactly the same way as 1-N relationships in Workbench. Choose the table which will get the foreign key by clicking it first when placing the connector.

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.

When you place an N-M connector in Workbench, the new table, with its required foreign and primary keys, is created automatically for you. You can then give it a new name and create any required attributes.

Note that the lines drawn by Workbench for an M-N relationship are solid, because, once the new table is created, the resulting two relationships are identifying (not non-identifying).

Relationship settings

To edit the settings of a relationship, double-click the connector to open the Relationship' pane at the bottom of the screen. This pane has 2 tabs which are available from the bottom of the pane*. In these tabs you can change the name of the relationship and set the cardinality and participation constraints.

Category 3: Identifying Relationships

Identifying relationships involve a weak entity. They are shown with solid lines in Workbench.

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.

When you place a 1-N identifying relationship in Workbench, the FK created in the weak-side table is automatically made the PK as well. You must create the column(s) corresponding to the weak entity's partial key by hand, and make them part of the PK. In the screenshot below, the identifying connector from DOCTOR to APPOINTMENT created the column DOCTOR_DoctorID automatically. The partial key column has not yet been created.

Note how Workbench denotes a column that is both a PK and FK in the diagram (the notation differs slightly across versions). In the screenshot below, the key icon next to the PK-FK column DOCTOR_DoctorID in APPOINTMENT is red, while the key next to PK ClinicName in CLINIC is gold. In APPOINTMENT, the FK from CLINIC has a solid gold diamond because it is a not-null FK, while the FK from PATIENT is an empty gold diamond.

1-1 identifying relationships

• The PK from identifying side becomes a FK and the PK of the weak-side table.
• There is no partial key in a 1-1 identifying relationship – this relationship is equivalent to a superclass-subclass relationship.
• Any attributes on the relationship move into the table with the FK.
• Another option is to merge the 2 tables.

The 1-1 identifying relationship connector in Workbench does all the necessary steps automatically.

Category 4: 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.

In Workbench, create the new table with no columns and then connect it to the table (which needs this multi-valued attribute) with a 1-N identifying connector. This will create the PK-FK in the new table automatically. Then add the column for this attribute into the new table by hand, and make it part of the PK.

Category 5: Specialization Hierarchies

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.