diaxon.com  

Home Up

 

 

 

 

 

 

 

 

Practical Guide to Relational Database Design

TABLE OF CONTENTS

Preface 7


1 Introduction 1
The Importance of Good Design 1
A Brief History of Database Systems 2
Bespoke Systems: Why the Need? 3

2 RDBMS-The Concept 5
The Fundamental Components of an Integrated Database 5
  The Environment 6
  The Database Engine 6
  Accessing and Manipulating Data 6
  Queries, Reports and other Output 7
  Screen Access for Data Entry, Maintenance and Query 8
Menus 9
Housekeeping and other Utilities 9
Introducing the RDBMS Method 10
  The Main Components of a Relational Database are: 10
  The Primary Key and the Uniqueness of a Row 11
  Candidate Keys 12
  Relationships Formed Between Tables 12
  The Foreign Key (FK) 12
  Using SQL, Joining Tables 13
The Graphical Approach to Queries 15

3 The Enterprise Model 17
The Holistic View 17
The Scope of Enterprise Models 17
Overview of the Components 18
The Case for using the Enterprise Approach 19
Modelling Functions and Processes 19
Introduction 19
  Functions 20
  Processes 21
  Elemental Processes 21
Building Functional Decomposition Diagram (FDD) 22
  Identifying Logical Functions and Processes 22
Data Flow Modelling and the Data Flow Diagram (DFD) 23
Introduction 23
  Definitions 24
  Use of DFDs: Strengths and weaknesses 25

4 Data Modelling 26 (*Free* in the .pdf download)
Introduction to Data Modelling 26
The Logical Model: Entities, Attributes, Relationships & Keys. 26
  Logical vs. Physical Models 27
  Producing an Entity Relationship Diagram (ERD) 27
  Definitions 28
Modelling Entities and Relationships 30
  Identifying Entities 30
  A Simple Case Study for Entity Modelling 31
More Information about Entities 33
  Entity Naming & Conventions 33
  What do we Need to Know About an Entity? 33
Attributes 34
  Attribute Naming and Conventions 34
  What Information do we Need to Know About an Attribute? 34
  Attribute Format Types 34
  Ranges and Validation 35
  Look-up Lists 35
  Optionality 35
Guide-lines for Attributes to Include in Entities 36
  What Not to Include as Attributes 36
Domains 38
  Why use Domains? 39
The Principles of Normalisation 40
Unique Identifiers (UIDs). The Role of the Primary Key 40
  Choosing a Primary Key (PK) 41
  Types of Primary Key 41
  Another example of an Artificial Primary Key 42
  Representing Primary Keys on the ERD 42
Further Advice on Creating a Primary Key 43
  Selecting a Primary Key - A Decision Chart to help you select one 43
  Candidate or Alternative Keys (CK) 44
The Foreign Key (FK) 44
Relationships: Definitions 45
  A relationship line carries with it three essential pieces of information: 46
  Reading Relationship Lines 46
Relationships and Business Rules 47
Relationship Modelling 48
More about Recursive Relationships ('Pig's Ears') 49
More Complex Relationships 51
  Hierarchies Super-types and Sub-types 51
  Modelling Super-types and Sub-types 51
  'Complex' Relationships: Arcs 52
  Rules for Exclusive Arcs 53
Some Common Modelling Solutions 54
Creating a Data Dictionary (DD) 56

5 Project Planning : Appreciating Life Cycle Methods 58
Project Planning 58
What is a Life Cycle Methodology? 59
Why use a methodology? 60
An Imaginary Project Plan for XYZ Engineering 63
  Horses for Courses 65
The Phases of Design 66
  Strategic Business Analysis 66
  The Management Life Cycle 66
Detailed Analysis 68
  Design 69
  What are the main prerequisites for the design process? 69
Avoid Modelling the exceptions 70
Build Phase 71
Implementation Phase 71
Production Phase 72
Defining the Deliverables or Products 73
Strategy 73
Detailed Analysis 74
Information Gathering 76
Partnership with the Customer 77

6 Advanced Entity Modelling 80
Intersection Entities and Time 80
Recursive (Pig's Ear) Relationships 81
BOM Structure example: 82
Retaining Historical Data 82
Laying an Audit Trail 83
Grouping Entities 84
Convergent vs. Divergent Data Model 85
  Simplifying for Performance 85
  Simplifying for Flexibility 86
  Simplifying for Future Changes 87
  Designing Out Arcs 88
Advanced Sub-Types 89
  Another Example of Designing out Arcs 90
  But…. Beware of Over-simplification 91
Some Common Pitfalls: Logic Traps (Fan, Chasm) 91
  Fan Trap 91
  Chasm Trap 91
  Advanced Connection Traps 92
Denormalisation 93
  Relationship Denormalisation 94
  Denormalising on Low Attribute 94
  Denormalising on Low Volume 94
  Denormalising on Low Functionality 95
Fringe Tables 95
Converting Legacy Structures 96
  Representing Multi-Dimension Data 96
  Field Overloading 96
  Concatenated Attributes 97
  Overlapping Data 97
  Using Free Text to Handle Exceptions 97
Complicated Splits 98
Design Dilemmas 99
  Single Table vs Multiple Tables 99
  Sub-Types of Sub-Types 99
  Arc vs. Sub-types 99
  Generic vs Specific Entities 100
  Separation vs Combination of Column/attribute components 101
  Delete vs Archive 101
  Null vs Not Null columns 102
  Same Entity with Different Names 102

7 Getting Physical 104
Practical Considerations 104
Prerequisites 104
Tables and Columns 105
  Conventions for Tables 105
  Conventions for Columns 105
Differences Between ERD and Physical Models 105
  Why should logical and physical models diverge? 107
1:1 Entity : Table Mapping 107
Dealing with Sub-Types 107
  1) Single Table Solution 108
  2) Multiple Table Solution 108
  3) Exclusive Arc Solution 109
Dealing with Exclusive Arcs 109
Further Reasons for Physical Mapping to Diverge from ERD 111
  Adding Intersection Tables 111
  Splitting a Table by Attributes 112
  Splitting Tables Vertically 112
  Splitting Tables in Both Directions 112
Dangers of Diverging and Denormalising in Client/Server and Distributed Environments 113
Striving Towards the Data-Driven Approach. 113
A simple case 113
Designing 'Codes' or 'Reference' Tables 114
Views and their Place in the Database Designer's Armoury 116
Snapshots 117
Creating Indexes 117
  Indexes and Database keys 117
  Where should Indexes be Applied? 118
  How are indexes implemented? 118
  Composite Indexes 119
  Do Indexes have a Downside? 119
  Do's and Don'ts 120
Avoiding Creeping Deterioration in Performance 121
Design Considerations for Legacy Systems 121
  Incompatible Codes 122
  Structured Codes 122
  Data Structures Not Supported in the New System 123
Functions to Modules 123
Using Proprietary RDBMS Features 124
  Constraints 124
  Stored Procedures and Triggers 125
Checklist of Other Design Considerations 126

8 OLAP Databases and Data Warehouses 127
Differences between OLTP and OLAP Databases 127
OLAP Tools 128
Example of an OLAP style transformation 129
What is a Data Warehouse and why build one? 130
DW Terminology 130
  What is a DataMart? 130
  Warehouse-Specific Design Techniques 130
Star Schemas 131
Snowflakes and Storage Granularity 131
Drill-Down and Roll-up: Retrieval Granularity 132
Dicing and Slicing 132
Data Mining and Serendipity 132
Data Sources and Data Quality, Data Quality, Data Quality 133
Populating a Warehouse 134
Some Guidance for Selecting an OLAP Solution 135
Recent Developments 137
Conclusions 137

9 People and Organisations 139 ( *Free* - view now)
Why is 'Customer' so vexing? 139
What is a Customer? 139
About Individuals 140
About Addresses 140
About Organisations 140
The Data Cloud Concept 141
Customer Reference vs. Customer Accounts 143
CRM Products 144

10 Using a CASE Tool 145
Pros and Cons 145
Pros 145
Cons 145
What will CASE do for your Project? 147

11 Database Security 149
Treating Data as Assets 149
Minimising the Risks 150
  Reducing the Risks Due to Human Error 150
  Avoiding Loss of Data Integrity 151
  Avoiding Destruction of Data 152
  Use of audit trails 152
  Misuse of the database 153

12 Quality And Completeness Checking 155
Means of Checking for Accuracy and Completeness 155
  Cross Referencing Entities with Functions 155
  Other Object Cross Referencing 156
  The Walkthrough (Peer Review) 156
The Use of Prototyping 156
Reviewing and Refining the Physical Model 157
Will the Physical Model Support the Enterprise Model? 157
Are We Delivering a Well Designed Database? 158
Database Design CheckList 159

13 Business Case Study 162
A Training System for XYZ Engineering Company 162
Background Information 162
Outline of the Analysis Phase to be Used For the Project 162
Summary of Strategic Interviews 163
The Business Model 164
  High Level FDD 164
  DFD (Data Flow Diagram) 166
  KPI (Key Performance Indicators) 167
  First Cut ERD #1 167
Phased Plan 170
Summary of the Detailed Analysis 170
Further Improvements to Our First Cut Model 171
Second Cut Model #2 171
Further Improvements to our Second Cut model 171
Our generic approach begins to pay dividends 174
Entities and Attributes 175
Getting Physical with Training! 178
Application Design 179
Deriving the Perspectives 179

GLOSSARY OF TERMS 181
Index 184

© Diaxon Ltd. 2000-2009 Last updated: 21/11/2009