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.

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.

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 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.

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.

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.

And a word about indexes and performance tuning

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.

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