3 4
Visio Professional 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 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.
Figure 19-13. For convenience while editing the diagram, drag the Database Properties window by its title bar into the Output window to merge the windows at the bottom of the screen.
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 name space value for the table to distinguish it from similarly named tables in the model. This category 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 database drivers. |
Notes | Use this category to add notes about a table. |
Visio 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 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 displays the relationship between parent and child tables, as Figure 19-14 shows.
Figure 19-14. If you're using IDEF1X notation, Visio displays dependent tables with rounded corners.
Note
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.
Figure 19-15. If you're using Relational notation, you can display primary keys, foreign keys, and indexes on the table.
Tip
Follow these steps to add and name a new table:
Tip
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 automatically creates unique indexes on primary keys. Other columns can be assigned a non-unique index called the inversion key.
Figure 19-16. OfficeLoc is the primary key (PK); StreetAddress, IsHeadquarters, and Countryname are required values and appear in bold. PostCode, StateCode, and CityName are unique indexed columns (U1).
Before you start adding columns, you might want to define a default naming convention. Visio 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 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:
Table 19-2 describes the options available in the Column Properties dialog box. Visio 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 in the Physical Name box to specify the column's name as it appears in the database or in the Conceptual Name box to specify the name 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 Functional 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 edit, add, or remove check clauses for a column. When you click Add or Edit, the Code Editor opens so that you can create or edit a check clause. |
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. |
Tip
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 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 can represent complete categories, in which all subtypes are included, or incomplete categories, which include only some subtypes.
Figure 19-17. This complete category uses the C_Code column as the category discriminator.
Follow these steps to define a category:
Each column in a table must be assigned a data type, and Visio 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 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, as Figure 19-18 shows.
Figure 19-18. The Types window lists all the data types you can assign.
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.
Tip
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:
InsideOut
When you define your own data types, you control the way Visio 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:
Visio does not create the foreign key when you connect parent and child tables.
Unless you change the default behavior, Visio creates a foreign key relationship when you connect a relationship line between two tables. In the child table, Visio adds a new column in and identifies a foreign key relationship between the two tables. When a foreign key is not automatically propagated, ask yourself the following questions:
Visio 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. In IDEF1X notation, a dot specifies the child table, as Figure 19-19 shows.
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.
Figure 19-19. Relationship connectors indicate parent-child relationships between tables.
To add a relationship and specify its properties, follow these steps:
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 non-identifying, 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. |
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.
Figure 19-20. You can display the verb phrase, inverse phrase, or both on a relationship. The inverse phrase "is of" appears here.
Follow these steps to define and display role text:
Tip
The Verb and Inverse Verb Phrase options for relationships in Visio Professional 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 non-technical audiences to understand. Visio Professional includes a standalone 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 data definition language (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/downloads/search.asp?. For more information about ORM, visit http://www.orm.net, a site maintained by Dr. Terry Halpin, who formalized the ORM notation.
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. For example, in Figure 19-19, Crow's Feet notation is used for the relationships and shows that the cardinality of the parent-to-child relationship is one to zero or more for both relationships.
Tip
Follow these steps to display referential integrity or cardinality:
Note
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.
Figure 19-21. You can see the properties of an indexed column when you select Indexes in the Database Properties window.
To add an index, click the New button, type a name, and then click OK. In the Available Columns box, select the name of each column that you want to include in this index, and then click Add. The Disp. Name area shows 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
Option | Description |
---|---|
Index Type | Choose whether the index is unique or non-unique 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. |
Note