60,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
A quick and reliable way to build proven databases for core business functions Industry experts raved about The Data Model Resource Book when it was first published in March 1997 because it provided a simple, cost-effective way to design databases for core business functions. Len Silverston has now revised and updated the hugely successful 1st Edition, while adding a companion volume to take care of more specific requirements of different businesses. This updated volume provides a common set of data models for specific core functions shared by most businesses like human resources management, accounting, and project management. These models are standardized and are easily replicated by developers looking for ways to make corporate database development more efficient and cost effective. This guide is the perfect complement to The Data Model Resource CD-ROM, which is sold separately and provides the powerful design templates discussed in the book in a ready-to-use electronic format. A free demonstration CD-ROM is available with each copy of the print book to allow you to try before you buy the full CD-ROM.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 612
Veröffentlichungsjahr: 2011
Ähnliche
Publisher: Robert IpsenEditor: Robert M. ElliottAssistant Editor: Emilie HermanManaging Editor: John AtkinsAssociate New Media Editor: Brian SnappText Design & Composition: Publishers' Design and Production Services, Inc.
Designations used by companies to distinguish their products are often claimed as trademarks. In all instances where John Wiley & Sons, Inc., 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.
This book is printed on acid-free paper.
Copyright © 2001 by Len Silverston. All rights reserved.
Published by John Wiley & Sons, Inc.
Published simultaneously in Canada.
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8700. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, e-mail: permcoordinator@ wiley.com.
This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold with the understanding that the publisher is not engaged in professional services. If professional advice or other expert assistance is required, the services of a competent professional person should be sought.
Library of Congress Cataloging-in-Publication Data:
ISBN: 978-0-471-38023-8
10 9 8
Advance Praise for The Data Model Resource Book, Revised Edition, Volume 1
“The Data Model Resource Book, Revised Edition, Volume 1, is the best book I've seen on data architecture. It does not merely address the top levels of a data architecture (Zachman Framework row one or two); it provides both common and industry-specific logical models as well as data designs that may be customized to meet your requirements. The end result is a rich framework whose models span the higher and lower levels of a data architecture, including high-level models, logical models, warehouse designs, star schemas, and SQL scripts. You can use the data models, designs, and scripts as templates or starting points for your own modeling, an introduction to subject areas you might not be familiar with, a reference to validate your existing models, and a help to building an enterprise data architecture. The book provides techniques to transform models from one level to another, as well as tips and techniques for getting the appropriate levels of abstraction in the models. Instance tables (sample data) help bring the models to life. I have customized and used the models from the first edition on many projects in the last two years—it is an invaluable resource to me.”
Van Scott
President, Sonata Consulting, Inc.
“Len Silverston has produced an enormously useful two-volume compendium of generic (but not too generic) data models for an extensive set of typical enterprise subject areas, and for various industries that any data modeler will likely encounter at some point in his or her career. The material is clearly written, well organized, and goes below the obvious to some of the more perverse and difficult information requirements in an enterprise. This is an invaluable resource for doing one's homework before diving into any modeling session; if you can't find it here, there is certainly a very similar template that you can use for just about any situation with which you might be faced.”
William G. Smith
President, William G. Smith & Associates
“In today's fast-paced e-oriented world, it is no longer acceptable to bury business constraints in hard-to-change data structures. Data architects must comprehend complex requirements and recast them into data architecture with vision for unforeseen futures. Len's models provide an outstanding starting point for novice and advanced data architects for delivering flexible data models. These models position an organization for the business rule age. Their proper implementation and customization allows the organization to externalize and manage business policies and rules so that the business can proactively change itself. In this way, the data architecture, based on Len's models and procedures for customizing them, becomes by design the foundation for business change.”
Barbara von Halle
Founder, Knowledge Partners, Inc.
Co-author of Handbook of Relational Database Design
“These books are long overdue and a must for any company implementing universal data models. They contain practical insights and templates for implementing universal data models and can help all enterprises regardless of their level of experience. Most books address the needs for data models but give little in the way of practical advice. These books fill in that void and should be utilized by all enterprises.”
Ron Powell
Publisher, DMReview
“Businesses across the world are demanding quality systems that are built faster by IT shops. This book provides a foundation of patterns for data modelers to expand upon and can cut days, if not weeks, off a project schedule. I have found The Data Model Resource Book, Revised Edition, Volume 1, valuable as a resource for my modeling efforts at L.L. Bean, Inc. and feel it is an essential component in any modelers toolkit.”
Susan T. Oliver
Enterprise Data Architect, L.L. Bean, Inc.
“I was first introduced to The Data Model Resource Book three years ago when I was hired by a firm who wanted an enterprise data model. This company did not believe the dictum that ‘all companies are basically the same’; they felt they were somehow unique. After a little analysis with Len Silverston's help, we found that we were actually quite a bit the same: we had customers, accounts, employees, benefits, and all the things you'd find in any corporation. All we had to do was adapt the product component of Len's book and we were ready to move ahead with a great framework for all of our data. A CD-ROM (that can be purchased separately) provides scripts to build the model in Oracle very quickly. We then began mapping all of our detailed data types to the enterprise model and, voila, we could find a place for all of those various spellings and misspellings of Account Number.
“Volume 2 of this revised edition provided even more exciting features: models of industry-specific data. I began to see interesting patterns that permeated this volume. For example, a reservation is a reservation, whether you're an airline, a restaurant, or a hotel. (We even have something similar in the oil industry–the allocation.)
“Another concept from the book that has changed my thinking and vocabulary is the word ‘party.’ I recently managed a project in which an employee could also function as a customer and as an on-line computer user. The team was in disagreement regarding a name for this entity; but after checking The Data Model Resource Book, we realized that here we had a party playing three roles.
“Whether your job is to jump-start a data warehouse project or borrow ideas for any subject area in your next operational database, I highly recommend The Data Model Resource Books, Revised Edition, Volumes 1 and 2, as your bible for design.”
Ted Kowalski
Equilon Enterprises LLC
Author of Opening Doors: A Facilitator's Handbook
Foreword
Acknowledgments
About the Author
Chapter 1 Introduction
Why Is There a Need for This Book?
Who Can Benefit from Reading This Book?
The Need for Universal Data Models
A Holistic Approach to Systems Development
What Is the Intent of This Book and These Models?
What Is New in the Second Edition of the Data Model Resource Book?
Conventions and Standards Used in This Book
Entities
Subtypes and Supertypes
Non-Mutually Exclusive Sets of Subtypes
Attributes
Relationships
Relationship Optionality
Relationship Cardinality
Foreign Key Relationships
Foreign Key Inheritance
Intersection or Association Entities to Handle Many-to-Many Relationships
Exclusive Arcs
Recursive Relationships
Physical Models
Conventions Used for Illustration Tables
Conventions Used to Reference Figures
The Companion Electronic Product
Chapter 2 People and Organizations
Organization
Person
Person—Alternate Model
Party
Party Roles
Organization Roles
Common Party Role Subtypes
Should Roles Be Defined at the Time of the Transaction?
Party Role Example
Role Types Throughout This Book
Party Relationship
Party Relationship Examples
Party Relationship Information
Status Types
Party Contact Information
Postal Address Information
Geographic Boundaries
Party Contact Mechanism—Telecommunications Numbers and Electronic Addresses
Party Contact Mechanism (Expanded)
Contact Mechanism Purpose
Facility Versus Contact Mechanism
Party Communication Event
Communication Event Follow-Up
Summary
Chapter 3 Products
Product Definition
Product Category
Product Identification Codes
Product Features
Product Feature Interaction
Product Feature Subtypes
Product Feature Examples
Unit of Measure
Suppliers and Manufacturers of Products
Inventory Item Storage
Product Pricing
Pricing Subtypes
Price Component Attributes and Relationship to Product or Product Feature
Pricing Factors
International Pricing
Example of Product Pricing
Product Costing
Product to Product Associations
Products and Parts
Summary
Chapter 4 Ordering Products
Standard Order Model
Order and Order Items
Order Parties and Contact Mechanisms
Sales Order Parties and Contact Mechanisms
Party Placing Order and Related Contact Mechanism
Party Taking Order and Related Contact Mechanism
Ship-to Party and Contact Mechanism
Bill-to Party and Contact Mechanism
Person Roles for Orders
Purchase Order Parties and Contact Mechanisms
Generic Order Roles and Contact Mechanisms
Order Adjustments
Order Status and Terms
Order Status
Order Terms
Order Item Association
Optional Order Models
Requirements
Requirement Roles
Requirements Status
Product Requirements
Order Requirement Commitments
Requirement Example
Requests
Request
Request Items
Quote Definition
Quote Roles
Quote
Quote Items
Quote Terms
Agreement Definition
Agreement Item
Agreement Terms
Agreement Pricing
Agreement to Order
Summary
Chapter 5 Shipments
Shipments
Shipment Types
Shipments Parties and Contact Mechanisms
Shipping Detail
Shipment Status
Shipment-to-Order Relationship
Shipment Receipts
Item Issuance for Outgoing Shipments
Shipment Documents
Shipment Routing
Shipment Vehicle
Summary
Chapter 6 Work Effort
Work Requirement and Work Efforts
Work Requirement Definition
Requirement Types
Anticipated Demand
Work Requirement Compared to Order
Work Requirement Roles
Work Effort Generation
Work Effort Type and Work Effort Purpose Type
Work Effort Attributes
Fulfillment of Work Requirements
Work Effort and Facility
Work Effort Generation—Alternate Model
Work Effort Associations
Work Effort Association Definition
Work Effort Dependency
Work Efforts and Work Tasks
Work Effort Party Assignment
Work Effort Party Assignment
Party Skill and Skill Type
Work Effort Status
Work Effort Party Assignment
Work Effort Role Type
Work Effort Assignment Facility
Work Effort Time Tracking
Work Effort Rates
Work Effort Assignment Rate
Inventory Assignments
Fixed Asset Assignments
Fixed Asset
Fixed Asset Type
Fixed Asset Assignment and Status
Party Fixed Asset Assignments
Work Effort Type Standards
Work Effort Skill Standards
Work Effort Good Standards
Work Effort Fixed Asset Standard
Work Effort Results
Summary
Chapter 7 Invoicing
Invoices and Invoice Items
Invoice Roles
Billing Account
Invoice Specific Roles
Invoice Terms and Status
Invoice Status
Invoice Terms
Invoice and Associated Transactions
Billing for Shipment Items
Billing for Work Efforts and Time Entries
Billing for Order Items
Payments
Financial Accounts, Deposits, and Withdrawals
Summary
Chapter 8 Accounting and Budgeting
Chart of Accounts for Internal Organizations
General Ledger Accounts and Types
Organization GL Account
Accounting Period
Accounting Transactions Definition
Business Transactions versus Accounting Transactions?
Accounting Transaction
Accounting Transactions and Their Related Parties
Accounting Transaction Details
Transaction Detail
Relationships between Accounting Transaction Details
Account Balances and Transactions
Subsidiary Accounts
Asset Depreciation
Budget Definition
Budget
Budget Item
Budget Status
Budget Revision
Budget Review
Budget Scenarios
Usage and Sources of Budgeted Amounts
Commitments against Budgets
Payments against Budgets
Budget Relationship to General Ledger
Budgeted Items versus General Ledger Accounts
Summary
Chapter 9 Human Resources
Standard Human Resources Model
Employment
Position Definition
Position
Position Authorization
Position Type
Position Responsibilities
Position Type Definition
Organization
Position Fulfillment and Tracking
Position Fulfillment
Position Status Type
Hiring Organization
Other Considerations
Position Reporting Relationships
Position Reporting Structure
Salary Determination and Pay History
Position Type Rate
Pay Grade and Salary Step
Pay History and Actual Salary
Benefits Definition and Tracking
Employment
Party Benefit
Period Type
Benefit Type
Payroll Information
Employee
Payment Method Type
Payroll Preference
Paycheck
Deduction and Deduction Type
Employment Application
Employee Skills and Qualifications
Employee Performance
Employee Termination
Summary
Chapter 10 Creating the Data Warehouse Data Model from the Enterprise Data Model
The Data Warehouse Architecture
The Enterprise Data Model
The Data Warehouse Design
The Departmental Data Warehouse Design or Data Mart
An Architected Data Warehouse Environment
The Enterprise Data Model
Transformation Requirements
Process Models
High-Level and Logical Data Models
Making the Transformation
Removing Operational Data
Adding an Element of Time to the Warehouse Key
Adding Derived Data
Creating Relationship Artifacts
Changing Granularity of Data
Merging Tables
Creation of Arrays of Data
Organizing Data According to Its Stability
Summary
Chapter 11 A Sample Data Warehouse Data Model
Transformation to Customer Invoice
Removing Operational Data
Adding an Element of Time
Adding Derived Data
Creating Relationship Artifacts
Accommodating Levels of Granularity
Merging Tables
Separation Based on Stability
Other Considerations
The Sample Data Warehouse Data Model
Common Reference Tables
Summary
Chapter 12 Star Schema Designs for Sales Analysis
Sales Analysis Data Mart
Customer Sales Facts
Customer Dimension
Customer Demographics Dimensions
Sales Reps Dimension
Internal Organizations Dimension
Addresses Dimension
Product Dimension
Time Dimension
Transaction-Oriented Sales Data Mart
Variations on the Sales Analysis Data Mart
Variation 1: Sales Rep Performance Data Mart
Customer Rep Sales Fact
Time Dimension
Variation 2: Product Analysis Data Mart
Product Sales Facts
Geographic Boundaries Dimension
Summary
Chapter 13 Star Schema Designs for Human Resources
Human Resources Star Schema
Human Resource Fact Table
Organizations Dimension
Position Types Dimension
Genders Dimension
Length of Services Dimension
Statuses Dimension
Pay Grades Dimension
EEOC Types Dimension
Time_By_Month Dimension
Human Resources Star Schema at a Higher Level of Granularization
Summary
Chapter 14 Additional Star Schema Designs
Inventory Management Analysis
Purchase Order Analysis
Shipment Analysis
Work Effort Analysis
Financial Analysis
Summary
Chapter 15 Implementing the Universal Data Models
The Enterprise Data Model—An Integrated Business View of the Enterprise's Information
Customizing the Universal Data Models
Degrees of Customization
Customizing the Models for Unique Business Terminology
Example of Changing the Terms for the Specific Enterprise
Additional Information Requirements Needed for the Enterprise
How the Universal Data Models and Enterprise Data Model Solve Business Problems
Using a Data Model for a Particular Application
Understanding Business Processes
Building the Logical Data Model
Physical Database Design
Basic Database Design Principles
Creating a Physical Database Design
Physical Database Design Examples
Review of the Party Role and Relationship Model
Party Roles and Relationships Physical Design, Option 1
Party Roles and Relationships Physical Design, Option 2
Party Roles and Relationships Generic Design, Option 3
Using the Data Warehouse Models
Summary
For More Information
Appendix A Logical Data Model Entities and Attributes
Appendix B Data Warehouse Data Model Tables and Columns
Appendix C Star Schema Design Tables and Columns
Other Reusable Data Model and Data Warehouse Design Resources
Index
Foreword
When I first became involved in data modeling in the mid-1970s, I was taught a set of diagramming conventions, the rules of normalization, and a few principles of good design. It did not take me long to discover that my education had covered only the easy part. The real challenge, as any experienced modeler knows, lies in understanding business requirements and choosing an appropriate set of concepts and structures to support them. The traditional advice to “ask which things the enterprise needs to keep information about and how they are related” is a gross over-simplification of the often very difficult process of identifying entities and relationships.
Research in the last few years has supported what practitioners have known for a long time: rather than modeling from first principles, experienced data modelers re-use and adapt models and parts of models from their previous work. In fact, their “experience” may well reside more in their personal library of models–typically remembered rather than documented–than in greater facility with the basic techniques. The use of pre-existing templates also changes the nature of the dialog between the business experts and modelers: modelers will seek to discover which model or models from their repertoire may be appropriate to the situation, then to check the detail of those models. This is a far more proactive role for modelers than that traditionally described, and recognizes that both parties can contribute ideas and content to the final model.
Of course, it takes time and exposure to a wide variety of business requirements for an individual to build up anything approaching a comprehensive library of models. Only specialist data modelers are likely to have this opportunity, and the reality is that much data modeling is performed by non-specialists.
The obvious step forward from this rather haphazard individual approach is for experienced modelers to develop and publish models for the most commonly encountered business requirements, so that solutions can be shared, reviewed and improved. Almost every commercial enterprise needs to keep data about customers, about staff, about sales. And almost every data modeler has spent time wrestling with these common–but by no means simple–situations, painfully aware that he or she is re-inventing the wheel, but without any confidence that any particular modeler has done a better job.
Such additions to data modeling's “body of knowledge” have been a long time coming. Books, papers, and educational material have continued to focus on the foundations of data modeling: modeling paradigms, diagramming conventions, and normalization. These are important topics, to be sure, but the absence of more developed material lends credence to the argument that data modeling does not deserve the status of a fully-fledged discipline.
Perhaps the reason for the gap in the literature is that the individuals best placed to recognize common situations and to develop models for them are data modeling practitioners–more particularly consultants who have had the opportunity to see a range of different business requirements. The models that they have developed over the years are a valuable professional resource, more profitably deployed on consulting assignments than as material for general publication. It also takes some courage to present one's own solutions for scrutiny by peers, all of whom will turn naturally to the problems for which they have personally developed the most elegant solutions!
I am therefore delighted that Len Silverston has chosen to publish a second and substantially expanded edition of The Data Modeling Resource Book. The first edition was essential reading for anyone charged with developing data models for business information systems, and was particularly notable for including contributions by specialists in particular data modeling domains. The second edition retains this feature, covers new business areas, and updates the original material. Len's willingness to continue to improve the material gives me hope that the core models will acquire a deserved status as standard starting points.
The second edition of The Data Modeling Resource Book is an excellent answer to the question “what is the second data modeling book I should purchase, once I've learned the basics?”–and every practitioner of data modeling should own at least two books on the subject!
Graeme Simsion1 January 2001
Acknowledgments
I wrote this book because I deeply feel that universal data models can provide effective solutions to many important data management and integration issues. However, this book would not have been possible without the insights and knowledge gained through my rewarding interactions and relationships with clients over the past 20 years. I am extraordinarily grateful to these clients who allowed me to provide service for them, while expanding my knowledge of business and information management. Their use, implementation of and modifications to universal data model constructs have greatly contributed to the content of this book. From among the many people that have contributed, I want to thank Regina Pieper, Howard Jenkins, Rob Jacoby, Chris Nickerson, Jay Edson, Dean Boyer, Joe Misiaszek, Paul Zulauf, Steve Seay, Ken Haley, Ted Kowalski, Mike Brightwell, Dan Adler, Linda Abt, Joe Lakitsky, Trent Hampton, Kevin Morris, Karen Vitone, Tracy Muesing, Steve Lark, and Chuck Dana. I also want to thank the many client organizations that have added to and supported the universal data model paradigm.
I am very thankful to the people who added to the content of this current edition of the book. A person that made a significant contribution is Bob Conway, who took time to review these models out of a very busy consulting schedule and who scrupulously reviewed the models, making insightful suggestions as only Bob could have done. I greatly appreciate the work that Burt Holmes has done in implementing these universal data models at numerous clients and in providing valuable feedback regarding changes required for practical implementation of these models. I am very grateful to Natalie Arsenault, who provided ongoing ideas about the universal data models based upon her extensive data modeling background and who also drafted the first cut of the Implementing Universal Data Models chapter. I thank David Templeton, who reviewed the Implementing Universal Data Model chapter.
I want to thank several people who were instrumental in the first edition of this book. A great deal of thanks is due to Bill Inmon for suggesting the first edition of this book to Wiley; if not for him, The Data Model Resource Book may not have been published. Bill Inmon has added to this book through his visionary perspectives on data warehousing and his method of how to convert logical data models to data warehouse designs. I am grateful to Kent Graziano whose discipline and writing contributions added a tremendous amount to the first edition of this book, and whose Designer 2000 expertise was instrumental in the creation of the first edition CD-ROM. I thank Claudia Imhoff for her help in data warehousing on the first edition as well as her positive foreword in the first edition.
There were mentors that helped guide me and helped me see this work through completion. I am grateful to Richard Flint for his inspiration, guidance and encouragement to follow my visions. I am very thankful to John DeMartini for helping me to view my life more holistically and for inspiring me to continually learn and write about holistic, integrated systems.
I feel honored to have been able to work on this book with Bob Elliott, the finest editor I know, at John Wiley & Sons and I appreciate his excellent vision, management, editing, and support for this book as well as his ongoing encouragement to me. I want to thank Emilie Herman from John Wiley & Sons for taking care of a great number of tasks at Wiley in publishing this book.
I am thankful to my mom, Dede Silverston, a writer herself, who inspired and supported me in my writing; my dad, Nat Silverston, who has been a great father; my brother and great friend, Steve Silverston, who has lifted my spirits and been there for me; and my sister, Betty Silverston, who has such a big heart. Most of all, I am blessed to have had the support, patience, and love of my beautiful wife, Annette, and daughters, Danielle and Michaela, throughout the trial and tribulations of writing this book.
About the Author
Len Silverston is an author, lecturer, consultant, and pioneer in the field of data management. He has devoted the last 20 years to helping organizations build and integrate information systems, using his unique approaches to develop information architectures, design databases, and solve data management issues.
Mr. Silverston has been an invited speaker at numerous national and international conferences and has written many articles on database design and data warehousing in publications such as Data Management Review and Data Warehouse Institute's Journal of Data Warehousing.
Len Silverston is the founder and owner of Universal Data Models, LLC (www.universaldatamodels.com), a Colorado-based firm providing consulting and training to help enterprises customize and implement “universal data models” and develop holistic, integrated systems. Universal Data Models, LLC, has helped many diverse organizations develop data architectures and designs in a fraction of the typical time through its extensive repository of reusable data models and data warehouse designs. The company offers several seminars that provide tools to deliver higher quality databases and information systems in less time.
Mr. Silverston lives in Castle Rock, Colorado, with his wife Annette and his daughters, Danielle and Michaela. He holds a masters degree in Computer Science from Renssellear Polytechnic Institute with a specialization in database management systems.
He can be reached at [email protected].
About the Contributors
Kent Graziano ([email protected]) is a Senior Technical Architect for Aris Corporation in Denver, Colorado, and is the current president of the Oracle Development Tools User Group. He has over 18 years of experience, with the last 12 years devoted to Oracle data modeling and data warehousing. Kent was the recipient of the 1999 Chris Wooldridge Award from the International Oracle Users Group for outstanding contributions to the Oracle user community.
W. H. Inmon, the acknowledged “father of data warehousing,” is a partner in www.billinmon.com, a Web site for the corporate information factory and modern systems architecture. He has written more than 40 books on databases, database management, and data warehouse technology, including the recently published Exploration Warehousing (Wiley). Bill is also a frequent speaker at leading industry conferences and contributes to DM Review.
Natalie Arsenault has worked for major Fortune 100 companies in database administration, design, and modeling for most of her 20-year career. Her current work supports an enterprise data framework that is consistently leveraged throughout the company. She is involved with data standards, metadata planning and is a member of the enterprise technical architecture team.
Ms. Arsenault has been a conference speaker at several international conferences on data modeling, and her colleagues seek her expertise.
CHAPTER1Introduction
If you see can see more of the whole, you are moving closer towards the truth.
Why Is There a Need for This Book?
On many data modeling consulting engagements, clients have asked the same question: “Where can we find a book showing a standard way to model this structure? Surely, we are not the first company to model company and address information.”
Many organizations develop their data models or data warehouse designs with very few outside reference materials. A large cost is associated with either hiring experienced consultants or using internal staff to develop this critical component of the system design. Often there is no objective reference material that the company can use to validate its data models or data warehouse designs or to seek alternate options for database structures.
Based on numerous experiences of using template or “universal data models” and customizing them for various enterprises, we have concluded that usually more than 50 percent of the data model (corporate or logical) consists of common constructs that are applicable to most organizations, another 25 percent of the model is industry specific (these models are covered in The Data Model Resource Book, Volume 2), and, on average, about 25 percent of the enterprise's data model is specific to that organization. This means that most data modeling efforts are recreating data modeling constructs that have already been created many times before in other organizations.
With this in mind, doesn't it make sense to have a source to use to get a head start on your data model so that you are not “reinventing the wheel” each time a company develops a new system? Organizations can save time and money by leveraging the use of common or universal database structures. Even if a company has data models from its previous systems development efforts, it is very helpful to be able to check the designs against an unbiased source in order to evaluate alternative options.
Although a large number of publications describe how to model data, very few compilations of data model examples exist in published form. This book provides both a starting point and a source for validating data models. It can help data modelers minimize design costs and develop more effective and integrated database designs.
Who Can Benefit from Reading This Book?
This book can assist many different systems development professionals: data administrators, data modelers, data analysts, database designers, data warehouse administrators, data warehouse designers, data stewards, corporate data integrators, or anyone who needs to analyze or integrate data structures. Systems professionals can use the database constructs contained in this book to increase their productivity and provide a checkpoint for quality designs.
The Need for Universal Data Models
Data modeling first gained recognition in Dr. Peter Chen's 1976 article, “Entity-Relationship Modeling,” which illustrated his newfound approach. Since then data modeling has become the standard approach used to design databases. By properly modeling an organization's data, the database designer can eliminate data redundancies, which are a key source of inaccurate information and ineffective systems.
Currently, data modeling is a well-known and accepted method for designing effective databases. Therefore, there is a great need to provide standard templates to enterprises (the term “enterprise” is used to describe the organizations for whom the models and systems are being developed) so that they can refine and customize their data models instead of starting from scratch.
Although many standards exist for data modeling, there is a great need to take data modeling to the next step: providing accessibility to libraries of common data model examples in a convenient format. Many different organizations and industries should be able to use these libraries of data models. Such universal data models can help save tremendous amounts of time and money spent in the systems development process.
A Holistic Approach to Systems Development
One of the greatest challenges to building effective systems is integration. Systems are often built separately to meet particular needs at different times within each enterprise. Enterprises need to build many systems: contact management systems, sales order systems, project management systems, accounting systems, budgeting systems, purchase order systems, and human resources systems, to name a few.
When systems are built separately, separate pools of information are created for each system. Many of these systems will use common information about organizations, people, geographic locations, or products. This means that each separate system will build and use its own source of information. A huge problem with this approach is that it is almost impossible to maintain accurate, up-to-date information because the same type of information is stored redundantly across many systems. In large organizations, it is not uncommon to see information about customers, employees, organizations, products, and locations stored in dozens of separate systems. How is it possible to know which source of information is the most current or most accurate?
Another disadvantage of building separate systems with non-integrated data structures is that the enterprise (the organization for which the models and systems are being designed) does not have the benefit of viewing integrated information. Being able to see a complete profile for a person, organization, product, or inventory item is an enormous benefit. Imagine systems that are built so that each part of an organization knows what the other part is doing, where the customer service, sales, purchasing, and accounting departments of an organization have integrated information about the people, organizations, and products of the enterprise. This integration can make a big different in the service, sales, and performance of an enterprise.
Another way to approach systems development is from a perspective that an enterprise's systems are connected and, in fact, may be viewed as one interconnected system. From this perspective, there are tremendous benefits to building an enterprise-wide framework so that systems can work together more effectively. Part of this framework should include a corporate data model (i.e., an enterprise data model) that can assist the enterprise in maintaining one of its most valued assets: information. Because each system or application may use similar information about people, organizations, products, and geographic locations, a shared information architecture can be invaluable.
The IS (information systems) industry has recognized the need for integrated designs, prompting the many corporate data modeling and corporate data warehouse modeling efforts. Unfortunately, the IS track record for building and implementing corporate data models has been very poor. Enterprises have realized that it takes a tremendous amount of time and resources to build these models.
Enter CASE (Computer-Aided Systems Engineering) tools. These tools claimed tremendous productivity and time savings when used for corporate-wide modeling efforts. While these tools help document the models, unfortunately they do not reduce the time needed to develop good corporate models.
Many enterprises have stopped building corporate data models because of their time constraints. They are looking at the track record of corporate data modeling and CASE efforts and choosing other alternatives.
Enter data warehousing. Finally, here is an approach to provide executives with the management information they need, without all the time and expense of corporate data modeling. Enterprises are now extracting the various pieces of information they need directly from their operational systems in order to build decision support systems.
The only problem with this approach is that the same problem, exists! First of all, the information in the data warehouse may be extracted from several different, inconsistent sources. If there are multiple places where customer information is being held, which system represents the most accurate source of information?
According to data warehousing principles, the transformation routines are responsible for consolidating and cleansing the data. If different departments have different needs for various pieces of data, then each department may build its own extracts from the operational systems. One department may transform the information using one algorithm; a different department may use another algorithm. For example, if two departments are extracting sales analysis information, one department may use the order entry system as its source and another department may use the invoicing system as its source. A high-level manager may view information from both data warehouses and see inconsistent results, thus questioning the credibility of all the information. This type of scenario actually compounds the initial problem of many data sources by creating even more slices of data.
This is not to say that data warehousing is the wrong approach. It is an ingenious approach that can be used extremely effectively not only to create decision support systems but also to build a migration path to an integrated environment. The data warehouse transformation process helps to identify where there are data inconsistencies and data redundancies in the operational environment. It is imperative, though, to use this information to migrate to more integrated data structures.
The answer is still to build integrated data structures in order to provide good, accurate information. The only effective way to do this is to understand how the data within an enterprise and the relationships fit together and to be able to see the data in a holistic integrated manner. It is necessary to understand the nature of the data in order to build effective systems. Instead of saying that corporate data modeling or CASE is the wrong approach because it just takes too long, the IS community needs to find a way to make it work effectively. By building common, reusable data structures, the IS community can produce quicker results and move toward integrated structures in both the transaction processing and data warehouse environments.
What Is the Intent of This Book and These Models?
Most data modeling books focus on the techniques and methodologies behind data modeling. The approach behind this book is dramatically different. This book assumes that the reader knows how to model data. Data modeling has been around long enough that most information systems professionals are familiar with this concept and will be able to understand this book. Therefore, this book makes no efforts to teach data modeling principles, except by example. Data modelers can use this book, and their previous experience, to build on and refine the data model examples contained within the book in order to develop more customized data models. Essentially, it gives the modeler fundamental tools and building blocks that can be reused. Therefore, the modeler can be more productive and save a great deal of time by starting with standard data models instead of building data models from scratch.
Furthermore, the reader can also benefit from the data warehouse models that are applicable to decision support environments. This book not only presents examples of data warehouse designs, but it also explains in detail how to convert the logical data models to an enterprise-wide data warehouse, then to departmental data marts. The logical data models and data warehouse models presented here are applicable across a wide variety of enterprises.
These models are intended to be a starting point for developing logical and data warehouse data models for an enterprise. Each enterprise will have its own detailed requirements; the models will need to be modified and customized in order to be implemented for a specific enterprise. Because the data warehouse data models reflect actual database designs (as opposed to logical data models), they are even more dependent on the business needs of the specific enterprise wishing to use these models. In addition, the models in this book can be used to validate an enterprise's existing data models.
The models presented in the first part of this book (Chapters 2 through 9) are logical data models, not physical database designs. Therefore, these models are normalized and may require some denormalization when designing the physical database. Consistent with this point, the logical data models do not include any derived attributes because derived attributes do not add anything to the information requirements of a business. They merely serve to enhance performance of the physical database.
These logical data models represent possible data requirements for enterprises. They do not include many of the business processing rules that may accompany data models. The data models generally provide all the information needed to enforce business rules; however, the reader is advised in many cases that additional business rules may need to be developed to supplement the data models. Examples of the need for business rules are provided throughout this book.
These data models were designed to benefit many different industries and enterprises. They were picked specifically because they represent very common data constructs that appear in most organizations. Within these models, whenever there was a data modeling decision that may have been dependent on a specific enterprise, the most flexible data modeling option was chosen in order to accommodate many different enterprises.
Furthermore, the chapter on Implementing Universal Data Models provides an explanation on how to use the data models to build an enterprise data model, logical data models, and physical database designs. Detailed examples are provided for how to transform the data models into a physical database design that can be implemented for a database management system.
What Is New in the Second Edition of the Data Model Resource Book?
The second edition of the Data Model Resource Book provides many enhancements and additional models. There are a great number of updates and additions; the following points describe them at a high level.
A great majority of the data models in the original Data Model Resource Book have been significantly enhanced with additional entities, attributes, and relationships.
Many of the data models have slightly different and more enhanced data structures. Based on numerous usages and implementations of these models, the models have been updated to reflect even more effective data structures.
A number of new chapters have been added to the second edition. Chapter 14 provides additional star schemas that can be used as templates for data analysis solutions. Chapter 15 provides an explanation of how to use the universal data models to create an enterprise data model, a logical data model, and a physical database design. This chapter provides examples of customizing enterprise and logical data models and several physical database design examples for implementing one of the universal data models. A great number of new universal data models have been added to the already existing comprehensive library from the first edition. Table 1.1 provides a listing of the new models.
Table 1.1 Data Models Added in Second Edition
CHAPTERNEW DATA MODELS THAT HAVE BEEN ADDED FROM THE FIRST EDITION TO THE SECOND EDITION2 Parties2b Person-alternate model 2.4 Party roles 2.5 Specific party relationships 2.6 Common party relationships 2.11 Facility versus contact mechanism 2.12 Party communication event 2.13 Communication event follow up event3 Products3.4 Product feature 3.10a Products and parts 3.10b Products and parts-alternate model4 Orders4.3 Sales order parties and contact mechanisms 4.4 Purchase order parties and contact mechanisms 4.6 Order adjustments 4.12 Agreement roles5 Shipments5.4 Shipment receipt for incoming shipments 5.5 Item issuances for outgoing shipments 5.6 Shipping documents 5.7 Shipment route segments6 Work Efforts6.1 Work requirement 6.2 Work requirement roles 6.12 Work effort results7 Invoices7.8a Invoice payments 7.8b Invoice payments-alternate model 7.9 Financial accounts, withdrawals and deposits8 Accounting8.2 Business transactions versus accounting transactions 8.4 General ledger account associations and subsidiary ledger accounts 8.7 Budget revision 8.8 Budget revision review 8.9 Budget scenario9 Human Resources9.8 Benefits tracking 9.10 Employee application 9.11 Employee skills and qualifications 9.12 Employee performance 9.13 Employee termination12 Star Schema Designs for Sales Analysis Star Schema Designs12.2 Transaction oriented sales data mart14 Additional Star Schema Designs14.1 Inventory management star schema 14.2 Purchase order star schema 14.3 Shipment star schema 14.4 Work effort star schema 14.5 Financial analysis star schema15 Implementing Universal Data Models15.2. Customized party contact mechanism (using different terms) 15.3 Additions to the party contact mechanism model 15.4 Detailed model for sales force (showing a customized version for a particular application) 15.6 Party roles and relationships physical design option 1 15.7 Party roles and relationships physical design option 2 15.8 Party roles and relationships physical design option 3Conventions and Standards Used in This Book
The following section describes the naming standards and diagramming conventions used for presenting the models in this book. Details are provided for entities, subtypes, attributes, relationships, foreign keys, physical models, and illustration tables.
Entities
An entity is something of significance about which the enterprise wishes to store information. Whenever entities are referenced throughout the book, they are shown in capital letters. For example, ORDER represents an entity that stores information about a commitment between parties to purchase products. When the name of an entity is used in a sentence to illustrate concepts and business rules, it may be shown in normal text—for example, “Many enterprises have mechanisms such as a sales order form to record sales order information.”
The naming conventions for an entity include using a singular noun that is as meaningful as possible to reflect the information it is maintaining. Additionally, the suffix TYPE is added to the entity name if the entity represents a classification of information such as an ORDER TYPE (i.e., sales versus purchase order) rather than a specific instance of a real thing such as an ORDER (“order #23987”).
The data models in this book include TYPE entities on the diagrams, even though they usually have only an id and a description. These entities are included for completeness and to show where allowable values or look-ups are stored.
Entities are included in the data model if it is a requirement of the enterprise to maintain the information included in the entity. For example, if an enterprise doesn't really care about tracking the tasks associated with a shipment, then even though this information exists in the real world, the data model should not incorporate this information because it may not be important enough information for the enterprise to maintain.
Entities are represented by rounded boxes. Figure 1.1 shows an example of the entity ORDER.
Figure 1.1 An entity.
Subtypes and Supertypes
A subtype, sometimes referred to as a subentity, is a classification of an entity that has characteristics such as attributes or relationships in common with the more general entity. LEGAL ORGANIZATION and INFORMAL ORGANIZATION are, for example, subtypes of ORGANIZATION.
Subtypes are represented in the data modeling diagrams by entities inside other entities. The common attributes and relationships between subtypes are shown in the outside entity, which is known as the supertype. The attributes and relationships of the supertype are therefore inherited by the subtype. Figure 1.2 shows the supertype ORGANIZATION and its sub-types LEGAL ORGANIZATION and INFORMAL ORGANIZATION. Notice that the name applies to the supertype ORGANIZATION and the federal tax ID applies only to the LEGAL ORGANIZATION subtype. It is therefore shown at the subtype level of LEGAL ORGANIZATION because it applies only to that subtype. Both LEGAL ORGANIZATION and INFORMAL ORGANIZATION would have a name because they will inherit the values of the supertype.
Figure 1.2 Subtypes and supertypes.
Supertypes may have many levels. Figure 1.2 shows that a CORPORATION and GOVERNMENT AGENCY are subtypes of LEGAL ORGANIZATION, which is also a subtype of ORGANIZATION. Thus boxes may be in boxes down to any level to illustrate which subtypes inherit the attributes and relationships of the parent supertype (its outer box).
The subtypes within an entity should represent a complete set of classifications (meaning that the sum of the subtypes covers the supertype in its entirety) and at the same time be mutually exclusive of each other (an exception of handling separate sets of non-mutually exclusive subtypes will be covered in the next section). Many times the data model includes an OTHER...subtype to provide for other possible classifications of the entity that may be defined by the enterprise using the model. For example, each INFORMATION ORGANIZATION may be a TEAM, FAMILY, or OTHER INFORMAL ORGANIZATION.
While the subtypes represent a complete set of possible classifications, there may be more detailed subtypes that are not included in the data model; instead, they may be included in a TYPE entity. In this case, subtypes are shown in two places on a model: as a subtype and in a TYPE entity that shows the domain of allowed types for the entity.
Non-Mutually Exclusive Sets of Subtypes
Sometimes, subtypes are not mutually exclusive; in other words, supertypes may be subtyped different ways and more than one set of subtypes may apply to the same supertype.
Consider Figure 1.3, which shows that a REQUIREMENT may be subtyped different ways. A REQUIREMENT may be from a customer (CUSTOMER REQUIREMENT) or may represent an internal requirement of the enterprise (INTERNAL REQUIREMENT). At the same time, the REQUIREMENT may be a requirement that states the need for a specific product (PRODUCT REQUIREMENT) or a requirement that states the need for work to be done (WORK REQUIREMENT).
Figure 1.3 Non-mutually exclusive subtypes and supertypes.
Therefore, more than one subtype could occur for a REQUIREMENT; for instance, it could be a CUSTOMER REQUIREMENT and PRODUCT REQUIREMENT. Figure 1.3 illustrates a convention to show mutually exclusive sets of subtypes by having a box around each set of possible subtypes with no name for the box. The boxes merely serve to establish when there is more than one set of subtypes for a supertype.
Attributes
An attribute holds a particular piece of information about an entity, such as the order date on an order. Attributes are identified in the text of the book by boldface, lowercase letters such as the previous order date example.
Attributes may be part of the unique identifier of an entity (also referred to as a primary key), mandatory, or optional. The primary key attribute(s) is identified by a “#” sign preceding the attribute name on the diagram. Mandatory attributes are signified by a “*” before the attribute name. Optional attributes have an “o” before the attribute. Figure 1.4 shows that the ORDER entity has order ID as a primary key attribute, order date as a mandatory attribute, and entry date as an optional attribute.
Figure 1.4 Attributes.
Certain strings included in an attribute's name have meanings based on the conventions shown in Table 1.2.
Table 1.2 Conventions Used in Attribute Naming
STRING WITHIN ATTRIBUTE NAMEMEANINGIDSystem-generated sequential unique numeric identifier (i.e., 1, 2, 3, 4,…)Seq idSystem-generated sequence within a parent ID (e.g., order line sequence number)CodeUnique mnemonic—used to identify user-defined unique identifiers that may have some meaning embedded in the key (i.e., an example of a geo code to store Colorado may be “CO”)NameA proper pronoun such as a person, geographical area, organizationDescriptionThe descriptive value for a unique code or identifierInd (indicator)A binary choice for values (i.e., yes/no or male/female)from dateAttribute that specifies the beginning date of a date range and is inclusive of the date specifiedthru dateAttribute that specifies the end date of a date range and is inclusive of the date specified (to date is not used because thru date more clearly represents an inclusive end of date range)Relationships
Relationships define how two entities are associated with each other. When relationships are used in the text, they are usually shown in lowercase as a normal part of the text. In some situations, where they are specifically highlighted, they are identified by boldface lowercase letters. For example, manufactured by could be the way a relationship may appear in the text of this book.
Relationship Optionality
Relationships may be either optional or mandatory. A dotted relationship line next to an entity means that the relationship from that entity is optional, and a continuous line means that the relationship is mandatory (the relationship has to exist for all occurrences of each entity). Figure 1.5 shows a relationship that “each SHIPMENT must beshipped from one and only one POSTAL ADDRESS.” This means that the postal address for each shipment must be specified in order to create a shipment instance. The same relationship has an optional aspect when read in the other direction: “Each POSTAL ADDRESS may bethe source of one or more SHIPMENTs.” Hence, there could be a postal address which has not been used for a shipment yet.
Figure 1.5 Mandatory versus optional relationships.
Relationship Cardinality
Relationships may be one-to-one, one-to-many, or many-to-many. This is generally known as the cardinality of the relationship. The presence of a crowsfoot (a three-pronged line that looks like a crow's foot) defines whether an entity points to more than one occurrence of another entity. Figure 1.6 shows that “each ORDER must be composed ofone or more ORDER ITEMs” because the crows-foot is at the ORDER ITEM side. The other relationship side states that “each ORDER ITEM must be part ofone and only one ORDER.” A one-to-one relationship doesn't have any crowsfeet on the relationship, and a many-to-many relationship has crowsfeet at both ends of the relationship. Sometimes, one-to-many relationships are referred to as parent-child relationships.
Figure 1.6 One-to-many relationship.
Sometimes the term “over time” needs to be added to the relationship sentence to verify whether the relationship is one-to-many. For instance, an ORDER may appear to have only one ORDER STATUS; however, if status history is required, then each ORDER may be in the status of by one or more ORDER STATUSes, over time.
The data models in the book have very few one-to-one relationships because most of the time one-to-one relationships can be grouped together into a single entity when normalized. The data model diagrams do not show many-to-many relationships because many-to-many-relationships are almost always broken out into intersection entities.
Foreign Key Relationships
A foreign key is defined as the presence of another entity's (or table's) primary key in an entity (or table). For example, in Figure 1.6 the order ID from the ORDER entity is part of the ORDER ITEM entity; therefore, it is a foreign key. Any one-to-many relationship indicates that the primary key of the entity on the one side of the relationship is brought into the entity on the many side of the relationship. Some data modelers show this foreign key as an attribute of the entity (this is sometimes known as key migration). The data models in this book do not show the foreign keys of entities as attributes because this is redundant.
Instead, the relationship itself identifies the foreign key. In Figure 1.6, the order ID is not shown as an attribute in the ORDER ITEM entity because the one-to-many nature of the relationship reveals that it is a foreign key.
Foreign Key Inheritance
A diagramming convention in this book is to use a tilde (“~”) relationship line to indicate that the inherited foreign key is part of the primary key of the child entity. The tilde (“~”) line across the relationship in Figure 1.6 indicates that the order ID is part of the ORDER ITEM entity primary key. This convention allows a shorthand notation, providing for the primary key to be identified as a combination of the primary key attributes (identified with a “#”) as well as the primary keys of the entity to which the relationship with a tilde is pointing.
Therefore the primary key to the ORDER ITEM is the order item seq ID plus the primary key of the order, order id.
This convention allows a shorthand notation to document the primary keys of each entity without taking up a great deal of space by repeated foreign keys that form part of another entity's primary key. This notation also shows the semantics of the primary key by clearly specifying the relationships that make up the primary key as well as any attributes with a “#” symbol next to them.
Intersection or Association Entities to Handle Many-to-Many Relationships
Intersection entities are also known as associative entities or cross-reference entities. They are used to resolve many-to-many relationships by cross-referencing one entity to another. Often they include additional attributes that may further delineate the relationship. Figure 1.7 shows a many-to-many relationship between a PARTY and a CONTACT MECHANISM that is resolved in this way. The diagram indicates that a PARTY may be contacted viamore than one CONTACT MECHANISM such as a POSTAL ADDRESS, TELECOMMUNICATIONS NUMBER, or ELECTRONIC ADDRESS because a party may have many ways to be contacted. Conversely, a CONTACT MECHANISM may be used bymore than one PARTY. For instance, many people may have the same work address or work phone number. This many-to-many relationship is resolved by the intersection entity PARTY CONTACT MECHANISM.
Figure 1.7 Many-to-many relationships.
Each associative entity inherits the key to each of the entities it intersects. Therefore the tilde (“~”) is always used in the reference relationships of an associative entity to show that the associative entity inherits the key of each of the referenced entities (see “foreign key inheritance” mentioned in the last section). For example, the party id and the contact mechanism id are parts of the primary key to PARTY CONTACT MECHANISM, along with the from date.
Notice that in all the examples given, each relationship has two relationship names associated with it that describe the relationship in both directions. The relationship names should be combined so that they read as a complete sentence, as shown in the following format: “Each ENTITY {must be/may be} relationship name {one and only one/one or more} ENTITY, over time,” where the appropriate choices are filled in.
In the models presented, the crowsfeet on the relationships generally point up and to the left in order to provide a consistent mechanism for reading the diagrams. This tends to organize the data models in a more understandable format.
Exclusive Arcs
Exclusive arcs are used to identify relationships where an entity is related to two or more other entities, but only one relationship can exist for a specific entity occurrence. The exclusive arc is represented by a curved line going through two or more relationship lines. Figure 1.8 shows an example of an exclusive arc. The relationships are read as “Each INVENTORY ITEM must be either located at one and only FACILITY or must be located within one and only one CONTAINER, but not both.” This communicates that inventory items are stored at one of two types of levels: They are either located at facilities such as a warehouse or stored within containers such as a bin that is located within a facility.
Figure 1.8 Exclusive arcs.
Recursive Relationships
Recursive relationships are relationships that show how one entity is related to itself. For example, a recursive relationship could be modeled either via a relationship pointing from an entity to itself or via a many-to-many-relationship. This depends on if it is a many-to-many recursion or a one-to-many recursion. It is possible for an entity to have many recursive relationships.
Figure 1.9 shows an example of a one-to-many recursion around the WORK EFFORT entity to show that work efforts may be redone. It also shows a many-to-many recursion that is resolved by the intersection entity WORK EFFORT ASSOCIATION to show that work efforts may be either dependent on other work efforts (WORK EFFORT DEPENDENCY subtype) or broken down into several lower-level work efforts (WORK EFFORT BREAKDOWN subtype).
Figure 1.9 Recursive relationships.
Physical Models
The data warehouse models and diagrams (Chapters 10 through 14) as well as some of the models in Chapter 15, represent physical database designs. The
same notations can be used as previously stated with the exception that because these models represent physical database designs, each box represents a table, and the field names are columns.
Conventions Used for Illustration Tables
Many parts of the data models are illustrated via tables that contain possible values for attributes. Each illustration table is normally defined to show a specific entity and the relevant information from related entities. For instance, there may be a table illustrating the ORDER ITEM entity, as shown in Table 1.3.
Table 1.3 Order Item
In order to illustrate the details of an ORDER ITEM, Table 1.3 brings in some attribute information from the ORDER entity. Whenever data from each illustration table is referenced in the text of this book, it is surrounded by double quotes. For instance, the text may refer to specific order “12930,” order item seq id “1”, which has a comment of “Need this item urgently.”
Conventions Used to Reference Figures
Because there are two volumes for the Data Model Resource Book, figures are referenced by the following notation:
Vx:Figure x.xWhereVx signifies a reference to either Volume 1 or Volume 2andFigure x.x references a specific figure in that volume.For example, V1:2.1 references Figure 2.1 in Volume 1, the Organization data model. V2:2.2 references Figure 2.2 (Parts and products) in the second volume. If there is no Vx in front of the reference, then the reader may assume that the figure is in the current volume.
The Companion Electronic Product
This book and its appendices provide very detailed descriptions of the models discussed. The diagrams lay out all the relationships, the mandatory attributes and columns, the primary keys, and they even include some optional attributes. The appendices include the physical details for the attributes and columns, such as the datatype and size. With this information, it would be possible for a data modeler or database designer to recreate these models in the tool of his or her choice or write the SQL code to build them in a database.
This, however, would take a substantial amount of time and opens the possibility of data entry errors. To assist those interested in quickly implementing the models described in these pages, the models are provided in electronic form. The Demo CD-ROM that accompanies this book offers a free demonstration of the electronic models. The full set of models for Volume 1 is sold separately. See “How to Use the Electronic Product” at the end of this book for details on purchasing, accessing, and using the CD-ROM.
The demo files on this CD-ROM contain a sample of SQL scripts derived directly from the models in the book. In the full version, all the entities, attributes, tables, and columns discussed are implemented with this code. Scripts are provided for several database platforms. There are also generic standard SQL scripts that could be used with other ODBC databases.
Because the CD-ROM includes standard SQL scripts, they should work with not only the current versions of these database management systems but also with future versions. This includes object-relational databases assuming that they support relational designs. The constructs in the book are, of course, also generally applicable to any relational or object-relational database.
Use of the scripts on the CD-ROM will allow an enterprise to more rapidly deploy the models presented in this book. In addition to the time savings, there is obviously a cost savings as well (nobody has to type in all the definitions or write SQL scripts). Once the scripts have been run, the models could be reverse-engineered into the enterprise's favorite CASE tool (most popular CASE tools provide a reverse-engineering feature). Once the models have been brought into a repository, they are easily accessible and may be customized for a specific enterprise's needs. Additionally, they can be used to jump-start the development of corporate data models, new applications, data warehouse designs, or decision support systems.
The CD-ROM also contains the data model diagrams in elecronic formats and a series of reports that list and cross-reference the subject data areas, entities, attributes, tables, and columns of the data models.
