Contents
Taking a Brief Jaunt Through History
Introducing Codd’s Rules for an RDBMS
Recognizing Relational Data Structures
Introducing Databases and Schemas
Understanding Tables, Rows, and Columns
Working with Missing Values (NULLs)
Assigning Uniqueness Constraints (Keys)
Working with Binary Relationships
Working with Nonbinary Relationships
Working with Functional Dependencies
Outlining the Database-Specific Project Phases
Chapter 2: Introduction to Requirements
What Rules Govern the Use of the Data?
Will the Data Need to Be Integrated with Other Systems?
Working with Existing Systems and Prototypes
Utilizing Other Types of Documentation
Contracts or Client Work Orders
Chapter 3: The Language of Data Modeling
Verb Phrases (Relationship Names)
Alternative Modeling Methodologies
Management Studio Database Diagrams
Chapter 4: Initial Data Model Production
Relationships between Entities
Identifying Attributes and Domains
A List of Entities, Attributes, and Domains
Identifying Fundamental Processes
The Intermediate Version of the Logical Model
Identifying Obvious Additional Data Needs
Repeat Until the Customer Agrees with Your Model
All Rows Must Contain the Same Number of Values
Clues That an Existing Design Is Not in First Normal Form
Surrogate Keys Effect on Dependency
Clues That Your Database Is Not in BCNF
Fourth Normal Form: Independent Multivalued Dependencies
Chapter 6: Physical Model Implementation Case Study
Determining Domain Implementation
Implement as a Column or Table?
Using DDL to Create the Database
Creating the Basic Table Structures
Adding Relationships (Foreign Keys)
Adding Basic Check Constraints
Triggers to Maintain Automatic Values
Chapter 7: Data Protection with Check Constraints and Triggers
CHECK Constraints Based on Simple Expressions
CHECK Constraints Using Functions
Enhancing Errors Caused by Constraints
Relationships That Span Databases and Servers
Dealing with Triggers and Constraints Errors
Chapter 8: Patterns and Anti-Patterns
Images, Documents, and Other Files, Oh My
Chapter 9: Database Security and Security Patterns
Guidelines for Server Security
Using the Contained Database Model
Controlling Access to Data via T-SQL-Coded Objects
Stored Procedures and Scalar Functions
Views and Table-Valued Functions
Using Impersonation to Cross Database Lines
Using a Certificate-Based Trust
Different Server (Distributed Queries)
Watching Table History Using DML Triggers
Chapter 10: Table Structures and Indexing
Nonclustered Indexes on Clustered Tables
Nonclustered Indexes on a Heap
Advanced Index Usage Scenarios
Index Dynamic Management View Queries
Chapter 11: Coding for Concurrency
Coding for Integrity and Concurrency
Implementing a Single-Threaded Code Block
Chapter 12: Reusable Standard Database Components
Determining the Contents of a String
Finding Gaps in a Sequence of Numbers
Separating Comma Delimited Items
Chapter 13: Considering Data Access Strategies
All Things Considered...What Do I Choose?
Guidelines for Choosing a CLR Object
Requirements-Gathering Process
Dimensional Modeling for Analytical Reporting