Agile Database Techniques: Effective Strategies for the Agile Software Developer

Foreword by Jon Kern xvii

Foreword by Douglas K. Barry xix

Acknowledgments xxi

Introduction xxiii

About the Author xxvii

Part One Setting the Foundation 1

Chapter 1 The Agile Data Method 3

Why Working Together Is Currently Hard 4

Detecting That You Have a Problem 6

The Agile Movement 7

The Manifesto for Agile Software Development 8

The Principles for Agile Software Development 9

The Philosophies of Agile Data 10

Agile Data in a Nutshell 11

Agile DBAs 11

Application Developers 12

Enterprise Administrators 13

Enterprise Architects 14

Agile Software Developers 16

Does Agile Data Solve Our Problems? 17

Summary 18

Chapter 2 From Use Cases to Databases — Real-World UML 19

An Overview of Object-Oriented Concepts 20

An Introduction to the Unified Modeling Language (UML) 23

Core UML Diagrams 24

Use Case Diagrams 24

Sequence Diagrams 26

Class Diagrams 28

Supplementary UML Diagrams 30

Activity Diagrams 30

Communication/Collaboration Diagrams 31

Component Diagrams 33

Deployment Diagrams 35

State Chart Diagrams 35

A UML Profile for Data Modeling 38

Indicating the Type of Model or Storage Mechanism 39

Modeling Tables, Entities, and Views 40

Modeling Relationships 43

Modeling Data Attributes and Columns 45

Modeling Keys 45

Modeling Constraints and Triggers 48

Modeling Stored Procedures 49

Modeling Sections within a Database 49

Modeling Everything Else 49

Summary 50

Chapter 3 Data Modeling 101 51

The Role of the Agile DBA 52

What Is Data Modeling? 52

How Are Data Models Used in Practice? 53

Notation 101: How to Read Data Models 57

How to Model Data 59

Identify Data Entities 59

Identify Attributes 59

Apply Data-Naming Conventions 60

Identifying Relationships 60

Apply Data Model Patterns 63

Assign Keys 63

How to Become Better at Modeling Data 67

Summary 68

Chapter 4 Data Normalization 69

Why Data Normalization? 70

The Role of the Agile DBA 70

The Rules of Data Normalization 70

First Normal Form (1NF) 72

Second Normal Form (2NF) 74

Third Normal Form (3NF) 75

Beyond 3NF 76

Summary 77

Chapter 5 Class Normalization 79

How Does Class Normalization Relate to Other Object Design Practices? 80

The Role of the Agile DBA 80

The Rules of Class Normalization 80

First Object Normal Form (1ONF) 81

Second Object Normal Form (2ONF) 82

Third Object Normal Form (3ONF) 84

Beyond 3ONF 84

Summary 86

Chapter 6 Relational Database Technology, Like It or Not 87

Relational Database Technology 88

Simple Features of Relational Databases 88

Advanced Features of Relational Databases 90

Coupling: Your Greatest Enemy 92

Additional Challenges with Relational Databases 95

Encapsulation: Your Greatest Ally 96

Beyond Relational Databases: You Actually Have a Choice 98

Summary 103

Chapter 7 The Object-Relational Impedance Mismatch 105

The Role of the Agile DBA 106

The Technological Impedance Mismatch 106

Deceptive Similarities 106

Subtle Differences 108

The Cultural Impedance Mismatch 111

Summary 113

Chapter 8 Legacy Databases — Everything You Need to Know But Are Afraid to Deal With 115

The Role of the Agile DBA 116

Sources of Legacy Data 118

Understanding Common Problems with Legacy Data 119

Data Quality Challenges 120

Database Design Problems 120

Data Architecture Problems 129

Process Mistakes 129

Strategies for Working with Legacy Data 134

Try to Avoid Working with Legacy Data 134

Develop a Data-Error-Handling Strategy 134

Work Iteratively and Incrementally 135

Prefer Read-Only Legacy Data Access 135

Encapsulate Legacy Data Access 138

Introduce Data Adapters for Simple Legacy Access 138

Introduce a Staging Database for Complex Data Access 139

Adopt Existing Tools 140

Data Integration Technologies 141

Summary 142

Part Two Evolutionary Database Development 143

Chapter 9 Vive L’ Évolution 145

The Need for Methodological Flexibility 146

Beware of Data-Oriented BDUF 147

Evolutionary Development on a Project 150

The “Natural Order” of Things and Evolutionary Development 153

Summary 155

Chapter 10 Agile Model-Driven Development (AMDD) 157

The Role of the Agile DBA 158

What Is Agile Modeling? 158

AM Values 159

AM Principles 159

Agile Modeling Practices 161

When Is a Model Agile? 163

What Is Agile Model-Driven Development (AMDD)? 163

Agile Documentation 164

Summary 166

Chapter 11 Test-Driven Development (TDD) 167

How Does TDD Work? 168

The Steps of TDD 168

TDD and Traditional Testing 171

TDD and Documentation 171

Test-Driven Database Development 172

TDD and Agile Model-Driven Development (AMDD) 173

Summary 174

Chapter 12 Database Refactoring 175

Refactoring 176

Database Refactoring 177

Preserving Semantics 178

What Database Refactorings Aren’t 178

Categories of Database Refactorings 179

Why Database Refactoring Is Hard 179

How to Refactor Your Database 182

Step 1: Start in Your Development Sandbox 182

Verify That a Database Refactoring Is Required 183

Choose the Most Appropriate Database Refactoring 184

Determine Data Cleansing Needs 184

Write Your Unit Tests 185

Deprecate the Original Schema (Structural Refactorings Only) 186

Implement the Change 187

Update Your Database Management Script(s) 189

Run Your Regression Tests 189

Document the Refactoring 189

Version Control Your Work 190

Step 2: Implement the Code in Your Integration Sandbox(es) 190

Step 3: Install the Code in Production 192

Common Database Refactoring Smells 192

Adopting Database Refactoring within Your Organization 193

Database Refactoring Best Practices 195

Database Refactoring in the Real World 196

Summary 197

Chapter 13 Database Encapsulation Strategies 199

Database Encapsulation Layers 200

The Role of the Agile DBA 201

Encapsulation-Layer Architectures 202

Encapsulation-Layer Implementation Strategies 205

Brute Force (the Encapsulation Strategy That Isn’t One) 205

Data Access Objects 207

Persistence Frameworks 208

Services 214

When to Use Each Strategy 216

Transitioning between Strategies 216

Marshaling and Data Validation 220

Error Handling 220

Summary 221

Chapter 14 Mapping Objects to Relational Databases 223

The Role of the Agile DBA 224

Basic Mapping Concepts 225

Shadow Information 228

Mapping Meta Data 230

Mapping Inheritance Structures 231

Map Entire Class Hierarchy to a Table 233

Map Each Concrete Class to Its Own Table 234

Map Each Class to Its Own Table 234

Map Classes to a Generic Structure 237

Comparing the Mapping Strategies 239

Mapping Multiple Inheritance 239

Mapping Object Relationships 244

Types of Relationships 245

How Relationships Are Implemented between Objects 247

How Relationships Are Implemented in Relational Databases 247

Relationship Mappings 249

Mapping One-to-One Relationships 250

Mapping One-to-Many Relationships 252

Mapping Many-to-Many Relationships 253

Mapping Ordered Collections 254

Mapping Recursive Relationships 256

Mapping Class Scope Properties 258

Why Data Schemas Shouldn’t Drive Object Schemas 261

Implementation Impact on Your Objects 264

Implications for the Model Driven Architecture (MDA) 265

Patternizing the Mappings 266

Summary 267

Chapter 15 Performance Tuning 269

An Overview of Performance Tuning 269

The Role of the Agile DBA 271

Step One: Identify a Performance Problem 271

Step Two: Profile the Problem 271

Step Three: Tune the Problem Away 273

System Tuning 273

Database Access Tuning 274

Choose the Right Access Strategy 274

Tune Your SQL 275

Tune Your Mappings 276

Database Tuning 276

Denormalize Your Data Schema 277

Rework Database Logs 282

Update Your Database Configuration 282

Reorganize Data Storage 282

Rework Your Database Architecture/Design 283

Application Tuning 284

Share Common Logic 284

Implement Lazy Reads 284

Introduce Caches 285

Combine Granular Functionality 285

Summary 285

Chapter 16 Tools for Evolutionary Database Development 287

Tools 287

Sandboxes 291

Scripts 293

Summary 294

Part Three Practical Data-Oriented Development Techniques 295

Chapter 17 Implementing Concurrency Control 297

The Role of the Agile DBA 298

Collisions 298

Types of Locking 299

Pessimistic Locking 299

Optimistic Locking 300

Overly Optimistic Locking 302

Resolving Collisions 302

Understanding Transactions 303

The Basics of Transactions 304

ACID Properties 304

Two-Phase Commits 305

Nested Transactions 305

Implementing Transactions 306

Database Transactions 306

Object Transactions 307

Distributed Object Transactions 309

Including Nontransactional Sources in a Transaction 309

Summary 310

Chapter 18 Finding Objects in Relational Databases 311

The Role of the Agile DBA 311

Find Strategies 312

Brute Force (Embedded SQL) 312

Query Objects 313

Meta Data-Driven 314

When to Apply Each Strategy 316

Implementation Techniques 317

Use the Native Error-Handling Strategy 317

Expect “Logic” Errors 318

Always Return a Collection 318

Use Proxies and Lazy Initialization for Search Lists 318

Use Lazy Reads for High-Overhead Attributes 319

Program for People 319

Representing Find Results 320

Summary 324

Chapter 19 Implementing Referential Integrity and Shared Business Logic 325

The Role of the Agile DBA 326

How Object Technology Complicates Referential Integrity 326

Multiple Entity/Relationship Representation 328

Object Relationship Management 329

Database Cascades 329

Object Relationship Cascades 329

Implications of Cascades 330

Cascading Strategies 330

Lazy Reads 332

Caches 332

Aggregation, Composition, and Association 333

Architectural Layering 334

Removal from Memory versus Persistent Deletion 334

Where Should You Implement Referential Integrity? 335

Referential Integrity Implementation Options 336

Business Logic Implementation Options 340

General Implementation Strategies 340

Summary 342

Chapter 20 Implementing Security Access Control 343

The Role of the Agile DBA 344

Authentication 344

Authorization 345

Issues 345

Database Implementation Strategies 347

Security Design Patterns 348

Object-Oriented Implementation Strategies 350

Implications 354

Effective Security Strategies 354

Summary 356

Chapter 21 Implementing Reports 357

The Role of the Agile DBA 358

Database Deployment Architecture 360

Reporting within Your Application 362

Reporting outside Your Application 365

Database Design Strategies 365

Implementation Strategies 366

Challenges That Make Reporting Hard 367

Summary 368

Chapter 22 Realistic XML 369

The Role of the Agile DBA 370

An XML Primer 370

Strengths of XML 372

Weaknesses of XML 373

Practical Applications for XML 374

Vocabularies 375

How to Model XML 376

XML Mapping and Data Binding 380

How to Persist XML in Relational Databases 382

How to Persist XML in XML Databases 383

XML Development Strategies 385

Summary 386

Part Four Adopting Agile Database Techniques 387

Chapter 23 How You Can Become Agile 389

You Don’t Have to Be Superhuman 389

Agility Is Really Just a Mindset 390

Become a Generalizing Specialist 391

Summary 393

Chapter 24 Bringing Agility into Your Organization 395

Change the Way You Look at Software Development 396

Understand the Challenges You Face 397

Actually Try It 399

Block Nonagile Coworkers 399

Be Realistic 400

Parting Thoughts 401

Appendix Database Refactoring Catalog 403

References and Suggested Reading 421

Index 429