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.

Figure 1: Creating a database connection

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

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.

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.

Figure 4: Database objects in the PlayPen

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

Figure 5: Saving the data model (project)

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.

Figure 7: New Table tool

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

Figure 8: Adding hardware table

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

Figure 9: New Column menu selection

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

Figure 10: The model column

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

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.

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.

Figure 13: software and product tables are related