Pro SQL Server 2000 Database Design : Building Quality OLTP Databases

by Davidson, Louis

• ISBN13: 9781590593028
• ISBN10: 1590593022
• Format: Paperback
• Copyright: 2004-02-01
• Publisher: Springer-Verlag New York Inc

Summary

This book takes an in-depth, practical look at the process of designing and building efficient relational databases. It is a book of two halves. The first covers the logical design of databases, and the second looks at the physical design and implementation on SQL Server 2000. Whereas many texts on this topic are rather dry and theoretical, this books considers design issues from a "real world" point of view. It provides a full and realistic case study that clearly illustrates the full process, from initial discussions of a client's needs, through development of a logical model, to a full implementation of the system.

Table of Contents

Introduction	xix
Chapter 1: Introduction to Database Concepts	1	(12)
Introduction	1	(1)
History of Relational Database Structures	1	(8)
The Four Modules of the Modem Database System	3	(1)
Online Transactional Processing	4	(1)
Operational Data Store	5	(1)
Data Warehouse	6	(1)
Data Marts	7	(2)
Architecture	9	(1)
An Overview of the SQL Server Database Design Process	9	(1)
Case Study Introduction	10	(1)
Summary	11	(2)
Chapter 2: Gathering Information for a Database Project	13	(14)
The Database Design Team	14	(1)
Documentation and Communication	15	(1)
Client Approval	16	(1)
Minimum Information Requirements	16	(1)
Database Prototypes	16	(1)
Client Interviews	17	(4)
Questions That Need Answers	18	(3)
Other Places to Look for Information	21	(1)
Outline of the Case Study	22	(4)
Client Interview	23	(1)
Preliminary Documentation	24	(2)
Summary	26	(1)
Chapter 3: Fundamental Database Concepts	27	(28)
Introduction	27	(1)
The Relational Model	28	(1)
Database	28	(1)
Table	28	(14)
Rows and Columns	30	(5)
Row Identifiers	35	(3)
Optional Attribute Values	38	(1)
Rowcount	38	(1)
Additional Considerations for Entities	39	(3)
Relational Operators	42	(6)
Relationships	48	(3)
Binary Relationships	48	(2)
Nonbinary Relationships	50	(1)
Definitions of Essential Concepts	51	(2)
Functional Dependency	51	(1)
Multivalued Dependency	52	(1)
Summary	53	(2)
Chapter 4: Entities, Attributes, Relationships, and Business Rules	55	(36)
Introduction	55	(2)
Divorcing Yourself from the Final Structure	56	(1)
Identifying Entities	57	(5)
People	58	(1)
Places	58	(1)
Objects	59	(1)
Ideas	59	(1)
Other Entities	60	(1)
A List of Entities	61	(1)
Identifying Attributes	62	(6)
Identifiers	62	(2)
Descriptive Information	64	(1)
Locators	65	(1)
Related Information	66	(1)
A List of Entities, Attributes, and Domains	66	(2)
Relationships Between Entities	68	(3)
One-to-n Relationships	68	(2)
Many-to-Many Relationships	70	(1)
Listing Relationships	70	(1)
Identifying Business Rules	71	(2)
Identifying Fundamental Processes	73	(2)
Is This Trip Over Yet?	75	(2)
Identification of Additional Data	75	(1)
Review with the Client	76	(1)
Repeat Until Gaining Customer Acceptance	77	(1)
Case Study	77	(11)
Client Interview	77	(2)
Documents	79	(3)
Bank Account Object Listing	82	(4)
Business Rules and Processes	86	(2)
Best Practices	88	(1)
Summary	88	(3)
Chapter 5: Data Modeling	91	(38)
Introduction	91	(1)
Data Models	92	(1)
Data Modeling Format	93	(1)
Entities	93	(3)
Entity Type	94	(1)
Naming	95	(1)
Attributes	96	(5)
Primary Key	96	(1)
Alternate Keys	97	(1)
Foreign Keys	98	(1)
Domains	98	(2)
Naming	100	(1)
Relationships	101	(18)
One-to-Many	103	(1)
Identifying Relationships	103	(1)
Nonidentifying Relationships	103	(2)
Mandatory	105	(1)
Optional	105	(1)
Cardinality	106	(1)
Role Names	107	(1)
Other Types of Relationships	108	(1)
Recursive	108	(2)
Categorization Relationships	110	(2)
Many-to-Many	112	(1)
Verb Phrases (Relationship Names)	113	(2)
Alternate Methods of Relationship Display	115	(4)
Descriptive Information	119	(1)
Case Study	120	(7)
Best Practices	127	(1)
Summary	128	(1)
Chapter 6: Normalization Techniques	129	(36)
Why Normalize?	130	(2)
Reducing NULLs	130	(1)
Eliminating Redundant Data	131	(1)
Avoiding Unnecessary Coding	131	(1)
Maximizing Clustered Indexes	131	(1)
Lowering the Number of Indexes per Table	131	(1)
Keeping Tables Thin	132	(1)
The Process of Normalization	132	(1)
Attribute Shape	133	(6)
All Attributes Must Be Atomic	133	(2)
All Instances in an Entity Must Contain the Same Number of Values	135	(1)
All Occurrences of a Row Type in an Entity Must Be Different	136	(1)
Programming Anomalies Avoided by the First Normal Form	136	(3)
Clues That Existing Data Isn't in First Normal Form	139	(1)
Relationships Between Attributes	139	(16)
Second Normal Form	140	(5)
Third Normal Form	145	(5)
Boyce-Codd Normal Form	150	(5)
Case Study	155	(8)
First Normal Form	155	(2)
Boyce-Codd Normal Form	157	(5)
Model	162	(1)
Best Practices	163	(1)
Summary	163	(2)
Chapter 7: Advanced Normalization Topics	165	(26)
Introduction	165	(1)
Fourth Normal Form	166	(13)
Ternary Relationships	166	(7)
Lurking Multivalued Attributes	173	(3)
Attribute History	176	(3)
Fifth Normal Form	179	(1)
Denormalization	180	(1)
Case Study	181	(8)
Example 1-Account.BalanceDate	181	(1)
Example 2-Payee.Address Id and Payee.Phone	182	(3)
Example 3-check.UsageType	185	(4)
Best Practices	189	(1)
Summary	189	(2)
Chapter 8: Ending the Logical Design Phase	191	(28)
Data Usage	192	(12)
Common Processes	193	(1)
Reporting	194	(4)
Determine Data Usage and Ownership	198	(2)
Interfacing with External Systems	200	(4)
Volumetrics	204	(1)
Project Plan	204	(2)
Test Plan	205	(1)
Final Documentation Review	206	(2)
Future Requirements	207	(1)
Case Study	208	(8)
User and Process Identification	208	(4)
Interfacing to External Systems	212	(1)
Volumetrics	213	(2)
Project Plan	215	(1)
Test Plan	215	(1)
Final Documentation Review	216	(1)
Best Practices	216	(1)
Summary	217	(2)
Chapter 9: Planning the Physical Architecture	219	(26)
Reporting Issues	221	(4)
Size of Data	221	(4)
Performance Concerns	225	(5)
Connection Speed	225	(3)
Amount of Data	228	(1)
Budget	228	(1)
Number of Users	229	(1)
SQL Server Facilities	230	(5)
Replication	230	(1)
Linked Servers	231	(1)
Data Transformation Services	232	(1)
Distributed Transaction Controller	232	(1)
SQL Data Management Objects (SQL-DMO)	233	(1)
COM Object Instantiation	233	(1)
SQL Mail	234	(1)
Full Text Search	234	(1)
SQL Server Agent	235	(1)
Basic Topology Discussion	235	(7)
Thin Client vs. Thick Client	235	(3)
Client to Data Configurations	238	(4)
Case Study	242	(1)
Best Practices	242	(1)
Summary	243	(2)
Chapter 10: Building the Basic Table Structures	245	(70)
Database Generation Tools	246	(1)
Physically Designing the Schema	246	(1)
Transforming the Logical Design	247	(4)
Subtypes	247	(4)
A Reason Not to Stray from the Logical Design	251	(1)
Tables	251	(4)
Naming	252	(1)
Owner	253	(1)
Limits	254	(1)
Columns	255	(7)
Naming	255	(2)
Choosing the Datatype	257	(5)
User-Defined Datatypes	262	(5)
Error Reporting Using Rules	263	(1)
Inconsistent Handling of Variables	264	(1)
UDT Cannot Be Modified When in Use	265	(1)
Calculated Columns	266	(1)
Physical-Only Columns	267	(6)
Concurrency Control	267	(4)
Collation (Sort Order)	271	(2)
Keys	273	(5)
Primary Keys	273	(3)
Alternate Keys	276	(1)
Naming	277	(1)
Other Indexes	278	(1)
Relationships	278	(4)
Foreign Keys	278	(1)
Naming	279	(1)
Cascading Deletes and Updates	280	(2)
Cross-Database Relationships	282	(1)
Sharing the Details of Your Database with Developers	282	(10)
Information Schema and System Stored Procedures	283	(3)
Descriptive Properties	286	(6)
Case Study	292	(20)
Cleaning Up the Model	292	(6)
Preparing the Model for Implementation	298	(11)
Physical Model	309	(3)
Best Practices	312	(1)
Summary	313	(2)
Chapter 11: Declarative Data Protection	315	(26)
Example Tables	316	(15)
Constraints	317	(1)
Default Constraints	318	(4)
Check Constraints	322	(7)
Handling Errors Caused by Constraints	329	(2)
Case Study	331	(8)
Default Constraints	332	(1)
Check Constraints	333	(6)
Best Practices	339	(1)
Summary	339	(2)
Chapter 12: Programmatic Data Protection	341	(26)
Triggers	342	(11)
Using AFTER Triggers to Solve Common Problems	343	(1)
Cascading Inserts	344	(1)
Range Checks	345	(1)
Cascading Deletes Setting the Values of Child Tables to NULL	346	(1)
Uses of INSTEAD OF Triggers	347	(6)
Client Code and Stored Procedures	353	(5)
Mutable Rules	355	(1)
Optionally Cascading Deletes	356	(1)
Rules That Are Based Upon How a Person Feels	357	(1)
Case Study	358	(6)
Remove the Time from Transaction Dates Trigger	359	(1)
Validate City and Zip Codes Match Trigger	360	(2)
Transaction Allocations Cannot Exceed the Amount Stored Trigger	362	(2)
Optional Rules	364	(1)
Best Practices	364	(1)
Summary	365	(2)
Chapter 13: Advanced Data Access and Modification	367	(92)
Query Considerations	368	(31)
Transactions	368	(4)
Major Coding Issues	372	(16)
Views	388	(7)
Batches of SQL Code	395	(4)
Stored Procedures	399	(32)
Returning Values from Stored Procedures	400	(3)
Error Handling	403	(7)
Encapsulation	410	(2)
Security	412	(1)
Transaction Count	413	(4)
Common Practices with Stored Procedures	417	(14)
Compiled SQL vs. Ad Hoc SQL for Building Apps	431	(5)
Ad Hoc SQL	431	(2)
Compiled SQL	433	(3)
Tips to Consider	436	(1)
Security Considerations	436	(6)
Cross-Database Considerations	442	(4)
Same Server	443	(2)
Different Server (Distributed Queries)	445	(1)
Case Study	446	(10)
Base Stored Procedures	446	(6)
Custom Stored Procedures	452	(3)
Security for the Processes	455	(1)
Best Practices	456	(1)
Summary	457	(2)
Chapter 14: Determining Hardware Requirements	459	(52)
Types of Databases	460	(1)
OLTP Databases	460	(1)
OLAP Databases	460	(1)
Growth of OLTP Tables	461	(1)
Rapid Growth	461	(1)
Slow Growth	461	(1)
No Growth	462	(1)
Growth of OLAP Tables	462	(1)
Batch Growth	462	(1)
Growth Through Company Growth	463	(1)
"We Need More Reports!"	463	(1)
Don't Forget the Indexes	463	(1)
Calculating Complete Table Size	463	(1)
Data Size Calculation	464	(4)
Reviewing Index B-Tree Structures	467	(1)
Index Size Calculation	468	(3)
Transaction Log Size	471	(2)
Archive Data When It Makes Sense	473	(1)
The Cost of Archiving	473	(4)
Archiving Details	474	(1)
Archiving by Time Period	474	(1)
Archiving by Fact Table Date-Partitioning	475	(1)
Archiving by Fact Table Characteristics	475	(1)
Accessing Archived Data	476	(1)
Server Performance	477	(4)
Windows Server 2003 Editions	477	(1)
Memory Subsystems	478	(3)
Memory Performance Monitoring	481	(7)
The Art of Performance Monitoring	482	(1)
Memory Tuning: The Operating System and SQL Server	483	(1)
SQL Server 2000 Dynamic Memory Tuning	484	(2)
Free Memory Target	486	(1)
Multiple Instance Memory Tuning	486	(2)
SQL Server Process Memory Tuning	488	(1)
Adjusting Server Performance	488	(2)
CPU Subsystems	490	(2)
CPU Performance Monitoring	491	(1)
Textile Management	492	(1)
Disk Subsystems	492	(7)
The Basic Disk Subsystem	493	(1)
The RAID Subsystem	494	(3)
Multiple Controller/Channel Solutions	497	(1)
Disk Tuning and Performance Monitoring	498	(1)
User Connections	499	(5)
Locking and Blocking	501	(1)
Monitoring Blocking	502	(2)
Case Study	504	(5)
Summary	509	(2)
Chapter 15: Completing the Project	511	(32)
Performance Tuning	512	(1)
Read-Only Support Databases	513	(18)
Modeling the Transformation	515	(12)
Uses	527	(4)
Enterprise Data Models	531	(2)
Moving from a Test Environment	533	(4)
Development	534	(1)
Quality Assurance	535	(1)
Production	536	(1)
Case Study	537	(5)
Best Practices	541	(1)
Summary	542	(1)
Index	543