Database/Data Model Round-Trip Engineering with MySQL

Exclusive offer: get 50% off this eBook here
Creating your MySQL Database: Practical Design Tips and Techniques

Creating your MySQL Database: Practical Design Tips and Techniques — Save 50%

A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.

$11.99    $6.00
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.

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

Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: $11.99
Book Price: $19.99
See more
Select your format and quantity:

Do similarly with the hardware table. When you're done, the ER diagram will look like in Figure 14.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 14: Model changes completed (hardware and software tables)

Save the model.

Creating your MySQL Database: Practical Design Tips and Techniques

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.

Creating your MySQL Database: Practical Design Tips and Techniques

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).

Creating your MySQL Database: Practical Design Tips and Techniques

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).

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 18: Selecting conn_pact connection

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 19: Selecting sales database

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 20: Selecting SQL for MySQL

When you click Start, the DDL to implement the changes are generated and shown as in Figure 21.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 21: Generating DDL

To finish off our data models comparison click Save, which saves the DDL in an SQL script.

Creating your MySQL Database: Practical Design Tips and Techniques

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).

Creating your MySQL Database: Practical Design Tips and Techniques

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).

Creating your MySQL Database: Practical Design Tips and Techniques

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.

Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: $11.99
Book Price: $19.99
See more
Select your format and quantity:

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

 

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

Creating your MySQL Database: Practical Design Tips and Techniques
Creating your MySQL Database: Practical Design Tips and Techniques

Mastering Oracle Scheduler in Oracle 11g Databases
Mastering Oracle Scheduler in Oracle 11g Databases

SOA Cookbook
SOA Cookbook

Oracle Modernization Solutions
Oracle Modernization Solutions

Oracle 10g/11g Data and Database Management Utilities
Oracle 10g/11g Data and Database Management Utilities

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

Oracle Essbase 9 Implementation Guide
Oracle Essbase 9 Implementation Guide

 

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
s
T
Y
g
B
4
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software