Toad World Blog

Using Toad Data Modeler with Toad Edge for MySQL 5.x - I

Dec 17, 2018 12:00:00 PM by Deepak Vohra

Toad Data Modeler and Toad Edge are two tools that complement each other’s functionality very well. Toad Data Modeler provides all the features needed to model data and generate DDL from a model but does not provide support for DBA features such as connecting to a database to explore database objects, manage users, and run SQL DDL and DML statements. Toad Edge is the tool designed for a DBA; using which, database schemas, objects, and users may be managed and a SQL Worksheet used to run SQL statements. In two articles we shall discuss how Toad Data Modeler could be used with Toad Edge to model data and apply the model to MySQL database. The two articles have the following sections.

Setting the Environment

Creating a Model

Adding Entities

Saving Model

Generating DDL

Creating Connection in Toad Edge

Running DDL to Create Tables

Running DML to Add Table Data

Querying Table

Dropping Table

Setting the Environment

Download and install the following software on Windows OS.

Creating a Model

Toad Data Modeler is used to create logical and physical data models, using entities and database structures to model a database.  In this section we shall create a top-level physical data model and use it to develop a logical data model for MySQL database table/s.  To create a model, select File>New>Model in Toad Data Modeler as shown in Figure 1.

Figure 1. Creating a new model in Toad Data Modeler

Figure 1. File>New>Model

In New Model window the Physical Data Model tab is selected by default; from which, select MySQL 5.7, which is the highest MySQL database version supported by Toad Edge, as shown in Figure 2. The Model Name gets set to the Data Model selected.

Figure 2. Selecting MySQL 5.7 from the Physical Data Model choices

Figure 2. New Model>MySQL 5.7

Click on OK as shown in Figure 3 to create a model.

Figure 3. Clicking on OK to create the new data model

Figure 3. New Model>OK

A new Physical Data Model called MySQL 5.7 gets created and added to the Physical Model Explorer, as shown in Figure 4.

Figure 4. Physical Model Explorer shows the newly created data model

Figure 4. Physical Data Model MySQL 5.7 created

Adding Entities

In this section we shall create entities to model MySQL database tables. We shall be creating a logical data model for two MySQL database tables Log and LogEntry for WebLogic server log data. The Log table is to have columns logid (primary key), category, type and servername. The LogEntry table is to have columns logid, timestamp, code and msg. The logid column in LogEntry  is both the primary and foreign key.

Entities are logical data structures and define attributes and relationships that are used to model database tables and relationships between the tables. To add an entity right-click on the Entities in the MySQL 5.7 physical data model and select Add Entity as shown in Figure 5.

Figure 5. Selecting Add Entity

Figure 5. Entities>Add Entity

To add a new entity, alternatively select the MySQL 5.7 model and select Objects>Add New>Entity as shown in Figure 6.

Figure 6. Adding a new entity from the Objects tab

Figure 6. Objects>Add New>Entity

A new cursor gets generated when positioned over the All Items window pane, as shown in Figure 7.

Figure 7. A new cursor in All Items

Figure 7. A new cursor in All Items

Click with cursor over the All Items and an Entity diagram gets created, as shown in Figure 8. A default Entity name is added starting with Entity1 for the first Entity added, and incrementing with name format Entityn with each subsequent Entity n added.

Figure 8. Entity1 Added

Figure 8. Entity1 Added

Next, we shall modify the entity to add attributes and relationships to develop a logical data model for database tables. Right-click in the Entity1 header and select Edit… as shown in Figure 9.

Figure 9.  Right-click in the Entity1 header and select Edit…

Figure 9. Entity1>Edit…

An Entity Properties window gets displayed. Add/modify Caption to set it to Log, which is the first entity that we shall model, as shown in Figure 10.

Figure 10. Entity Properties window

Figure 10. Entity Properties

To add attributes, select the Attributes tab as shown in Figure 11.

Figure 11. Attributes tab

Figure 11. Attributes

To add an attribute click on Add as shown in Figure 12.

Figure 12. Clicking the Add button on the Attributes window

Figure 12. Attributes>Add

A dummy attribute gets added with default values, as shown in Figure 13.

Figure 13. Dummy Attribute with Default Values

Figure 13. Dummy Attribute with Default Values

With the attribute selected click on Edit as shown in Figure 14.

Figure 14. Clicking the Edit button to edit a dummy attribute

Figure 14. Edit

A Confirm dialog prompts that changes have not yet been confirmed, as shown in Figure 15. Click on Yes.

Figure 15. Clicking on the Yes button to confirm changes to the attribute

Figure 15. Confirm

An Attributes Properties - Log window gets displayed, as shown in Figure 16. Default Caption and Name field values are provided. Add a caption to the Caption field as logid. By default the Name field gets set to the same value as Caption when the Caption field value is modified from the default value.  If the Name field is modified from the default value the Caption field value does not get modified as well. Caption and Name could be made different but we have used the same value. Select Data Type for the logid attribute as Integer as shown in Figure 16.

Figure 16. Setting Attribute Name and Data Type in the Attributed Properties Log window

Figure 16. Setting Attribute Name and Data Type

The Primary Key checkbox must be checked for the logid attribute as it is the primary key for the Log table, as shown in Figure 17.

Figure 17. Setting Primary Key

Figure 17. Setting Primary Key

Other database specific properties may also be set. MySQL supports auto-increment of primary key; therefore, select Autoincrement as shown in Figure 18.

Figure 18. Selecting Autoincrement

Figure 18. Selecting Autoincrement

To add the logid attribute and add another attribute subsequently, click on OK+Add as shown in Figure 19.

Figure 19. Clicking on OK+Add

Figure 19. Ok+Add

A new Attribute Properties - Log window gets displayed; in which, add attribute name as category and select Data Type as Varchar(x) as shown in Figure 20.

Figure 20. Adding another Attribute

Figure 20. Adding another Attribute

As category is not a primary key attribute, the Primary Key and Not Null checkboxes must not be selected, as shown in Figure 21. And the Database Specific properties are listed based on other properties. As category is not a primary key and Primary Key is not selected, the Autoincrement property is not available to be selected. Click on OK+Add.

Figure 21 Adding a second attribute

Figure 21 Adding a second attribute

Similarly, add the other entity attributes type and servername. After the last attribute is added click on OK instead of OK+Add, as shown in Figure 22.

Figure 22. Adding the last attribute, servername

Figure 22. Adding last attribute servername

In the Entity Properties window all the attributes added are listed when Attributes tab is selected, as shown in Figure 23. Click on OK.

Figure 23. Clicking OK in the Entity Properties window

Figure 23. Entity Properties>OK

The Entity gets modified in the model, as shown in Figure 24.  The entity diagram also gets modified.

Figure 24. Entity Log

Figure 24. Entity Log

Next, we shall add another entity called LogEntry  that has a foreign key mapped to the primary key of the Log entity. The Log entity has a 1:1 relationship with the LogEntry entity but the cardinality of the relationship could be different and is defined as M:N in general. To add the second entity select Objects>Add New>M:N Relationship as shown in Figure 25.

Figure 25. Adding the second entity

Figure 25. Objects>Add New>M:N Relationship

A '+' shaped cursor gets generated, as shown in Figure 26. Position the cursor over the Log entity and double-click.

Figure 26. Position the plus-shape cursor over the Log entity and double click

Figure 26. Adding M:N Relationship

A second entity gets generated with two PFK (Primary Foreign Key) keys, as shown in Figure 27.

Figure 27. A new entity has been added

Figure 27. New Entity added

The new entity added may not be well laid out. To reposition the new entity, click in the entity header and drag the entity to a new position as shown in Figure 28.

Figure 28. Applying Entity Layout

Figure 28. Applying Entity Layout

We need to modify the default entity name and attributes. Double-click in the entity header as shown in Figure 29.

Figure 29. Double-clicking in Entity Header

Figure 29. Double-clicking in Entity Header

The Entity Properties window gets displayed, as shown in Figure 30.  The new entity has two PFK attributes called logid mapped to the primary key of the Log entity. As the entity relationship we need to define is 1:1 we need to delete one of the PFK attributes. Select one of the logid attributes and click on Delete as shown in Figure 30.

Figure 30. Deleting a PFK Attribute

Figure 30. Entity Properties>Deleting a PFK Attribute

A Confirm dialog prompts whether the foreign key attribute should be deleted, as shown in Figure 31. Click on Yes.

Figure 31. Confirm Dialog to delete an Attribute

Figure 31. Confirm Dialog to delete an Attribute

One of the logid attribute gets deleted, as shown in Figure 32. Set the attribute name and caption to LogEntry. If the other logid attribute needs to be edited select the attribute and click on Edit.

Figure 32. Clicking on Edit to edit the remaining logid

Figure 32. logid>Edit

In Attribute Properties the Foreign Keys tab lists the relationship to the logid attribute of the Log entity, as shown in Figure 33.

Figure 33. the Foreign Keys tab lists the relationship to the logid attribute of the Log entity

Figure 33. Foreign Keys

The General tab displays the general properties, as shown in Figure 34. We don’t need to modify any of the attribute properties for logid. Click on OK.

Figure 34. Attribute General Properties

Figure 34. Attribute General Properties

We do need to add attributes timestamp, code and msg to the LogEntry entity. Click on Add in Entity Properties as shown in Figure 35.

Figure 35. Click on Add in Entity Properties to add more attributes

Figure 35. Entity Properties>Add

A new attribute gets added with default values, as shown in Figure 36. Using the same procedure as discussed for the Log entity add the timestamp, code and msg attributes. To edit the default new attribute you would need to click on Edit.

Figure 36. New Attribute with Default Values

Figure 36. New Attribute with Default Values

Set attribute name to timestamp and select Data Type as Varchar(x) as shown in Figure 37. Modify the default length of “20” to “55” in the Length field.

Figure 37. Adding the Attribute timestamp

Figure 37. Adding Attribute timestamp

Click on OK+Add to add the other attributes code and msg. Set the msg attribute Length field to 255 to have provision for descriptive log messages. Click on OK after adding the last attribute msg as shown in Figure 38.

Figure 38. Adding the Attribute msg

Figure 38. Adding Attribute msg

All the attributes for the LogEntry entity attribute are shown in Figure 39.

Figure 39. Entity LogEntry Attributes

Figure 39. Entity LogEntry Attributes

The Name Uniqueness messages in the Entity Properties window (Figure 40) indicate that the Entity - LogEntry is not unique in the model and multiple entities called LogEntry may have been added during model development. We shall subsequently delete duplicate entities if any. Click on OK.

Figure 40. Clicking on OK

Figure 40. Entity Properties>OK

The second entity gets modified, as shown in Figure 41.

Figure 41. The LogEntry entity has been modified

Figure 41. Entity LogEntry

To modify the default relationship name or any of its other properties double-click on the Relationship8 label as shown in Figure 42.

Figure 42. Double click on the Relationship 8 label

Figure 42. Relationship8>Double-click

The Relationship Properties window gets displayed, as shown in Figure 43. Modify the relationship name and caption and click on OK.

Figure 43. Relationship Properties window

Figure 43. Relationship Properties

The relationship name gets applied, as shown in Figure 44.

Figure 44. The Relationship name has been modified

Figure 44. Relationship name modified

Next, we need to delete any extraneous entities that may have been added during model development. Two entities called LogEntry are listed, as shown in Figure 45, and Figure 44 also. One of the LogEntry entities has only one attribute, called Attribute1, and the entity may have been added during model development and testing. An extraneous entity may not always exist and if the procedure in this article is followed an extra entity or any other extra data structure should not get added, but it is a best practice to check for any extra data structures.

Figure 45. Extra LogEntry Entity

Figure 45. Extra LogEntry Entity

To delete the extra LogEntry entity, right-click on the entity and select Delete Item, as shown in Figure 46.

Figure 46. Select Delete Item from the menu

Figure 46. LogEntry>Delete Item

Click on Yes in Confirm as shown in Figure 47.

Figure 47. Confirmation Dialog for deleting the extra LogEntry entity

Figure 47. Confirm Dialog

The MySQL5.7 model should contain only entities and entity relationships that we added, as shown in Figure 48.

Figure 48. MySQL 5.7 Model now shows only the entities and relationships we added

Figure 48. MySQL 5.7 Model

The entity diagram region provides several options that could be applied to the model and the diagram. Right-click on the diagram region to list these options.  As an example, select Optimal Style to all lines as shown in Figure 49.

Figure 49. Selecting Optimal Style to All Lines

Figure 49. Optimal Style to All Lines

The diagram lines style gets updated, as shown in Figure 50.

Figure 50. Diagram lines have been styled

Figure 50. Diagram lines styled

Saving Model

To save the model select File>Save Model as shown in Figure 51.

Figure 51. Selecting Save Model

Figure 51. File>Save Model

In the Save Model as specify model file name and click on Save as shown in Figure 52.

Figure 52. In the 'Save Model as' window click on Save

Figure 52. Save Model as>Save

Summary

In this first of two articles we developed a model for a MySQL database in Toad Data Modeler and added entities to model database tables. In a subsequent article we shall generate DDL from the model in Toad Data Modeler and run the DDL in Toad Edge to create database tables in a MySQL database.

Tags: Toad Data Modeler Toad Edge MySQL

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.