Database Reference
In-Depth Information
Figure 8-2
Reverse-Engineered Data
Model
Figure 8-2 shows the RE data model of the View Ridge Gallery database produced by the
MySQL Workbench from a MySQL 5.6 version of the View Ridge Gallery database created in
Chapter 7. Note that due to the limitations of the MySQL Workbench, this is a physical data-
base design rather than a logical data model. Nonetheless, it illustrates the reverse engineering
technique we are discussing.
We used the MySQL Workbench because of its general availability. The MySQL
Workbench, as discussed in Appendix E uses standard IE Crow's Foot database modeling
notation. Figure 6-35 shows the VRG data model, and Figure 6-37 shows the VRG database
design. If you compare these to the VRG RE data model in Figure 8-2, you will see that the
MySQL Workbench came close to duplicating the VRG database design rather than the VRG
data model. The MySQL Workbench:
●
Contains the final
primary keys
and
foreign keys
, rather than the data model entity
indentifiers.
●
Contains the
customer_artist_int
table, rather than the N:M relationship between
CUSTOMER and ARTIST shown in the data model.
●
Contains wrong
minimum cardinality
values. All of the many sides of the 1:N relation-
ships should be optional except for the WORK-to-TRANS relationship, based on the
VRG database design.
All in all, however, this is a reasonable representation of the View Ridge Gallery database
design. For more information about using the MySQL Workbench, see Appendix E.
Although the MySQL Workbench produces only a database design and not a data model,
some other design software, such as CA Technologies' ERwin can create both logical (data
model) and physical (database design) versions of the database structure. In addition to tables
and views, some data modeling products will capture constraints, triggers, and stored proce-
dures from the database (in fact, the MySQL workbench can capture some of these, although we
have not included them in Figure 8-2).
These constructs are not interpreted, but their text is imported into the data model. With
some products, the relationship of the text to the items it references also is obtained. The rede-
sign of constraints, triggers, and stored procedures is beyond the scope of our discussion here.
You should realize that they, too, are part of the database, however, and are subject to redesign.
Dependency Graphs
Before making changes to database structures, it is vitally important to understand the depen-
dencies of those structures. What changes will impact what? For example, consider chang-
ing the name of a table. Where is the table name used? In which triggers? In which stored
Search WWH ::
Custom Search