Designing Effective Database Systems

Designing Effective Database Systems

by Rebecca M. Riordan
Released January 2005
Publisher(s): Addison-Wesley Professional
ISBN: 0321290933

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

“Riordan covers core skills for any developer—database design and development—in a perfect amount of detail. This book should be on every professional developer’s reading list.”

—Duncan Mackenzie, developer, Microsoft (MSDN)

“Designing a database is not a trivial subject. Riordan brings experience and clear explanations to a fundamental part of software development.”

—Patrick Birch, database and technical writing consultant

“If you buy only one book on database design, make it this one. Riordan has a talent for explaining technical issues in simple language, without over simplifying.”

—Brendan Reynolds, developer, Dataset IT Systems and Microsoft Access MVP

“A book that will expertly guide you in how to develop a database for a client— and how to do it right the first time!”

—Kenneth D. Snell, Ph.D., ACCESS developer and Microsoft Access MVP

“Riordan has produced a unique book that brings together a formal, yet commonsense, approach to relational database design...and then goes further! Many database designers will find immense value in the steps to developing practical data warehouse designs. If you are seeking a framework for designing transactional databases, or want to step out into the world of analytical databases, Riordan’s book excels at bridging both worlds.”

—Paul Irvine, vice president, engineering, Via Training

“Riordan takes a complex subject and makes it easy. If you’re over your head on a database design project, this book will help bail you out!”

—Mike Gunderloy, contributing editor, Application Development Trends

“This book covers a wide range of database design and data modeling topics in a well-organized, easy to understand format.”

—Amy Sticksel, Sticksel Data Systems, Inc.

“In Designing Effective Database Systems, Riordan’s style, wit, and attention to detail are outstanding.”

—Sandra Daigle, Microsoft Access MVP

The Software Developer’s Step-by-Step Guide to Database Design

World-renowned expert Rebecca M. Riordan has written the definitive database design book for working developers who aren’t database experts. No matter how messy or complex your data challenge, Designing Effective Database Systems shows you how to design an effective, high-performance database to solve it.

Riordan begins by thoroughly demystifying the principles of relational design, making them accessible to every professional developer. Next, she offers the field’s clearest introduction to dimensional database modeling—practical insight for designing today’s increasingly important analytical applications.

One task at a time, the author illuminates every facet of database analysis and design for both traditional databases and the dimensional databases used for data warehousing, showing how to avoid common architectural pitfalls that complicate development and reduce extensibility. The book concludes with comprehensive, expert guidance on designing databases for maximum usability.

This book will teach you to

  • Understand relational database models, structures, relationships, and data integrity principles

  • Define database system goals, criteria, scope, and work processes

  • Construct accurate conceptual models: relationships, entities, domain analysis, and normalization

  • Build efficient, secure database schema

  • Master the elements of online analytical processing (OLAP) design: fact tables, dimension tables, snowflaking, and more

  • Architect and construct easy, efficient interfaces for querying and reporting

  • Learn from practice examples based on Microsoft’s Northwind sample database

    • Riordan has helped thousands of professionals master database design and development, earning Microsoft’s coveted MVP honor for her exceptional contributions. Nobody is more qualified to help you master database design and apply it in your real-world environment.

    Table of contents

    1. Copyright
      1. Dedication
    2. Praise for Designing Effective Database Systems
    3. Preface
    4. Acknowledgments
    5. I. Relational Database Theory
      1. 1. Basic Concepts
        1. What Is a Database?
        2. Database Tools
          1. Database Engines
          2. Data Access Object Models
          3. Data Definition Environments
          4. Front-End Development
        3. The Relational Model
        4. Relational Terminology
        5. The Data Model
          1. Entities
          2. Attributes
          3. Domains
          4. Relationships
          5. Entity Relationship Diagrams
        6. Summary
      2. 2. Database Structure
        1. Eliminating Redundancy
        2. Ensuring Flexibility
        3. Basic Principles
          1. Lossless Decomposition
          2. Candidate Keys and Primary Keys
          3. Functional Dependency
        4. First Normal Form
        5. Second Normal Form
        6. Third Normal Form
        7. Further Normalization
          1. Boyce/Codd Normal Form
          2. Fourth Normal Form
          3. Fifth Normal Form
        8. Summary
      3. 3. Relationships
        1. Terminology
        2. Modeling Relationships
        3. One-to-One Relationships
          1. Sub-Classing Entities
        4. One-to-Many Relationships
        5. Many-to-Many Relationships
        6. Unary Relationships
        7. Ternary Relationships
        8. Relationships of Known Cardinality
        9. Summary
      4. 4. Data Integrity
        1. Integrity Constraints
          1. Domain Integrity
          2. Transition Integrity
          3. Entity Integrity
          4. Referential Integrity
          5. Database Integrity
          6. Transaction Integrity
        2. Implementing Data Integrity
          1. Unknown and Nonexistent Values (Again)
          2. Violation Responses
          3. Declarative and Procedural Integrity
          4. Domain Integrity
          5. Entity Integrity
          6. Referential Integrity
          7. Other Kinds of Integrity
        3. Summary
      5. 5. Relational Algebra
        1. Nulls and Three-Valued Logic (One More Time)
        2. Relational Operations
          1. Restriction
          2. Projection
          3. Join
            1. Equi-Joins
            2. Natural Joins
            3. Theta-Joins
            4. Outer Joins
          4. Divide
        3. Set Operators
          1. Union
          2. Intersection
          3. Difference
          4. Cartesian Product
        4. Special Relational Operators
          1. Summarize
          2. Extend
          3. Rename
          4. Transform
          5. Rollup
          6. Cube
        5. Summary
    6. II. Dimensional Database Theory
      1. 6. Basic Dimensional Concepts
        1. The Dimensional Database Model
        2. Terminology
        3. A Potted History of Business Intelligence
        4. Summary
      2. 7. Fact Tables
        1. The Structure of a Fact Table
        2. The Characteristics of a Fact Attribute
          1. Grain
          2. Types of Fact Tables
          3. Heterogeneous Facts
        3. Summary
      3. 8. Dimension Tables
        1. The Structure of a Dimension Table
        2. Snowflaking
        3. Changing Dimensions
        4. Summary
    7. III. Designing Database Systems
      1. 9. The Design Process
        1. Life Cycle Models
        2. The Database Design Process
          1. Defining the System Parameters
          2. Defining the Work Processes
          3. Building the Conceptual Data Model
          4. Preparing the Database Schema
          5. Designing the User Interface
        3. A Note on Design Methodologies and Standards
      2. 10. Defining the System Parameters
        1. Determining the System Goals
        2. Developing the Design Criteria
          1. Directly Measurable Criteria
          2. Environmental Criteria
          3. General Design Strategies
        3. Determining the System Scope
          1. Cost-Benefit Analysis
        4. Summary
      3. 11. Defining the Work Processes
        1. Determining Current Work Processes
          1. Speaking to Users
          2. Identifying Tasks
        2. Analyzing Work Processes
        3. Documenting Work Processes
        4. User Scenarios
        5. Summary
      4. 12. The Conceptual Data Model
        1. Identifying the Data Objects
        2. Defining Relationships
          1. The Cardinality of the Relationship
          2. The Optionality of the Relationship
          3. The Attributes of the Relationship
          4. Additional Constraints on the Relationship
        3. Reviewing Entities
          1. The Relationship between the Entity and the Problem Space
          2. Work Processes that Affect the Entity
          3. Interactions between Entities
          4. Business Rules and Constraints
          5. Attributes
        4. Domain Analysis
          1. Choosing a Data Type
        5. Restricting the Range of Values
          1. Defining the Format
        6. Normalization
        7. Summary
      5. 13. The Database Schema
        1. Systems Architectures
          1. Code Architectures
            1. Three-Tiered Model
            2. Four-Layer Model
            3. Code Architectures and the Database Schema
          2. Data Architectures
            1. Single-Tier Architectures
            2. Two-Tiered Architectures
            3. N-Tiered Architectures
            4. Detached Architectures
        2. Database Schema Components
          1. Defining Tables and Relationships
            1. Constraints
            2. Relationships
          2. Views and Queries
        3. Security
          1. Security Levels
            1. Auditing
        4. Summary
      6. 14. Communicating the Design
        1. Audience and Purpose
        2. Document Structure
        3. Executive Summary
        4. System Overview
        5. Work Processes
        6. Conceptual Data Model
        7. Database Schema
        8. User Interface
          1. Interface Prototyping
          2. Interface Specification
        9. Change Management
        10. Summary
    8. IV. Designing the User Interface
      1. 15. The Interface as Mediator
        1. Effective Interfaces
        2. Interface Models
        3. User Levels
          1. Beginner
          2. Intermediate
          3. Expert
        4. Putting Users in Charge
        5. Minimizing the Memory Load
        6. Being Consistent
        7. Summary
      2. 16. User Interface Architectures
        1. Supporting the Work Processes
        2. Document Architectures
          1. Single Document Interfaces
            1. Workbook Applications
            2. The Outlook-Style Interface
          2. Multiple Document Interfaces
            1. The “Classic” MDI Architecture
            2. Switchboard Interfaces
            3. Project Interfaces
            4. Wizards
        3. Summary
      3. 17. Representing Entities in Form Design
        1. Simple Entities
        2. One-to-One Relationships
        3. One-to-Many Relationships
        4. Hierarchies
        5. Many-to-Many Relationships
        6. Summary
      4. 18. Choosing Windows Controls
        1. Representing Logical Values
        2. Representing Sets of Values
          1. Capturing a Single Value from a Set of Values
          2. Capturing a Set of Values
        3. Representing Numbers and Dates
        4. Representing Text Values
        5. Summary
      5. 19. Maintaining Database Integrity
        1. Classes of Integrity Constraints
        2. Intrinsic Constraints
          1. Data Type
          2. Format
          3. Length
          4. Nulls
          5. Ranges
          6. Entity and Referential Integrity Constraints
        3. Business Constraints
          1. Accidental Entries
          2. Reality vs. the System Model
        4. Summary
      6. 20. Reporting
        1. Sorting, Searching, and Filtering Data
          1. Sorting Data
          2. Filter By Selection
          3. Filter By Form
          4. Advanced Filter/Sort
          5. Microsoft English Query
        2. Producing Standard Reports
          1. Listing Reports and Detail Reports
          2. Summary Reports
          3. Form-Based Reports
          4. Reporting Interfaces
          5. Handling Printer Errors
          6. Automatic and On-Demand Printing
        3. Producing Ad Hoc Reports
          1. Report Designers
          2. Customized Report Design
          3. Standard Letters
        4. Summary
      7. 21. User Assistance
        1. User Levels
        2. Passive Assistance Mechanisms
          1. Mnemonic Access Keys
          2. ToolTips
          3. Status Bars
        3. Reactive Assistance Mechanisms
          1. On-Line Help
          2. What’s This? Tips
          3. Audible Feedback
          4. Error Messages
        4. Proactive Assistance
        5. User Training
        6. Summary
    9. Bibliography
      1. Part I: Relational Database Theory
      2. Part II: Designing Relational Database Systems
      3. Part III: Designing the User Interface
    10. Glossary

    Product information

    • Title: Designing Effective Database Systems
    • Author(s): Rebecca M. Riordan
    • Release date: January 2005
    • Publisher(s): Addison-Wesley Professional
    • ISBN: 0321290933