Database Modeling with Microsoft Visio for Enterprise Architects

427 Pages·2003·13.64 MB·English

by  Terry Halpin

toc

Database Modeling with Microsoft Visio for Enterprise Architects
by Terry Halpin, Ken Evans, Patrick Hallock and Bill MacLean ISBN:1558609199
Morgan Kaufmann Publishers © 2003 (426 pages)
This text explains how to use the database modeling solution in VEA, provides an
introduction to data modeling using Object-Role Modeling (ORM), offers practical
advice on managing database projects, and much more.
Table of Contents
Database Modeling with Microsoft Visio for Enterprise Architects
Foreword
Preface
Part 1 - Overview of Database Modeling and the Database Modeling Tool
Chapter 1 - Introduction
Chapter 2 - Database Modeling
Chapter 3 - Getting Started
Part 2 - The Conceptual Modeling Solution (ORM)
Chapter 4 - Object Types, Predicates, and Basic Constraints
Chapter 5 - ORM Constraints
Chapter 6 - Configuring, Manipulating, and Reusing ORM Models
Chapter 7 - Mapping ORM Models to Logical Database Models
Chapter 8 - Reverse Engineering and Importing to ORM
Chapter 9 - Conceptual Model Reports
Part 3 - The Logical Modeling Solution (ER and Relational)
Chapter 10 - Creating a Basic Logical Database Model
Chapter 11 - Generating a Physical Database Schema
Chapter 12 - Editing Logical Models—Intermediate Aspects
Chapter 13 - Editing Logical Models—Advanced Aspects
Chapter 14 - Reverse Engineering Physical Schemas to Logical Models
Chapter 15 - Logical Database Model Reports
Part 4 - Managing Database Projects
Chapter 16 - Change Propagation and Round Trip Engineering
Chapter 17 - Other Features and Best Practices
Glossary and ORM Notation
Further Resources
Index
List of Figures
List of Tables

file:///D|/1/7052final/toc.html [06.12.2007 23:44:20]
backcover

Back Cover
Database Modeling with Microsoft Visio for Enterprise Architects explains how to use the database
modeling solution in VEA, providing information unavailable anywhere else. Although primarily
focused on tool features, the book also provides an introduction to data modeling using Object-Role
Modeling (ORM), a conceptual approach to data modeling that is gaining increased adoption in
industry. This book also includes practical, tool-agnostic advice on managing database projects.
Written by four top ORM veterans, including one of the creators of VEA, this book provides
unsurpassed insight and guidance to the deep functionality of this solution.
Explains how to model databases with Microsoft Visio for Enterprise Architects (VEA), focusing on
●
tool features
Provides a platform-independent introduction to data modeling using both ORM and Entity
●
Relationship Modeling (ERM), and includes practical advice on managing database projects
About the Authors
Dr. Terry Halpin is a professor at Northface University and is the world’s foremost authority on ORM.
He has led database research teams at several companies, including Visio Corporation and Microsoft
Corporation, where he worked on the conceptual and logical database modeling technology in
Microsoft Visio for Enterprise Architects. His publications include over 100 technical papers and five
books.
Ken Evans has taught and applied ORM in English and French for 10 years. His know-how in data and
process modeling and complex systems management comes from over 30 years in industry,
including international jobs with IBM, EDS, Honeywell Controls, and Plessey and clients among the
Fortune 500.
Patrick Hallock, M.S., is the founder of InConcept, a consulting firm, and teaches object modeling
throughout the United States. He has been in the industry for 30 years, focusing on database design,
with an emphasis on ORM.
Bill MacLean, CPA, is an independent consultant and teacher who has worked with relational
databases for over 15 years, and consulted in database design for the last 9. He believes that the
purpose of a data model is to turn business requirements into buildable specifications.

file:///D|/1/7052final/backcover.html [06.12.2007 23:44:21]
Database Modeling with Microsoft Visio for Enterprise Architects

Database Modeling with Microsoft Visio for Enterprise Architects
Terry Halpin Northface University
Salt Lake City, Utah
Ken Evans Perpetual Data Systems Lincolnshire,
UK
Patrick Hallock In Concept, Inc.
Lake Elmo, Minnesota
Bill MacLean Orthogonal Software Corporation Scottsdale,
Arizona
MORGAN KAUFMANN PUBLISHERS
AN IMPRINT OF ELSEVIER
Senior Editor: Lothlórien Homet
Editorial Assistant: Corina Derman
Publishing Services Manager: Simon Crump
Project Manager: Sarah Manchester
Full-Service Provider: Kolam USA
Cover Design: Ross Carron
Interior Printer: The Maple-Vail Book Manufacturing Group
Cover Printer: Phoenix Color
Cover Photo: Nino Mascardi/The Image Bank
Designations used by companies to distinguish their products are often claimed as trademarks or registered trademarks. In all instances in
which Morgan Kaufmann Publishers is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however,
should contact the appropriate companies for more complete information regarding trademarks and registration.
Microsoft in a registered trademark of the Microsoft Corporation.
No part of this publication may be reproduced, stored in retrieval system, or transmitted in any form or by any means— electronic,
mechanical, photocopying, scanning, or otherwise—without prior written permission of the Publisher.
Permissions may be sought directly from Elsevier's Science and Technology Right Department in Oxford, UK: phone: [phone],
fax: [phone], e-mail: [email protected]. You may also complete your request on-line via the Elsevier homepage
(http://elsevier.com), by selecting "Customer Support" and then "Obtaining Permissions."
Morgan Kaufmann Publishers
An imprint of Elsevier
340 Pine Street, Sixth Floor
San Francisco, CA 94104-3205
http://www.mkp.com
Copyright © 2003 Elsevier (USA).
All rights reserved.
07 06 05 04 03 5 4 3 2 1
Library of Congress Control Number: 2003104297
1-55860-919-9

file:///D|/1/7052final/LiB0001.html [06.12.2007 23:44:21]
Foreword

Foreword
I am delighted to write the foreword for this important and useful book on Microsoft's product for database modeling and design. When
Microsoft acquired Visio Corporation a few years ago, it gained not only a suite of two-dimensional drawing technologies, but also state-of-
the-art technologies for both database and software modeling that could be extended and adapted to blend harmoniously with Microsoft
software environments.
These modeling solutions are now available in Microsoft Visio for Enterprise Architects (VEA), which is incorporated in the Enterprise
Architect edition of Visual Studio.NET. While the software modeling solution focuses on the Unified Modeling Language (UML), the database
modeling solution uses Object-Role Modeling (ORM) for conceptual analysis and varieties of Entity-Relationship (ER) and relational
modeling for the logical design of relational tables. The functionality of the database modeling solution is quite deep, and this book provides a
thorough and authoritative coverage of this functionality. To provide both an overview of the database modeling approaches themselves and
a detailed treatment of Microsoft's database modeling solution within a book of economic length, the authors have omitted coverage of the
UML solution— a topic addressed by other books.
Like most other database design tools, Microsoft's VEA product supports ER and relational modeling, allowing these logical designs to be
forward engineered to physical database schemas for implementation in several popular database management systems, including Microsoft
SQL Server, Microsoft Access, IBM DB2, and Oracle. The tool also supports reverse engineering from these physical schemas to logical
models.
What really sets VEA apart from most other database design tools, however, is its deep support for the ORM conceptual modeling approach,
which offers many advantages over traditional approaches. ORM can capture many more business rules in both graphical and high-level
textual languages, where they can be easily validated by domain experts using the tool's automatic rule verbalization feature, as well as
sample fact populations. Moreover, ORM's attribute-free approach allows all relevant information to be verbalized naturally in sentences,
while making information models much more stable in the face of changes to the business.
To derive optimum benefit from VEA's database modeling solution, I encourage the reader to consider the ORM approach for conceptual
analysis, where the data model of the business can be easily validated with the business experts who best understand the application
domain. Such domain experts are often unfamiliar with and uninterested in lower level design details such as foreign keys and check
clauses, but these experts can readily understand ORM models expressed clearly in plain language. The VEA tool is capable of forward-
engineering ORM models to logical models and then onto physical database schemas and can also reverse engineer physical schemas
directly to ORM models. Round-trip engineering is also supported, so that changes made at one level can be automatically propagated to
other levels. For readers unfamiliar with the ORM approach, the authors have included a detailed overview of this methodology.
The book's main author, Dr. Terry Halpin, is the world's foremost authority on ORM and also worked as program manager for the database
modeling solution in VEA before returning to academia at Northface University, which focuses its curricula on model-driven database and
software development. While we miss Terry's expertise at Microsoft, we are delighted that Terry has documented within this book much of
his knowledge and insights into both database modeling and the VEA database modeling solution.
The co-authors, Ken Evans, Pat Hallock, and Bill MacLean, are all experienced consultants in database modeling and implementation. As
well as working for many years as modeling and database practitioners, they are also intimately familiar with VEA's database modeling
solution. They know its strengths and current weaknesses and share many of their practical insights and hints on how to best use the tool. In
addition, they provide practical advice on how to manage database projects in industrial settings.
While the functionality of VEA's database modeling solution is deep, not all of it is easy to discover. Since the first release of VEA, there has
been a need for a publication that covers this functionality in detail. Not only is this the first book to comprehensively fill this gap, but it is
written by experts in a style that is easily understandable. I heartily recommend this book to any practitioner or student interested in the
science and art of database modeling.
Chandrika Shankarnarayan
Technical Product Manager
Microsoft Visual Studio for Enterprise Architects

file:///D|/1/7052final/LiB0002.html [06.12.2007 23:44:21]
Preface

Preface
This book explains how to model databases with Microsoft Visio for Enterprise Architects (VEA). Although primarily focused on tool features,
this book also provides an introduction to data modeling and includes practical advice on managing database projects. We have all used
VEA extensively in industrial database projects, and the principal author worked on the design of VEA's database modeling solutions.
Unlike most other database design tools, VEA allows you to model data at a truly conceptual level, using a fact-based approach known as
Object-Role Modeling (ORM). For conceptual information analysis, ORM has many advantages over other methods such as Entity-
Relationship (ER) modeling. ORM's attribute-free approach facilitates model validation by verbalization and population, and it helps to
minimize the impact of changes to the application domain. Moreover, ORM diagrams can graphically display many more business rules than
other approaches.
ER diagrams are also useful because they summarize the main features of a data model in a more compact form, and their structures are
more closely related to those implemented in physical database schemas. The VEA tool enables you to display logical database models in a
variety of notations, including IDEF1X, Information Engineering, and pure relational notation.
You can use the VEA tool to get the best of both conceptual modeling and logical modeling approaches by specifying the conceptual data
model in ORM notation and then automatically transforming it to a fully normalized logical data model in ER or relational notation. You can
also create a logical data model directly.
Once you have a logical data model, you can automatically generate a physical database schema for a relational DBMS such as SQL
Server, DB2, or Oracle. The VEA tool also supports reverse engineering from physical databases and even round-trip engineering between
conceptual, logical, and physical levels.
Given the considerable power and depth of VEA's database modeling solutions, there is much to learn in order to fully exploit its modeling
features, and this is the main emphasis of this book. While some basic background in relational database modeling is assumed, a simple
introduction to the ORM conceptual modeling approach is included so that you can enjoy the benefits of this approach even if it is new to
you. A thorough examination of the ORM methodology is provided in a separate book (Halpin, 2001).
The book has four main parts. Part 1 provides an overview of the database modeling features in VEA, summarizes the different tasks
involved in database modeling, explains how to install the product and get started with the Visio interface, and demonstrates how to forward
engineer a data model from conceptual to logical to physical levels using a simple example. You should read Part 1 first.
Part 2 focuses on the conceptual modeling solution (ORM). It discusses how to construct an ORM model from object types, predicates, and
constraints; how to configure, manipulate, and reuse ORM models; how to map ORM models to logical data models; how to reverse engineer
physical database schemas to ORM; and how to generate reports that document ORM models. We recommend that you read Part 2 before
Part 3. If you want to first explore the logical modeling solution, you may skip Part 2 until you feel ready to reap the benefits of conceptual
modeling.
Part 3 examines the logical data modeling solution in detail. It shows how to create and edit a logical database model and how to generate a
physical database schema from it. It also explains how to reverse engineer a physical database schema to a logical data model and how to
generate reports that document the logical model.
Part 4 deals with advanced topics, and should be read only after you have digested Part 2 or Part 3. It examines how to deal with changes at
any level (conceptual, logical, or physical) and how to perform round-trip engineering while keeping your models synchronized. It also
discusses useful ways to customize and extend your models and provides practical advice on managing your database projects.
The Glossary summarizes the terms and ORM notations used by the VEA database modeling solutions. Use this to quickly check on the
meaning of key concepts and the graphical symbols used in ORM models and logical database models.
The Further Resources section lists related texts and resources available in print and online. The book's website includes sample models
that are used for discussion purposes at various stages in the book. You may freely download these models for your own personal use.
Acknowledgments
At Morgan Kaufmann Publishers, our editor, Lothlórien Homet, has been a delight to work with from the outset. Her patient professionalism
file:///D|/1/7052final/LiB0003.html (1 von 2) [06.12.2007 23:44:22]
Preface
and thoughtful consideration are much appreciated. Several passages in the book have benefited from detailed feedback by Dr. Andy
Carver. Three consultants at InConcept Inc. (Necito dela Cruz, Dick Barden, and Patricia Schiefelbein) also provided valuable suggestions
and reviews. Terry and Patrick thank their respective wives, Norma and Linda, for being so understanding and supportive during the writing
task.
Finally, the development team at Microsoft is to be congratulated for creating such a productive tool for database modeling. If you are a
Microsoft Developer Network (MSDN) Universal subscriber, the VEA tool is included in your subscription as part of Visual Studio .NET
Enterprise Architect. We hope that this book will encourage others to exploit the vast potential of this database modeling tool.
Terry Halpin
Patrick Hallock
Ken Evans
Bill MacLean

file:///D|/1/7052final/LiB0003.html (2 von 2) [06.12.2007 23:44:22]
Part 1: Overview of Database Modeling and the Database Modeling Tool

Part 1: Overview of Database Modeling and the Database Modeling Tool
Chapter List
Chapter 1: Introduction
Chapter 2: Database Modeling
Chapter 3: Getting Started
This book has four main parts.
Part 1 provides an overview of the database modeling features in Visio for Enterprise Architects (VEA) and summarizes the different tasks
involved in database modeling. Part 1 comprises three chapters.
Chapter 1 describes the scope of database modeling and what you can do with VEA. You learn how to best use this book and how to
interpret its format conventions.
Chapter 2 explains the purpose of database modeling and the four different levels for working with data: external, conceptual, logical, and
physical. It briefly reviews the main concepts underlying database modeling at the conceptual and logical levels.
Chapter 3 explains how to install the product and get started. You learn to use the Visio user interface and help facility. A worked example
introduces you to the process of creating a data model and forward engineering it.
Parts 2 and 3 focus respectively on the conceptual and logical modeling solutions, and Part 4 discusses advanced topics such as round-
trip engineering and model management.

file:///D|/1/7052final/LiB0004.html [06.12.2007 23:44:22]
Chapter 1: Introduction

Chapter 1: Introduction
If you have a well-designed database management system, you have the keys to the kingdom of data processing and
decision support.
Dr. E. F. Codd
Overview
This book will help business analysts, database designers, and database administrators to use the powerful database modeling facilities
within Microsoft Visio for Enterprise Architects (VEA) to create and manage well-designed databases.
Process analysts, project managers, application development managers, and business experts will also benefit by understanding why data
models are used and how they are built and maintained.

file:///D|/1/7052final/LiB0005.html [06.12.2007 23:44:23]
1.1 Why Read This Book?

1.1 Why Read This Book?
Practical. This book offers a practical guide to VEA's powerful database modeling solution. Step-by-step instructions and worked
examples help you learn how to specify, design, and build databases that accurately reflect your application domain.
You learn to reverse engineer a database into natural language facts. This function makes it easy for domain experts to check the database
for conformance to business requirements.
You learn to model business rules as conceptual constraints or derivation rules and propagate them to a physical database. When you store
business rules in a database, you need fewer lines of procedural code, which reduces development costs.
You learn to use domain-based data types. VEA generates physical data types for all columns that draw their values from a conceptual
domain. This is a great timesaver and helps to ensure database consistency.
Clear. The examples in this book are illustrated by screen shots of menus, dialog boxes, and more. The glossary summarizes modeling
terms and graphical symbols used.
Flexible. You can choose to begin with Object-Role Modeling (ORM), or you can create a logical model directly using Entity-Relationship
Modeling (ERM).
Written by modeling experts and practitioners. The principal author, Dr. Terry Halpin, formalized the ORM methodology and
worked as program manager at Microsoft for the database modeling solutions in VEA. Ken Evans, Pat Hallock, and Bill Maclean have
managed and developed dozens of industrial projects involving conceptual, logical, and physical database design. Collectively, the author
team has almost a century of practical experience in systems and modeling.
Unique. This book is the only through and practical guide to the comprehensive and powerful database modeling solution in Microsoft Visio
for Enterprise Architects.
In summary, this book shows you how to use VEA's powerful database modeling solution to improve database quality and reduce
development costs.

file:///D|/1/7052final/LiB0006.html [06.12.2007 23:44:23]