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 .

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.

What Can You Do with Visio for Enterprise Architects?

In addition to the database modeling solution overviewed in the next section, VEA provides several stencils and templates for specific tasks that you may find useful in developing and managing applications. It is also a superb tool for general purpose drawing. The main " non-database " features of interest for application development are summarized below.

Software engineering. You can use the UML Model Diagram solution to model object-oriented applications, with forward and reverse engineering between class models and programming code. You can draw UML diagrams, document data flows, and component architectures and create user interface designs such as prototypes of dialog boxes, menus , toolbars , and wizards to explore user interaction.

Network engineering. You can forward and reverse engineer directory structures. When connected to a local area network, VEA can automatically discover an existing network and display an appropriate equipment symbol complete with serial number for each discovered network object.

Project administration. You can use the Project Schedule solution to document project information. You can use the Organization Chart to document organization hierarchies.

Website design. You can use the Web Diagram solution to design a conceptual web-site and to map an existing website.

Process and quality diagrams. VEA's Flowchart category has templates for Total Quality Management (TQM) diagrams, workflows, cross-functional flowcharts, Specification and Description Language (SDL) diagrams, and more.

General purpose drawing. VEA includes the drawing functions of Microsoft Visio Professional, which makes it easy to enhance your models by combining many kinds of shapes and documents. You can simultaneously open as many stencils as you like.

Some other possibilities.
Multi-application files. You can reuse non-VEA files, for example, by adding an Excel spreadsheet to your database diagram, adding an organization chart to your database project, or adding a voice recording of a domain expert's explanation.

Run a virtual modeling project. You can open or save documents stored on a SharePoint team Web site and have online discussions.

What Can You Do with the Database Modeling Solution?

Database modeling transforms the information structures of an application domain into data structures within a physical database. This process is normally divided into separate modeling phases. Figure 1-1 shows how the different types of model relate to each other and to each of the database modeling tasks on the right. You often find seemingly conflicting and vague definitions in an application domain, which is why it is represented by a cloud. Models are precise, which is why they are represented by rectangles. Your first step is to create an orderly conceptual model from the seeming chaos of the application domain.

Figure 1.1: Database modeling in context.

Each database modeling task has a different purpose and requires different knowledge. Think of a database modeling task as a role to be played by a person with appropriate skills rather than as a job title. For example, the business analyst role is people-centered and requires skills in interviewing, presentation, and semantic modeling. In contrast, the database design role is technology-centered and requires extensive and up-to-date technical knowledge. To do both tasks, you need the skills for both roles.

Table 1-1 shows the meaning of the database modeling task names used in this book. Each task has a different purpose.

Table 1-1: Database modeling tasks.

Database Modeling Task	What the Task Does
Business analysis	Creates a conceptual model of an application domain, using terms and language easily understood by business people.
Logical data design	Transforms a conceptual model into a set of relational tables and associated constraints.
Physical database design	Creates an SQL schema for a specific database management system, including physical data types and indexes.
Performance design	Tunes a physical model for optimum performance on the specific software and hardware platform.

The following paragraphs outline four typical database modeling tasks: Each of the examples summarizes a step in the forward engineering process.

Task Business Analysis

The business analyst and domain expert collaborate to create a semantically accurate conceptual model of an application domain, in terms easily understood by the domain expert. Object-Role Modeling (ORM) is best for creating a comprehensive definition of an application domain and its business rules.

Although pure Entity Relationship (ER) modeling can be used to model at the conceptual level, the versions of ER supported by VEA are more aligned with relational models than conceptual models. For example, a many-to-many relationship between entity types must be modeled indirectly by introducing an intersection entity type. Because entity types in the ER solution correspond to relational tables, models created with the ER solution are more accurately described as logical models rather than conceptual models. Hence, we often refer to such ER models as logical data models.

Task Logical Data Design

The business analyst and logical data designer create a normalized data model that accurately represents the conceptual model with tables and columns uniquely named. ER modeling is good for summarizing database table structures. If an ORM conceptual model was specified, the tool can use this to automatically create a fully normalized logical data model. ORM constraints are used to generate check clauses, stored procedures, triggers, and more. VEA applies domain-based data types from your conceptual model to the generated columns. You can also create a logical data model directly, without doing an ORM model first. You can choose relational notation or various ER notations (e.g., IDEF1X). If you don't start with ORM, you have to do many things yourself such as manually normalize the table structure, check all your column names for consistency, and manually cross-check data types on those columns that are drawn from a common domain.

Task Physical Database Design

The logical data engineer and physical data engineer prepare a logical data model for use with a specific relational database management system (RDBMS) such as Microsoft SQL Server or IBM DB2. VEA helps by providing built-in, product-specific data types, triggers and stored procedures, database drivers, data source set-up tools, and more. To see if your physical model conforms to the rules of a specific database, simply select the relevant database driver (three mouse clicks) and run an error check (three mouse clicks).

Task Performance Design

Database performance is affected by many factors such as RDBMS product characteristics, operating system characteristics, hardware characteristics, and application type (e.g., query/update pattern). Since database performance is implementation specific, it is not discussed in this book. You should seek product performance information in the relevant product manuals.

Caution

When you change a database schema, you change what the database means. What may appear to be a "just a simple technical change" could have unintended semantic consequences. You should carefully check the semantic implications of each change to your database schema. Fortunately, ORM reports make it easy to check the semantic consequences of schema changes.

For conceptual modeling, VEA supports ORM via its ORM Source Model solution. With ORM, you specify what your database means before defining how your database is to be implemented. Focusing on semantics before system design helps to minimize specification errors and reduce development costs. The ORM Source Model solution is explained in Part 2.

For logical modeling, VEA supports ER and pure relational modeling, using its ER Source Model and Database Model Diagram solutions. The ER Source Model and Database Model Diagram solutions are explained in Part 3.

VEA makes it easy to design an application-neutral conceptual model that can be used to create and manage several different physical databases. The three shaded shapes in represent VEA's three modeling components . The database modeling tasks on the right cross-refer to Figure 1-1. The fourteen numbered arrows represent database modeling sub-tasks. Arrow 8 is dotted because this route is suitable only for creating models that you do not wish to reuse when building other models. The arrow functions are outlined in Table 1-2. The arrows are numbered for ease of reference. The number sequence is not intended to imply an activity sequence.

Table 1-2: VEA sub-task summary.

Arrow	Function	Chapters
1	Review an ORM conceptual model.	9, 17
2	Create a conceptual model using ORM.	10, 13
3	Create a logical model using relational or ER notation.	Part 3
4	Review an ER model.	15, 17
5	Reverse engineer a physical schema into an ORM conceptual model. Update an ORM conceptual model to reflect changes in a physical schema.	8
6	Reverse engineer a logical model into an ORM conceptual model. Update an ORM conceptual model to reflect changes in a logical model.	7
7	Transform an ORM conceptual model to a logical model in fifth normal form. Update a logical model with changes to an ORM conceptual model.	7
8	Design a logical model directly. Update a logical model with changes to an application domain.	10
9	Review a logical model with domain experts.	15, 17
10	Generate a logical model from an ER model. Update a logical model with changes to an ER model.	10
11	Generate an ER model from a logical model. Update an ER model to reflect changes in a logical model.	10
12	Reverse engineer a physical database schema into an ER model. Update an ER model to reflect changes in a physical schema.	14
13	Reverse engineer from a physical database to a physical model. Update a physical model to reflect changes in a physical database schema.	14
14	Transform a physical model into a physical database. Update a physical database schema to reflect changes to a physical model.	11

Table 1-2 gives a summary of the functions of the arrows in Figure 1-2. The arrows represent business processes and are provided to give you an idea of the different task sequences that you can choose through VEA's database solution. The chapter references on the right refer to the main chapters that deal with the function on that row. You can use Figure 1-2 and Table 1-2 to plan your own reading sequence.

Figure 1-2: Database modeling and VEA.

The following paragraphs outline some activities that illustrate some of VEA's powerful capabilities. The numbers in parentheses refer to the arrows in Figure 1-2.

Create a source model. (2, 3). Use a conceptual model to define the scope and semantics of your application domain independently of your target database technology. Build a conceptual model using ORM's natural language approach or create a logical model directly using ERM. Within the ORM Source Model solution, you may define portable data types that map to more than one database product, and you can define physical data types that map to a specific database product. These data types are domain level data types that help to ensure consistency between the data types in a logical model and the conceptual domains from which they are drawn. VEA's domain mapping feature helps you to ensure data type consistency across multiple databases even if they use different database products. If you have to do this manually, the bigger the model the harder it gets. Fortunately, VEA does this for you, which is a great time saver.

Transform a conceptual model into a 5NF logical model. (7) VEA can automatically transform an ORM Source Model into a logical model in fifth normal form ”another great time saver! You can choose to display your logical model in either Relational or an ER notation (e.g. IDEF1X or Information Engineering).

Create a logical model. (3, 8) Create a logical model directly using the ER Source Model, the Database Model Diagram, or a combination of the two. VEA provides comprehensive features for editing logical models. You can edit names, definitions, indexes, stored procedures, formatting and much more.

Generate a physical database schema. (14) You can generate a physical schema from a logical schema. You choose a target database product by selecting a suitable driver and select from a drop-down box of built-in physical data types. VEA has database drivers for Microsoft SQL Server, Microsoft Access, Oracle Server, IBM DB2 Universal Database, Informix OnLine/SE Server, and Sybase Adaptive Server Enterprise. You will also find generic drivers for OLE and ODBC.

Validate models. You can periodically validate your model to keep it error-free.

Hint

Although the command sequence for model error checking is the same, you get different types of error messages depending on whether you are validating an ORM Source Model, an ER Source Model, or a Database Model Diagram.

Reverse engineer an existing database into a conceptual model. (5, 1) Many domain experts find logical models hard to understand. With VEA's ORM solution, you can present the semantics of a complex database using easy to understand English language facts. This is great for helping domain experts to identify conflicts between database semantics and their knowledge of the application domain.

Reverse engineer an existing database into a logical model. (13) VEA makes it easy to extract a detailed logical model from an existing database.

Convert from one database product to another. (5, 1, 2, 7, 14) You can convert a database from one database product to another. For example, an Oracle database can be converted to a Microsoft SQL Server database and vice versa. A typical conversion requires six main steps:

1. Reverse engineer the existing database schema.
2. Review and edit the conceptual model to ensure semantic accuracy.
3. Generate and edit the logical model to ensure correct normalization.
4. Edit the physical model to ensure data type and SQL code dialect accuracy.
5. Generate a new physical database schema in the target database product.
6. Migrate data from the source to the target database.

Prepare comprehensive and customized reports. (1, 4, 9) VEA's report wizard helps you generate the seven types of report named in Figure 1-3. The arrows indicate the different reporting options for different kinds of model. For example, you can generate an Objects report from an ORM Source Model and from a Database Model Diagram, but you can generate a Statistics report only from a Database Model Diagram. Chapter 9 shows you how to generate ORM reports for objects, facts, constraints, and subtypes . Chapter 15 shows you how to generate reports for statistics, tables, and data types.

Figure 1-3: VEA reports.

VEA's natural language ORM reports help non-technical domain experts to check the semantics of a database schema. VEA's logical reports help database engineers to modify a physical database schema to meet performance or other technical requirements.

The New Report Wizard allows you to control the amount of detail in your reports using select, sort , and format. You can create summary reports that show the big picture or reports that show all the details. You can export your reports in .rtf format to make it easy for you to edit your report with a word processing program.

Save your work as web pages. VEA's command File>Save as Web Page makes it easy to save your diagrams in .htm format so that you can publish them on the web.

Model databases with a team. Each team member can create a sub-model in either ORM or ERM. You can then create a single, comprehensive logical model by merging the teams ' source model documents into a single project document.

Create reusable submodels. You can create a library of source model building blocks and then mix and match them to quickly build a large database model.

Manage naming conventions. You can define three different names for each table and column: a conceptual name, a physical name, and a name space. Modelers often find that two or more tables with the same meaning have been given different names and vice versa. VEA's name space feature helps you to assign a unique name to each table and each column even across multiple databases. If you have to merge two or more existing databases you will find the name space feature to be very useful.

Share your models with users who do not have Visio installed. The Visio Viewer is for those who do not have Visio but who want to view Visio diagrams (in the same way that Acrobat reader iss free for viewing .pdf files). You can find more information about Visio Viewer in the Further Resources section.

Visio Viewer

Visio users should NOT install the Visio Viewer because it then becomes the default program for opening Visio files, and you have to go back and reset your default program for opening Visio documents.

Customize shape behavior. You can customize the behavior of the shapes that represent tables, columns, and relationships in your database model diagram. For example, you can specify what is to happen when you remove a table or foreign key in a database model diagram. You can specify how foreign keys are propagated; choose how name conflicts are resolved, and how default name prefixes are created.

Edit shapes. In addition to editing a logical model you can use Visio's "right click" menu to edit the display properties of shapes on the drawing surface.

Note

Strictly speaking, a model is a schema plus a population of fact instances. Informally, this book often uses the term model as a synonym for schema.

And a word about indexes and performance tuning

Note

Performance is product and application dependent so we don't say much about it in this book. Consult your product manuals and review your application objectives to understand how to tune a database schema for best performance. The ORM domain indexing feature does generate physical indexes, but its main value is to make it easy to manage index consistency.

How Can You Best Use This Book?

The following sample scenarios will give you an idea of how you can best use this book in your own situation.

Scenario and Question	Answer
Database Modeler Database modeling is my job. How can this book help me to do my daily work faster?	Import your existing database schema into a Database Model Diagram (see Chapter 14), then use your own models in parallel with trying each of the examples shown in this book.
Business Analyst I have to use data models as part of my requirements specifications. How can this book help me to be more efficient?	Reverse engineer an existing database schema into an ORM Source Model (see Chapter 8). Use the ORM Source Model to generate suitable semantic reports (see Chapter 9).
Student I know that database modeling is important. How can this book help me to learn more so that I can get a job as a database engineer?	Follow the book from cover to cover. Build some conceptual models in ORM and ERM, and use the New Report Wizard to prepare in-depth reports. Forward engineer your models to your preferred database product. Make changes to the physical database schema and reverse engineer it back into ER and ORM models and run the same reports as you did on the original models. Compare the before and after reports to see the effects of your changes.
Database Administrator I'm snowed under with management requests for explanations of our corporate database. How can this book help me?	Reverse engineer a database schema into an ORM Source Model (see Chapter 8). Run the reports described in Chapter 9 and export them to an .rtf file. Edit the .rtf file to fit your corporate standard and add appropriate ORM models or model fragments to support the reports (see Chapter 9). Consider using the Visio Viewer (see Chapter 17) for electronic model distribution. Ask the managers to confirm or deny the correctness of the ORM objects, facts, and constraints.
Application Development Manager I have just been promoted from Chief Programmer and I need a better understanding of what my database engineers really do. They seem to take a long time to make simple user - requested changes and my programmers complain that database changes result in lots of unnecessary reprogramming and testing. Can I use this book to improve the effectiveness of my team?	Prepare ORM reports (see Chapter 9) and conduct a semantic review with domain experts (see Part 4). Use the reports described in Chapter 15 to review the potential impact of any changes on data elements (e.g., UML classes) in their application programs. Look out for duplications in your database schema caused by failure to correctly normalize or by unnoticed homonyms or synonyms. Review chapter 6 to see how to reuse model fragments to speed new development. Migrate business rules from application program code into SQL code stored in the database (see Chapters 5, 11, 13).
Program Manager, USAF Sub-Contractor I have just been appointed as program manager for new B3 bomber that is so stealthy that even I don't know where it is. The DoD insists that we use IDEF1X but I have never been able to fully understand the real meaning of all those lines, blobs, diamonds and boxes. How can I use this book to get a better understanding of what the IDEF1X data models really mean?	Reverse engineer your database schema into a Database Model Diagram (see Chapter 13) Set the IDEF1X notation options (Chapter 10) and print the IDEF1X schema. Run the reports described in Chapter 9 and compare them with the IDEF1X schema.

How you use this book depends on your desired result and on your level of knowledge. Here are some examples of how VEA can be used to support various job types. The examples are indicative rather than comprehensive.

Business analyst. Use the reports described in Chapter 9 to communicate with nontechnical domain experts. Use the reports from Chapter 15 for database engineers. If you want to start a new requirements analysis project, make sure you understand the contents of Part 1 and then apply the methods described in Part 2 to your own project.

If you want to understand the semantics of an existing database, reverse engineer the database schema into an ORM Source Model (see Chapter 5) and run the reports described in Chapter 9.

Entity Relationship modeling expert. If you are an expert in ER modeling and want to get straight into logical modeling, make sure you understand the contents of Part 1 and then apply the methods described in Part 3 to your own project.

If you want to study the logical and physical structure of an existing database, you can reverse engineer a physical database schema into a Database Model Diagram (see chapter 13). Then you can use the reports described in Chapters 9 and 15.

Application development manager. Review the re-use section and have one of your database engineers try a small project using Part 4 as a guide.

Database administrator. You can reverse engineer multiple databases into a single conceptual model and use it to ensure that all relational columns that are drawn from a single domain use a common data type and naming convention.

Systems architect. VEA allows re-use of all or part of an existing model for future applications. This helps to reduce development costs. You can use a single conceptual model to manage domain level metadata across several applications. Business rules can be maintained in a single conceptual model and re-used in many applications. You can easily add new ORM objects to extend the scope of the conceptual model and quickly generate a new logical model in fifth normal form. VEA helps by eliminating the tedium of repetitive manual normalization.

Student. If you are new to database analysis and design, you should follow the natural sequence of the book. If you want to learn more about ORM and database design, look at papers on the ORM website http://www.orm.net and read the book, Information Modeling and Relational Databases (Halpin, 2001).

Format Conventions

Term	Meaning
Click	Left click of the mouse.
Press	Always refers to a key on the keyboard.
Choose	To select from a set of options, such as radio buttons or check-boxes.
1, 2	Numbered steps are a guide to an action sequences.
Actions and menus	Menu commands are often shown in parentheses after a statement of an action; e.g., Save As (File > Save As, Filename) .
Shaded box	Shows a helpful hint, shortcut, or note.
A+B	A plus (+) sign between two keys means that you must press the keys at the same time (e.g., Press Ctrl+Shift).
Code	Code examples and menu options are often shown in this font.
File> New>	Text with initial capital letters separated by ">" shows a menu selection sequence.
Caution	Shows that the action described may lead to irreversible consequences.
.erx	A period and three lower case letters refers to a file type.