
Pro SQL Server 2005 Database Design and Optimization
By: Louis Davidson, Kurt Windisch (As told to), Kevin Kline (As told to)
Paperback | 1 May 2006
At a Glance
676 Pages
22.86 x 17.78 x 3.18
Paperback
RRP $89.99
$88.99
or 4 interest-free payments of $22.25 with
 orÂShips in 5 to 7 business days
This book teaches the SQL Server database professional effective strategies for designing proper databases, covering everything from how to gather business requirements, to logical data modeling, and normalization. It then shows you how to implement your design on SQL Server 2005.
However, this new fully-revised edition does not stop there. Having established an understanding of the process of designing and implementing SQL Server 2005 databases, it then provides completely new material describing how to optimize and secure access to this data, covering indexing strategies, SQL design and optimization as well as strategies for increased scalability to support large numbers of concurrent users. It also provides in-depth advice on optimal code distribution in SQL Server 2005 applications, in light of innovations such as the ability to use .NET code in the database itself (see SQL Server 2005 Assemblies Revealed, Apress, ISBN: 1-59059-412-6).
| Foreword | p. xv |
| About the Authors | p. xvii |
| About the Technical Reviewers | p. xix |
| Acknowledgments | p. xxi |
| Introduction | p. xxiii |
| Introduction to Database Concepts | p. 1 |
| Database Design Phases | p. 2 |
| Conceptual | p. 3 |
| Logical | p. 4 |
| Implementation | p. 5 |
| Physical | p. 6 |
| Relational Data Structures | p. 6 |
| Database and Schema | p. 7 |
| Tables, Rows, and Columns | p. 7 |
| The Information Principle | p. 11 |
| Domains | p. 13 |
| Metadata | p. 14 |
| Keys | p. 14 |
| Missing Values (NULLs) | p. 21 |
| Relationships | p. 22 |
| Foreign Keys | p. 22 |
| Types of Relationships | p. 23 |
| Data Access Language (SQL) | p. 28 |
| Understanding Dependencies | p. 29 |
| Functional Dependency | p. 30 |
| Determinant | p. 31 |
| Multivalued Dependency | p. 31 |
| Summary | p. 32 |
| Data Modeling | p. 33 |
| Introduction to Data Modeling | p. 33 |
| Entities | p. 35 |
| Entity Naming | p. 36 |
| Attributes | p. 38 |
| Primary Key | p. 39 |
| Alternate Keys | p. 41 |
| Foreign Keys | p. 42 |
| Domains | p. 43 |
| Naming | p. 45 |
| Relationships | p. 46 |
| Identifying Relationship | p. 47 |
| Nonidentifying Relationship | p. 48 |
| Optional Identifying Relationship | p. 49 |
| Cardinality | p. 51 |
| Role Names | p. 52 |
| Other Types of One-to-N Relationships | p. 54 |
| Subtypes | p. 56 |
| Many-to-Many Relationship | p. 58 |
| Verb Phrases (Relationship Names) | p. 60 |
| Descriptive Information | p. 62 |
| Alternative Modeling Methodologies | p. 64 |
| Information Engineering | p. 64 |
| Chen ERD | p. 66 |
| Management Studio Database Diagrams | p. 67 |
| Best Practices | p. 68 |
| Summary | p. 69 |
| Conceptual Data Modeling | p. 71 |
| Understanding the Requirements | p. 72 |
| Documenting the Process | p. 73 |
| Requirements Gathering | p. 75 |
| Client Interviews | p. 75 |
| Questions to Be Answered | p. 76 |
| Existing Systems and Prototypes | p. 80 |
| Other Types of Documentation | p. 81 |
| Identifying Objects and Processes | p. 82 |
| Identifying Entities | p. 84 |
| Relationships Between Entities | p. 92 |
| Identifying Attributes and Domains | p. 99 |
| Identifying Business Rules and Processes | p. 112 |
| Identifying Business Rules | p. 112 |
| Identifying Fundamental Processes | p. 114 |
| Finishing the Conceptual Model | p. 116 |
| Identifying Obvious Additional Data Needs | p. 116 |
| Review with the Client | p. 117 |
| Repeat Until the Customer Agrees with Your List of Objects | p. 118 |
| Best Practices | p. 118 |
| Summary | p. 119 |
| The Normalization Process | p. 121 |
| Why Normalize? | p. 122 |
| Eliminating Duplicated Data | p. 122 |
| Avoiding Unnecessary Coding | p. 122 |
| Keeping Tables Thin | p. 122 |
| Maximizing Clustered Indexes | p. 123 |
| Lowering the Number of Indexes Per Table | p. 123 |
| How Far to Normalize | p. 123 |
| The Process of Normalization | p. 124 |
| Entity and Attribute Shape: First Normal Form | p. 125 |
| All Attributes Must Be Atomic | p. 125 |
| All Instances in an Entity Must Contain the Same Number of Values | p. 129 |
| All Occurrences of an Entity Type in an Entity Must Be Different | p. 131 |
| Programming Anomalies Avoided by First Normal Form | p. 132 |
| Clues That Existing Data Is Not in First Normal Form | p. 136 |
| Relationships Between Attributes | p. 137 |
| Second Normal Form | p. 138 |
| Third Normal Form | p. 144 |
| Boyce-Codd Normal Form | p. 151 |
| Multivalued Dependencies in Entities | p. 155 |
| Fourth Normal Form | p. 156 |
| Fifth Normal Form | p. 169 |
| Denormalization | p. 171 |
| Best Practices | p. 171 |
| Summary | p. 172 |
| Bonus Example | p. 173 |
| The Story of the Book So Far | p. 179 |
| Implementing the Base Table Structures | p. 181 |
| The Design Process | p. 181 |
| Reviewing the Logical Design | p. 185 |
| Transforming the Design | p. 186 |
| Naming Concerns | p. 186 |
| Dealing with Subtypes | p. 190 |
| Choosing Primary Keys | p. 195 |
| Domain Specification | p. 201 |
| Setting up Schemas | p. 213 |
| Reviewing the "Final" Implementation Model | p. 214 |
| Property Tables | p. 215 |
| Implementing the Design | p. 217 |
| Basic Table Creation | p. 218 |
| Uniqueness Keys | p. 228 |
| Default Constraints | p. 233 |
| Relationships (Foreign Keys) | p. 239 |
| Large-Value Datatype Columns | p. 251 |
| Collation (Sort Order) | p. 253 |
| Computed Columns | p. 255 |
| Implementing Complex Datatypes | p. 257 |
| Documentation | p. 266 |
| Best Practices | p. 270 |
| Summary | p. 271 |
| Protecting the Integrity of Your Data | p. 273 |
| Best Practices | p. 274 |
| Constraints | p. 276 |
| Example Schema | p. 277 |
| Basic Syntax | p. 278 |
| Constraints Based on Functions | p. 283 |
| Handling Errors Caused by Constraints | p. 286 |
| Programmatic Data Protection | p. 289 |
| DML Triggers | p. 289 |
| Stored Procedures | p. 326 |
| Programmatic Data Protection Outside the RDBMS | p. 329 |
| More Best Practices | p. 332 |
| Summary | p. 333 |
| The Continuing Story of the Book So Far | p. 333 |
| Securing Access to the Data | p. 335 |
| Controlling Data Access | p. 337 |
| Principals and Securables | p. 337 |
| Database Security Overview | p. 339 |
| Controlling Object Access Via Coded Objects | p. 357 |
| Views and Table-Valued Functions | p. 370 |
| Obfuscating Data | p. 377 |
| Keeping an Eye on Users | p. 380 |
| Watching Table History Using Triggers | p. 381 |
| DDL Triggers | p. 385 |
| Logging with Profiler | p. 388 |
| Best Practices | p. 391 |
| Summary | p. 392 |
| Table Structures and Indexing | p. 395 |
| Physical Database Structure | p. 396 |
| Files and Filegroups | p. 396 |
| Extents and Pages | p. 399 |
| Indexes Overview | p. 402 |
| Basic Index Structure | p. 402 |
| Index Types | p. 404 |
| Basics of Index Creation | p. 409 |
| Basic Index Usage | p. 411 |
| Advanced Index Usage Scenarios | p. 427 |
| Foreign Key Indexes | p. 428 |
| Using Indexed Views to Optimize Denormalizations | p. 432 |
| Best Practices | p. 435 |
| Summary | p. 436 |
| Coding for Concurrency | p. 439 |
| What Is Concurrency? | p. 439 |
| Query Optimization Basics | p. 441 |
| OS and Hardware Issues | p. 443 |
| Transactions | p. 444 |
| Transaction Syntax | p. 445 |
| Compiled SQL Server Code | p. 453 |
| SQL Server Concurrency Controls | p. 460 |
| Locks | p. 460 |
| Isolation Levels | p. 465 |
| Coding for Integrity and Concurrency | p. 475 |
| Pessimistic Locking | p. 476 |
| Optimistic Locking | p. 478 |
| Logical Unit of Work | p. 485 |
| Best Practices | p. 487 |
| Summary | p. 488 |
| Code-Level Architectural Decisions | p. 489 |
| Data-Access Strategies | p. 489 |
| Ad Hoc SQL | p. 490 |
| Stored Procedures | p. 501 |
| Opinions | p. 512 |
| Choosing Between T-SQL and CLR | p. 514 |
| Good Reasons to Use .NET | p. 515 |
| Hosting the CLR | p. 516 |
| Using the .NET CLR for SQL Server Objects | p. 518 |
| Guidelines and Opinions | p. 536 |
| Best Practices | p. 537 |
| Summary | p. 538 |
| Database Interoperability | p. 541 |
| Datatypes | p. 541 |
| Identifier Rules | p. 543 |
| Basic SQL Statements | p. 545 |
| The DELETE Statement | p. 546 |
| The INSERT Statement | p. 549 |
| The SELECT Statement | p. 552 |
| The UPDATE Statement | p. 557 |
| Creating Database Objects | p. 560 |
| Creating Tables | p. 560 |
| Creating Indexes | p. 564 |
| Creating Views | p. 566 |
| Creating Triggers | p. 567 |
| Creating Procedures and Functions | p. 570 |
| Best Practices | p. 571 |
| Summary | p. 572 |
| Codd's 12 Rules for an RDBMS | p. 573 |
| The Information Rule | p. 573 |
| Guaranteed Access Rule | p. 574 |
| Systematic Treatment of NULL Values | p. 574 |
| Dynamic On-Line Catalog Based on the Relational Model | p. 575 |
| Comprehensive Data Sublanguage Rule | p. 575 |
| View Updating Rule | p. 576 |
| High-Level Insert, Update, and Delete | p. 576 |
| Physical Data Independence | p. 576 |
| Logical Data Independence | p. 577 |
| Integrity Independence | p. 578 |
| Distribution Independence | p. 579 |
| Non-Subversion Rule | p. 579 |
| Summary | p. 580 |
| Datatype Reference | p. 581 |
| Precise Numeric Data | p. 582 |
| Integer Numbers | p. 583 |
| Decimal Values | p. 585 |
| Approximate Numeric Data | p. 589 |
| Date and Time Data | p. 591 |
| smalldatetime | p. 591 |
| datetime | p. 591 |
| Using User-Defined Datatypes to Manipulate Dates and Times | p. 592 |
| Character Strings | p. 594 |
| char(length) | p. 595 |
| varchar(length) | p. 596 |
| varchar(max) | p. 596 |
| text | p. 598 |
| Unicode Character Strings: nchar, nvarchar, nvarchar(max), ntext | p. 598 |
| Binary Data | p. 598 |
| binary(length) | p. 599 |
| varbinary(length) | p. 600 |
| varbinary(max) | p. 600 |
| image | p. 601 |
| Other Datatypes | p. 601 |
| rowversion (a.k.a. timestamp) | p. 601 |
| uniqueidentifier | p. 602 |
| cursor | p. 605 |
| table | p. 605 |
| XML | p. 607 |
| sql_variant Data | p. 607 |
| Summary | p. 611 |
| Index | p. 613 |
| Table of Contents provided by Ingram. All Rights Reserved. |
ISBN: 9781590595299
ISBN-10: 1590595297
Series: Pro
Published: 1st May 2006
Format: Paperback
Language: English
Number of Pages: 676
Audience: General Adult
Publisher: Springer Nature B.V.
Country of Publication: US
Dimensions (cm): 22.86 x 17.78 x 3.18
Weight (kg): 1.16
Shipping
| Standard Shipping | Express Shipping | |
|---|---|---|
| Metro postcodes: | $9.99 | $14.95 |
| Regional postcodes: | $9.99 | $14.95 |
| Rural postcodes: | $9.99 | $14.95 |
Orders over $79.00 qualify for free shipping.
How to return your order
At Booktopia, we offer hassle-free returns in accordance with our returns policy. If you wish to return an item, please get in touch with Booktopia Customer Care.
Additional postage charges may be applicable.
Defective items
If there is a problem with any of the items received for your order then the Booktopia Customer Care team is ready to assist you.
For more info please visit our Help Centre.
You Can Find This Book In

Data Governance: The Definitive Guide
People, Processes, and Tools to Operationalize Data Trustworthiness
Paperback
RRP $152.00
$73.75
OFF

Apache Iceberg: The Definitive Guide
Data Lakehouse Functionality, Performance, and Scalability on the Data Lake
Paperback
RRP $133.00
$64.75
OFF






















