A developer's guide to data modeling for SQL server covering SQL server 2005 and 2008

✍ Scribed by Johnson, Eric;Jones, Joshua

• Publisher: Addison-Wesley Professional
• Year: 2008
• Tongue: English
• Leaves: 303
• Series: Addison-Wesley Microsoft technology series
• Category: Library

✦ Synopsis

""A Developer's Guide to Data Modeling for SQL Server" explains the concepts and practice of data modeling with a clarity that makes the technology accessible to anyone building databases and data-driven applications.
"Eric Johnson and Joshua Jones combine a deep understanding of the science of data modeling with the art that comes with years of experience. If you're new to data modeling, or find the need to brush up on its concepts, this book is for you."
--Peter Varhol, Executive Editor, "Redmond Magazine" Model SQL Server Databases That Work Better, Do More, and Evolve More Smoothly
Effective data modeling is essential to ensuring that your databases will perform well, scale well, and evolve to meet changing requirements. However, if you're modeling databases to run on Microsoft SQL Server 2008 or 2005, theoretical or platform-agnostic data modeling knowledge isn't enough: models that don't reflect SQL Server's unique real-world strengths and weaknesses often lead to disastrous performance.
"A Developer's Guide to Data Modeling for SQL Server"is a practical, SQL Server-specific guide to data modeling for every developer, architect, and administrator. This book offers you invaluable start-to-finish guidance for designing new databases, redesigning existing SQL Server data models, and migrating databases from other platforms.
You'll begin with a concise, practical overview of the core data modeling techniques. Next, you'll walk through requirements gathering and discover how to convert requirements into effective SQL Server logical models. Finally, you'll systematically transform those logical models into physical models that make the most of SQL Server's extended functionality. All of this book's many examples are available for download from a companion Web site.
This book enables you to
Understand your data model's physical elements, from storage to referential integrityProvide programmability via stored procedures, user-defined functions, triggers, and .NET CLR integrationNormalize data models, one step at a timeGather and interpret requirements more effectivelyLearn an effective methodology for creating logical modelsOvercome modeling problems related to entities, attribute, data types, storage overhead, performance, and relationships Create physical models--from establishing naming guidelines through implementing business rules and constraintsUse SQL Server's unique indexing capabilities, and overcome their limitationsCreate abstraction layers that enhance security, extensibility, and flexibility

✦ Table of Contents

Cover......Page 1
Contents......Page 10
Preface......Page 16
Acknowledgments......Page 18
About the Authors......Page 20
PART I: Data Modeling Theory......Page 22
Chapter 1 Data Modeling Overview......Page 24
Databases......Page 25
Why a Sound Data Model Is Important......Page 27
The Process of Data Modeling......Page 35
Summary......Page 42
Entities......Page 44
Attributes......Page 45
Referential Integrity......Page 53
Relationships......Page 56
Using Subtypes and Supertypes......Page 63
Summary......Page 65
Physical Storage......Page 66
Referential Integrity......Page 80
Programming......Page 92
Implementing Supertypes and Subtypes......Page 96
Summary......Page 100
What Is Normalization?......Page 102
Determining Normal Forms......Page 111
Denormalization......Page 112
Summary......Page 115
PART II: Business Requirements......Page 116
Chapter 5 Requirements Gathering......Page 118
Gathering Requirements Step by Step......Page 119
Business Needs......Page 132
Gathering Usage Data......Page 133
Summary......Page 137
Mountain View Music......Page 138
Compiling Requirements Data......Page 140
Determining Model Requirements......Page 142
Determining Business Rules......Page 159
Requirements Documentation......Page 162
Looking Ahead: The Business Review......Page 164
Summary......Page 166
PART III: Creating the Logical Model......Page 168
Diagramming a Data Model......Page 170
Using Requirements to Build the Model......Page 178
Building the Model......Page 185
Summary......Page 191
Entity Problems......Page 192
Attribute Problems......Page 197
Relationship Problems......Page 203
Summary......Page 206
PART IV: Creating the Physical Model......Page 208
Naming Guidelines......Page 210
Deriving the Physical Model......Page 219
Implementing Business Rules in the Physical Model......Page 232
Summary......Page 240
Indexing Overview......Page 242
Database Usage Requirements......Page 251
Determining the Appropriate Indexes......Page 254
Implementing Indexes in SQL Server......Page 257
Summary......Page 260
What Is an Abstraction Layer?......Page 262
Why Use an Abstraction Layer?......Page 263
An Abstraction Layer’s Relationship to the Logical Model......Page 266
An Abstraction Layer’s Relationship to Object-Oriented Programming......Page 267
Implementing an Abstraction Layer......Page 268
Summary......Page 275
Appendix A: Sample Logical Model......Page 276
Appendix B: Sample Physical Model......Page 282
Appendix C: SQL Server 2008 Reserved Words......Page 288
Appendix D: Recommended Naming Standards......Page 290
C......Page 292
E......Page 293
J......Page 294
N......Page 295
R......Page 296
V......Page 297
X......Page 298