Simsion-Witt_FM 12/14/04 11:32 PM Page i Data Modeling Essentials This page intentionally left blank Simsion-Witt_FM 12/14/04 11:32 PM Page iii Data Modeling Essentials Third Edition Graeme C. Simsion and Graham C. Witt AN IMPRINT OF ELSEVIER AMSTERDAM BOSTON LONDON NEW YORK OXFORD PARIS SAN DIEGO SAN FRANCISCO SINGAPORE SYDNEY TOKYO Simsion-Witt_FM 12/14/04 11:32 PM Page iv Publishing Director Diane Cerra Senior Editor Lothlórien Homet Publishing Services Manager Simon Crump Project Manager Kyle Sarofeen Editorial Coordinator Corina Derman Cover Design Dick Hannus, Hannus Design Associates Cover Image Creatas Composition Cepha Imaging Pvt. Ltd. Copyeditor Broccoli Information Management Proofreader Jacqui Brownstein Indexer Broccoli Information Management Interior printer Maple-Vail Book Manufacturing Group Cover printer Phoenix Color Corp. Morgan Kaufmann Publishers is an imprint of Elsevier. 500 Sansome Street, Suite 400, San Francisco, CA 94111 This book is printed on acid-free paper. © 2005 by Elsevier Inc. All rights reserved. Designations used by companies to distinguish their products are often claimed as trademarks or registered trademarks. In all instances in which Morgan Kaufmann Publishers is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trade- marks and registration. 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, scanning, or otherwise— without prior written permission of the publisher. Permissions may be sought directly from Elsevier’s Science & Technology Rights Department in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: permissions @elsevier.com.uk. You may also complete your request online via the Elsevier homepage (http://elsevier.com) by selecting “Customer Support” and then “Obtaining Permissions.” Library of Congress Cataloging-in-Publication Data Application submitted. ISBN: 0-12-644551-6 For information on all Morgan Kaufmann publications, visit our Web site at www.mkp.com or www.books.elsevier.com Printed in the United States of America 05 06 07 08 09 5 4 3 2 1 Simsion-Witt_FM 12/14/04 11:32 PM Page v This new edition of Data Modeling Essentials is dedicated to the memory of our friend and colleague, Robin Wade, who put the first words on paper for the original edition, and whose cartoons have illustrated many of our presentations. This page intentionally left blank Simsion-Witt_FM 12/14/04 11:32 PM Page vii Contents Preface xxiii Part I The Basics 1 Chapter 1 What Is Data Modeling? 3 1.1 Introduction 3 1.2 A Data-Centered Perspective 3 1.3 A Simple Example 4 1.4 Design, Choice, and Creativity 6 1.5 Why Is the Data Model Important? 8 1.5.1 Leverage 8 1.5.2 Conciseness 9 1.5.3 Data Quality 10 1.5.4 Summary 10 1.6 What Makes a Good Data Model? 10 1.6.1 Completeness 10 1.6.2 NonRedundancy 11 1.6.3 Enforcement of Business Rules 11 1.6.4 Data Reusability 11 1.6.5 Stability and Flexibility 12 1.6.6 Elegance 13 1.6.7 Communication 14 1.6.8 Integration 14 1.6.9 Conflicting Objectives 15 1.7 Performance 15 1.8 Database Design Stages and Deliverables 16 1.8.1 Conceptual, Logical, and Physical Data Models 16 1.8.2 The Three-Schema Architecture and Terminology 17 Simsion-Witt_FM 12/14/04 11:32 PM Page viii viii ■ Contents 1.9 Where Do Data Models Fit In? 20 1.9.1 Process-Driven Approaches 20 1.9.2 Data-Driven Approaches 20 1.9.3 Parallel (Blended) Approaches 22 1.9.4 Object-Oriented Approaches 22 1.9.5 Prototyping Approaches 23 1.9.6 Agile Methods 23 1.10 Who Should Be Involved in Data Modeling? 23 1.11 Is Data Modeling Still Relevant? 24 1.11.1 Costs and Benefits of Data Modeling 25 1.11.2 Data Modeling and Packaged Software 26 1.11.3 Data Integration 27 1.11.4 Data Warehouses 27 1.11.5 Personal Computing and User-Developed Systems 28 1.11.6 Data Modeling and XML 28 1.11.7 Summary 28 1.12 Alternative Approaches to Data Modeling 29 1.13 Terminology 30 1.14 Where to from Here?—An Overview of Part I 31 1.15 Summary 32 Chapter 2 Basics of Sound Structure 33 2.1 Introduction 33 2.2 An Informal Example of Normalization 34 2.3 Relational Notation 36 2.4 A More Complex Example 37 2.5 Determining Columns 40 2.5.1 One Fact per Column 40 2.5.2 Hidden Data 41 2.5.3 Derivable Data 41 2.5.4 Determining the Primary Key 41 2.6 Repeating Groups and First Normal Form 43 2.6.1 Limit on Maximum Number of Occurrences 43 2.6.2 Data Reusability and Program Complexity 43 2.6.3 Recognizing Repeating Groups 44 2.6.4 Removing Repeating Groups 45 Simsion-Witt_FM 12/14/04 11:32 PM Page ix Contents ■ ix 2.6.5 Determining the Primary Key of the New Table 46 2.6.6 First Normal Form 47 2.7 Second and Third Normal Forms 47 2.7.1 Problems with Tables in First Normal Form 47 2.7.2 Eliminating Redundancy 48 2.7.3 Determinants 48 2.7.4 Third Normal Form 51 2.8 Definitions and a Few Refinements 53 2.8.1 Determinants and Functional Dependency 53 2.8.2 Primary Keys 54 2.8.3 Candidate Keys 54 2.8.4 A More Formal Definition of Third Normal Form 55 2.8.5 Foreign Keys 55 2.8.6 Referential Integrity 56 2.8.7 Update Anomalies 57 2.8.8 Denormalization and Unnormalization 58 2.8.9 Column and Table Names 59 2.9 Choice, Creativity, and Normalization 60 2.10 Terminology 62 2.11 Summary 63 Chapter 3 The Entity-Relationship Approach 65 3.1 Introduction 65 3.2 A Diagrammatic Representation 65 3.2.1 The Basic Symbols: Boxes and Arrows 66 3.2.2 Diagrammatic Representation of Foreign Keys 67 3.2.3 Interpreting the Diagram 68 3.2.4 Optionality 69 3.2.5 Verifying the Model 70 3.2.6 Redundant Arrows 71 3.3 The Top-Down Approach: Entity-Relationship Modeling 72 3.3.1 Developing the Diagram Top Down 74 3.3.2 Terminology 75 3.4 Entity Classes 76 3.4.1 Entity Diagramming Convention 77 3.4.2 Entity Class Naming 78 3.4.3 Entity Class Definitions 80