Designing a database with MySQL using MySQL Workbench

Afroshok

Follow

Oct 29, 2018 · 7 min read

If you have just stepped in, please have a look at the first two parts in this series, Designing a database with MySQL using the Command Line Interface, Part 1 and Part 2. We continue to work with the database we developed in these two articles. As in the previous articles, pick any name for your tables in the database if you find these a bit too abstract.

Now that we are comfortable with the building of a database in MySQL directly, let us look at designing the databases through the MySQL Workbench 6.3.x. For information on installation procedures for different OSs, look at the ‘workbench-en.pdf’ manual available from the MySQL website. It also has a tutorial on how to build a database in the Workbench.

In this part, we will work with the database “Waks_Noma” that we built previously. We will import the database into the MySQL Workbench (Workbench), understand the visual representation of our waks_noma database, add a table into the EER(Entity-Entity Relationship), export it into the existing live database and make queries from Workbench.

Workbench

When one opens Workbench, the initial screen looks like so:

To work on the waks_noma database, we will open our MySQL instance on our local machine.

Click on the Local instance 3306 button and you may be asked enter the name and password.

NOTE: When working in a multi-user environment and online to a live remote server, the name and password security issues should be taken very seriously.

We get:

The schemas of the databases present on the local computer appear Schemas tab at the bottom left. We choose our waks_noma and collapse the Management tab.

Adding the Designers’ Table : waseenoma

With waks_mona selected, click on Add Table:

Add the new name add the following columns:

NOTE: Set the PK (Primary Key) to AI( auto-increment), and make sure all tthe colums are NN (NOT_NULL). We will click on Apply and we see something familiar:

Click on Apply to run the script, and get our new table, waseenoma:

Let us go to our command line and confirm that it works:

mysql> show tables;+---------------------+| Tables_in_waks_noma |+---------------------+| majobo              || majobskils          || majobtek            || maklayo             || maklayojobo         || maskils             || teknoma             || waseenoma           |+---------------------+

Success.

Adding Designers’ names into ‘waseenoma’

Let us go back to our Schemas tab, select waseenoma table. Click on the icon on the right :

We insert our data in the editing tab that is named waseenoma 1 and click on the Apply changes to data :

Click on Apply:

Click on the Apply again.

Now to check that the data has really been applied to the waseenoma, at the command line we get:

mysql> select * from waseenoma;+----------+--------------+-------------------+| mnoma_id | mnoma_name   | mnoma_desc        |+----------+--------------+-------------------+|        1 | Mchora       | Graphics Chief    ||        2 | Blossom      | Layout Sweetener  ||        3 | Helium Ninja | Content Developer ||        4 | Msafara      | Video Converger   |+----------+--------------+-------------------+4 rows in set (0.00 sec)

We are certain that the Workbench works and we can go onto work with EER diagrams.

In the Home tab and click on the “ > ” icon:

Click on the “Create EER Model from Database”.

Unless there is a good reason, like you have many users on the machine, leave these settings as they are. Continue to connect to the DBMS:

Click on continue:

We choose our database waks_noma:

We execute:

Once we have completed the steps, we open the EER Diagram and arrange our ERDs:

Joining majobo and waseenoma: majobnoma Table

Above the waseenoma table, go an pick “Place a New Table” and insert a table majobnoma. Edit majobnoma and in the Foreign Keys tab, create the FKs to majobo.job_id and wanoma.mnoma_id.

We notice the “Crow Feet” connection that automatically appear between the majobo table and the wanoma table. You will notice that the existing “joining tables” that we made directly in the command line don’t automatically relate to their respective table FKs.

We will correct this by changing them from the columns that are in the tables and using the Foreign Keys tab update them. Make sure that you connect the column in majobnoma and the referenced column both majobo and waseenoma.

Once we finish the connecting the joining tables; majobskils, makalyojobo and majobtek, the new EER looks like so:

Exporting the EER to waks_noma

Finally we need to implement this EER onto the actual database. When we query the database, we get:

mysql> show tables;+---------------------+| Tables_in_waks_noma |+---------------------+| majobo              || majobskils          || majobtek            || maklayo             || maklayojobo         || maskils             || teknoma             || waseenoma           |+---------------------+8 rows in set (0.07 sec)

As we can see the database does not know about the majobnoma table. In the Workbench, go to the Home tab and get the to the local instance. When you look at the tables, it is clear the majobnoma table is missing.

So we will set up Workbench to create the SQL statements we need to sync the EER diagram with the live database schema. Go to the File > Export > Forward Engineer SQL CREATE Script.

Add a name for the SQL script and press continue. The instructions for the options in this tab can be found here — Forward Engineering Using an SQL Script.

The “Export …” will be highlighted:

We export the table objects and save. Make sure you are on the local instance tab. We then open the script to run it:

The following window pops up:

And if you have done everything tight:

Opening the Workbench local instance, we can see the majobnoma is in the database now:

And checking through the CLI:

mysql> show tables;+---------------------+| Tables_in_waks_noma |+---------------------+| majobnoma           || majobo              || majobskils          || majobtek            || maklayo             || maklayojobo         || maskils             || teknoma             || waseenoma           |+---------------------+9 rows in set (0.00 sec)

If you have reached this far, be rest assured you are on your way to being a on your way to being a MySQL ninja!

That concludes our series on designing relational databases using MySQL using the CLI and the Workbench desktop application. If you have enjoyed the series, please give us some feedback to encourage us to extend development of databases with MySQL or PostgreSQL.

Thanks for reading.

• Sql
• MySQL
• Database
• Design

1

WRITTEN BY

Afroshok

Follow

Shok and Oh!

Follow

Afroshok is a digital production and consulting, web design and development boutique in Nairobi, Kenya. We specialise in creating cutting edge, ground breaking, brand driven immersive projects. We work through Progressive Web Applications to deliver content where it matters.