Introduction

This article is about designing Database schema using MySQL Workbench. MySQL Workbench helps us to create visual data model which can be translated into a physical database on the MySQL Database Server. For demonstration purpose we’ll be designing an Contact Manager Application Database. This DB has four tables namely ContactPerson, Address, EmailAddress and PhoneNumber.

I’ll not be discussing various DB issues like Normalization, Entity-Relationship(ER)/Enhanced Entity-Relationship(EER) models or SQL programming. Please refer to the various fine materials available. Below are few recommended textbooks:

  1. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

  2. Beginning Database Design: From Novice to Professional

  3. SQL in 10 Minutes, Sams Teach Yourself

  4. Learning SQL

  5. Head First SQL: Your Brain on SQL — A Learner’s Guide

Download the latest version of MySQL Database from https://www.mysql.com/downloads/. Make sure that you have downloaded the community edition of MySQL.

Steps Involved In Designing Database Schema

I presume that you already have a working copy of MySQL installed in your system.

Step 1:

Invoke MySQL workbench. That should look like the below screen. The top Window Pane displays MySQL Connections. There can be any number of MySQL instances. Here we have one instance of MySQL connection with root user. The bottom pane shows the Models that you have created. The right window pane shows shortcuts to various MySQL tools.

We’ll create Visual Data Models and then it’ll be converted to the actual physical database schema on the MySQL database server. Remember, creation of Data Models doesn’t mean that you have already created actual physical database schema.

In the Models window pane click on the + sign.

Image_1

Step 2:

You will see a screen as shown below. By default the DB schema is named as mydb. Let’s change this. Double click on mydb in the Physical Schemas window pane.

Image_2

In the bottom window pane change the name from mydb to ContactManagerApplication.

Image_3

In the Refactor Schema windows click on OK button.

Image_4

Step 3:

Double click on Add Table.

Image_5

Step 4:

You will see the below screen.

Image_6

Step 5:

Change the table name to ContactPerson. Also, fill the Column Name or attributes with various values with appropriate data types as shown in the below screen.

Image_7

Step 6:

Add another table called Address and fill the Column Name with various values with appropriate data types as shown in the below screen.

Image_8

Step 7:

Add another table called EmailAddress and fill the Column Name with various values with appropriate data types as shown in the below screen.

Image_9

Step 8:

Add another table called PhoneNumber and fill the Column Name with various values with appropriate data types as shown in the below screen.

Image_10

Step 9:

Click on Save button in the Menu bar. Save the data model as ContactManagerApplication in some folder. At this stage we have created all the tables for our schema and have saved the model. We have still not converted this model to actual physical database on MySQL database server. Lets’ migrate these models to connect to MySQL Database.

Image_11

Step 10:

Go to the menu bar and click on Database and select Forward Engineer.

Image_12

In the Forward Engineer to Database window, various parameters have been set. This depends on your configuration while installing MySQL database. I’m using the Local Instance of MySQL and root user. Leave the Default Schema blank. This forward engineering generates all the SQL code to create a physical database on the target server.

Image_13

Click on Next button.

Image_14

Give the Password for the root User and click on OK button.

Image_15

Click on Next button.

Image_16

In the window you can see the SQL script that has been generated by the MySQL workbench. You can review the script and save the script as a file and click on Next button.

Image_17

Again, give the Password for the root User and click on OK.

Image_18

Click on Close button.

Image_19

Step 11:

From the previous step(Forward Engineering) we have generated the SQL script for our models. We’ll use Reverse Engineering to connect the generated SQL script to the SQL Server database thereby creating the actual physical database on the SQL Server. Go to Database in the menu and click on Reverse Engineer.

Image_20

Again, you see the default parameters that are set and click on Next button.

Image_21

Give the Password for root User and click on OK button.

Image_22

Click on Next button.

Image_23

Now at this stage you can see the available list of various Database schemas. Select the contactmanagerapplication checkbox and click on Next button.

Image_24

Give Password for the root User and click on OK button.

Image_25

Click on Next button.

Image_26

Click on Execute button.

Image_27

Click on Next button.

Image_28

Click on Finish button.

Image_29

Step 12:

An EER diagram will be automatically generated by the MySQL workbench. It shows the data model which we have created. Let’s establish relation between various tables. Click on the highlighted button (which is an 1 to many relation) in the column bar.

Image_30

First click on the name on whose side the many relationship should come and then click on the name on whose side 1 relationship should come. For e.g., to establish 1 to many relationship between address table and contactperson table, first click on ContactPersonID in the address table and then click on ContactPersonID in the contactperson table.

Repeat the above process between other tables to establish a relationship as shown below.

Image_31

Step 13:

In the previous step, we have established the relationship between various tables as seen in the EER diagram. But this relationship is not reflected in the actual physical database schema which we have created. To do so, we have to synchronize between the actual physical database schema and the data model. Go to menu and click on Database and select Synchronize Model. When ever there is some changes done to the data model as seen in the EER diagram it won’t get reflected automatically in the Database Schema. We have to synchronize the changes done to the data model as displayed in EER diagram with the Database Schema to see the changes.

Image_32

Click on Next button.

Image_33

Click on Next button.

Image_34

Enter the Password for root User. Click on OK button.

Image_35

Click on Next button.

Image_36

Select the checkbox and click on Next button.

Image_37

Click on Next button.

Image_38

The tool compares all aspects of the current data model with the actual physical MySQL Database schema and lists out the difference between the current data model and the actual physical Database schema. Click on Next button.

Image_39

You’ll be able to see a sequence of SQL scripts. Click on execute button.

Image_40

Give the Password for root User and click on OK button.

Click on close button.

Image_41

Step 14:

To see whether the data model has been migrated to the MySQL database, go to menu and click on Database and select Connect to Database.

Image_42

Click on OK button.

Image_43

On the left pane you can see the contactmanagerapplication database schema and expand it see the list of tables.

Image_44

Conclusion

I have shown the various steps involved in creating an Database Schema using MySQL workbench GUI without any coding.

You can find MySQL Workbench file and SQL code for ContactManagerApplication Schema @

https://github.com/gowrishankarnath/MySQL_Workbench


Comments

comments powered by Disqus