Pro SQL Server 2005 Database Design and Optimization

Title Pro SQL Server 2005 Database Design and Optimization Authors Louis Davidson, with Kevin Kline and Kurt Windisch Publisher APress Published 2006 ISBN 981-1-59059-529-9 Price USD 59.99 Pages 644

Introduction

A while ago, I was looking for a book to brush up on my SQL Server database design skills. I found Pro SQL Server 2005 Database Design and Optimization to be a very helpful addition to my library. The book is filled with 11 chapters of very useful information. Though the back cover states the book is targeted towards the Intermediate/Advanced users, the first five chapters are very helpful for beginners when it comes to database design and concepts. This review is laid out as a quick summary of what is covered in each chapter. This book is a highly recommended book to have in any I.T. library whether it be at the office or at home.

Chapters 1-5: Design

Davidson introduces the very basics of good database design in the first chapter of the book. He covers database design phases, how to set up relational data structures, relationships and foreign keys, SQL, and data dependencies. One important part of this chapter in regards to database design is that he covers conceptual ideas, logical, and physical implementations of the database.

In Chapter 2, he introduces the reader to data modeling concepts. He starts off by discussing entities and attributes. He discusses concepts of primary keys, alternate keys, foreign keys, and domains. In this chapter, he does a very good job at explaining identifying and non-identifying relationships. He also cover one-to-many relationships, and many-to-many relationships. He concludes each of the chapters with a best practices section, which is very helpful for even the more experienced database designer.

He covers conceptual data modeling in Chapter 3. He takes most of everything from Chapter 2 and extends it in a more advance notion. He does cover four very good topics that should be addressed when putting together the conceptual model. These topics are: understanding and gathering requirements, documenting the process, identifying business rules and process, and finishing the conceptual model with client verification of what they need.

Chapter 4 is perhaps the biggest chapter in the book for the beginning reader. It covers a topic that most database designers may have a difficult time working with at first. It covers the database normalization process. He does an excellent job at describing the five normal forms and gives examples of how each one works.

In Chapter 5, the authors cover how to implement the base table structures. In this chapter, the book covers reviewing of the logical design of the database, naming concerns, correctly choosing the appropriate fields for primary keys, and implementing the actual design into a database.

Chapters 6-7: Data

In both Chapters 6 and 7, the authors cover data integrity, and securing data access, respectively. Chapter 6 introduces constraints, programmatic data protection in the form of DML triggers, stored procedures, and also how to protect data outside of the database. In Chapter 7, data security is discussed in the form of principals and securables, data obfuscation, and watching user behavior by using triggers to implement a table history or audit trail.

Chapters 8-11: Optimization

The next several chapters cover how to optimize the database. In Chapter 8, the authors introduce indexing of database tables. They cover the basics, as well as advanced usage of indexes.

In Chapter 9, coding for concurrency is introduced. The authors cover query optimization, OS/hardware issues, handling transactions, concurrency controls, and pessimistic and optimistic locking.

Chapter 10 covers code level architectural design and decisions. This chapter covers the benefits of ad-hoc SQL, stored procedures, and author opinion about each. The biggest thing in this chapter was on using .NET CLR in SQL Server 2005 databases. The example code is pretty straightforward and once understood, it is easy to implement.

Chapter 11 wraps up the book by covering database interoperability. This particular chapter is very useful when it comes to working with more than one database platform. It covers the comparable data types from most of the major database platforms out there (Oracle, MySQL, SQL, and DB2).

Points of Interest

The key points of interest in this book were the quick review of database design, data security, and optimizing the database to the best it can be based on the client requirements.

History

• 8/9/2008: Initial article