Pro SQL Server 2008 Relational Database Design and Implementation (eBook)

Louis Davidson has been in the IT industry for more than 15 years as a corporate database developer and architect. He has spent the majority of his career working with Microsoft SQL Server, beginning from the early days of version 1.0. He has a bachelor s degree from the University of Tennessee at Chattanooga in computer science, with a minor in mathematics. Louis is the data architect for Compass Technology (Compass.net) in Chesapeake, Virginia, leading database development on their suite of nonprofit oriented CRM products, built on the Microsoft CRM platform and SQL Server technologies.

Contents at a Glance 5
Contents 6
Foreword 14
About the Author 15
About the Contributing Authors 15
About the Technical Reviewers 16
Acknowledgments 17
Introduction 19
Purpose of Database Design 20
Structure of This Book 21
Introduction to Database Concepts 23
Database Design Phases 24
Relational Data Structures 29
Relationship Between Entities 45
Data Access Language (SQL) 51
Understanding Dependencies 52
Summary 54
The Language of Data Modeling 55
Introduction to Data Modeling 55
Entities 57
Attributes 60
Relationships 68
Descriptive Information 83
Alternative Modeling Methodologies 84
Best Practices 90
Summary 91
Conceptual Data Modeling 93
Understanding the Requirements 94
Documenting the Process 95
Requirements Gathering 96
Identifying Objects and Processes 104
Identifying Business Rules and Processes 130
Finishing the Conceptual Model 134
Best Practices 137
Summary 137
The Normalization Process 138
Why Normalize? 139
How Far to Normalize? 141
The Process of Normalization 141
Entity and Attribute Shape: First Normal Form 142
Relationships Between Attributes 155
Multivalued Dependencies in Entities 172
Denormalization 187
Best Practices 188
Summary 188
Bonus Example 189
The Story of the Book So Far 196
Implementing the Base Table Structures 197
Reviewing the Logical Design 201
Transforming the Design 201
Implementing the Design 231
Best Practices 290
Summary 291
Protecting the Integrity of Your Data 292
Best Practices 293
Automatic Data Protection 295
Manual Data Protection 349
More Best Practices 356
Summary 357
Patterns and Query Techniques 358
Precalculated Values 359
Binary Large Valued Objects (BLOB) 376
Storing User-Specified Data 379
Commonly Implemented Objects 392
Anti-patterns 393
Summary 402
The Continuing Story of the Book So Far 403
Securing Access to the Data 404
Principals and Securables 406
Database Security Overview 407
Controlling Object Access Via T-SQL Coded Objects 424
Views and Table-Valued Functions 438
Obfuscating Data 445
Monitoring and Auditing 448
Best Practices 462
Summary 463
Table Structures and Indexing 465
Physical Database Structure 466
Indexes Overview 478
Basic Index Structure 478
Index Types 480
Basics of Index Creation 486
Basic Index Usage Patterns 488
Advanced Index Usage Scenarios 505
Best Practices 513
Summary 514
Coding for Concurrency 516
What Is Concurrency? 517
Query Optimization Basics 518
OS and Hardware Issues 521
Transactions 522
SQL Server Concurrency Controls 539
Coding for Integrity and Concurrency 556
Best Practices 569
Summary 570
Considering Data Access Strategies 571
Ad Hoc SQL 573
Stored Procedures 585
T-SQL and the CLR 600
Best Practices 608
Summary 610
Codd’s 12 Rules for an RDBMS 611
Rule 1: The Information Rule 611
Rule 2: Guaranteed Access Rule 612
Rule 3: Systematic Treatment of NULL Values 612
Rule 4: Dynamic Online Catalog Based on the Relational Model 612
Rule 5: Comprehensive Data Sublanguage Rule 613
Rule 6: View Updating Rule 613
Rule 7: High-Level Insert, Update, and Delete 614
Rule 8: Physical Data Independence 614
Rule 9: Logical Data Independence 615
Rule 10: Integrity Independence 616
Rule 11: Distribution Independence 616
Rule 12: Non-Subversion Rule 616
Summary 617
Scalar Datatype Reference 618
Precise Numeric Data 620
Date and Time Data 628
Character Strings 634
Binary Data 638
Other Datatypes 640
Not Simply Scalar Datatypes 648
Index 650