Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Visio-Based Database Modeling in Visual Studio .NET Enterprise Architect: Part 1

Visio-Based Database Modeling in Visual Studio .NET Enterprise Architect: Part 1

Visual Studio .NET 2003
 

Terry Halpin
Visual Studio Team
Microsoft Corporation

November 2001

Summary: This is the first in a series of articles introducing the Visio-based database modeling component of Microsoft Visual Studio .NET Enterprise Architect. The initial emphasis is on the Object-Role Modeling (ORM) support provided by this tool. (9 printed pages)

Contents

Introduction
Creating a New ORM Model
Adding Sentence Types Using the Fact Editor
Adding Basic, Internal Constraints Using the Fact Editor
Adding Examples to Fact Types
Displaying Sentence Types on the Drawing
Mapping an ORM Model to a Logical Database Model
Conclusion
References

Introduction

The database modeling solution within Microsoft® Visio® Enterprise 2000 provided basic support for conceptual information analysis using Object-Role Modeling (ORM), as well as logical database modeling using relational, IDEF1X, crowsfoot and object-relational notations. ORM schemas could be forward engineered to logical database schemas, from which physical database schemas could be generated for a variety of database management systems (DBMSs). Physical databases could have their structures reverse engineered to logical database schemas or to ORM schemas. The recently released Microsoft Visio 2002 products include Standard and Professional editions only. The Professional edition subsumes the formerly separate Technical edition, but does not include Enterprise. Although Visio 2002 Professional does include an ORM stencil, this is for drawing only—its ORM diagrams cannot be mapped to logical database schemas and cannot be obtained by reverse engineering from a physical database. Visio 2002 Professional includes a database modeling solution for defining new logical database schemas or reverse engineering them from existing databases, but forward engineering to physical database schemas is not provided.

For some time, Microsoft has supported database design and program code design (using UML) in its Visual Studio product range. After acquiring Visio Corporation, Microsoft had two separate products (Visio Enterprise and Visual Studio) that overlapped significantly in functionality, with each supporting database design and UML. As a first step towards unifying these product offerings, the deep modeling solutions formerly within Visio Enterprise have been enhanced and moved into a new Microsoft product known as Visio for Enterprise Architects (VEA), included in Microsoft® Visual Studio® .NET Enterprise Architect. These Visio-based modeling solutions are included in beta 2 of Visual Studio. NET Enterprise, with the final release to follow later. The deep ORM solution in VEA is completely different from the simple ORM drawing stencil in Visio Professional, and cannot translate between them. However, the database modeling solution in VEA can import logical database models from Visio Professional, and then forward engineer them to DDL scripts or physical database schemas.

This series of articles provides a simple introduction to using the database modeling solution within VEA. Various aspects of these articles (for example, company names, product names, user interfaces) are trademarked, copyrighted, or patented by Microsoft Corporation. In this article, the emphasis is on the basics of the ORM solution. Familiarity with ORM and relational database modeling is assumed. Overviews of ORM are online [References 1 and 2]. A deep treatment of ORM and database modeling is discussed in my latest book [Reference 3].

Creating a New ORM Model

The Visio-based modeling tool runs as a separate solution within Visual Studio .NET Enterprise Architect. When you open the tool, the opening screen of the beta looks like Figure 1. Choose Database as the Drawing Type, then select the relevant ORM template. If in the United States, you normally choose ORM Source Model (US units), as shown—this defaults the page size to Letter, and measurements to inches. As you hover the cursor over a template icon, the icon is highlighted and a tool tip appears on the left. Visio provides both US and International (metric) versions of templates. If you select ORM Source Model (without (US units)), the page size defaults to A4 and the units to metric.

Note   In the final release, templates for just your country or region's standard unit system are installed unless you choose otherwise.

When you select an ORM Source Model template, a screen like Figure 2 appears. In addition to the menus and icons at the top, there is an ORM stencil, a Drawing window, and an area for displaying the Business Rules editor, Database Properties sheet, and other windows you might open (for example, Verbalizer).

Click here for larger image

Figure 1. Choosing to work with an ORM Source Model

Click here for larger image

Figure 2. ORM stencil, Drawing window, and Business Rules window

To reduce the space used by Figure 2, I've resized the display quite a bit. Normally, the Drawing window will occupy most of the screen. By default, the three shapes in the ORM stencil appear on the same horizontal row. You can give the Drawing window more room by reducing the width of the ORM stencil so that the shapes line up vertically, as shown here. To do this, hover the cursor on the border between the stencil and the Drawing window until the cursor changes to a resize cursor, then drag it to the left.

Adding Sentence Types Using the Fact Editor

You can add sentence types (fact types or reference types) to an ORM model by dragging Object Type and Predicate shapes from the stencil to the Drawing window. Alternatively, you can add sentence types using the Fact Editor. For now, let's use the Business Rules editor to do this. Move the cursor to the bottom row displayed in the Fact Types pane of the Business Rules window (in our example, there is only one row). Start typing the fact type or press the F2 key. The Fact Editor will appear. You can also invoke the Fact Editor by choosing Database | View | Fact Editor from the Database menu at the top of the screen. By default, the Fact Editor's input style is Guided, as shown in Figure 3.

Click here for larger image

Figure 3. Fact Editor using Guided input style window

You can enter a binary relationship giving both a forward reading (for example, Employee works for Department) and an inverse reading (for example, Department employs Employee). If needed, the arity (number of roles) in the relationship can be changed by choosing a different setting from Binary. The Object pane allows you to classify the object type as an entity type, value type, or external object type. In the case of an entity type with a simple identification scheme, you can add its reference mode (for example, empNr for Employee and code for Department).

Once you are familiar with the Fact Editor, you will probably want to change its input style to Freeform. This allows you to enter sentence types much faster by using a formal syntax. You can use the option button to change to Freeform. You can also make Freeform the default by going to the Database menu at the top of the screen, choosing Database | Options | Modeling…, then opening the Fact Editor pane and setting the preferred mode to Freeform, as shown in Figure 4. In many languages, it is convenient to identify object types by starting their name with a capital letter, assuming the name is a single word (for example, Employee, VicePresident). For languages where this doesn't work or when the name uses multiple words separated by spaces, bracketed mode should be chosen: this encloses the object type name in square brackets (for example, [employee], [vice president]).

Figure 4. Setting the default input style for the Fact Editor to Freeform

In Freeform mode, reference modes appear in parentheses after the object type name. If an inverse reading is supplied, a slash (/) is used to separate forward and inverse readings. Figure 5 shows an example.

Click here for larger image

Figure 5. Fact Editor using Freeform input style

Once a reference scheme has been supplied for an entity type, there is no need to repeat the reference scheme in specifying later fact types. Unlike entity types, value types (for example, EmployeeName, RoomNr) have no reference scheme since their instances are just literal constants (for example, character strings or numbers used to name or refer to entities) so they identify themselves. In Freeform mode, value types are indicated by appending empty parentheses [()]. Examples of some fact types are given below using the formal, Freeform syntax:

Employee(empNr) works for / employs Department(code)
Employee has EmployeeName()
Employee has MobileNr()
Employee drives / is driven by Car(regNr)

Use the Fact Editor now to enter these fact types (using either Guided or Freeform input). Click the Apply button after each of the first three fact types to have the fact type added. When you have entered the fourth fact type, click OK. This adds the last fact type and then closes the Fact Editor. The fact types do not yet appear on the drawing window but should now be listed in the Business Rules editor. If you move the cursor to one of these fact types, an Edit button appears to its right (see Figure 6). If you click the Edit button, it brings up the Fact Editor with that fact type displayed for editing. This provides one way to add basic constraints and examples to fact types.

Click here for larger image

Figure 6. Fact types are listed in the Business Rules editor, and may be edited

Adding Basic, Internal Constraints Using the Fact Editor

A constraint is internal if it applies to just one predicate; otherwise it is external. The Fact Editor allows you to declare the following internal constraints: internal uniqueness, simple mandatory, internal frequency, and ring constraints. It does not allow you to specify internal, set-comparison constraints (for example, an exclusion constraint between two roles of the same predicate), external constraints (for example, an external uniqueness constraint, or a set-comparison constraint between two predicates) or value constraints (for example, restricting Sexcode values to {M, F}). In practice, constraints declared in the Fact Editor are best restricted to simple internal uniqueness and simple mandatory constraints. To declare any other kind of constraint, there is a much faster method (see Part 2 of this series of articles).

To add a constraint on a fact type displayed in the Fact Editor, choose the Constraints tab. By default, the constraints pane combines uniqueness and mandatory constraints to make it faster to specify them. For instance, in Figure 7, choosing "exactly one" means both "at least one" (mandatory) and "at most one" (unique). The constraint symbols and verbalization automatically appear to help you see the result of your choice. If you don't want to use this default shortcut, open the Database Modeling Preferences dialog box (Figure 4) and uncheck the option that indicates combined uniqueness and mandatory (UM).

Click here for larger image

Figure 7. Adding constraints in the Fact Editor

For practice, use the Fact Editor to add each of the following constraints. In the current version of the tool, "some" is used instead of "the same" in the final constraint, which indicates that the drives relationship is optional and many-to-many.

Each Employee works for some Department
Each Employee works for at most one Department
Each Employee has some EmployeeName
Each Employee has at most one EmployeeName
Each Employee has at most one MobileNr
It is possible that the same Employee drives more than one Car and 
   that the same Car is driven by more than one Employee

Adding Examples to Fact Types

It is a good idea to include examples for all fact types. To add a constraint on a fact type displayed in the Fact Editor, click the Examples tab, and enter enough examples to illustrate the relevant constraints. For example, Figure 8 shows three fact instances for our Employee works for Department fact type. Here, Employees 101 and 102 work for the sales department (SLS) and Employee 103 works for the marketing department (MKTG). The population is consistent with our situation where each employee works for at most one department (first column values are unique) but the same department may employ more than one employee (SLS is duplicated in column 2).

Click here for larger image

Figure 8. Adding example fact instances for Employee works for Department

You can use the Analyze button to request the tool to induce the constraints from your examples, or to check for inconsistencies between your data and your constraint specification. Try it out for yourself. This is a very useful feature for validating constraints.

Saving a Model

To save your model, choose File | Save from the File menu, or click the Save icon. This opens the SaveAs dialog box. Choose the folder where you want to save the model, add a filename for the model, click the Save button in the dialog box, then click OK in the properties dialog box. The file will be saved with the extension .vsd (Visio document).

Displaying Sentence Types on the Drawing

To display any sentence types on the diagram that were entered using the Fact Editor, locate the fact types of interest in the Business Rules editor. To select a contiguous series of fact types, hold the Shift key down as you select the first and last fact type in the series. All but the first fact type will appear highlighted. You can then drag the fact types on to the drawing page where you want them displayed.

Try this now for the four fact types in our model. The diagram appears by default as shown in Figure 9. You can finesse the display by moving the predicate text and object types around.

A handy alternative is to open the Object Types pane of the Business Rules window, drag out one or more of the relevant object types, and use the Show Relationships option. For example, if you drag the Employee object type onto any drawing page, right-click Employee, and select Show Relationships from its right-click menu, all the relationships in which Employee plays will be displayed on that page. This ShowRelationships feature is an extremely useful feature in schema browsing and in reverse engineering. It is one of many new features that were not provided previously in VisioModeler or Visio Enterprise.

Figure 9. Diagram formed by dragging the four fact types from the Business Rules editor

Mapping an ORM Model to a Logical Database Model

To map an ORM model to a logical database model, you first add the ORM model to a database model project, then build it. From the File menu, open the logical database modeling solution by choosing File | New | Database | Database Model Diagram (US units). If you want the metric template, choose just Database Model Diagram without the (US units). The screen should now look like Figure 10, except that I have reduced the size of the drawing window significantly. The Entity Relationship stencil can be used to create logical database models from scratch, but for now we will derive the database model from our ORM model.

Click here for larger image

Figure 10. The logical database modeling solution

To create a database model project, choose Database | Project | Add existing document from the Database menu. An Add Document to Project dialog box should now appear. Use the Look in: field to navigate to your saved ORM model, and then click the Open button. The ORM model (mine was called JCM1.vsd) should then be listed in the project window. Save the project file by clicking the Save icon on the main menu and giving it a file name (I chose ProjJCM1). The project file will also have the extension .vsd. The name and page of your current model is always listed in the title bar at the top of the screen. Figure 11 shows what the screen should look like at this stage.

Click here for larger image

Figure 11. A database model project that includes an ORM source model

Now build the logical model by choosing Database | Project | Build from the Database menu. The relational schema is now automatically built, and the resulting table schemes appear in the Tables and Views window at the left of the screen (see Figure 12).

Click here for larger image

Figure 12. Two table schemes are built by mapping the ORM model

To see these table schemes on a diagram, drag them onto the drawing page. The result is shown in Figure 13. There are two table schemes with one foreign key connection between them. Each table has its name in the shaded header, with its columns listed below. Primary keys are underlined, marked "PK", and appear in the top compartment for the columns. Mandatory (not null) columns are in bold. Foreign key columns are marked FKn where n is the number of the foreign key with a table. In this case, we have only one foreign key that targets the primary key of the Employee table. The foreign key connection itself is depicted as an arrow from the foreign key to its target key.

Figure 13. The relational schema mapped from the ORM model

In this example, the names of the tables and columns are those that are generated automatically by default. In practice we would normally rename many of these and also change many of the default data types that have been chosen. Various configuration options exist for controlling how table and column names are generated. In practice, it is best to set the data types on the ORM model, where object types correspond to conceptual domains. The correct data types then automatically propagate to all the attributes based on these domains. Such issues are ignored in this first article.

Generating the physical database schema

You can generate the internal schema for a selected target DBMS by clicking Database | Generate from the Database menu. Generation gives you the option of generating the DDL script rather than having the tool build the tables for you. It is usually best to first generate the DDL script, which you can later execute within your chosen DBMS. Follow the steps in the generate wizard, choose your driver (for example, Microsoft® SQL Server™ 2000), enter a database name (for example, mydb), accept defaults for the next screen, choose Yes to view the generated DDL script, then save the DDL script as a text file.

Conclusion

This article has only touched on the basics of creating a very simple ORM model and then mapping it to a logical and then physical database schema. Later articles will examine how to specify much more powerful ORM models involving advanced constraints and nesting, and also provide more details about the logical database modeling facilities. You can gain some idea of the more advanced features by playing with the Employee ORM Source Model that is included in the sample files issued with the product.

References

  1. Halpin, T. A. Object Role Modeling: An Overview, MSDN, 2001 (also available at www.orm.net, 1998).
  2. Halpin, T.A. "Object Role Modeling (ORM/NIAM)." Chapter 4 of Handbook on Architectures of Information Systems, ed. P. Bernus, K. Mertins, and G. Schmidt (Heidelberg: Springer-Verlag, 1998), available at www.orm.net.
  3. Halpin, T.A. Information Modeling and Relational Databases (San Francisco: Morgan Kaufmann Publishers, 2001), available at www.mkp.com/books_catalog/catalog.asp?ISBN=1-55860-672-6.

(An earlier version of this article first appeared in The Journal of Conceptual Modeling at InConcept, Inc.: http://www.inconcept.com)

See Also

Object Role Modeling: An Overview | Visio-Based Database Modeling in Visual Studio .NET Enterprise Architect: Part 2 | Visio-Based Database Modeling in Visual Studio .NET Enterprise Architect: Part 3 | Visio-Based Database Modeling in Visual Studio .NET Enterprise Architect: Part 4 | Visio-Based Database Modeling in Visual Studio .NET Enterprise Architect: Part 5

Show:
© 2015 Microsoft