How to draw a relational data model with MySQL workbench on Windows.

This post is a tutorial on how to get started with MySQL Workbench which is a GUI (Graphical User Interface) for MySQL. It is a visual tool for database architects, developers, and database administrators . MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. This tutorial is done using the community version of Workbench for windows. Workbench is available for mac OS and Linux as well.

Prerequisite

To be able to follow this tutorial smoothly, you are going to need the following software;

• MySQL workbench. You can checkout my step-by-step tutorial on how to download and install Workbench for windows here. The version at the time of this writing is version 8.0.22

• We need to have XAMPP( a free and open-source cross-platform web server solution stack package developed by Apache Friends) downloaded and installed too. Checkout how to install XAMPP here.

After all is said and done, we are now going to see how to use MySQL Workbench. This will be done in three sections.

1. Firstly how to create a connection to a server (local or remote server). Our focus will be creating a connection to a local server though.

2. We will look at how to create a simple data model with Workbench.

3. To top it all up, we are going to end with learning how to add data to a database so buckle up and lets get started.

• ### Creating a connection to the server

1. The first step is launching the XAMPP control panel and starting the MySQL server. To do this click the start button pointed to by the arrow in the picture below.
   
   Clicking on that button starts running MySQL server.

2. Now launch MySQL Workbench. This is how the homepage looks on windows.
   
   To create a new connection to the MySQL server click on the small plus button pointed to be the blue arrow in the above image. This opens the Set New Connection Dialog Box.
   
   In the dialog box that appears we have to fill in some input fields. All we are going to add is the connection name and just leave the other fields as they are and then click OK to create the connection my_tutorial. And there you have you just created a connection to a MySQL server.

• ### Creating a simple data model.

Now it is time for us to create a simple data model. We are going to create a data model for a small school database. To do this open the my_tutorial connection we created a minute ago. This opens the following tab.

In the tab that opens, click on file in the menu bar and select New Model or use the shortcut Ctrl + N. This takes you to MySQL model tab.

In the tab that appears double-click on the add diagram icon pointed to by an arrow in the image above. This opens up a new tab in which you can create a data model. We are going to create a relational model for a school database with these entities:

N/B: For each entity, we have its attributes in bracket.

• Student Entity(SID, Name, Email, Department)

• Instructor Entity(SSN, Name, Salary)

• Course Entity(Course ID, Course Name, Course Description)

We are now going to add three tables to our data model, one for each entity.

To add a new table to our diagram, click on the icon pointed to by the blue arrow on the vertical toolbar and then click on blank diagram area to add the table. To edit the table, double-click on the table you want to edit. This opens a tab like the one in the image below.

Here you can edit the name of table, add columns, specify the datatypes for each column, choose a primary key and also add some constraints. Once you done editing your table, you can close the editing window. Now try creating the three tables for this tutorial, you can continue with the tutorial once you done adding all your tables.

Now lets go on to create some relationships between our entities. Looking at our tables, it is clear that there is a relationship between student and course and between instructor and course. We going to add these relationships to our data model. This how it is done.

In the picture above, the area that is highlighted are the tools that we need to create relationships. If you look closely you'd see icons with different relationship cardinalities like 1:1, 1:n, m:n and so on. The relationship between the course and student entities is a one to many relationship since many students can offer one course.

To represent this relationship on our data model click on the fourth icon(the one with 1:n) in the highlighted area in the image above and then click on the course table followed by the student table to create a relationship between them. Do the same thing for the instructor-course relationship but clicking on the third icon instead since the relationship between instructor and course is a one-to-one relationship.

Woahh and there you have it. I'm glad you saw this one through 👏🏾👏🏾. Please share like and share this post if you found it helpful.