Table Of ContentPro 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
