Database/Data Model Round-Trip Engineering with MySQL

0
123
2 min read

Power*Architect—from SQL Power—is a free software data modeling tool, which you can download from its website www.sqlpower.ca and use it under GPLv3 license.

Reverse Engineering

To reverse engineer is to create the data model of an existing database.

To reverse engineer an existing database in Power*Architect, we need to connect to the database. Figure 1 shows the Power*Architect’s connection window where we define (create) our connection to the MySQL sales database that we’d like to reengineer.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 1: Creating a database connection

By adding the conn_packt connection, the sales database objects are now available in Power*Architect.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 2: Adding a database connection

By expanding the sales database, you can see all the objects that you need to create its data model.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 3: Database objects

You create the ER diagram of the sales data model by dragging the sales object into the canvas (called playpen in Power*Architect)

Note that the objects in the model (those in the diagram) are now in the PlayPen Database.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 4: Database objects in the PlayPen

Now that you have created the data model, you might want to save it.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 5: Saving the data model (project)

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 6: Saving sales.architect data model (project)

You have completed the sales database reverse-engineering.

Updating the Data Model

Let’s now add two new tables (hardware and software) and relate them to the product table. You add a table by clicking the New Table tool and dropping your cursor on the white space of the canvas.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 7: New Table tool

Type in the name of the table, and then click OK.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 8: Adding hardware table

We now add a column to the hardware table by right-clicking the table and selecting New Column.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 9: New Column menu selection

Type in the name of the column (model), select VARCHAR data type (and its length), then click OK.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 10: The model column

After adding the two tables and their columns, our ER diagram will look like in Figure 11.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 11: The hardware and software tables

Our last update is relating the hardware and software tables to the product table.

Select the New Identifying Relationship tool; click it to the product and then the software.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 12: New Identifying Relationship tool

The software table is now related to the product table. Note that the product’s primary key is migrated to the software table as a primary key.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 13: software and product tables are related

LEAVE A REPLY

Please enter your comment!
Please enter your name here