Usage Driven Database Design 1st edition by George Tillmann 9781484227213 1484227212

Part I: Introduction

Chapter 1: Introduction to Usage-Driven Database Design

Database Design Principle 1: Separation Principle

Database Design Principle 2: Distinction Principle

The Difference Between Separation and Distinction

Database Design Principle 3: Convergence Principle

The Separation, Distinction, and Convergence Principles

Database Design Principle 4: Minimal Regression Principle

Usage-Driven Database Design

Logical Data Modeling

Physical Schema Definition

The Terminology Trap

Notes

Part II: Logical Data Modeling

Chapter 2: The E-R Approach

A Little Data Modeling History

Some Important Definitions

Logical Data Modeling Objects

Entities

Type-Instance Distinction

Relationships

Attributes

Notes

Chapter 3: More About the E-R Approach

More About Relationships

Membership Class

Cardinality

Modality

Degree

Binary Relationship

N-ary Relationships

Unary or Recursive Relationships

Relationship Constraints

Inclusion

Exclusion

Conjunction

Simple Conjunction

Conditional Conjunction

Recursive Modality Constraints

More About Entities

Attributive Entity

Associative Entities

Supertype and Subtype Entities (Generalization and Specialization)

More About Attributes

Attribute Domain

Attribute Source: Primitive and Derived

Attribute Descriptor and Unique Identifier

Compound or Concatenated Unique Identifiers

Attribute Complexity: Simple and Group

Attribute Valuation: Single Value and Multivalue

Attribute Complexity and Valuation

Chapter 4: Building the Logical Data Model

The Interview Process

Gather Information and Review

1. Identify the Users Who Are Authorities or Experts on the Subject

2. Meet and Interview the Experts and Identify the Subject (Application) Entities

Preparation

The First Interview

3. Identify Relationships Between the Entities

4. Identify the Properties or Attributes of the Entities and Relationships

Analyze Information

Construct Model

Repeat as Necessary

Making Sense of the Interview

Modeling Rules

Verifying What You Have Heard

Immediate Interview Feedback

Formal Walk-Throughs

Increasing E-R Diagram Comprehension

Subject Areas

Entity Fragments

Neighborhood Diagrams

Relationship Bridges and Stubs

Some Model Building Best Practices

Getting Started

Don’t Lose Control of the Project to Users

Don’t Lose Control of the Project to Technical Staff

Don’t Become Dependent on Tools or Techniques

Don’t Get Bogged Down in Endless Analysis

The Players…and the Rules of Engagement

Deliverables

Examples of Deliverables

Sample Data Dictionary, Data Object Definitions

Notes

Chapter 5: LDM Best Practices

Abbreviations

Almost Unique Identifiers

Clarity

Compound Unique Identifiers

Conceptual Integrity

Conjunctive Relationships

Duplicate Super-Subtypes “Type” Data

Exclusive and Nonexclusive Generalization

Required and Nonrequired Participation

Exclusive Relationships

Group Attributes

Level of Abstraction

Many-to-Many Relationships

N-ary Relationships

N-ary Relationships and Membership Class

Naming Objects

Multiple Names

Naming Conventions

Name Uniqueness

Naming Convention Goals

Null Attributes

There Be Blanks in Them Thar Nulls

Optional Relationships (Optional-Optional Relationships)

Subject Areas

Supertypes and Subtypes

Unique Identifiers

Note

Chapter 6: LDM Pitfalls

Circular Relationships

Data Values

Data Value–Differentiated Entities and Attributes

Derived Data

Three Poor Arguments Against Modeling Derived Data

Derived Data as Process

Derived Data and Physical Database Design

Discrete Attributes

Embedded Attributes

Uniqueness

Group Attributes

The Problem with Embedded Attributes

The Solution

The Moral of the Story

Entity Fragmentation

Foreign Keys

Junction Entities

Normalization

Presentation Data

Primary Keys

Process Data

Repeating Groups

Multivalue Attribute

Group Attribute

Single-Attribute Entities

Code

Multivalue Attribute (Repeating Group)

Associative Entities

Substitution Data

Substitution Tables

Transient Data

Location-Dependent Data

Chapter 7: LDM Perils to Watch For

Associatives Related to Other Associatives

Diagrammable Objects

Disassociated Entity Clusters (“Islands”)

Duplicate Unique Identifiers

One Entity, Two or More Identifiers

One Identifier, Two or More Entities

Multiple Relationships

One Relationship, Multiple Views

Multiple Different but Similar Relationships

One-of-a-Kind (OOAK) Entities

One-to-One Relationships

Rare Entity Relationships

Recursive Modality Constraints

Updating the Constraints

Spiderwebs

Too Many Blanks or Nulls

Too Many Recursives

Next U3D Phase: Physical Schema Definition

Notes

Part III: Physical Schema Definition

Chapter 8: Introduction to Physical Database Design

A Short Incondite History of Automated Information Management (or, a Sequential Look at Random Acces

Information Management Era 1: Sequential Processing

Information Management Era 2: The First Random Access DBMS

A Small Digression: A Couple of Words About Database Access

Hashing

Inverted Indices

Database Pages

B-Trees

Bitmaps

Associative Arrays

Information Management Era 3: Inverted File Systems

Information Management Era 4: The Age of Relational

Problems with Relational

First—Performance Issues

Second—Not So Simple Simplicity

Data Types

Procedural Code

Groups

Third—Communication and Language

Fourth—Relational: Theory or DBMS?

Fifth—Where Are You Relational Model?

Just Because It Has Failings Doesn’t Mean It’s a Failure

Information Management Era 5: Object Technology

Object-Oriented Programming Led to Object-Oriented Analysis and Design, Which Eventually Led to the

A Small Digression (Again): The ACID Test

Information Management Era 6: NoSQL

Key-Value

Graph

Document Management

Multimodal

Is That an ACID or a BASE?

And the Winner Is…

What’s to Come

References

Notes

Chapter 9: Introduction to Physical Schema Definition

Usage-Driven Database Design: Physical Schema Definition

Step 1: Transformation

Task 1.1: Translation

Task 1.2: Expansion

Step 2: Utilization

Task 2.1: Usage Analysis

Task 2.2: Path Rationalization

Step 3: Formalization

Task 3.1: Environment Designation

Task 3.2: Constraint Compliance

Step 4: Customization

Task 4.1: Resource Analysis

Task 4.2: Performance Enhancement

Summary

Chapter 10: Transformation: Creating the Physical Data Model

Task 1.1: Translation

Activity 1.1.1: Transform LDM Objects to PDM Objects

Entities to Record Types

Relationships to Linkages

Linkage Membership Class

Linkage Degree

Linkage Constraints

Attributes to Data Items

Data Item Domain

Data Item Source: Primitive and Derived

Primitive Data Item: Unique Identifiers and Descriptors

Data Item Complexity: Simple and Group

Data Item Valuation: Single Value and Multivalue

Other Data Item Information

Activity 1.1.2: Diagram the Objects

Task 1.2: Expansion

Activity 1.2.1: Assign Keys

Activity 1.2.2: Normalize the Model

Adjustments Needed for Normalization: Keys—Foreign and Domestic?

Zero Normal Form

First Normal Form

Before Getting to Second Normal Form, a Slight Digression

Second Normal Form

Third Normal Form

Post-Normalization—Retreat of Sally Forth?

Issues with Normalization

Tranformation Notes

Deliverables

Examples of Deliverables

Chapter 11: Utilization: Merging Data and Process

Task 2.1: Usage Analysis

Process Modeling

Logical Process Modeling

Natural-Language Logical Process Modeling Techniques

Plain English

Oy Vey, There Has Got to Be a Better English Translation

Structured English

Graphical Logical Process Modeling Techniques

Physical Process Modeling

Natural-Language Physical Process Modeling Techniques

Plain English

Structured English

Pseudocode

Graphic Physical Process Modeling Techniques

Flow Charts

Structure Charts

Activity 2.1.1: Create Usage Scenarios

Clearing the Decks for Action

Putting a Usage Scenario Together

An Example

Activity 2.1.2: Map Usage Scenarios to the PDM

Task 2.2: Path Rationalization

Activity 2.2.1: Reduce to Simplest Paths

Activity 2.2.2: Simplify Model

Utilization Notes

Deliverables

Example of Deliverables

Further Reading

Structured English

Data Flow Diagramming

Flow Charts

Pseudocode

Structure Charts

Chapter 12: Formalization: Creating a Schema

Task 3.1: Environment Designation

Hierarchical Systems

Network Systems

Relational Systems

Object-Oriented

NoSQL

DBMS Product and Version Selection

Task 3.2: Constraint Compliance

Pseudocode…Again

Activity 3.2.1: Map Rationalized Physical Data Model to the Data Architecture

Record Types

Proper

Associative

Attributive

S-Type

Links

Membership Class: Cardinality

One-to-One

One-to-Many

Many-to-Many

Membership Class: Modality

Mandatory

Optional

Degree

Unary

Binary

N-ary

Constraints

Inclusion

Exclusion

Conjunction

Data Items

Domains

Source: Primitive and Derived

Primitive Data Items

Derived Data Items

Complexity: Simple and Group

Valuation: Single Value and Multivalue

Activity 3.2.2: Create a DBMS Product/Version-Specific Functional Physical Database Design

Subschema Creation

Formalization Notes

Deliverables

Example of Deliverables

Chapter 13: Customization: Enhancing Performance

Task 4.1: Resource Analysis

The Trade-Off Triangle

Task 4.2: Performance Enhancements

Activity 4.2.1: Customize Hardware

A Few Words About Secondary Storage

Add Disk

Faster Disk

Main Memory

Activity 4.2.2: Customize Software

Indices (B-Tree, Hash, Bitmap)

Clustering

Example Using Indices and Clusters

Question 1: Should Order Be Indexed?

Question 2: Should Product Be Indexed?

Question 3: Should Line Item Be Indexed?

Question 4: Should Line Item Be Clustered?

Question 5: Should Line Item Be Clustered Around Order or Product?

Alternative 1: Line Item Clustered Around Order

Alternative Two: Line Item Clustered Around Product

Partitioning

Derived and Duplicate Data

Denormalization

Get Rid of ACID

Big Data, Big Problems, Big Solution

To Plunge or Not to Plunge

NoSQL

Modeling Big Data U3D Style

Customization Notes

Deliverables

Examples of Deliverables

Chapter 14: The Data Warehouse

The Data Warehouse

Data Warehouse Architecture

Using U3D to Develop a Data Warehouse

Step 1: Transformation

Step 2: Utilization

The Time Dimension

Step 3: Formalization

Step 4: Customization

Streamlining

Duplication

Denormalization

Indices and Hashing

Bitmaps

Bulk Loading

Clustering

Partitioning

Distributed Processing

All Together Now…

Oops…

Customization Notes

Note

Chapter 15: The Big Data Decision Support System

Structured, Unstructured, and Semistructured Data—Another Small Digression

DSS and Big Data

Using U3D to Develop a Big Data Decision Support System

Step 1: Transformation

Step 2: Utililization

Step 3: Formalization

Step 4: Customization

A Little About Hadoop

Putting It All Together

Deliverables

Part IV: Where from Here?

Chapter 16: A Look Ahead

We Need to Ask the Awkward Questions

Tools Need to Take Usage into Account

The One and Only DBMS

Better Training

Notes

Part V: Appendixes

Appendix A: Glossary

Appendix B: Logical Data Modeling Definitions

Entity

Relationship

Attribute

Domain

Appendix C: Physical Schema Definition Object Definitions

Record

Linkage

Data Item

Domain

Cluster

Partition