• Article

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

When All You Want is an ER Diagram

Karen Watterson

Paul Munkenbeck's great review of ERwin last month inspired me to reflect on less expensive alternatives we can use to crank out "quick and dirty" ER diagrams. Here's what I came up with. If you've got other ideas, please share them as a letter to the editor, tip, or mini-review. We'll all benefit! Also, in forthcoming issues, look for reviews of Embarcadero's ER/Studio and Sybase's PowerDesigner.

I suspect that almost all SQL Server Professional readers are familiar with ER diagrams, entity-relationship diagrams that are sometimes referred to as ERDs. For small projects, you probably sketch out the entities and relationships of your logical data model on a sheet of notebook paper—or even on a proverbial cocktail napkin. For larger ones, however (say, databases with 100+ tables—or databases you inherit), it's nice to have a "crow's foot" (or similar IDEF1X) diagram that you can just print out. You might want to thumb-tack it onto an appropriate empty section of cubicle wall, flash it at your boss, or even (gasp) use it as part of project documentation. Hence, the appeal of products such as ERwin, reviewed by Paul Munkenbeck in last month's issue.

Unfortunately, however, ERwin's current owner, Computer Associates, doesn't exactly give ERwin away, and not all organizations will choose to invest several thousand dollars in ERwin or competing products such as Embarcadero's ER/Studio, Sybase's PowerDesigner, or Popkin's System Architect. Indeed, why should you if all you want is a "quick and dirty" ER diagram? In this article, I'll show you how you can use SQL Server itself, Microsoft Access, or Visio 2000 Enterprise/2002 Professional to create logical data models that you can print out.

SQL Server's own Diagrammer

For SQL Server 7 or higher user databases (though not for master, model, or msdb for some reason), you can simply right-click on the "diagrams" feature in the Enterprise Manager hierarchy tree to launch the Create Database Diagram Wizard. (Another way to start most wizards, but not this one, is to click on the EM's "wizard" icon, the wand.) You select the tables to add (opting to have the wizard automatically select related tables if you so desire), and bingo—instant ER diagram (see Figure 1).

As you might expect, you can manually arrange the tables, modify the relationships, opt whether or not to display attributes, and so on. You can even add text annotations and, of course, print out your masterpiece. No, you don't have a lot of flexibility when it comes to importing, exporting, and moving EM diagrams around, but a SELECT * FROM dtproperties (for the database where you saved your diagram) gives you the clues you need to copy a diagram's "recipe" to another server, for example. You won't, however, be able to opt for different diagramming notations. You're "stuck" with EM Diagrammer's use of the key and infinity symbols to depict one-to-many relationships, for example. Commercial products let you choose among a variety of diagramming notations and methodologies.

Another SQL Server tool you can harness to help you generate ER diagrams is DTS. Remember, you can use DTS to get virtually any data or database into SQL Server. Once you've imported it (or just its schema), it's often a matter of just a few seconds to have EM's Diagrammer generate a diagram for you. You can even use DTS to get a database into Access—that is, move an Oracle8i database into Access, for example, bypassing SQL Server altogether. And don't forget that the SQL Server 2000 version of DTS also supports XML. The ReadFile and WriteFile transformations give you a way to import and export XML files as part of your transformations, and the parallel data pump task lets you process XML's hierarchical recordsets. (For more on using XML with SQL Server, see Tom Moreau's March 2001 column, "Feeding XML to Stored Procedures," and consider purchasing Graeme Malcolm's excellent new book, Programming Microsoft SQL Server 2000 with XML [ISBN 0-7356-1369-9].)

What about Access?

If you use a sample Microsoft database like Northwind and then select Tools | Relationships (see Figure 2), you'll probably think Access's Relationships designer is even faster than Enterprise Manager's.

Figure 3 illustrates another example of Access's prowess, here modeling SQL Server's Analysis Services FoodMart2000 database. Like SQL Server, there are limitations to what you can do with the diagrams. You can print an Access "relationships" diagram, but you can't change the notation (a "1" and an infinity symbol for 1:M relationships, for example) or add annotations.

What you don't realize until you try to generate a diagram for a non-Microsoft sample database is that "someone" has to manually set up all of the relationships (EM automates the process by making intelligent "guesses" based on primary and foreign keys). Granted, Access's Relationships designer is reasonably flexible (you can define relationships as 1:1, 1:N, or 1:M and specify whether referential integrity is to be enforced), and we've been able to save Access Relationships at least since Access 2.0, but the Relationships designer isn't smart enough to figure out the relationship itself.

The Relationships designer and menu in Access 2002/XP (see Figure 4) seems to be the same as it is in Access 2000. The main feature that potentially extends its features is Access XP's ability to import and export XML.

What about Visio 2000?

Visio 2000 Enterprise Edition SR-1, which is included in the MSDN Universal Subscription as disks 672 and 673, is no longer available commercially. If you're not an MSDN subscriber and want a copy of any version of Visio 2000, you can either try to find an unsold copy in the channel or buy Visio 2002 and then "downgrade" to Visio 2000. (For information about Visio 2000's SR-1, you can read "Visio 2000: How to Obtain the Visio 2000 Service Release 1 [SR-1] Update" at https://support.microsoft.com/support/kb/articles/Q263/7/21.ASP and/or Q264400, "What Is Fixed in the Service Release 1 [SR-1] Update for Visio 2000 [All Editions].") Actually, you might not need EE to do database diagramming, since Visio 2000 Professional lets you create six types of database diagrams: Bachman, Chen ERD, Database Model, Express-G, Martin ERD, and ORM Diagramming.

Visio 2000 EE supports seven types of diagrams: Bachman, Chen ERD, Database Model Diagram, Express-G, Martin ERD, ORM Source Model, and ER Source Model.

The Bachman, Chen, Martin, DBM, and ER Source model diagrams are undoubtedly familiar to most DBAs as variations on the ER theme of depicting tables with annotated lines between them. Less familiar, perhaps, is Express-G, a graphical notation for information models. (See www.steptools.com/support/stdev_v8_docs/devtools/devtools_9.html for information about Express and its support of STEP. STEP, the Standard for the Exchange of Product Model Data, is a comprehensive ISO standard [ISO 10303] that describes how to represent and exchange digital product information. STEP was born in 1983, and was based on previous national efforts such as IGES, VDAFS, SET, CAD*I, and PDDI.) ORM refers to Dr. Terry Halpin's Object Role Modeling methodology, which Visio inherited when it acquired InfoModeler. See www.orm.net/halpin.html for more about ORM.

Visio, of course, is a commercial modeling product, so you'd expect it to offer more than SQL Server's EM Diagrammer or Access's Relationships designer, and indeed it does. In addition to creating databases or data models in Visio Professional or EE, you can also reverse engineer database models from existing databases. You can also import ERwin ERX diagrams (but not the newer ER1 models—see the "ERwin Filenames" sidebar).

To test Visio 2000, I decided to try to reverse engineer a data model. I opted to create a new Database Model Diagram (you can only reverse engineer three of the seven diagrams: Database Model Diagram, ER Source Model, and the ORM Source Model because the others are "left over" shape-only stencils from earlier versions of Visio) and was rewarded by Visio 2000 EE's Reverse Engineer Wizard. The first step is to select the Visio database driver for the source DBMS, and the wizard will guide you through the process of configuring and testing the ODBC data source if you need to.

Next, you check the boxes for the type of information you want to extract (see Figure 5).

Then you check the tables (and views, if any) that you want to extract (fortunately, there's a Select All button). If you checked Stored Procedures, you'll see a screen asking you to check the procedures that you want to extract (again, there's a Select All option to extract them all).

The wizard extracts the selected information and displays notes about the extraction process in the Output window and lists the tables in the Tables window (see Figure 5).

At this point, the human work begins. You have to drag the tables you want from the Tables window onto the drawing page and manually create the relationships using the icons from the toolbox on the left side of Figure 5. Visio is a rich product and gives you all kinds of options such as keeping your drawing and the original database schema in sync. When you save a Visio 2000 EE drawing, you can opt to have your VSD file saved for printing, for PPT output, or for HTML/GIF output. For information about ways to include Visio 2000 diagrams in other docs, refer to www.microsoft.com/technet/prodtechnol/visio/maintain/spot1.asp.

Visio 2000 Professional and EE also support UML 1.2 diagrams and reverse engineering from Visual Studio, though you can only generate Visual Studio code from UML (Unified Modeling Language) models with the EE. If you want to use UML with Visio 2000, you should download the 240KB Microsoft Visio 2000 UML Shapes Update from https://office.microsoft.com/downloads/2000/visUMLShapesddl.aspx. The download replaces some of your existing Visio 2000 Professional edition UML shapes with shapes you can manually resize. You might also want to read Frank Sternberg's white paper on "Iterative Development Using UML and MS Visio 2000 Enterprise Edition" at www.microsoft.com/technet/prodtechnol/visio/maintain/visiodev.asp?frame=true.

The best sources of current Visio 2000 information are www.microsoft.com/office/previous/visio/default.htm and the 20 or so newsgroups (including ones in French, German, Dutch, Spanish, and Japanese) at public.microsoft.com/visio…. You can search for Microsoft Online Seminars dealing with Visio. You can also subscribe to Visio's bi-monthly Visio SmartPages newsletter from Microsoft and work through several online database-related tutorials at www.microsoft.com/presspass/guides/visio/professional/enttutorials/defaultd.asp.

Interesting bit of trivia: Microsoft BizTalk customers and developers might have noticed that the BizTalk Orchestration Designer is based on Visio.

Visio 2002 Professional

Microsoft began shipping Visio 2002 Standard and Professional editions earlier this year for $199 U.S. and $499 U.S., respectively. There's no downloadable evaluation version available for these products, but U.S. and Canadian customers can order a 30-day trial version CD for $7.50 ($10 Canadian). There's no Enterprise edition because "the advanced database and software modeling solutions previously found in Visio 2000 Enterprise Edition will be delivered in Microsoft Visual Studio. NET," presumably as part of the recently announced VS.NET Enterprise Architect (https://msdn.microsoft.com/vstudio/productinfo/overview/eaoverview.asp). Here's the main URL for info about Visio 2002: www.microsoft.com/office/visio/evaluation. The main URLs for Visio programmers and developers are https://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28000456 and https://www.microsoft.com/technet/prodtechnol/visio/default.mspx.

I'd been unsuccessful installing the Visio 2002 Marketing beta on either 1) any of three different Windows 98 systems or 2) a Windows 2000 system that had Office XP installed, but the shipping release product did install on both. I wouldn't, however, recommend using it on a Win98 box unless you're desperate. (It brings new meaning to the notion of "slow.")

Visio 2002 Professional does provide you with features that let you generate ER diagrams, but, instead of Visio 2000 Professional's six, or Visio 2000 EE's seven, you only get three choices: Database Model Diagram, Express-G, and ORM Diagram (see Figure 6). Basically, the Bachman, Chen, and Martin ER diagrams have been subsumed into the Database Model Diagram, but Visio 2002 Professional does provide all of the shapes you need to create logical models using today's popular ER and IDEF1X notations. (IDEF1X is a method for designing relational databases with a syntax designed to support the semantic constructs necessary in developing a conceptual schema. For more about IDEF1X, see www.idef.com/idef1x.html or buy Thomas Bruce's 1992 classic Designing Quality Databases With IDEF1X Information Models [ISBN 0932633188].)

As with Visio 2000 Enterprise, I was able to successfully reverse engineer pubs and generate a Database Model Diagram. Unlike Visio 2000 Enterprise, however, I couldn't generate DDL (a big minus, IMHO), nor could I reverse engineer databases from DDL scripts. (These features will be offered in what amounts to a Visio 2002 Enterprise, which apparently will be available in the form of the Enterprise Edition of Visual Studio .NET. VSEA is not available in the general release of .NET Beta 2, but was distributed in mid-July to TechEd attendees and MSDN Universal subscribers. And speaking of Visual Studio, it too offers developers a "quick and dirty" ER diagram generator of sorts. In VB6, for example, you can use the Data Environment's data diagrams, and a similar functionality is available in VS.NET via the Data Form Wizard, which actually stores the schema as a VSD file.)

New in Visio 2002 Professional, however, is XML support, both in the guise of an optional new file format (VDX) for Visio diagrams and for reverse engineering. See the June white paper "XML for Visio Scenarios," which illustrates how XML for Visio can be used to extract Visio data for use in solution development, data analysis, text localization, Web publication, and database interoperability for more on Visio's XML.

Here are some additional resources for Visio 2002:

  • COM Add-in Designer for Visio 2002.
  • Visio MVP Graham Wideman's Unofficial Visio (still mainly Visio 2000) FAQ site at www.diagramantics.com/diagenvs/visio/faq/index.htm. (I also highly recommend Graham's extremely useful Visio 2002 Developer's Survival Pack, ISBN 1-55212-682-X. Owners can download a 7MB ZIP of samples and tools.) Note from Graham: "One currently missing opportunity is that, though Visio in general is programmable by Automation, the DB and UML modeling solutions have not been. So, you can use Automation to visit and read the shapes of a UML or DB diagram, but that doesn't get you very far, as there's much more information in the model than in the shapes. So counter to expectations, you can't write your own code to do something (useful) automatically with a Visio DB or UML diagram."
  • Short animated illustration of how to use database modeling and UML features: www.microsoft.com/office/visio/evaluation/techtour/page4.htm.

Conclusion

This just represents tools that I use to generate low-cost ER diagrams when I'm in a hurry or at the site of a client who doesn't have one of the high-end commercial tools. If you know of others you think should be included, please let us all know.

Link to www.microsoft.com/office/visio

(Adapted from the June 20 issue of Karen's SQL eXTRA eNewsletter #84. Subscribe at www.FREEeNewsletters.com.)

Perhaps I'm reading too much between the lines here at TechEd, but it seems to me that Microsoft is favoring its own database modeling software—software acquired when it bought Visio (which had itself acquired InfoModeler)—over competing tools such as Computer Associates' ERwin. Indeed, Visio's ORM also seems poised to supplant Visual Modeler, a subset of Rational Rose that ships with Visual Studio 6.0 Professional and Enterprise editions.

Here's what Microsoft was saying as of June 18: "Visual Studio .NET Enterprise Developer (VSED) will include functional testing tools for XML Web services, full Unified Modeling Language (UML) 1.2 software modeling, industry-leading database modeling (spanning conceptual, logical, and physical models), and enterprise frameworks and templates—a set of technologies that enable software architects to create an application recipe (ingredients and instructions) and provide it directly to developers within the Visual Studio .NET environment. The enterprise features aren't provided in the public Visual Studio .NET Beta 2 release due to timing issues."

So what's ORM and where did it come from? Object Role Modeling (ORM) is a methodology for designing and documenting databases, and www.orm.net is "the official site for conceptual data modeling." As the site's owner, Dr. Terry Halpin, now program manager in Microsoft's Database Modeling, Enterprise Framework and Tools Unit, explains, "Object Role Modeling (ORM) is a method for designing and querying database models at the conceptual level, where the application is described in terms easily understood by non-technical users. In practice, ORM data models often capture more business rules, and are easier to validate and evolve than data models in other approaches." This site features a number of technical papers and articles on ORM, as well as other approaches such as the UML. Permission has been obtained from the relevant publishers to reproduce these publications on this Web site, and to make them freely downloadable.

Halpin, whose new book Information Modeling and Relational Databases has just been published (ISBN 1-55860-672-6), formalized the Object Role Modeling notation and has authored four books and more than 90 technical papers.

Related: The former ORM tool known as VisioModeler (essentially InfoModeler) is now freely available as an unsupported product from Microsoft Corporation (as a 26.5MB download). Models developed in VisioModeler should be able to be exported to Microsoft's current and future ORM solutions, which are based on the Visio drawing engine. (Visio MVP Graham Wideman recommends using this rather than the incomplete version of ORM that shipped with Visio 2000 Enterprise.)

See https://msdn.microsoft.com/library/default.asp?url=/downloads/list/visio.asp for the 26.5 MB download.

  • Conceptual database design is a process that might be described as "getting your head around" the data you need to model. The result is a high-level model of the data that you can use to describe the database to high-level managers and end users, for example.
  • Logical database design is the process of constructing a model according to the conventions of a specific modeling methodology, but independent of a particular DBMS and physical considerations.
  • Physical database design is the process of producing a description of the database (often in terms of DDL scripts, for example) in terms of secondary storage. It will include descriptions of the base tables and views, the indexes, constraints, and so on.

ER1 and ERX are both filename extensions associated with ERwin (Entity Relationship for Windows), a popular database modeling program originally created by Logic Works, Inc., and subsequently purchased by Platinum Technology, which was itself acquired by Computer Associates (www.cai.com). After a long wait (the previous version, ERwin 3.52, hadn't been updated since 1998), Computer Associates finally released ERwin 4.0.

One of the outputs of all of these programs is an ER diagram that graphically illustrates database tables (entities) and the relationships (one-to-one, one-to-many, or many-to-many) between them. At any rate, if you run across references to ER1 or ERX, they're probably referring to ERwin files:

  • ER1—Standard ERwin diagram file format.
  • ERX—Text-based version of ERwin diagram file format. The text-based ERX file format is designed to transfer model data and comments between ERwin and other tools.

Those aren't the only types of files ERwin creates. Others include:

  • ERT—ERwin diagram file saved as a template.
  • ERV—ERwin diagram saved as an Intersolv PVCS archive file. You must have Intersolv's PVCS version 5.1 or later installed to open an ERV file. (Intersolv is now owned by Merant, www.merant.com.)
  • EAX and BPX—Files with these extensions are associated with BPwin, the business process model tool.
  • ERS—ERwin physical database schema script text file.
  • SQL—SQL DDL (Data Definition Language) schema script text file.
  • SML—Structured Modeling Language text file. Similar to a SQL data file used to store information about an ERwin model in text format.
  • CMT—Text file containing logical entity/attribute and physical table/column definitions (comments).
  • DF (SQL)—A special version of SQL files used for Progress Software databases.

Conceptual, Logical, and Physical Models>

To find out more about Microsoft SQL Server Developer and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the August 2001 issue of Microsoft SQL Server Developer. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Developer is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.