| |
| |
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 | |