Pro SQL Server 2005 Database Design And Optimization

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9781590595299 | 1590595297
Cover: Paperback
Copyright: 4/24/2006

Buy New

In Stock Usually Ships in 24-48 Hours

$57.61
eBook

Available Instantly

Online: 180 Days

Downloadable: 180 Days

$40.22

Pro SQL Server 2005 Database Design and Optimization will teach you effective strategies for designing proper databases. It covers 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. This essential book will ensure that projects have a well-designed database and secure, optimized data access strategies right from the start.

Foreword

About the Authors

xvii

About the Technical Reviewers

xix

Acknowledgments

xxi

Introduction

xxiii

Introduction to Database Concepts

(32)

Database Design Phases

(4)

Conceptual

(1)

Logical

(1)

Implementation

(1)

Physical

(1)

Relational Data Structures

(16)

Database and Schema

(1)

Tables, Rows, and Columns

(4)

The Information Principle

(2)

Domains

(1)

Metadata

(1)

Keys

(7)

Missing Values (NULLs)

(1)

Relationships

(6)

Foreign Keys

(1)

Types of Relationships

(5)

Data Access Language (SQL)

(1)

Understanding Dependencies

(3)

Functional Dependency

(1)

Determinant

(1)

Multivalued Dependency

(1)

Summary

(1)

Data Modeling

(38)

Introduction to Data Modeling

(2)

Entities

(3)

Entity Naming

(2)

Attributes

(8)

Primary Key

(2)

Alternate Keys

(1)

Foreign Keys

(1)

Domains

(2)

Naming

(1)

Relationships

(16)

Identifying Relationship

(1)

Nonidentifying Relationship

(1)

Optional Identifying Relationship

(2)

Cardinality

(1)

Role Names

(2)

Other Types of One-to-N Relationships

(2)

Subtypes

(2)

Many-to-Many Relationship

(2)

Verb Phrases (Relationship Names)

(2)

Descriptive Information

(2)

Alternative Modeling Methodologies

(4)

Information Engineering

(2)

Chen ERD

(1)

Management Studio Database Diagrams

(1)

Best Practices

(1)

Summary

(2)

Conceptual Data Modeling

(50)

Understanding the Requirements

(1)

Documenting the Process

(2)

Requirements Gathering

(7)

Client Interviews

(1)

Questions to Be Answered

(4)

Existing Systems and Prototypes

(1)

Other Types of Documentation

(1)

Identifying Objects and Processes

(30)

Identifying Entities

(8)

Relationships Between Entities

(7)

Identifying Attributes and Domains

(13)

Identifying Business Rules and Processes

112

(4)

Identifying Business Rules

112

(2)

Identifying Fundamental Processes

114

(2)

Finishing the Conceptual Model

116

(2)

Identifying Obvious Additional Data Needs

116

(1)

Review with the Client

117

(1)

Repeat Until the Customer Agrees with Your List of Objects

118

(1)

Best Practices

118

(1)

Summary

119

(2)

The Normalization Process

121

(60)

Why Normalize?

122

(1)

Eliminating Duplicated Data

122

(1)

Avoiding Unnecessary Coding

122

(1)

Keeping Tables Thin

122

(1)

Maximizing Clustered Indexes

123

(1)

Lowering the Number of Indexes Per Table

123

(1)

How Far to Normalize

123

(1)

The Process of Normalization

124

(1)

Entity and Attribute Shape: First Normal Form

125

(12)

All Attributes Must Be Atomic

125

(4)

All Instances in an Entity Must Contain the Same Number of Values

129

(2)

All Occurrences of an Entity Type in an Entity Must Be Different

131

(1)

Programming Anomalies Avoided by First Normal Form

132

(4)

Clues That Existing Data Is Not in First Normal Form

136

(1)

Relationships Between Attributes

137

(18)

Second Normal Form

138

(6)

Third Normal Form

144

(7)

Boyce-Codd Normal Form

151

(4)

Multivalued Dependencies in Entities

155

(16)

Fourth Normal Form

156

(13)

Fifth Normal Form

169

(2)

Denormalization

171

(1)

Best Practices

171

(1)

Summary

172

(1)

Bonus Example

173

(6)

The Story of the Book So Far

179

(2)

Implementing the Base Table Structures

181

(92)

The Design Process

181

(4)

Reviewing the Logical Design

185

(1)

Transforming the Design

186

(31)

Naming Concerns

186

(4)

Dealing with Subtypes

190

(5)

Choosing Primary Keys

195

(6)

Domain Specification

201

(12)

Setting up Schemas

213

(1)

Reviewing the ``Final'' Implementation Model

214

(1)

Property Tables

215

(2)

Implementing the Design

217

(53)

Basic Table Creation

218

(10)

Uniqueness Keys

228

(5)

Default Constraints

233

(6)

Relationships (Foreign Keys)

239

(12)

Large-Value Datatype Columns

251

(2)

Collation (Sort Order)

253

(2)

Computed Columns

255

(2)

Implementing Complex Datatypes

257

(9)

Documentation

266

(4)

Best Practices

270

(1)

Summary

271

(2)

Protecting the Integrity of Your Data

273

(62)

Best Practices

274

(2)

Constraints

276

(13)

Example Schema

277

(1)

Basic Syntax

278

(5)

Constraints Based on Functions

283

(3)

Handling Errors Caused by Constraints

286

(3)

Programmatic Data Protection

289

(37)

DML Triggers

289

(37)

Stored Procedures

326

(3)

Programmatic Data Protection Outside the RDBMS

329

(3)

More Best Practices

332

(1)

Summary

333

(1)

The Continuing Story of the Book So Far

333

(2)

Securing Access to the Data

335

(60)

Controlling Data Access

337

(40)

Principals and Securables

337

(2)

Database Security Overview

339

(18)

Controlling Object Access Via Coded Objects

357

(13)

Views and Table-Valued Functions

370

(7)

Obfuscating Data

377

(3)

Keeping an Eye on Users

380

(11)

Watching Table History Using Triggers

381

(4)

DDL Triggers

385

(3)

Logging with Profiler

388

(3)

Best Practices

391

(1)

Summary

392

(3)

Table Structures and Indexing

395

(44)

Physical Database Structure

396

(6)

Files and Filegroups

396

(3)

Extents and Pages

399

(3)

Indexes Overview

402

(25)

Basic Index Structure

402

(2)

Index Types

404

(5)

Basics of Index Creation

409

(2)

Basic Index Usage

411

(16)

Advanced Index Usage Scenarios

427

(8)

Foreign Key Indexes

428

(4)

Using Indexed Views to Optimize Denormalizations

432

(3)

Best Practices

435

(1)

Summary

436

(3)

Coding for Concurrency

439

(50)

What Is Concurrency?

439

(2)

Query Optimization Basics

441

(2)

OS and Hardware Issues

443

(1)

Transactions

444

(16)

Transaction Syntax

445

(8)

Compiled SQL Server Code

453

(7)

SQL Server Concurrency Controls

460

(15)

Locks

460

(5)

Isolation Levels

465

(10)

Coding for Integrity and Concurrency

475

(12)

Pessimistic Locking

476

(2)

Optimistic Locking

478

(7)

Logical Unit of Work

485

(2)

Best Practices

487

(1)

Summary

488

(1)

Code-Level Architectural Decisions

489

(52)

Data-Access Strategies

489

(25)

Ad Hoc SQL

490

(11)

Stored Procedures

501

(11)

Opinions

512

(2)

Choosing Between T-SQL and CLR

514

(23)

Good Reasons to Use .NET

515

(1)

Hosting the CLR

516

(2)

Using the .NET CLR for SQL Server Objects

518

(18)

Guidelines and Opinions

536

(1)

Best Practices

537

(1)

Summary

538

(3)

Database Interoperability

541

(32)

Step One: Datatypes

541

(2)

Step Two: Identifier Rules

543

(2)

Step Three: Basic SQL Statements

545

(15)

The Delete Statement

546

(3)

The Insert Statement

549

(3)

The Select Statement

552

(5)

The Update Statement

557

(3)

Step Four: Creating Database Objects

560

(11)

Creating Tables

560

(4)

Creating Indexes

564

(2)

Creating Views

566

(1)

Creating Triggers

567

(3)

Creating Procedures and Functions

570

(1)

Best Practices

571

(1)

Summary

572

(1)

APPENDIX A Codd's 12 Rules for an RDBMS

573

(8)

Rule 1: The Information Rule

573

(1)

Rule 2: Guaranteed Access Rule

574

(1)

Rule 3: Systematic Treatment of NULL Values

574

(1)

Rule 4: Dynamic On-Line Catalog Based on the Relational Model

575

(1)

Rule 5: Comprehensive Data Sublanguage Rule

575

(1)

Rule 6: View Updating Rule

576

(1)

Rule 7: High-Level Insert, Update, and Delete

576

(1)

Rule 8: Physical Data Independence

576

(1)

Rule 9: Logical Data Independence

577

(1)

Rule 10: Integrity Independence

578

(1)

Rule 11: Distribution Independence

579

(1)

Rule 12: Non-Subversion Rule

579

(1)

Summary

580

(1)

APPENDIX B Datatype Reference

581

(32)

Precise Numeric Data

582

(9)

Integer Numbers

583

(2)

Decimal Values

585

(4)

Approximate Numeric Data

589

(2)

Date and Time Data

591

(3)

smalldatetime

591

(1)

datetime

591

(1)

Using User-Defined Datatypes to Manipulate Dates and Times

592

(2)

Character Strings

594

(4)

char(length)

595

(1)

varchar(length)

596

(1)

varchar(max)

596

(2)

text

598

(1)

Unicode Character Strings: nchar, nvarchar, nvarchar(max), ntext

598

(1)

Binary Data

598

(3)

binary(length)

599

(1)

varbinary(length)

600

(1)

varbinary(max)

600

(1)

image

601

(1)

Other Datatypes

601

(10)

rowversion (a.k.a. timestamp)

601

(1)

uniqueidentifier

602

(3)

cursor

605

(1)

table

605

(2)

XML

607

(1)

sql_variant Data

607

(4)

Summary

611

(2)

Index

613

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Pro SQL Server 2005 Database Design And Optimization

Summary

Table of Contents

Supplemental Materials