1st Edition
Logical Database Design Principles
Until now, almost all books on logical database design focused exclusively on relational design. However, modern database management systems have added powerful features that have driven a movement away from truly normalized database design. Logical Database Design Principles reflects these recent changes. The book begins by covering traditional logical design principles, followed by an analysis of the normalizing and modeling of data. It then examines designing for specific purposes, such as object-oriented databases, online transaction processing (OLTP), and data warehouses. As the text progresses, it moves from the purely logical into some physical design, as determined by how the features of modern databases are implemented.
Because this is a logical-design book, the authors attempt to make the examples database-neutral. Because various vendors implement features in different ways, this volume sometimes offers examples from a specific database management system. When necessary, it highlights a specific application, clearly demonstrating the concept being discussed.
Understanding a Database
Database Architectures
Relational Databases
Creating the Database
System Development Life Cycle (SDLC)
Systems Planning: Assessment and Feasibility
System Analysis: Requirements
System Analysis: Requirements Checklist
Models Tracking and Schedules
Design Modeling
Functional Decomposition Diagram
Data Flow Diagrams
Data Dictionary
Logical Structures and Decision Trees
System Design: Logical
SYSTEM DESIGN AND IMPLEMENTATION
The ER Approach
Entities and Entity Types
Attribute Domains
Attributes
Set-Valued Attributes
Weak Entities
Constraints
Lookup Tables and Consistency
Modeling Approaches
Relationships
Cardinality
Schemas
Implementation: Converting the ERD
Specifications and Documentation
CONVERTING THE LOGICAL DATA MODEL
Database Architecture
SQL
DDL Commands
Data Retrieval
DML Statements and Implicit Cursors
SELECT Statements
Cartesian Product
DML: INSERT Commands
DML: UPDATE Command
DML: DELETE Statements
Transaction Commands
DCL Commands
FORMAL DATA NORMALIZATION
Definitions of Normal Forms
Introduction to Relational Algebra
Comparison Operations and Logical Operators
Summary
DATA DENORMALIZATION
Historical Perspective on Disk Costs
The Importance of Database Performance to the Logical Model
Introducing Data Redundancy: Size versus Volatility
The Theory of Dr. Ralph Kimball
Pre-Joining Entities Together
Pre-Aggregating Summary Information
Non-First Normal Form Entities
Horizontal and Vertical Partitioning for Performance
Summary
OBJECT-ORIENTED DESIGN
Object-Oriented Terminology
Object-Oriented Design
Creating the Object Model
Methods and Database Objects
Summary
OBJECT-RELATIONAL DESIGN
Advantages of Object-Relational Databases
Object Types
Object Views
Using Logical Pointers
Comparison of Object-Oriented and Object-Relational Databases
Creating the Object-Relational Design
Summary
DESIGNING REPLICATED DATABASES
Manual Replication Techniques
Standby Database
Replication Using Triggers
Replication Using Views
One-Way Replication
Writeable Replication
Updateable Replication
What Are Conflicts?
Which Form of Replication is Right for You?
Planning Your Replication Schema
Summary
DESIGN OF THE OLTP DATABASE
Designing for Online Transaction Processing
Normalization
Data Storage
SQL Reuse
Database Locking and Waiting
Deadlocks
Rollbacks
Indexing
Summary
DESIGN OF DATA WAREHOUSES AND DECISION
SUPPORT SYSTEM DATABASES
What Is a Data Warehouse?
Gathering Data
Staging and Processing Data
Loading and Unloading Data
Data Marts
Designing the Data Warehouse
Summary
Biography
John Garmany, Jeff Walker, Terry Clark