Database Design

Table of Contents

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?

I. OVERVIEW OF DATABASE DESIGN.

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

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

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

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

II. ANALYZING AND MODELING BUSINESS REQUIREMENTS.

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

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

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

8. 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 #1. Sample Normalization Exercise #2. Normalization Self-test. Summary.

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

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

III. DESIGNING THE DATABASE.

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

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

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

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

IV. LIFE AFTER DESIGN.

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

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

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

Appendix A. Sample Physical Database Implementation.

Appendix B. Popular Database Design Tools.

Appendix C. 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.

Appendix D. Sample Database Designs.

BILLING. CLASS SCHEDULING. CLIENT CONTACTS. GROCERY STORE MANAGEMENT. HUMAN RESOURCES. PRODUCT INVENTORY. PRODUCT ORDERS. RESUME MANAGEMENT. SYSTEM MANAGEMENT. USER MANAGEMENT.

Appendix E. Sample Table Sizing Worksheet.

Glossary.

Index.

Read More