Table Of Content

Pro SQL Server 2000 Database Design: Building Quality OLTP Database& Louis Davidson APress Media, LLC Pro SQ.L Server 2000 Database Design: Building Q.uality OLTP Databases Copyright © 2004 by Louis Davidson Originally pubIished by SpringerVerlag New York, Inc. in 2004 All rights reserved. No part of this work may be reproduced or transmitted in any form or by any me~, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written pennission of the copyright owner and the publisher. ISBN 978-1-59059-302-8 ISBN 978-1-4302-0708-5 (eBook) DOI 10.1007/978-1-4302-0708-5 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Technical Reviewer: Craig Weldon Editorial Board: Dan Appleman, Craig Berry, Gary Cornell, Tony Davis, Steven Rycroft,Julian Skinner, Martin Streicher,Jim Sumser, Karen Watterson, Gavin Wray,John Zukowski Assistant Publisher: Grace Wong Project Manager: KylieJohnston Copy Editor: Mark Nigara Production Manager: Kari Brooks Production Editor: Kelly Winquist Proofreader: Patrick Vincent Compositor: Katy Freer Artist: April MiIne Cover Designer: Kurt Krames Manufacturing Manager: Tom Debolski The information in this book is distributed on an "as is" basis, without warranty. Although every precaution bas been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirecdy by the information contained in this work. The source code for this book is available to readers at htlp:/Iwww.apress.comin the Downloads section. You will need to answer questions pertaining to this book in order to successfully download the code. This book is dedicated to Dr. Paul Durham. He didn't know a dang thing about data bases, but he was a great man, a minister with a huge heart, and an interesting teacher. I count the members ofh is family among my best friends .... Acknowledgments God, the Creator for giving me the ability to design and program computer software. My wife Valerie and daughter Chrissy, who have to put up with looking more at my laptop than my face for the past few years .... My Mom and Dad, without whom I would never have had the tenacity to pull off such an amazing amount of work ... .I still miss you Dad .... Chuck Hawkins for writing the chapter that I never could have .... Chip Broecker, Donald Plaster, Mike Farmer, and the best friend I ever had, all of whom have been important personal influences (in reverse chronological order!). All of the professors, academics, authors (of books and various web pages), presenters, and so on who have influenced me, not the least of which is E. F. Codd, who passed away between the first two editions of the book. In no particular order, the following persons who have worked with me on getting this book to you: Tony Davis, Kyliejohnston, Craig Weldon, Mark Nigara, Kelly Winquist, Sarah Neidhardt,Jessica Dolcourt, Beckie Stones, Beth Christmas, and of course Gary Comell for trusting my work enough to publish it. I hope I haven't forgotten to mention anyone! The editors, who made this book (both editions) better than it might have been. Contents at a Glance Introduction xix Chapter 1: Introduction to Database Concepts 1 Chapter 2: Gathering Information for a Database Project 13 Chapter 3: Fundamental Database Concepts 27 Chapter 4: Entities, Attributes, Relationships, and Business Rules 55 Chapter 5: Data Modeling 91 Chapter 6: Normalization Techniques 129 Chapter 7: Advanced Normalization Topics 165 Chapter 8: Ending the Logical Design Phase 191 Chapter 9: Planning the Physical Architecture 219 Chapter 10: Building the Basic Table Structures 245 Chapter 11: Declarative Data Protection 315 Chapter 12: Programmatic Data Protection 341 Chapter 13: Advanced Data Access and Modification 367 Chapter 14: Determining Hardware Requirements 459 Chapter 15: Completing the Project 511 Index 543 Contents Introduction xlx Chapter 1: Introduction to Database Concepts 1 Introduction .......................................................................................................................... 1 ffistory of Relational Database Structures ........................................................................ 1 The Four Modules of the Modem Database System ................................................ 3 Online Transactional Processing ................................................................................. .4 Operational Data Store .................................................................................................. 5 Data Warehouse ............................................................................................................ 6 Data Marts ...................................................................................................................... 7 Architecture .................................................................................................................... 9 An Overview of the SQL Server Database Design Process ............................................ 9 Case Study Introduction .................................................................................................... 10 Summary .............................................................................................................................. 11 Chapter 2: Gathering Information for a Database Project 13 The Database Design Team .............................................................................................. 14 Documentation and Communication .............................................................................. 15 Client Approval ............................................................................................................ 16 Minimum Information Requirements .............................................................................. 16 Database Prototypes .......................................................................................................... 16 Client Interviews ................................................................................................................ 17 QJ!estions That Need Answers .................................................................................... 18 Other Places to Look for Information .............................................................................. 21 Outline of the Case Study .................................................................................................. 22 Client Interview ............................................................................................................ 23 Preliminary Documentation ........................................................................................ 24 Summary .............................................................................................................................. 26 lx Contents Chapter 3: Fundamental Database Concepts 27 Introduction ........................................................................................................................ 27 The Relational Model ........................................................................................................ 28 Database .............................................................................................................................. 28 Table .................................................................................................................................... 28 Rows and Columns ...................................................................................................... 30 Row Identifiers .............................................................................................................. 35 Optional Attribute Values .......................................................................................... 38 Rowcount ...................................................................................................................... 38 Additional Considerations for Entities ...................................................................... 39 Relational Operators .......................................................................................................... 42 Relationships ...................................................................................................................... 48 Binary Relationships .................................................................................................... 48 N onbinary Relationships ........................................... ,. ............................................... 50 Definitions of Essential Concepts ......................................................................................5 1 Functional Dependency .............................................................................................. 51 Multivalued Dependency ................................................................................ ,. .......... 52 Summary .............................................................................................................................. 53 Chapter 4: Entitles, Attributes, Relationships, and Business Rules 55 Introduction ., ...................................................................................................................... 55 Divorcing Yourself from the Final Structure ............................................................ 56 Identifying Entities .............................................................................................................. 51 People .......................................................................... ., ................................................ 58 Places .............................................................................................................................. 58 Objects .................................................. ., ...................................................................... 59 Ideas ........................ ., .................................................................................................... 59 Other Entities ................................................................................................................ 60 A List of Entities .......................................................................................................... 61 Identifying Attributes .......................................................................................................... 62 Identifiers ...................................................................................................................... 62 Descriptive Information .............................................................................................. 64 Locators ........................................................................................................................ 65 Related Information .................................................................................................... 66 A List of Entities, Attributes, and Domains .............................................................. 66 Relationships Between Entities .......................................................................................... 68 One-to-n Relationships ................................................................................................ 68 Many-to-Many Relationships ...................................................................................... 70 Listing Relationships .................................................................................................... 70 Identifying Business Rules ................................................................................................ 71 Identifying Fundamental Processes .................................................................................. 73 Is This Trip Over Yet? ...................................................................................................... 75 Identification of Additional Data ................................................................................ 75 X Contents Review with the Client ................................................................................................ 76 Repeat Until Gaining Customer Acceptance ............................................................ 77 Case Study .......................................................................................................................... 77 Client Interview ............................................................................................................ 77 Documents .................................................................................................................... 79 Bank Account Object Listing ...................................................................................... 82 Business Rules and Processes ...................................................................................... 86 Best Practices ...................................................................................................................... 88 Summary .............................................................................................................................. 88 Chapter 5: Data Modellng 91 Introduction ........................................................................................................................ 91 Data Models ........................................................................................................................ 92 Data Modeling Format ...................................................................................................... 93 Entities .................................................................................................................................. 93 Entity Type .................................................................................................................... 94 Naming .......................................................................................................................... 95 Attributes .............................................................................................................................. 96 Primary Key .................................................................................................................. 96 Alternate Keys .............................................................................................................. 97 Foreign Keys ................................................................................................................ 98 Domains ........................................................................................................................ 98 Naming ........................................................................................................................ lOO Relationships .................................................................................................................... 10 1 One-to-Many .............................................................................................................. 103 Identifying Relationships .......................................................................................... 10 3 N onidentifying Relationships .................................................................................... 10 3 Mandatory .................................................................................................................. ! 05 Optional ...................................................................................................................... 10 5 Cardinality .................................................................................................................. 10 6 Role Names ................................................................................................................ 107 Other Types of Relationships .................................................................................. 10 8 Recursive .................................................................................................................... 10 8 Categorization Relationships .................................................................................... 11 0 Many-to-Many ............................................................................................................ 112 Verb Phrases {Relationship Names) ........................................................................ 113 Alternate Methods of Relationship Display ........................................................... .115 Descriptive Information .................................................................................................. 119 Case Study ........................................................................................................................ 120 Best Practices .................................................................................................................... 127 Summary ............................................................................................................................ 128 xi Contents Chapter 6: Normalization Techniques 129 Why Normalize? .............................................................................................................. 130 Reducing NULI...s ........................................................................................................ 130 Eliminating Redundant Data ........................................ ;. .......................................... 131 Avoiding Unnecessary Coding ................................................................................ 131 Maximizing Clustered Indexes ................................................................................ 131 Lowering the Number of Indexes per Table .......................................................... 131 Keeping Tables Thin .................................................................................................. 132 The Process of Normalization ........................................................................................ 132 Attribute Shape ................................................................................................................ 133 All Attributes Must Be Atomic ................................................................................ 133 All Instances in an Entity Must Contain the Same Number of Values ............. .135 All Occurrences of a Row Type in an Entity Must Be Different. ......................... 136 Programming Anomalies Avoided by the First Normal Form ............................ 136 Clues That Existing Data Isn't in First Normal Form ............................................ 139 Relationships Between Attributes .................................................................................. 139 Second Normal Form ................................................................................................ 140 Third Normal Form .................................................................................................. 145 Boyce-Codd Normal Form ........................................................................................ 150 Case Study ........................................................................................................................ 155 First Normal Form ...................................................................................................... 155 Boyce-Codd Normal Form ........................................................................................ 157 Model .......................................................................................................................... 162 Best Practices .................................................................................................................... 163 Summary ............................................................................................................................ 163 Chapter 7: Advanced Normalization Topics 165 Introduction ...................................................................................................................... 165 Fourth Normal Form ........................................................................................................ 166 Ternary Relationships ................................................................................................ 166 Lurking Multivalued Attributes ................................................................................ 173 Attribute llistory ........................................................................................................ 176 Fifth Normal Form ............................................................................................................ 179 Denormalization ................................................................................................................ 180 Case Study ........................................................................................................................ 181 Example 1-Account.BalanceDate ............................................................................ 181 Example 2-Payee.Address Id and Payee.Phone .................................................... 182 Example 3-check.UsageType .................................................................................. 185 Best Practices .................................................................................................................... 189 Summary ............................................................................................................................ 189 XII