Skip to content

HW3: Relational Mapping

Due: Monday, September 23

MySQL Workbench dbdiagram.io
Icons for MySQL Workbench and dbdiagram.io

Objectives

  • Create a relational model given an EER model.
  • Generate an SQL database creation script for the model.
  • Use dbdiagram.io to create a DBML version of the relational model.

EER Model

The following EER model represents a simple Veterinary Office system:

Vet Office EER Diagram

Instructions

Step 1: Create a relational model in Workbench and take a screenshot.

Use the rules explained in the slides and the lab to convert the EER model to a relational model in MySQL Workbench. Take advantage of the fact that Workbench can do almost all of this work automatically.

  • Some relationships cannot be made until one or both sides of the relationship have defined primary keys.
  • Be sure to check that you have created the relationship in the right order to get the FK(s) on the correct side of the relationship.

Use the names and attributes given in the EER model. Select appropriate data types. Do not add or remove attributes except in the case of the inheritance relationships – you may want/need to change the attributes depending on the table structure you choose to represent the 2 specialization hierarchies.

Step 2: Use forward engineering to generate a database creation SQL script.

Use forward engineering to create an SQL script with all the table creation statements, as well as the primary and foreign key constraints. Test the script by building the database in your personal schema on the server to be sure it works.

You will need to change the schema name from mydb to your JMU username in order to run the script. (The schema mydb does not exist, and you don't have permission to create new schemas.)

Step 3: Import into DBDiagram.io and export as dbml and pdf.

Go to dbdiagram.io and click "Create your diagram" or open the app. Use the menu in the upper left (under the logo) to create a new diagram. Use import from MySQL, and upload your SQL script. This should automatically create a relational diagram and the associated DBML (database markup language). Adjust the diagram and/or DBML to make the diagram readable. Check that any comments you added in MySQL Workbench should be included in the DBML as notes.

Submission

Upload the following three files to Gradescope:

  1. hw3.png – screenshot of your model in MySQL Workbench
  2. hw3.dbml – the DBML code generated by dbdiagram.io
  3. hw3.pdf – final diagram exported from dbdiagram.io

Please take the time to make both diagrams (png and pdf) easy to read. To the extent possible, arrange the tables in the same order as in the original EER diagram.