Agile Database Techniques: Effective Strategies for the Agile Software Developer

Agile Database Techniques: Effective Strategies for the Agile Software Developer

by Scott Ambler
Released October 2003
Publisher(s): Wiley
ISBN: 9780471202837

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

  • Describes Agile Modeling Driven Design (AMDD) and Test-Driven Design (TDD) approaches, database refactoring, database encapsulation strategies, and tools that support evolutionary techniques

  • Agile software developers often use object and relational database (RDB) technology together and as a result must overcome the impedance mismatch

  • The author covers techniques for mapping objects to RDBs and for implementing concurrency control, referential integrity, shared business logic, security access control, reports, and XML

  • An agile foundation describes fundamental skills that all agile software developers require, particularly Agile DBAs

  • Includes object modeling, UML data modeling, data normalization, class normalization, and how to deal with legacy databases

  • Scott W. Ambler is author of Agile Modeling (0471202827), a contributing editor with Software Development (www.sdmagazine.com), and a featured speaker at software conferences worldwide

Table of contents

  1. Copyright
  2. Foreword by jon Kern
  3. Foreword by Douglas K. Barry
  4. Acknowledgments
  5. Introduction
    1. The Audience for This Book
    2. Why the Focus on Agile DBAs?
    3. An Overview
      1. Part I
      2. Part II
      3. Part III
      4. Part IV
  6. About the Author
  7. I. Setting the Foundation
    1. 1. The Agile Data Method
      1. 1.1. Why Working Together Is Currently Hard
      2. 1.2. Detecting That You Have a Problem
      3. 1.3. The Agile Movement
        1. 1.3.1. The Manifesto for Agile Software Development
        2. 1.3.2. The Principles for Agile Software Development
      4. 1.4. The Philosophies of Agile Data
      5. 1.5. Agile Data in a Nutshell
        1. 1.5.1. Agile DBAs
        2. 1.5.2. Application Developers
        3. 1.5.3. Enterprise Administrators
        4. 1.5.4. Enterprise Architects
      6. 1.6. Agile Software Developers
      7. 1.7. Does Agile Data Solve Our Problems?
      8. 1.8. Summary
    2. 2. From Use Cases to Databases — Real-World UML
      1. 2.1. An Overview of Object-Oriented Concepts
      2. 2.2. An Introduction to the Unified Modeling Language (UML)
        1. 2.2.1. Core UML Diagrams
          1. 2.2.1.1. Use Case Diagrams
          2. 2.2.1.2. Sequence Diagrams
          3. 2.2.1.3. Class Diagrams
        2. 2.2.2. Supplementary UML Diagrams
          1. 2.2.2.1. Activity Diagrams
          2. 2.2.2.2. Communication/Collaboration Diagrams
          3. 2.2.2.3. Component Diagrams
          4. 2.2.2.4. Deployment Diagrams
          5. 2.2.2.5. State Chart Diagrams
      3. 2.3. A UML Profile for Data Modeling
        1. 2.3.1. Indicating the Type of Model or Storage Mechanism
        2. 2.3.2. Modeling Tables, Entities, and Views
        3. 2.3.3. Modeling Relationships
        4. 2.3.4. Modeling Data Attributes and Columns
        5. 2.3.5. Modeling Keys
        6. 2.3.6. Modeling Constraints and Triggers
        7. 2.3.7. Modeling Stored Procedures
        8. 2.3.8. Modeling Sections within a Database
        9. 2.3.9. Modeling Everything Else
      4. 2.4. Summary
    3. 3. Data Modeling 101
      1. 3.1. The Role of the Agile DBA
      2. 3.2. What Is Data Modeling?
        1. 3.2.1. How Are Data Models Used in Practice?
        2. 3.2.2. Notation 101: How to Read Data Models
      3. 3.3. How to Model Data
        1. 3.3.1. Identify Data Entities
        2. 3.3.2. Identify Attributes
        3. 3.3.3. Apply Data-Naming Conventions
        4. 3.3.4. Identifying Relationships
        5. 3.3.5. Apply Data Model Patterns
        6. 3.3.6. Assign Keys
      4. 3.4. How to Become Better at Modeling Data
      5. 3.5. Summary
    4. 4. Data Normalization
      1. 4.1. Why Data Normalization?
      2. 4.2. The Role of the Agile DBA
      3. 4.3. The Rules of Data Normalization
        1. 4.3.1. First Normal Form (1NF)
        2. 4.3.2. Second Normal Form (2NF)
        3. 4.3.3. Third Normal Form (3NF)
        4. 4.3.4. Beyond 3NF
      4. 4.4. Summary
    5. 5. Class Normalization
      1. 5.1. How Does Class Normalization Relate to Other Object Design Practices?
      2. 5.2. The Role of the Agile DBA
      3. 5.3. The Rules of Class Normalization
        1. 5.3.1. First Object Normal Form (1ONF)
        2. 5.3.2. Second Object Normal Form (2ONF)
        3. 5.3.3. Third Object Normal Form (3ONF)
        4. 5.3.4. Beyond 3ONF
      4. 5.4. Summary
    6. 6. Relational Database Technology, Like It or Not
      1. 6.1. Relational Database Technology
        1. 6.1.1. Simple Features of Relational Databases
        2. 6.1.2. Advanced Features of Relational Databases
      2. 6.2. Coupling: Your Greatest Enemy
      3. 6.3. Additional Challenges with Relational Databases
      4. 6.4. Encapsulation: Your Greatest Ally
      5. 6.5. Beyond Relational Databases: You Actually Have a Choice
      6. 6.6. Summary
    7. 7. The Object-Relational Impedance Mismatch
      1. 7.1. The Role of the Agile DBA
      2. 7.2. The Technological Impedance Mismatch
        1. 7.2.1. Deceptive Similarities
        2. 7.2.2. Subtle Differences
      3. 7.3. The Cultural Impedance Mismatch
      4. 7.4. Summary
    8. 8. Legacy Databases — Everything You Need to Know But Are Afraid to Deal With
      1. 8.1. The Role of the Agile DBA
      2. 8.2. Sources of Legacy Data
      3. 8.3. Understanding Common Problems with Legacy Data
        1. 8.3.1. Data Quality Challenges
        2. 8.3.2. Database Design Problems
        3. 8.3.3. Data Architecture Problems
        4. 8.3.4. Process Mistakes
      4. 8.4. Strategies for Working with Legacy Data
        1. 8.4.1. Try to Avoid Working with Legacy Data
        2. 8.4.2. Develop a Data-Error-Handling Strategy
        3. 8.4.3. Work Iteratively and Incrementally
        4. 8.4.4. Prefer Read-Only Legacy Data Access
        5. 8.4.5. Encapsulate Legacy Data Access
        6. 8.4.6. Introduce Data Adapters for Simple Legacy Access
        7. 8.4.7. Introduce a Staging Database for Complex Data Access
        8. 8.4.8. Adopt Existing Tools
      5. 8.5. Data Integration Technologies
      6. 8.6. Summary
  8. II. Evolutionary Database Development
    1. 9. Vive L' Évolution
      1. 9.1. The Need for Methodological Flexibility
      2. 9.2. Beware of Data-Oriented BDUF
      3. 9.3. Evolutionary Development on a Project
      4. 9.4. The "Natural Order" of Things and Evolutionary Development
      5. 9.5. Summary
    2. 10. Agile Model-Driven Development (AMDD)
      1. 10.1. The Role of the Agile DBA
      2. 10.2. What Is Agile Modeling?
        1. 10.2.1. AM Values
        2. 10.2.2. AM Principles
        3. 10.2.3. Agile Modeling Practices
      3. 10.3. When Is a Model Agile?
      4. 10.4. What Is Agile Model-Driven Development (AMDD)?
      5. 10.5. Agile Documentation
      6. 10.6. Summary
    3. 11. Test-Driven Development (TDD)
      1. 11.1. How Does TDD Work?
      2. 11.2. The Steps of TDD
      3. 11.3. TDD and Traditional Testing
      4. 11.4. TDD and Documentation
      5. 11.5. Test-Driven Database Development
      6. 11.6. TDD and Agile Model-Driven Development (AMDD)
      7. 11.7. Summary
    4. 12. Database Refactoring
      1. 12.1. Refactoring
      2. 12.2. Database Refactoring
        1. 12.2.1. Preserving Semantics
        2. 12.2.2. What Database Refactorings Aren't
        3. 12.2.3. Categories of Database Refactorings
      3. 12.3. Why Database Refactoring Is Hard
      4. 12.4. How to Refactor Your Database
        1. 12.4.1. Step 1: Start in Your Development Sandbox
          1. 12.4.1.1. Verify That a Database Refactoring Is Required
          2. 12.4.1.2. Choose the Most Appropriate Database Refactoring
          3. 12.4.1.3. Determine Data Cleansing Needs
          4. 12.4.1.4. Write Your Unit Tests
          5. 12.4.1.5. Deprecate the Original Schema (Structural Refactorings Only)
          6. 12.4.1.6. Implement the Change
          7. 12.4.1.7. Update Your Database Management Script(s)
          8. 12.4.1.8. Run Your Regression Tests
          9. 12.4.1.9. Document the Refactoring
          10. 12.4.1.10. Version Control Your Work
        2. 12.4.2. Step 2: Implement the Code in Your Integration Sandbox(es)
        3. 12.4.3. Step 3: Install the Code in Production
      5. 12.5. Common Database Refactoring Smells
      6. 12.6. Adopting Database Refactoring within Your Organization
      7. 12.7. Database Refactoring Best Practices
      8. 12.8. Database Refactoring in the Real World
      9. 12.9. Summary
    5. 13. Database Encapsulation Strategies
      1. 13.1. Database Encapsulation Layers
      2. 13.2. The Role of the Agile DBA
      3. 13.3. Encapsulation-Layer Architectures
      4. 13.4. Encapsulation-Layer Implementation Strategies
        1. 13.4.1. Brute Force (the Encapsulation Strategy That Isn't One)
        2. 13.4.2. Data Access Objects
        3. 13.4.3. Persistence Frameworks
        4. 13.4.4. Services
        5. 13.4.5. When to Use Each Strategy
        6. 13.4.6. Transitioning between Strategies
      5. 13.5. Marshaling and Data Validation
      6. 13.6. Error Handling
      7. 13.7. Summary
    6. 14. Mapping Objects to Relational Databases
      1. 14.1. The Role of the Agile DBA
      2. 14.2. Basic Mapping Concepts
        1. 14.2.1. Shadow Information
        2. 14.2.2. Mapping Meta Data
      3. 14.3. Mapping Inheritance Structures
        1. 14.3.1. Map Entire Class Hierarchy to a Table
        2. 14.3.2. Map Each Concrete Class to Its Own Table
        3. 14.3.3. Map Each Class to Its Own Table
        4. 14.3.4. Map Classes to a Generic Structure
        5. 14.3.5. Comparing the Mapping Strategies
        6. 14.3.6. Mapping Multiple Inheritance
      4. 14.4. Mapping Object Relationships
        1. 14.4.1. Types of Relationships
        2. 14.4.2. How Relationships Are Implemented between Objects
        3. 14.4.3. How Relationships Are Implemented in Relational Databases
        4. 14.4.4. Relationship Mappings
          1. 14.4.4.1. Mapping One-to-One Relationships
          2. 14.4.4.2. Mapping One-to-Many Relationships
          3. 14.4.4.3. Mapping Many-to-Many Relationships
        5. 14.4.5. Mapping Ordered Collections
        6. 14.4.6. Mapping Recursive Relationships
      5. 14.5. Mapping Class Scope Properties
      6. 14.6. Why Data Schemas Shouldn't Drive Object Schemas
      7. 14.7. Implementation Impact on Your Objects
      8. 14.8. Implications for the Model Driven Architecture (MDA)
      9. 14.9. Patternizing the Mappings
      10. 14.10. Summary
    7. 15. Performance Tuning
      1. 15.1. An Overview of Performance Tuning
      2. 15.2. The Role of the Agile DBA
      3. 15.3. Step One: Identify a Performance Problem
      4. 15.4. Step Two: Profile the Problem
      5. 15.5. Step Three: Tune the Problem Away
        1. 15.5.1. System Tuning
        2. 15.5.2. Database Access Tuning
          1. 15.5.2.1. Choose the Right Access Strategy
          2. 15.5.2.2. Tune Your SQL
          3. 15.5.2.3. Tune Your Mappings
        3. 15.5.3. Database Tuning
          1. 15.5.3.1. Denormalize Your Data Schema
          2. 15.5.3.2. Rework Database Logs
          3. 15.5.3.3. Update Your Database Configuration
          4. 15.5.3.4. Reorganize Data Storage
          5. 15.5.3.5. Rework Your Database Architecture/Design
        4. 15.5.4. Application Tuning
          1. 15.5.4.1. Share Common Logic
          2. 15.5.4.2. Implement Lazy Reads
          3. 15.5.4.3. Introduce Caches
          4. 15.5.4.4. Combine Granular Functionality
      6. 15.6. Summary
    8. 16. Tools for Evolutionary Database Development
      1. 16.1. Tools
      2. 16.2. Sandboxes
      3. 16.3. Scripts
      4. 16.4. Summary
  9. III. Practical Data-Oriented Development Techniques
    1. 17. Implementing Concurrency Control
      1. 17.1. The Role of the Agile DBA
      2. 17.2. Collisions
        1. 17.2.1. Types of Locking
          1. 17.2.1.1. Pessimistic Locking
            1. 17.2.1.1.1. Write Locks
            2. 17.2.1.1.2. Read Locks
          2. 17.2.1.2. Optimistic Locking
          3. 17.2.1.3. Overly Optimistic Locking
        2. 17.2.2. Resolving Collisions
      3. 17.3. Understanding Transactions
        1. 17.3.1. The Basics of Transactions
          1. 17.3.1.1. ACID Properties
          2. 17.3.1.2. Two-Phase Commits
          3. 17.3.1.3. Nested Transactions
        2. 17.3.2. Implementing Transactions
          1. 17.3.2.1. Database Transactions
          2. 17.3.2.2. Object Transactions
          3. 17.3.2.3. Distributed Object Transactions
          4. 17.3.2.4. Including Nontransactional Sources in a Transaction
      4. 17.4. Summary
    2. 18. Finding Objects in Relational Databases
      1. 18.1. The Role of the Agile DBA
      2. 18.2. Find Strategies
        1. 18.2.1. Brute Force (Embedded SQL)
        2. 18.2.2. Query Objects
        3. 18.2.3. Meta Data-Driven
        4. 18.2.4. When to Apply Each Strategy
      3. 18.3. Implementation Techniques
        1. 18.3.1. Use the Native Error-Handling Strategy
        2. 18.3.2. Expect "Logic" Errors
        3. 18.3.3. Always Return a Collection
        4. 18.3.4. Use Proxies and Lazy Initialization for Search Lists
        5. 18.3.5. Use Lazy Reads for High-Overhead Attributes
        6. 18.3.6. Program for People
      4. 18.4. Representing Find Results
      5. 18.5. Summary
    3. 19. Implementing Referential Integrity and Shared Business Logic
      1. 19.1. The Role of the Agile DBA
      2. 19.2. How Object Technology Complicates Referential Integrity
        1. 19.2.1. Multiple Entity/Relationship Representation
        2. 19.2.2. Object Relationship Management
          1. 19.2.2.1. Database Cascades
          2. 19.2.2.2. Object Relationship Cascades
          3. 19.2.2.3. Implications of Cascades
          4. 19.2.2.4. Cascading Strategies
        3. 19.2.3. Lazy Reads
        4. 19.2.4. Caches
        5. 19.2.5. Aggregation, Composition, and Association
        6. 19.2.6. Architectural Layering
        7. 19.2.7. Removal from Memory versus Persistent Deletion
      3. 19.3. Where Should You Implement Referential Integrity?
        1. 19.3.1. Referential Integrity Implementation Options
        2. 19.3.2. Business Logic Implementation Options
        3. 19.3.3. General Implementation Strategies
      4. 19.4. Summary
    4. 20. Implementing Security Access Control
      1. 20.1. The Role of the Agile DBA
      2. 20.2. Authentication
      3. 20.3. Authorization
        1. 20.3.1. Issues
        2. 20.3.2. Database Implementation Strategies
        3. 20.3.3. Security Design Patterns
        4. 20.3.4. Object-Oriented Implementation Strategies
        5. 20.3.5. Implications
      4. 20.4. Effective Security Strategies
      5. 20.5. Summary
    5. 21. Implementing Reports
      1. 21.1. The Role of the Agile DBA
      2. 21.2. Database Deployment Architecture
      3. 21.3. Reporting within Your Application
      4. 21.4. Reporting outside Your Application
      5. 21.5. Database Design Strategies
      6. 21.6. Implementation Strategies
      7. 21.7. Challenges That Make Reporting Hard
      8. 21.8. Summary
    6. 22. Realistic XML
      1. 22.1. The Role of the Agile DBA
      2. 22.2. An XML Primer
        1. 22.2.1. Strengths of XML
        2. 22.2.2. Weaknesses of XML
      3. 22.3. Practical Applications for XML
      4. 22.4. Vocabularies
      5. 22.5. How to Model XML
      6. 22.6. XML Mapping and Data Binding
      7. 22.7. How to Persist XML in Relational Databases
      8. 22.8. How to Persist XML in XML Databases
      9. 22.9. XML Development Strategies
      10. 22.10. Summary
  10. IV. Adopting Agile Database Techniques
    1. 23. How You Can Become Agile
      1. 23.1. You Don't Have to Be Superhuman
      2. 23.2. Agility Is Really Just a Mindset
      3. 23.3. Become a Generalizing Specialist
      4. 23.4. Summary
    2. 24. Bringing Agility into Your Organization
      1. 24.1. Change the Way You Look at Software Development
      2. 24.2. Understand the Challenges You Face
      3. 24.3. Actually Try It
      4. 24.4. Block Nonagile Coworkers
      5. 24.5. Be Realistic
      6. 24.6. Parting Thoughts
    3. A. Database Refactoring Catalog
    4. References and Suggested Reading

Product information

  • Title: Agile Database Techniques: Effective Strategies for the Agile Software Developer
  • Author(s): Scott Ambler
  • Release date: October 2003
  • Publisher(s): Wiley
  • ISBN: 9780471202837