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)
Defining Uniqueness Constraints (Keys)
Working with Binary Relationships
Working with Nonbinary Relationships
Understanding Functional Dependencies
Understanding Functional Dependencies
Outlining the Database-Specific Project Phases
Chapter 2: Introduction to Requirements
Utilizing Other Types of Documentation
Contracts or Client Work Orders
Chapter 3: The Language of Data Modeling
Verb Phrases (Relationship Names)
Alternative Modeling Methodologies
Chapter 4: Conceptual and Logical Data Model Production
Identifying Relationships Between Entities
Identifying Attributes and Domains
Identifying Fundamental Processes
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
Choosing a Physical Model for Your Tables
Determining Domain Implementation
Enforce Domain in the Column, or With a 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
Viewing the Basic System Metadata
Chapter 7: Expanding 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
Dealing with Trigger and Constraint Errors
Chapter 8: Patterns and Anti-Patterns
Images, Documents, and Other Files, Oh My!
Chapter 9: Database Security and Security Patterns
Guidelines for Host Server Security Configuration
Using Specific-Purpose Views to Provide Row-Level Security
Using the Row-Level Security Feature
Using Data-Driven Row-Level Security
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)
Using Dynamic Data Masking to Hide Data from Users
Defining an Audit Specification
Viewing the Audit Configuration
Chapter 10: Index Structures and Application
Common OLTP Patterns of Index Usage
When to Cluster on Something Other Than the PRIMARY KEY
Indexing Dynamic Management View Queries
On-Disk Index Utilization Statistics
Chapter 11: Matters of Concurrency
SQL Server Concurrency Methods
Pessimistic Concurrency Enforcement
Optimistic Concurrency Enforcement
Coding for Asynchronous Contention
Coding for Logical Unit of Work Change Detection
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: Architecting Your System
Choosing the Engine for Your Needs
Requirements-Gathering Process
Dimensional Modeling for Analytical Reporting
Summary Modeling for Aggregation Reporting
Modeling (or Lack Thereof) for Operational Reporting
Appendix A: Scalar Datatype Reference
datetimeoffset [( (precision) )]
Unicode Character Strings: nchar, nvarchar, nvarchar(max), ntext