You are previewing Database Design for Mere Mortals.
O'Reilly logo
Database Design for Mere Mortals

Book Description

Sound design can save you hours of development time before you write a single line of code. Based on the author's years of experience teaching this material, Database Design for Mere Mortals is a straightforward, platform-independent tutorial on the basic principles of relational database design.

Database design expert Michael J. Hernandez introduces the core concepts of design theory and method without the technical jargon. Database Design for Mere Mortals will provide any developer with a common-sense design methodology for developing databases that work.

0201694719B04062001

Table of Contents

  1. Copyright
    1. Dedication
  2. Preface
    1. About the Author
    2. Foreword
    3. Preface and Acknowledgments
    4. Introduction
    5. Who Should Read This Book
    6. The Purpose of This Book
    7. How to Read This Book
    8. How This Book Is Organized
    9. Part One: Relational Database Design
    10. Part Two: The Design Process
    11. Part Three: Other Database Design Issues
    12. IMPORTANT: READ THIS SECTION!
      1. A Word About the Examples and Techniques in This Book
      2. A New Approach to Learning
  3. I. Relational Database Design
    1. 1. What Is a Relational Database?
      1. Types of Databases
      2. Early Database Models
        1. The Hierarchical Database Model
        2. The Network Database Model
      3. The Relational Database Model: A Brief History
      4. Relational Database Management Systems
      5. Summary
    2. 2. Design Objectives
      1. Why Should You Be Concerned with Database Design?
      2. The Importance of Theory
      3. The Advantage of Learning Good Design Methodology
        1. The Importance of Understanding Database Design
      4. Objectives of Good Design
      5. Advantages of Good Design
      6. Database Design Methods
        1. Traditional Design Methods
        2. The Design Method Presented in This Book
      7. Summary
    3. 3. Terminology
      1. Why This Terminology Is Important
      2. Value-related Terms
        1. Data
        2. Information
        3. Null
      3. Structure-related Terms
        1. Table
        2. Field
        3. Record
        4. View
        5. Keys
        6. Index
      4. Relationship-related Terms
        1. Relationships
        2. Types of Relationships
          1. One-to-One Relationships
          2. One-to-Many Relationships
          3. Many-to-Many Relationships
        3. Types of Participation
        4. Degree of Participation
      5. Integrity-related Terms
        1. Field Specification
        2. Data Integrity
      6. Summary
  4. II. The Design Process
    1. 4. Conceptual Overview
      1. The Importance of Completing the Design Process
      2. Defining a Mission Statement and Mission Objectives
      3. Analyzing the Current Database
      4. Creating the Data Structures
      5. Determining and Establishing Table Relationships
      6. Determining and Defining Business Rules
      7. Determining and Establishing Views
      8. Reviewing Data Integrity
      9. Summary
    2. 5. Starting the Process
      1. Conducting Interviews
      2. THE CASE STUDY: MIKES'S BIKES
      3. Defining the Mission Statement
        1. The Well-Written Mission Statement
        2. Composing a Mission Statement
        3. CASE STUDY
      4. Defining The Mission Objectives
        1. Well-Written Mission Objectives
        2. Composing Mission Objectives
        3. CASE STUDY
      5. Summary
    3. 6. Analyzing the Current Database
      1. Getting to Know the Current Database
        1. Paper-based Databases
        2. Legacy Databases
      2. Conducting the Analysis
      3. Looking at How Data Is Collected
      4. Looking at How Information Is Presented
      5. Conducting Interviews
      6. Conducting User Interviews
        1. Reviewing Data Type and Usage
        2. Reviewing the Samples
        3. Reviewing Information Requirements
          1. Current Information Requirements
          2. Additional Information Requirements
          3. Future Information Requirements
      7. Conducting Management Interviews
        1. Reviewing Current Information Requirements
        2. Reviewing Additional Information Requirements
        3. Reviewing Future Information Requirements
        4. Reviewing Overall Information Requirements
      8. Compiling a Complete List of Fields
        1. The Preliminary Field List
        2. The Calculated Field List
        3. Reviewing Both Lists with Users and Management
      9. CASE STUDY
      10. Summary
    4. 7. Establishing Table Structures
      1. Defining the Preliminary Table List
        1. Determining Implied Subjects
        2. Using the List of Subjects
        3. Using the Mission Objectives
      2. Define the Final Table List
        1. Refining the Table Names
          1. Guidelines for Creating a Table Names
        2. Indicating the Table Types
        3. Composing the Table Descriptions
          1. Guidelines for Composing a Table Description
          2. Interviewing Users and Management
      3. Associating Fields with Each Table
      4. Refining the Fields
        1. Improving the Field Names
          1. Guidelines for Creating Field Names
        2. Using the Ideal Field to Resolve Anomalies
          1. Elements of the Ideal Field
          2. Resolving Multipart Fields
          3. Resolving Multivalued Fields
      5. Refining the Table Structures
        1. A Word about Redundant Data and Duplicate Fields
        2. Using the Ideal Table to Refine Table Structures
          1. Elements of the Ideal Table
          2. Resolving Unnecessary Duplicate Fields
        3. Establishing Subset Tables
          1. Refine Previously Unidentified Subset Tables
          2. CASE STUDY
      6. Summary
    5. 8. Keys
      1. Why Keys Are Important
      2. Establishing Keys for Each Table
        1. Candidate Keys
          1. Elements of a Candidate Key
          2. Artificial Candidate Keys
        2. Primary Keys
          1. Elements of a Primary Key
          2. Rules for Establishing a Primary Key
          3. Alternate Keys
        3. Non-keys
        4. Table-Level Integrity
      3. Reviewing the Initial Table Structures
      4. CASE STUDY
      5. Summary
    6. 9. Field Specifications
      1. Why Field Specifications Are Important
      2. Field-Level Integrity
        1. Elements of the Ideal Field
      3. Anatomy of a Field Specification
        1. General Elements
          1. Field Name
          2. Label
          3. Parent Table
          4. Shared By
          5. Alias(es)
          6. Description
          7. Guidelines for Composing a Field Description
        2. Physical Elements
          1. Data Type
          2. Character Support
          3. Length
          4. Decimal Places
          5. Input Mask
          6. Display Format
        3. Logical Elements
          1. Type of Key
          2. Uniqueness
          3. Required Value
          4. Null Support
          5. Edit Rule
          6. Comparisons Allowed
          7. Operations Allowed
          8. Values Entered By
          9. Default Value
          10. Range of Values
        4. Specification Information
          1. Specification Type
          2. Based on Existing Specification
          3. Source Specification
      4. Defining Field Specifications for Each Field in the Database
      5. CASE STUDY
      6. Summary
    7. 10. Table Relationships
      1. Types of Relationships
        1. One-to-One Relationships
        2. One-to-Many Relationships
        3. Many-to-Many Relationships
          1. Problems with Many-to-Many Relationships
      2. Identifying Existing Relationships
      3. Establishing Each Relationship
        1. One-to-One and One-to-Many Relationships
          1. The One-to-One Relationship
          2. The One-to-Many Relationship
        2. The Many-to-Many Relationship
        3. Reviewing the Structure of Each Table
          1. Elements of the Ideal Table
        4. Refining All Foreign keys
          1. Elements of a Foreign Key
      4. Establishing Relationship Characteristics
        1. Establishing a Deletion Rule for Each Relationship
        2. Identifying the Type of Participation for Each Table
        3. Identifying the Degree of Participation for Each Table
        4. Verifying Table Relationships with Users and Management
      5. Relationship-Level Integrity
      6. CASE STUDY
      7. Summary
    8. 11. Business Rules
      1. What Are Business Rules?
        1. Types of Business Rules
        2. Categories of Business Rules
          1. Field-Specific Business Rules
          2. Relationship-Specific Business Rules
      2. Defining and Establishing Business Rules
        1. Working with Users and Management
        2. Defining and Establishing Field-Specific Business Rules
        3. Defining and Establishing Relationship-Specific Business Rules
      3. Validation Tables
        1. What Are Validation Tables?
        2. Using Validation Tables to Support Business Rules
      4. Reviewing the Business Rule Specification Sheets
      5. CASE STUDY
      6. Summary
    9. 12. Views
      1. What Are Views?
      2. Anatomy of a View
        1. Data Views
        2. Aggregate Views
        3. Validation Views
      3. Determining and Establishing Views
        1. Working with Users and Management
        2. Establishing Views
          1. Using Calculated Fields Where Appropriate
          2. Imposing Criteria to Filter the Data
          3. Using a View Specifications Sheet to Record the View
        3. Reviewing the Documentation for Each View
        4. CASE STUDY
      4. Summary
    10. 13. Reviewing Data Integrity
      1. Why You Should Review Data Integrity
      2. Reviewing and Refining Data Integrity
        1. At the Table Level
        2. At the Field Level
        3. At the Relationship Level
        4. At the Level of Business Rules
        5. At the Level of Views
      3. Assembling the Database Documentation
      4. Done at Last!
      5. CASE STUDY—WRAP UP
      6. Summary
  5. III. Other Database Design Issues
    1. 14. Bad Design—What Not To Do
      1. Flat-File Design
      2. Spreadsheet Design
        1. Dealing with the Spreadsheet View Mind-set
      3. Database Design Based on the Database Software
      4. A Final Thought
      5. Summary
    2. 15. Bending or Breaking the Rules
      1. When May You Bend or Break the Rules?
        1. Designing an Analytical Database
        2. Improving Processing Performance
          1. Is It Worth It?
          2. Improving Performance by Other Means First
      2. Documenting Your Actions
      3. Summary
    3. 16. In Closing
    4. A. Recommended Reading
    5. B. Sample Designs
    6. C. Diagram Symbols
    7. D. Documentation Forms
    8. References