| |
| |
| Introduction | |
| |
| |
| Who Should Read This Book? | |
| |
| |
| What Makes This Book Different? | |
| |
| |
| Table Conventions Used in This Book | |
| |
| |
| How This Book Is Organized | |
| |
| |
| What's on the Web Site? | |
| |
| |
| |
| Overview of Database Design | |
| |
| |
| |
| Understanding Database Fundamentals | |
| |
| |
| What Is a Database? | |
| |
| |
| What Are the Uses of a Database? | |
| |
| |
| Who Uses a Database? | |
| |
| |
| Database Environments | |
| |
| |
| Mainframe Environment | |
| |
| |
| Client/Server Environment | |
| |
| |
| Internet Computing Environment | |
| |
| |
| From Where Does a Database Originate? | |
| |
| |
| Business Rules | |
| |
| |
| Business Processes | |
| |
| |
| Information and Data | |
| |
| |
| Requirements Analysis | |
| |
| |
| Entities | |
| |
| |
| Attributes | |
| |
| |
| Business Process Reengineering | |
| |
| |
| What Elements Comprise a Database? | |
| |
| |
| Database Schema | |
| |
| |
| Table | |
| |
| |
| Data Types | |
| |
| |
| Does the Database Have Integrity? | |
| |
| |
| Primary Keys | |
| |
| |
| Foreign Keys | |
| |
| |
| Relationships | |
| |
| |
| Key Database Design Concepts | |
| |
| |
| Design Methodology | |
| |
| |
| Converting the Business Model to Design | |
| |
| |
| Application Design | |
| |
| |
| What Makes a Good Database? | |
| |
| |
| Storage Needs Met | |
| |
| |
| Data Is Available | |
| |
| |
| Data Protected | |
| |
| |
| Data Is Accurate | |
| |
| |
| Acceptable Performance | |
| |
| |
| Redundant Data Is Minimized | |
| |
| |
| Summary | |
| |
| |
| |
| Exploration of Database Models | |
| |
| |
| Types of Databases | |
| |
| |
| Flat-File Database Model | |
| |
| |
| Hierarchical Database Model | |
| |
| |
| Network Database Model | |
| |
| |
| Relational Database Model | |
| |
| |
| Object-Oriented (OO) Database Model | |
| |
| |
| Object-Relational (OR) Database Model | |
| |
| |
| The Modern Database of Choice | |
| |
| |
| Relational Database Characteristics | |
| |
| |
| Relational Database Objects | |
| |
| |
| SQL: The Relational Database Language | |
| |
| |
| Web Links for More Information on Database Models | |
| |
| |
| Making Your Selection | |
| |
| |
| Summary | |
| |
| |
| |
| Database Design Planning | |
| |
| |
| What Is a Database Design? | |
| |
| |
| Importance of Database Design | |
| |
| |
| Planning Database Design | |
| |
| |
| The Mission Statement | |
| |
| |
| Devising a Work Plan | |
| |
| |
| Setting Milestones and Making Deadlines | |
| |
| |
| Establishing the Design Team and Assigning Tasks | |
| |
| |
| Trademarks of a Solid Database Design | |
| |
| |
| Overview of Design Methodologies | |
| |
| |
| Logical Versus Physical Modeling | |
| |
| |
| Logical Modeling | |
| |
| |
| Physical Modeling | |
| |
| |
| Automated Design Tools | |
| |
| |
| Why Use an Automated Design Tool? | |
| |
| |
| Understanding the Capabilities of an Automated Design Tool | |
| |
| |
| Summary | |
| |
| |
| |
| The Database Design Life Cycle | |
| |
| |
| The System Development Process | |
| |
| |
| Traditional Method | |
| |
| |
| The Barker Method | |
| |
| |
| Adapted Design Methods | |
| |
| |
| Overview of Design Processes | |
| |
| |
| Defining Data | |
| |
| |
| Creating Data Structures | |
| |
| |
| Defining Data Relationships | |
| |
| |
| Determining Views | |
| |
| |
| Redesign of an Existing Database | |
| |
| |
| Overview of the Database Life Cycle | |
| |
| |
| Development Environment | |
| |
| |
| Test Environment | |
| |
| |
| Production Environment | |
| |
| |
| Summary | |
| |
| |
| |
| Analyzing and Modeling Business Requirements | |
| |
| |
| |
| Gathering Business and System Requirements | |
| |
| |
| Types of Requirements | |
| |
| |
| Business Requirements | |
| |
| |
| System Requirements | |
| |
| |
| Overview of Requirements Analysis | |
| |
| |
| Determining Business Requirements | |
| |
| |
| Who Has "Say So?" | |
| |
| |
| Interviewing Management | |
| |
| |
| Interviewing the Customer | |
| |
| |
| Interviewing the End User | |
| |
| |
| Studying the Existing Processes in Place | |
| |
| |
| Analyzing Business Requirements | |
| |
| |
| Determining System Requirements | |
| |
| |
| Identifying the Data | |
| |
| |
| Establishing Groups of Data | |
| |
| |
| Establishing a List of Fields | |
| |
| |
| Establishing Relationships | |
| |
| |
| Determining the Direction of Database Design | |
| |
| |
| Determining the Type of Database Model | |
| |
| |
| Selecting an Implementation | |
| |
| |
| Setting Naming Conventions and Standards to Be Used | |
| |
| |
| Setting Milestones and Deadlines | |
| |
| |
| Assigning Roles to Members of Design Team | |
| |
| |
| Preliminary Documentation | |
| |
| |
| High-level Work Plan | |
| |
| |
| Strategy Document | |
| |
| |
| Detailed Requirements Document | |
| |
| |
| Evaluating Analysis | |
| |
| |
| Summary | |
| |
| |
| |
| Establishing a Business Model | |
| |
| |
| Understanding Business Modeling Concepts | |
| |
| |
| Using the Information Gathered | |
| |
| |
| Business Model Diagrams | |
| |
| |
| Common Business Models | |
| |
| |
| Sample Elements in a Business Model | |
| |
| |
| Summary | |
| |
| |
| |
| Understanding Entities and Relationships | |
| |
| |
| Overview of Entities and Entity Relationships | |
| |
| |
| One-to-One Relationship | |
| |
| |
| One-to-Many Relationship | |
| |
| |
| Many-to-Many Relationship | |
| |
| |
| Recursive Relationships | |
| |
| |
| Mandatory Relationships | |
| |
| |
| Optional Relationships | |
| |
| |
| Transformation of the Entity in Design | |
| |
| |
| How Will the User Access the Data? | |
| |
| |
| Avoiding Poor Relationship Constructs | |
| |
| |
| Understanding Relationships and Table Joins | |
| |
| |
| Summary | |
| |
| |
| |
| Normalization: Eliminating Redundant Data | |
| |
| |
| Overview of Normalization | |
| |
| |
| Advantages of Normalization | |
| |
| |
| Disadvantages of Normalization | |
| |
| |
| Overview of the Normal Forms | |
| |
| |
| First Normal Form: The Key | |
| |
| |
| Second Normal Form: The Whole Key | |
| |
| |
| Third Normal Form: And Nothing but the Key | |
| |
| |
| Boyce-Codd Normal Form | |
| |
| |
| Fourth Normal Form | |
| |
| |
| Fifth Normal Form | |
| |
| |
| Denormalization | |
| |
| |
| Sample Normalization Exercise | |
| |
| |
| Sample Normalization Exercise #2 | |
| |
| |
| Normalization Self-test | |
| |
| |
| Summary | |
| |
| |
| |
| Entity Relationship Modeling | |
| |
| |
| Logically Modeling Business Entities | |
| |
| |
| Constructing Entities in the ERD | |
| |
| |
| Defining Entity Relationships | |
| |
| |
| Check to See if a Relationship Exists | |
| |
| |
| Identify the Verbs for the Relationship | |
| |
| |
| Identify the Optionality | |
| |
| |
| Identify a Degree | |
| |
| |
| Validate the Relationship | |
| |
| |
| Defining the Attributes for an Entity | |
| |
| |
| How an ERD Is Used | |
| |
| |
| Typical ERD Symbols | |
| |
| |
| An ERD for the Sample Company TrainTech | |
| |
| |
| Summary | |
| |
| |
| |
| Modeling Business Processes | |
| |
| |
| How Do Business Processes Affect Database Design? | |
| |
| |
| Defining Business Processes | |
| |
| |
| Overview of Process Modeling | |
| |
| |
| The Process Model | |
| |
| |
| The Function Hierarchy | |
| |
| |
| The Data Flow Diagram | |
| |
| |
| What Does One Gain from the Process Model? | |
| |
| |
| Typical Process Modeling Symbols | |
| |
| |
| Using Process Models in Database Design | |
| |
| |
| Process Models for the Sample Company TrainTech | |
| |
| |
| Summary | |
| |
| |
| |
| Designing the Database | |
| |
| |
| |
| Designing Tables | |
| |
| |
| Types of Tables | |
| |
| |
| Data Tables | |
| |
| |
| Join Tables | |
| |
| |
| Subset Tables | |
| |
| |
| Validation Tables | |
| |
| |
| Basic Table Structure | |
| |
| |
| Defining Your Tables | |
| |
| |
| Reviewing Naming Conventions | |
| |
| |
| Establishing a Table List | |
| |
| |
| Determining Column Specifications | |
| |
| |
| General Level | |
| |
| |
| Physical Level | |
| |
| |
| Logical Level | |
| |
| |
| Establishing a Column List | |
| |
| |
| Table Design Considerations | |
| |
| |
| Referential Integrity in Table Design | |
| |
| |
| Importance of the Logical Model in Table Design | |
| |
| |
| Denormalization During Physical Design | |
| |
| |
| Storage Considerations | |
| |
| |
| Table Growth and Sizing | |
| |
| |
| Actual Growth and Monitoring | |
| |
| |
| Views Versus Replication | |
| |
| |
| RAID | |
| |
| |
| Ownership of Tables | |
| |
| |
| Table Design for the Sample Company TrainTech | |
| |
| |
| Summary | |
| |
| |
| |
| Integrating Business Rules and Data Integrity | |
| |
| |
| How Do Business Rules Affect the Database? | |
| |
| |
| Application of a Primary Key Constraint in SQL | |
| |
| |
| Application of a Foreign Key Constraint in SQL | |
| |
| |
| Application of a Unique Constraint in SQL | |
| |
| |
| Application of a Check Constraint in SQL | |
| |
| |
| Extracting Business Rules from the Logical Model | |
| |
| |
| The Nature of the Data | |
| |
| |
| Data Type of Data | |
| |
| |
| Uniqueness of Data | |
| |
| |
| Case of Data | |
| |
| |
| References to Data | |
| |
| |
| Maintaining Historic Data | |
| |
| |
| Enforcing Business Rules | |
| |
| |
| Using Triggers to Enforce Business Rules | |
| |
| |
| Using Validation Tables to Enforce Business Rules | |
| |
| |
| Integrating Business Rules at the N-Tier Level | |
| |
| |
| Constraint Generation Using an AD Tool | |
| |
| |
| Constraint Integration for the Sample Company TrainTech | |
| |
| |
| Summary | |
| |
| |
| |
| Designing Views | |
| |
| |
| Overview of Views | |
| |
| |
| Why Use Views? | |
| |
| |
| Data Summarization | |
| |
| |
| Filtering Data | |
| |
| |
| Database Security | |
| |
| |
| Data Conversion | |
| |
| |
| Data Partitioning | |
| |
| |
| View Performance and Other Considerations | |
| |
| |
| Join Operations in View Definitions | |
| |
| |
| View Limitations | |
| |
| |
| View Relationships | |
| |
| |
| Managing Views | |
| |
| |
| Avoiding Poor View Design | |
| |
| |
| View Definitions for the Sample Company TrainTech | |
| |
| |
| Summary | |
| |
| |
| |
| Applying Database Design Concepts | |
| |
| |
| Database Design Case Study | |
| |
| |
| Making Sense of the Regurgitated Information | |
| |
| |
| Isolating Individuals Associated with the Grocery Store | |
| |
| |
| The Interviewee's Interpretation of the Data Required | |
| |
| |
| Formulating a Mission Statement and Design Objectives | |
| |
| |
| Defining Organizational Units | |
| |
| |
| Defining Data | |
| |
| |
| Defining Processes | |
| |
| |
| Proceeding with Database Design | |
| |
| |
| Constructing an ERD | |
| |
| |
| Constructing Process Models | |
| |
| |
| Designing Tables | |
| |
| |
| Defining Constraints | |
| |
| |
| Designing Views | |
| |
| |
| Summary | |
| |
| |
| |
| Life After Design | |
| |
| |
| |
| Implementing Database Security | |
| |
| |
| How Is Security Important to Database Design? | |
| |
| |
| Who Needs Access to the Database? | |
| |
| |
| Levels of Access | |
| |
| |
| Privileges | |
| |
| |
| Roles | |
| |
| |
| Who Is in Charge of Security? | |
| |
| |
| System Level Management | |
| |
| |
| Database-level Management | |
| |
| |
| Application-level Management | |
| |
| |
| Using Views and Procedures to Enhance Security | |
| |
| |
| Designing a Security Management System | |
| |
| |
| Taking Additional Precautionary Measures | |
| |
| |
| Network Security | |
| |
| |
| Network Firewall | |
| |
| |
| Secure Sockets Layer | |
| |
| |
| Breaches in Security | |
| |
| |
| Summary | |
| |
| |
| |
| Change Control | |
| |
| |
| Necessity of Change Control in Database Design | |
| |
| |
| Changes in Business Needs | |
| |
| |
| Changes in System Needs | |
| |
| |
| Improving Data Integrity | |
| |
| |
| Implementing Security Features for Sensitive Data | |
| |
| |
| Requirements-Based Testing | |
| |
| |
| Improving Consistency of Documentation | |
| |
| |
| Improving System Performance | |
| |
| |
| Formal Change-Control Methods | |
| |
| |
| Version Control | |
| |
| |
| Prioritizing Changes | |
| |
| |
| Tracking Change Requests | |
| |
| |
| Change-Control Participants | |
| |
| |
| Change-Process Implementation | |
| |
| |
| Basic Guidelines for Change Propagation | |
| |
| |
| Considerations for Using Automated Configuration Management Tools | |
| |
| |
| Summary | |
| |
| |
| |
| Analyzing Legacy Databases for Redesign | |
| |
| |
| Overview of the Legacy Database | |
| |
| |
| Is It Worth the Effort? | |
| |
| |
| Staying Current with Technology | |
| |
| |
| Hardware and Software Requirements | |
| |
| |
| Costs | |
| |
| |
| Business Interruptions | |
| |
| |
| Training Considerations | |
| |
| |
| Performance Issues | |
| |
| |
| Assessment of the Existing Database | |
| |
| |
| The Effects of Business Process Re-engineering | |
| |
| |
| Designing for the New System | |
| |
| |
| Database Design Method to Be Used | |
| |
| |
| Database Software to Be Used | |
| |
| |
| Redesigning Data Structures | |
| |
| |
| Migrating Legacy Data | |
| |
| |
| A Sample Conversion of Legacy Data | |
| |
| |
| Documentation | |
| |
| |
| Future of the New Database | |
| |
| |
| Summary | |
| |
| |
| Appendixes | |
| |
| |
| |
| Sample Physical Database Implementation | |
| |
| |
| |
| Popular Database Design Tools | |
| |
| |
| |
| Database Design Checklists | |
| |
| |
| Planning Database Design | |
| |
| |
| Gathering Information to Design the Database | |
| |
| |
| Modeling Entity Relationships | |
| |
| |
| Physical Design Considerations | |
| |
| |
| Security Considerations | |
| |
| |
| Legacy Database Redesign Considerations | |
| |
| |
| Evaluating the Completeness of Stages in the Database Life Cycle | |
| |
| |
| |
| Sample Database Designs | |
| |
| |
| Billing | |
| |
| |
| Class Scheduling | |
| |
| |
| Client Contacts | |
| |
| |
| Grocery Store Management | |
| |
| |
| Human Resources | |
| |
| |
| Product Inventory | |
| |
| |
| Product Orders | |
| |
| |
| Resume Management | |
| |
| |
| System Management | |
| |
| |
| User Management | |
| |
| |
| |
| Sample Table Sizing Worksheet | |
| |
| |
| Glossary | |
| |
| |
| Index | |