Pro SQL Server 2005 Database Design And Optimization

by Davidson, Louis

• ISBN13: 9781590595299
• ISBN10: 1590595297
• Format: Paperback
• Copyright: 2006-04-24
• Publisher: Springer-Verlag New York Inc

Summary

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.

Table of Contents

Foreword	xv
About the Authors	xvii
About the Technical Reviewers	xix
Acknowledgments	xxi
Introduction	xxiii
Introduction to Database Concepts	1	(32)
Database Design Phases	2	(4)
Conceptual	3	(1)
Logical	4	(1)
Implementation	5	(1)
Physical	6	(1)
Relational Data Structures	6	(16)
Database and Schema	7	(1)
Tables, Rows, and Columns	7	(4)
The Information Principle	11	(2)
Domains	13	(1)
Metadata	14	(1)
Keys	14	(7)
Missing Values (NULLs)	21	(1)
Relationships	22	(6)
Foreign Keys	22	(1)
Types of Relationships	23	(5)
Data Access Language (SQL)	28	(1)
Understanding Dependencies	29	(3)
Functional Dependency	30	(1)
Determinant	31	(1)
Multivalued Dependency	31	(1)
Summary	32	(1)
Data Modeling	33	(38)
Introduction to Data Modeling	33	(2)
Entities	35	(3)
Entity Naming	36	(2)
Attributes	38	(8)
Primary Key	39	(2)
Alternate Keys	41	(1)
Foreign Keys	42	(1)
Domains	43	(2)
Naming	45	(1)
Relationships	46	(16)
Identifying Relationship	47	(1)
Nonidentifying Relationship	48	(1)
Optional Identifying Relationship	49	(2)
Cardinality	51	(1)
Role Names	52	(2)
Other Types of One-to-N Relationships	54	(2)
Subtypes	56	(2)
Many-to-Many Relationship	58	(2)
Verb Phrases (Relationship Names)	60	(2)
Descriptive Information	62	(2)
Alternative Modeling Methodologies	64	(4)
Information Engineering	64	(2)
Chen ERD	66	(1)
Management Studio Database Diagrams	67	(1)
Best Practices	68	(1)
Summary	69	(2)
Conceptual Data Modeling	71	(50)
Understanding the Requirements	72	(1)
Documenting the Process	73	(2)
Requirements Gathering	75	(7)
Client Interviews	75	(1)
Questions to Be Answered	76	(4)
Existing Systems and Prototypes	80	(1)
Other Types of Documentation	81	(1)
Identifying Objects and Processes	82	(30)
Identifying Entities	84	(8)
Relationships Between Entities	92	(7)
Identifying Attributes and Domains	99	(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