Modeling Relational and Object-Relational Databases | Microsoft Office Visio 2003 Inside Out (Inside Out (Microsoft))

Whether you’ve inherited the company’s e-commerce back end and want to document it, or you simply need to understand the legacy purchasing system, an accurate database model diagram can help. Although Visio can reverse engineer a database and create a diagram based on the schema, the result is more than a series of connected shapes. Visio creates a model that represents the logical and physical structure of a database, including tables or entities, columns or attributes, and relationships. Visio supports notation for primary keys, foreign keys, alternate keys, and indexes, as well as referential integrity and IDEF1X cardinality constraints. To view and edit this information, you work in several special-purpose anchored windows as well as the drawing page, as Figure 19-1 shows.

click to expand
Figure 19-1: You can reverse engineer a database to extract tables, views, code, and other database elements and create a database model diagram.

The Database Model Diagram template is an application unto itself. As with other drawing types, you can drag shapes to add objects to your database model. However, the template adds the Database menu and other tools specifically for working with your database model diagram. Because of these additions, the way you create and modify database diagrams isn’t always the same way that you work with other Visio drawings.

Starting a New Database Model Diagram

Most people use the Database Model Diagram template to reverse engineer a database that already exists. However, you can also use the template to prototype new designs.

Follow these steps to start a new database model diagram and set modeling options:

Choose File, New, Database, Database Model Diagram.
To set the modeling options you want to use, choose Database, Options, Document.
Under Symbol Set, select IDEF1X or Relational, and then specify the name options you want to display.
Click the Table tab, and then choose the attributes you want to have displayed in table shapes, primary key order, data type display, and whether to display IDEF1X optionality.
Click the Relationship tab, and then choose whether to display relationships. If you do, specify the relationship notation you want to use (such as Crow’s Feet), whether to display cardinality and referential integrity, and how to display the relationship verb phrase and name (role text).

Click OK to apply the settings to the drawing file.

Tip

Import a Database Model Diagram If you have an existing database diagram from PLATINUM ERwin or VisioModeler that you would like to edit in Visio, you can import it. Visio Professional can import ERX files from ERwin 2.6, 3, and 3.52 (although Visio doesn’t import stored procedures, custom triggers, or ERwin displays). You can import models from VisioModeler 2 or later. To import a model, choose Database, Import.

Troubleshooting
The Database menu is no longer visible

Solution-specific menus can disappear when Visio encounters an unexpected error from an external source. If the Database menu no longer appears between the Shape and Window menus, choose Tools, Options. On the Security tab, select Enable Automation Events, and then click OK. Save changes to any open diagrams, and then exit Visio. Restart Visio, and then open your database model or start a new diagram with the Database Model Diagram template.

Viewing Tables and Other Model Elements

You can add shapes to the drawing page to provide one view of a database model. Visio stores another view of your model in the Tables And Views window, which lists the names of tables and views extracted from a database, as Figure 19-2 shows. To display this window, choose Database, View, Tables And Views. Whether or not the diagram shows every table and view in your model, the Tables And Views window does. You can drag elements from the window onto your diagram, where you can view them along with any associated relationships.

Figure 19-2: The Tables And Views window is an anchored window that you can float or dock in a convenient location as you work on your database model diagram.

Note

You can view the tables related to a particular table without viewing the entire model diagram.

Getting Feedback from the Output Window

When you start a new diagram with the Database Model Diagram template, the Output window appears below the drawing page. This window displays progress and status information when you reverse engineer a database, as Figure 19-3 shows. You can verify that the database schema was extracted as specified and view a summary at the bottom of the window of the number of tables and other items, including code, that were extracted from your database.

click to expand
Figure 19-3: The Output window shows you every step Visio takes when reverse engineering a database.

Visio validates your model when you reverse engineer a database. Messages about conflicting names, data types, or other errors appear in the Output window. You can copy the messages and paste them into Notepad or another text editor to keep them on hand for troubleshooting. Right-click in the Output window, and then choose Copy Message or Copy All Messages.

Inside Out
Output window options

At the top of the Output window is a list box with two options: Import and Info. In Visio Professional, the Info option isn’t used and is provided solely for compatibility with the advanced forward engineering and model validation features in Microsoft Visual Studio .NET Enterprise Edition.

Viewing Database Code

If your database includes code, such as check clauses or stored procedures, Visio can extract it when you reverse engineer a database. The Code window lists all code for a particular database platform, as Figure 19-4 shows. You can view, edit, and delete stored procedures, functions, triggers, check clauses, view definitions, and even raw data definition language (DDL) code, each of which is represented by a different icon in the Code window.

Figure 19-4: You can view, edit, and delete the code associated with your database model diagram.

To display the Code window, choose Database, View, Code. The Code window lists two types of code:

Global code refers to any stored procedure, function, view definition, or raw DDL code that’s not associated with a specific table.
Local code refers to triggers or check clauses for a specific table or column in your database model diagram.

To display any code module, select it, and then click Edit to open the Code Editor. The Code Editor is a full-featured editor. You can specify keyword colors, assign keyboard shortcuts, and more with the Window Properties button on the Code Editor toolbar. For example, you can indent lines automatically as you enter code in accordance with language-specific scoping rules. Use the Insert Code Skeleton button to do just that—insert a blank code skeleton for the type of code you have specified.

Setting Modeling Preferences

You can set preferences for the way shapes look and behave in a database model diagram. For example, you can specify whether deleting a shape from the diagram deletes the object from the model. When you choose Database, Options, Modeling, you specify the default settings that are stored with the Database Model Diagram template and remain in effect until you specify otherwise. As Figure 19-5 shows, the Logical Diagram tab includes the following options:

When Removing An Object From The Diagram Specifies whether to remove a deleted object from the drawing page only, retaining it in the Tables And Views window, or remove it from the drawing file (and model) altogether.
Show Relationships After Adding Table To Diagram Shows relationships on the current page between a newly added table and any other tables on the page.
Show Relationships After Adding Type To Diagram Shows relationships between a newly added type in the diagram and any other types on the page.
Sync Conceptual And Physical Names In New Tables And Columns When Typing Specifies that typing a name in one field creates the name in the other field automatically when you change database properties.

Figure 19-5: With the options on the Logical Diagram tab, you can customize the behavior of the shapes that represent your database model.

On the Logical Misc tab, you can control the way names and relationships appear in the diagram so that shapes reflect your preferred modeling style, as Figure 19-6 shows. You can set the following properties:

FK Propagation Specifies the behavior when a relationship shape is connected between a parent and child table. If Propagate On Add is selected, Visio creates a foreign key relationship when you connect a relationship shape. If Propagate On Delete is selected, Visio removes the foreign key from the model when you delete a relationship shape.
Name Conflict Resolution Specifies the behavior when you add a foreign key to a table that contains a column of the same name.
Default Name Prefixes Specifies the prefix that is added to the suffix under Default Name Suffixes to form the default conceptual name for objects added to a model.
Default Name Suffixes Specifies the suffix that is added to the prefix under Default Name Prefixes to form the default conceptual name for objects added to a model.
FK Name Generation Option Specifies how to construct the default foreign key name that appears in a model. Suffix refers to the value of the Foreign Key box under Default Name Suffixes.

Figure 19-6: On the Logical Misc tab, you can choose how foreign keys are propagated and how to build default names and resolve name conflicts.