Modifying a Database Model

Visio Professional 2007 provides extensive options for working with the tables, columns, views, and relationships in a database model diagram. You can define and edit settings for data types, referential integrity, indexes, and extended attributes, and see your changes reflected in the shapes on the page.

Visio Professional 2007 displays detailed information about the database shapes in the Database Properties window, as Figure 19-13 shows. To display this window, right-click a shape, and then choose Database Properties.

The Database Properties window makes it easy to refine the tables in your database model. In the Categories list, you can select an item to work with its properties. Table 19-1 summarizes the options available for each category.

Table 19-1. Categories in the Database Properties Window

Category	Description
Definition	Use this category to specify physical and conceptual names for the table and choose whether to synchronize the names. You can specify a namespace value for the table to distinguish it from similarly named tables in the model. Definition also displays information reported by the host DBMS about the database owner and the path of the source database. The defining type field is available only when a table is empty; it lists all composite data types, so you can create a typed table.
Columns	Use this category to add, remove, edit, and change the order of columns in a table. You can identify a column as a primary key or foreign key and specify whether physical or portable data types are displayed.
Primary ID	Use this category to edit, define, or delete primary keys from a list of available columns and to choose whether to create an index on primary keys.
Indexes	Use this category to create, edit, define, rename, delete, or set extended attributes for indexes. Use this tab to specify the type of index you create for a particular column.
Triggers	Use this category to add, edit, or remove the code for triggers that are included with your model. When you click Add or Edit, the Code Editor opens so that you can create or edit a trigger.
Check	Use this category to add, edit, or remove the code for check clauses that are included with your model. When you click Add or Edit, the Code Editor opens so that you can create or edit a check clause.
Extended	Use this category to set DBMS-specific extended attributes for use with the Visio Professional 2007 database drivers.
Notes	Use this category to add notes about a table.

Adding and Editing Tables and Columns

Visio Professional 2007 represents a table in your database model diagram with the Entity shape. When you want to add a new table to the diagram, you can drag a shape from either the Entity Relationship or Object Relational stencil depending on the modeling methodology you want to use. Visio Professional 2007 makes changes to the appearance of the Entity shape based on the modeling and display options you have specified. For example, if you specify IDEF1X, Visio Professional 2007 displays the relationship between parent and child tables, as Figure 19-14 shows.

When you specify Relational notation for your database model diagram, the Entity shape has a shaded box at the top that contains the table’s conceptual name, as Figure 19-15 shows.

Follow these steps to add and name a new table:

1.	Drag an Entity shape from the Entity Relationship or Object Relational stencil onto the drawing page.
2.	If the Database Properties window is not visible, right-click the new table, and then choose Database Properties. [View full size image]
3.	Select Definition in the Categories list, then type a name in the Physical Name box. Visio Professional 2007 updates the Conceptual Name box if the Sync Names When Typing check box is selected (the default); otherwise, you must type a value for Conceptual Name.

Adding Columns to a Table

You can add columns to an existing table or define columns for a new shape in the Database Properties window. When you add a column to a table, you can define it as the primary key. If you’re using Relational notation, primary, alternate, and foreign keys are visible on the table, as Figure 19-16 shows. Visio Professional 2007 automatically creates unique indexes on primary keys. Other columns can be assigned a nonunique index called the inversion key.

Before you start adding columns, you might want to define a default naming convention. Visio Professional 2007 generates default names for columns, primary keys, foreign keys, and other attributes based on a prefix and suffix specified in the Database Modeling Preferences dialog box. For example, if you add a new column to the Orders table, Visio Professional 2007 provides the name OrdersCol1. To specify default names, choose Database, Options, Modeling, and then change the settings on the Logical Misc tab.

Follow these steps to add a column and define its properties:

1.	Right-click the table to which you want to add a column, and then choose Database Properties.
2.	In the Categories box, choose Columns, and then click Add. [View full size image]
3.	To create a primary key, place a check mark in the PK column.
4.	To define the column’s attributes, click Edit.
5.	In the Column Properties dialog box, click the tab that contains the information you want to define and make your changes. Table 19-2 describes the options available in the Column Properties dialog box. Visio Professional 2007 updates the database model diagram to reflect your choices. Table 19-2. Options in the Column Properties Dialog Box Tab Options Definition On this tab you can type specify the column’s name in the Physical Name box as it appears in the database or specify the name in the Conceptual Name box as it appears in the database model. If you want to specify a default column value, type in the Default Value box, and then choose the Is Literal Value or Is An Expression Or Function Call option. Select the Allow NULL Values check box to make the column optional rather than mandatory. Data Type On this tab you can choose whether to display portable or physical data types and assign data types to columns. Collection On this tab you can specify whether the column in an object-relational model is a single value or contains a collection of information. Collections can be sets, lists, or multiple values. Check On this tab you can add or remove check clauses for a column. Extended On this tab you can set DBMS-specific extended attributes for use with the Visio database drivers. Notes On this tab you can add notes about a column.

Categorizing Subtypes of Tables

When you have a large number of tables of the same type, or attributes that are repeated for several entities, you can define a category, which Visio Professional 2007 represents with the Category shape, as Figure 19-17 shows. The Parent To Category and Category To Child connectors create one-to-one relationships between parent and child tables in the category. The parent table includes all the common attributes, or columns, for the category, including the discriminator, the value of which identifies the categories of the subtypes. Attributes unique to a category are assigned to the appropriate child, or subtype. Each subtype inherits the primary key of the parent automatically. Visio Professional 2007 can represent complete categories, in which all subtypes are included, or incomplete categories, which include only some subtypes.

Follow these steps to define a category:

1.	Drag a Category shape to your database model.
2.	Choose the Connector tool from the Standard toolbar, and then click the Parent To Category shape on either the Entity Relationship or Object Relational stencil.
3.	Drag from the parent table to the Category shape.
4.	With the Connector tool still selected, click the Category To Child shape, and then drag from the Category shape to the child table.
5.	Repeat step 4 for each child table.
6.	Right-click the Category connector shape, and then choose Database Properties.
7.	If the category represents a complete category, select the Category Is Complete check box. An incomplete shape displays a single line below the circle. A complete shape displays a double line below the circle.
8.	To specify a category discriminator, choose This Attribute, and then select the column you want to use.

Specifying Data Types

Each column in a table must be assigned a data type, and Visio Professional 2007 provides advanced options for defining and assigning data types. You can use portable data types so that your model remains independent of the implementation requirements for any particular DBMS. However, if you’re documenting a specific database, you can use the physical data types specified by the target DBMS. When you reverse engineer a database, Visio Professional 2007 displays physical data types by default. To see all the built-in physical data types available in your model, choose Database, View, Types to display the Types window.

The Types window also lists user-defined data types. If you’re working with portable data types, you can define your own types to provide consistent and reusable definitions for columns in your model.

Setting a Column’s Data Type

When you assign a physical data type to a column, you must be familiar with the requirements of your DBMS. When you work with portable data types, you can use the default specifications or create a user-defined data type.

Follow these steps to specify the data type for a column:

1.	Right-click the table containing the column you want to edit, and then choose Database Properties.
2.	In the Categories box of the Database Properties window, select Columns.
3.	In the Show area, select either Portable Data Type or Physical Data Type. Visio Professional 2007 updates the Data Type column according to your selection.
4.	Select a Column, and then click Edit.
5.	In the Column Properties dialog box, click the Data Type tab. Visio Professional 2007 displays either the mapped physical data type or the portable data type. To switch between two views, select either the Show Portable Data Type or Show Physical Data Type option at the bottom of the Column Properties dialog box.
6.	To map the column to a different data type, ensure the Show Physical Data Type option is selected, as delineated in step 5, then click Edit, specify the options you want, and click OK.
7.	When you’ve specified the data type options you want, click OK.

Creating User-Defined Data Types

When you define your own data types, you control the way Visio Professional 2007 represents portable data types in your model. You create your own data types by using the User Defined Types command to define the type attributes, such as category (numeric, text, logical, and so on), type, and size.

Follow these steps to create a user-defined data type:

1.	Choose Database, User Defined Types.
2.	Click Add, and then type a name in the Data Type Name box.
3.	To base your new data type on an existing one, select the Copy From check box, and then select the name of an existing user-defined data type.
4.	Click OK to return to the User Defined Types dialog box.
5.	Specify the category, type, and size in the appropriate box, and, if desired, type notes about the data type in the Description box as displayed in Figure 19-18. Figure 19-18. Notes about the data type can be typed in the Description box.
6.	Click OK.

Troubleshooting: Visio Professional 2007 does not create the foreign key when you connect parent and child tables Unless you change the default behavior, Visio Professional 2007 creates a foreign key relationship when you connect a relationship line between two tables. In the child table, Visio Professional 2007 adds a new column and identifies a foreign key relationship between the two tables. When a foreign key is not automatically propagated, ask yourself the following questions: Has the default foreign key behavior been reset? On the Logical Misc tab of the Database Modeling Preferences dialog box, the Propagate On Add check box must be selected for Visio Professional 2007 to create a foreign key relationship. Choose Database, Options, Modeling, and then verify that this check box is selected. Is the relationship shape glued to both tables? When you select a relationship connector, both endpoints appear red if the shape is properly glued to the two tables. If an endpoint is green, drag it slightly away from the table, and then press the Ctrl key as you drag it back to the table. A red border appears around the table shape to indicate that the shapes are connected (with shape-to-shape glue).

Specifying Relationships and Cardinality

Visio Professional 2007 represents the parent–child relationships between tables in your database model diagram with the Relationship connector. You can control the way tables interact with one another—specifically, the way a child table inherits from a parent—by adding relationships and editing their properties. Relationship properties include direction, referential integrity, and cardinality, all of which can be displayed in your database model diagram depending on the notation you’re using. For example, in Relational notation, an arrow signals the direction of the relationship and points to the parent table, as Figure 19-19 shows. In IDEF1X notation, a dot specifies the child table.

You can use the Relationship shape from either the Entity Relationship or Object Relational stencil to add a relationship that defines a foreign key relationship between tables. However, the Connector tool on the Standard toolbar does the same thing, and it’s a little easier to use.

Connector tool

To add a relationship and specify its properties, follow these steps:

1.	Click the Connector tool on the Standard toolbar, and then drag from the parent table to the child table. Visio Professional 2007 adds any primary key in the parent table to the child table as a foreign key.
2.	Right-click the new relationship connector, and then choose Database Properties.
3.	In the Categories box of the Database Properties window, select the category that contains the information you want to edit, and then make your changes. See Table 19-3 for details. Table 19-3. Categories for Specifying Relationship Properties Category Options Definition Use this category to create associations between columns in the parent and child tables and to enter role names for foreign keys. Name Use this category to type an optional phrase in the Verb Phrase box to describe the parent’s role and in the Inverse Phrase box for the child’s role. In the Physical Name box, you can type a name based on the requirements of the target DBMS. In the Notes box, you can add notes describing the relationship. Miscellaneous Use this category to choose the type of cardinality, whether the relationship is identifying or nonidentifying, and whether the relationship is optional. Referential Action Use this category to choose options that determine the effect on the child table when information in the parent table is updated or deleted.

Displaying Role Text on Relationships

To make your database model diagram easier to read, you can add role text to relationships. A role is the part played by an object in a relationship. To describe the role played by a parent table, you define a verb phrase. An inverse phrase describes the role in reverse, as played by a child table. Typical role phrases include is a, is of, or has a, as in Person is of Country, where the Person table and Country table have a foreign key relationship. Role text is displayed on the relationship connector, as Figure 19-20 shows.

Follow these steps to define and display role text:

1.	Right-click the new relationship connector, and then choose Database Properties.
2.	In the Categories box of the Database Properties window, select Name.
3.	Type the phrases you want to display in the Verb Phrase and Inverse Phrase boxes.
4.	Choose Database, Options, Document.
5.	On the Relationship tab, select Show Verb Phrase. If you want to display only the verb phrase, select the Forward Text check box. If you want to display only the inverse phrase, select the Inverse Text check box.
6.	Click OK.

Text Block tool

Role Text and Object Role Modeling The Verb and Inverse Verb Phrase options for relationships in Visio Professional 2007 are designed to work with conceptual models created with the Object Role Modeling (ORM) method. In ORM, you can design and query database applications using English-like language. The resulting models are simpler for nontechnical audiences to understand. Visio Professional 2007 includes a stand-alone ORM template, but you can’t use it to exchange data with the Database Model Diagram template. You can, however, use Visual Studio.NET Enterprise Edition to define business rules in an ORM diagram and then upload the model into a relational or object-relational database model diagram, which in turn can be used to generate a script in DDL or a physical database schema. The ORM functionality in Visual Studio is based on a tool known as VisioModeler, now freely available as an unsupported product from Microsoft Corporation. To download VisioModeler (a 25-MB download), go to http://www.microsoft.com/technet/downloads/visio.mspx. For more information about ORM, visit http://www.orm.net, a site maintained by Dr. Terry Halpin, who formalized the ORM notation.

Displaying Referential Integrity and Cardinality

When you define a parent–child relationship between tables, you can specify referential integrity as well, which determines how changes to the parent table affect the child. You can display an annotation on the relationship connector to indicate referential integrity. In addition, you can indicate the cardinality of a relationship in IDEF1X notation.

Defining an Index

You can view and edit the indexes on columns that you reverse engineered and define new indexes for columns you plan to search frequently in your DBMS. The Database Properties window displays the attributes of an index, as Figure 19-21 shows.

To add an index, select Indexes in the Categories list, click New, type a name, and then click OK. In the Available Columns box, select the names of the columns that you want to include in this index, and then click Add. The Disp. Name area displays the index notation as it will appear in your database model diagram. Table 19-4 summarizes the properties you can specify for an index.

Table 19-4. Index Properties

Opton	Description
Index Type	Choose whether the index is unique or nonunique or represents a constraint in this list box.
Asc	Select this option to set the sort order to ascending.
Options	Use this button to set driver-specific extended attributes for the index. Not all databases support extended attributes.