[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Database/Data Model Round-Trip Engineering with MySQL, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
by
Djoni Darmawikarta | May 2009 |
PHP
Round-trip engineering as applied to a database means reverse engineering (database to data model) and forward engineering (data model to database) back and forth.
In this article, Djoni Darmawikarta shows how to maintain an existing MySQL database using Power*Architect. Particularly, he shows by examples, how to reverse engineer an existing MySQL database, update the resulting data model, compare the updated model with the 'old' database, and implement the updates by forward-engineering them back to the database.
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
Do similarly with the hardware table. When you're done, the ER diagram will look like in Figure 14.
Figure 14: Model changes completed (hardware and software tables)
Save the model.
Figure 15: Saving the updated model
Comparing Data Models
To implement the changes we made back to the database, we need to compare the update data model to the existing database.
Figure 16: Compare menu icon
Our old "model" is the existing database; the newer is the model (project) we're working on, so click the Swap button to exchange the default (Figure 17).
Figure 17: Swapping the older and newer models
Select the conn_packt connection (Figure 18), sales database (Figure 19) and SQL for MySQL (Figure 20).
Figure 18: Selecting conn_pact connection
Figure 19: Selecting sales database
Figure 20: Selecting SQL for MySQL
When you click Start, the DDL to implement the changes are generated and shown as in Figure 21.
Figure 21: Generating DDL
To finish off our data models comparison click Save, which saves the DDL in an SQL script.
Figure 22: Saving DDL
Forward Engineering
You can implement the changes by running the SQL script you just saved. Another easier way is to just click the Execute button on the Compare DM window. (Figure 21 above)
When the execution gets completed, you'll get a successful message (Figure 23).
Figure 23: Forward-reengineering is completed
The sales database is now updated with the changes: the software and hardware tables, and their relationship to the product table (Figure 24).
Figure 24: sales database updated
We have now completed our round-trip engineering, which started by reverse engineering an existing sales database, updated the resulting data model, compared the old and new data models to get the updating DDL, and finally forward engineering to apply the update back to the sales database.
Summary
This article shows the no-nonsense functions of the SQL Power's Power*Architect for round-trip engineering database/data model. In addition to MySQL, the tool supports other databases, namely PostgreSQL, HSQLDB, SQL Server, Oracle, and IBM DB2. The tool has other data-related functions, such as data profiling, integration with Kettle (an open source ETL tool), and OLAP data modeling.
About the Author :
About the Author
Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.
Books From Packt
|
|
Post new comment