Logical Database Design Principles

by Garmany; John

• ISBN13: 9780849318535
• ISBN10: 084931853X
• Format: Hardcover
• Copyright: 2005-05-12
• Publisher: Auerbach Public

Summary

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.

Table of Contents

1 Introduction to Logical Database Design	1	(16)
Understanding a Database	1	(1)
Database Architectures	2	(2)
Relational Databases	4	(1)
Creating the Database	4	(1)
System Development Life Cycle (SDLC)	5	(1)
Systems Planning: Assessment and Feasibility	5	(1)
System Analysis: Requirements	6	(2)
System Analysis: Requirements Checklist	8	(1)
Models Tracking and Schedules	8	(1)
Design Modeling	9	(1)
Functional Decomposition Diagram	10	(1)
Data Flow Diagrams	11	(1)
Data Dictionary	12	(2)
Logical Structures and Decision Trees	14	(1)
System Design: Logical	15	(2)
2 System Design and Implementation	17	(18)
The ER Approach	17	(1)
Entities and Entity Types	18	(2)
Attribute Domains	20	(1)
Attributes	20	(1)
Set-Valued Attributes	20	(2)
Weak Entities	22	(1)
Constraints	23	(2)
Key Constraints: Primary	23	(1)
Key Constraints: Foreign	24	(1)
Lookup Tables and Consistency	25	(1)
Modeling Approaches	26	(1)
Relationships	26	(3)
Cardinality	29	(2)
Schemas	31	(1)
Implementation: Converting the ERD	32	(2)
Specifications and Documentation	34	(1)
3 Converting the Logical Data Model	35	(20)
Database Architecture	35	(3)
SQL	38	(1)
DDL Commands	38	(7)
Data Retrieval	45	(1)
DML Statements and Implicit Cursors	46	(1)
SELECT Statements	47	(2)
Cartesian Product	49	(1)
DML: INSERT Commands	50	(1)
DML: UPDATE Command	51	(1)
DML: DELETE Statements	52	(1)
Transaction Commands	53	(1)
DCL Commands	54	(1)
4 Formal Data Normalization	55	(18)
Definitions of Normal Forms	57	(2)
First Normal Form	57	(1)
Second Normal Form	57	(1)
Third Normal Form	57	(1)
Boyce-Codd Normal Form	58	(1)
Fourth Normal Form	58	(1)
Fifth Normal Form	58	(1)
Introduction to Relational Algebra	59	(6)
Comparison Operations and Logical Operators	65	(1)
Applying Normal Forms	66	(5)
First Normal Form	68	(1)
Second Normal Form	68	(2)
Third Normal Form	70	(1)
Boyce-Codd Normal Form	71	(1)
Fourth Normal Form	71	(1)
Fifth Normal Form	71	(1)
Summary	71	(2)
5 Data Denormalization	73	(10)
Historical Perspective on Disk Costs	73	(1)
The Importance of Database Performance to the Logical Model	74	(1)
Introducing Data Redundancy: Size versus Volatility	75	(1)
The Theory of Dr. Ralph Kimball	76	(1)
Pre Joining Entities Together	76	(2)
Pre-Aggregating Summary Information	78	(1)
Non-First Normal Form Entities	79	(1)
Horizontal and Vertical Partitioning for Performance	79	(1)
Summary	80	(3)
6 Object-Oriented Design	83	(18)
Object-Oriented Terminology	85	(6)
Classes	85	(1)
Objects	86	(1)
Messages	86	(1)
Abstract Classes	86	(1)
Encapsulation	86	(1)
Inheritance	87	(1)
Polymorphism	87	(1)
Containment	88	(1)
Extensibility	88	(1)
Object Identity	88	(2)
Transparent Persistence	90	(1)
Storage and Access of Objects	90	(1)
Object-Oriented Design	91	(4)
Object-Oriented Analysis	91	(1)
Different Models for Object Analysis	91	(2)
The Rumbaugh Method	93	(1)
The Booch Method	93	(1)
The Coad-Yourdon Method	93	(1)
The Shlaer-Mellor Method	94	(1)
The Unified Modeling Language	94	(1)
Creating the Object Model	95	(3)
Methods and Database Objects	98	(2)
Summary	100	(1)
7 Object-Relational Design	101	(20)
Advantages of Object-Relational Databases	102	(1)
Atomic, Normalized Data	102	(1)
Ad hoc Queries	102	(1)
Object Types	103	(7)
Large Objects	105	(2)
Collections	107	(9)
Arrays	107	(1)
Nested Tables	108	(2)
Object Views	110	(1)
Using Logical Pointers	111	(4)
Comparison of Object-Oriented and Object-Relational Databases	115	(1)
Creating the Object-Relational Design	116	(3)
Logical Object Pointers	117	(1)
Automated Tools	118	(1)
Summary	119	(2)
8 Designing Replicated Databases	121	(18)
Manual Replication Techniques	122	(1)
Standby Database	123	(1)
Replication Using Triggers	124	(1)
Replication Using Views	124	(1)
One-Way Replication	125	(1)
Writeable Replication	125	(1)
Updateable Replication	126	(4)
The Push-Pull Method	126	(1)
The Multi-Master Method	127	(2)
The Streams Method	129	(1)
What Are Conflicts?	130	(3)
Conflict Avoidance	131	(1)
Database Conflict Resolution	132	(1)
Which Form of Replication is Right for You?	133	(2)
Planning Your Replication Schema	135	(2)
Primary Keys	135	(1)
Foreign Keys	135	(1)
Indexes	136	(1)
Denormalization	136	(1)
Moving Large Datasets	136	(1)
Resynching Data	137	(1)
Summary	137	(2)
9 Design of the OLTP Database	139	(16)
Designing for Online Transaction Processing	139	(1)
Normalization	140	(4)
Data Storage	141	(1)
Database Data Cache Efficiency	142	(2)
Data Growth	144	(2)
SQL Reuse	146	(2)
Database Locking and Waiting	148	(1)
Deadlocks	149	(1)
Rollbacks	149	(1)
Indexing	150	(3)
Primary Keys	151	(1)
Table with Three or More Columns	151	(1)
Multiple Indexes with the Most Restrictive Lead Columns	151	(1)
Large Multi-Column Indexes	152	(1)
Organizing Tables as Indexes	152	(1)
Summary	153	(2)
10 Design of Data Warehouses and Decision Support System Databases	155	(18)
What Is a Data Warehouse?	155	(3)
OLAP, MOLAP, and ROLAP	156	(1)
Using an Operational Database as a Data Warehouse	157	(1)
Gathering Data	158	(1)
Staging and Processing Data	158	(1)
Loading and Unloading Data	159	(2)
Data Marts	161	(1)
Designing the Data Warehouse	161	(10)
Data Granularity	162	(1)
Partitioning Data	162	(5)
Which Attribute to Partition On	163	(1)
Where to Partition	164	(3)
Use of Keys	167	(1)
Normalization	168	(1)
Dimensional Modeling	168	(1)
Pre-Aggregation	169	(2)
Protect the Data	171	(1)
Summary	171	(2)
Index	173