Data Modeling, A Beginner's Guide

Data Modeling, A Beginner's Guide

by Andy Oppel
Released November 2009
Publisher(s): McGraw-Hill
ISBN: 9780071623995

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

Essential Skills--Made Easy!

Learn how to create data models that allow complex data to be analyzed, manipulated, extracted, and reported upon accurately. Data Modeling: A Beginner's Guide teaches you techniques for gathering business requirements and using them to produce conceptual, logical, and physical database designs. You'll get details on Unified Modeling Language (UML), normalization, incorporating business rules, handling temporal data, and analytical database design. The methods presented in this fast-paced tutorial are applicable to any database management system, regardless of vendor.

Designed for Easy Learning

  • Key Skills & Concepts--Chapter-opening lists of specific skills covered in the chapter
  • Ask the expert--Q&A sections filled with bonus information and helpful tips
  • Try This--Hands-on exercises that show you how to apply your skills
  • Notes--Extra information related to the topic being covered
  • Self Tests--Chapter-ending quizzes to test your knowledge

Andy Oppel has taught database technology for the University of California Extension for more than 25 years. He is the author of Databases Demystified, SQL Demystified, and Databases: A Beginner's Guide, and the co-author of SQL: A Beginner's Guide, Third Edition, and SQL: The Complete Reference, Third Edition.

Table of contents

  1. Cover Page
  2. Data Modeling A Beginner’s Guide
  3. Copyright Page
  4. Contents
  5. Acknowledgments
  6. Introduction
  7. Part I Data Modeling Concepts
    1. 1 Introduction to Data Modeling
      1. Data-Centric Design
      2. Anatomy of a Data Model
        1. Layers of Data Abstraction
        2. Types of Data Models
      3. Importance of Data Modeling
        1. Documentation of Business Rules
        2. Visualization
        3. Illustration of Alternatives
        4. Foundation for Future Expansion
        5. Promotion of Common and Standard Structures
        6. Provisions for Automation
      4. Measures of a Good Data Model
        1. Enforcement of Business Rules
        2. Flexible and Adaptable
        3. Easily Understood
        4. Balanced Perspective
        5. Promotion of Data Reusability
        6. Data Integration
      5. How Data Models Fit Into Application Development
        1. Process-Oriented Methodologies
        2. Data-Oriented Methodologies
        3. Hybrid Methodologies
        4. Object-Oriented Methodologies
        5. Prototyping Methodologies
        6. Agile Methodologies
      6. Data Modeling Participants
      7. Try This 1-1: Refining a Conceptual Model
      8. Chapter 1 Self Test
    2. 2 Relational Model Components
      1. Conceptual and Logical Model Components
        1. Entities
        2. Attributes
        3. Relationships
        4. Business Rules
      2. Physical Model Components
        1. Tables
        2. Columns and Data Types
        3. Constraints
        4. Integrity Constraints
        5. Views
      3. Try This 2-1: Conceptual Model Modification
      4. Chapter 2 Self Test
    3. 3 Data and Process Modeling
      1. Data Model Diagramming Alternatives
        1. ERD Formats
        2. Representing Supertypes and Subtypes
        3. Guidelines for Drawing ERDs
      2. Process Models
        1. The Flowchart
        2. The Function Hierarchy Diagram
        3. The Swim Lane Diagram
        4. The Data Flow Diagram
      3. Unified Modeling Language (UML)
        1. UML Class Diagrams
        2. Other UML Diagrams
      4. Relating Entities and Processes
      5. Try This 3-1: Drawing a Conceptual Model with Nested Subtypes
      6. Chapter 3 Self Test
    4. 4 Organizing Database Project Work
      1. The Traditional Life Cycle
        1. Planning
        2. Requirements Gathering
        3. Conceptual Design
        4. Logical Design
        5. Physical Design
        6. Construction
        7. Implementation and Rollout
        8. Ongoing Support
      2. Nontraditional Life Cycles
        1. Prototyping
        2. Rapid Application Development
      3. The Project Triangle
      4. Try This 4-1: Project Database Management Tasks
      5. Chapter 4 Self Test
  8. Part II Data Modeling Details
    1. 5 Conceptual Data Modeling
      1. The Conceptual Modeling Process
        1. Preparation
        2. Solution Design
        3. What Differentiates Conceptual Modeling from Logical Modeling?
      2. Creating the Model
        1. Generic Models and Patterns
        2. Roles vs. Subtypes
        3. Dealing with Hierarchies, Networks, and Linked Lists
        4. Bottom Up vs. Top Down Modeling
        5. Subject Areas
      3. Evaluating the Model
        1. What Makes a Good Conceptual Model?
      4. Try This 5-1: Conceptual Model for International Addresses
      5. Chapter 5 Self Test
    2. 6 Logical Database Design Using Normalization
      1. The Need for Normalization
        1. Insert Anomaly
        2. Delete Anomaly
        3. Update Anomaly
      2. Applying the Normalization Process
        1. Choosing a Primary Key
        2. First Normal Form: Eliminating Repeating Data
        3. Second Normal Form: Eliminating Partial Dependencies
        4. Third Normal Form: Eliminating Transitive Dependencies
      3. Denormalization
      4. Practice Problems
      5. Try This 6-1: UTLA Academic Tracking
        1. The User Views
      6. Try This 6-2: Computer Books Company
        1. The User Views
      7. Chapter 6 Self Test
    3. 7 Beyond Third Normal Form
      1. Advanced Normalization
        1. Boyce-Codd Normal Form
        2. Fourth Normal Form
        3. Fifth Normal Form
        4. Domain-Key Normal Form (DKNF)
      2. Resolving Supertypes and Subtypes
      3. Generalizing Attributes
      4. Alternatives for Reference Data
        1. Common Code Structures
        2. Crosswalk Tables
        3. Language Translation Tables
      5. Try This 7-1: Complex Logical Data Model
      6. Chapter 7 Self Test
    4. 8 Physical Database Design
      1. The Physical Design Process
      2. Designing Tables
      3. Try This 8-1: Drawing a Physical Data Model
        1. Implementing Supertypes and Subtypes
        2. Naming Conventions
      4. Integrating Business Rules and Data Integrity
        1. NOT NULL Constraints
        2. Primary Key Constraints
        3. Referential (Foreign Key) Constraints
        4. Unique Constraints
        5. Check Constraints
        6. Data Types, Precision, and Scale
        7. Triggers
      5. Adding Indexes for Performance
      6. Designing Views
      7. Try This 8-2: Mapping a Logical Model to a Physical Database Design
      8. Chapter 8 Self Test
  9. Part III Design Alternatives
    1. 9 Alternatives for Incorporating Business Rules
      1. The Anatomy of a Business Rule
        1. The Origin of Business Rules
      2. Implementing Business Rules in Data Models
        1. Implementing Terms
        2. Implementing Facts
        3. Implementing Derivations
      3. Limitations on Implementing Business Rules in Data Models
        1. Implementing Constraints
        2. Constraints That Cannot Be Shown in Entity Relationship Models
      4. Functional Classification of Business Rules
        1. Definitional Rules
        2. Data Validation Rules
        3. Data Derivation Rules
        4. Cardinality Rules
        5. Referential Integrity Rules
        6. Process Rules
      5. Try This 9-1: Modeling Business Rules
      6. Chapter 9 Self Test
    2. 10 Alternatives for Handling Temporal Data
      1. Temporal Data Structures
        1. When Does Time Matter?
        2. Adding History to Data Structures
        3. Processing Rules for History
        4. Handling Deletions
      2. Calendar Data Structures
      3. Business Rules for Temporal Data
      4. Try This 10-1: Adding History to Data Structures
      5. Chapter 10 Self Test
    3. 11 Modeling for Analytical Databases
      1. Data Warehouses
        1. OLTP Systems Compared with Data Warehouse Systems
        2. Data Warehouse Architecture
      2. Data Marts
      3. Modeling Analytical Data Structures
        1. OLAP Database Requirements
        2. Data Warehouse Modeling
        3. Data Mart Modeling
      4. Loading Data into Analytical Databases
        1. The Extract Process
        2. The Transform Process
        3. The Load Process
      5. Try This 11-1: Design Star Schema Fact and Dimension Tables
      6. Chapter 11 Self Test
    4. 12 Enterprise Data Modeling
      1. Enterprise Data Management
        1. The Case for Data Management
        2. Alternatives to Centralized Data Management
      2. The Enterprise Data Model
        1. What Is an Enterprise Data Model?
        2. The Anatomy of an Enterprise Data Model
        3. Building an Enterprise Data Model
      3. Try This 12-1: Enterprise Conceptual Model Development
      4. Chapter 12 Self Test
  10. Part IV Appendixes
    1. A Answers to Self Tests
    2. B Solutions to Try This Exercises
      1. Try This 1-1: Refining a Conceptual Model
      2. Try This 2-1: Conceptual Model Modification
      3. Try This 3-1: Drawing a Conceptual Model with Nested Subtypes
      4. Try This 4-1: The Database Life Cycle
      5. Try This 5-1: Conceptual Model for International Addresses
      6. Try This 6-1: UTLA Academic Tracking
      7. Try This 6-2: Computer Books Company
      8. Try This 7-1: Complex Logical Data Model
      9. Try This 8-1: Drawing a Physical Data Model
      10. Try This 8-2: Mapping a Logical Model to a Physical Database Design
      11. Try This 9-1: Modeling Business Rules
      12. Try This 10-1: Adding History to Data Structures
      13. Try This 11-1: Design Star Schema Fact and Dimension Tables
      14. Try This 12-1: Enterprise Conceptual Model Development
  11. Index

Product information

  • Title: Data Modeling, A Beginner's Guide
  • Author(s): Andy Oppel
  • Release date: November 2009
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071623995