| diaxon.com |
 |
|
|
|
||
| ||||||
|
|
Practical Guide to Relational Database DesignTABLE OF CONTENTS
A Brief History of Database Systems 2 Bespoke Systems: Why the Need? 3 2 RDBMS-The Concept 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 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) 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 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 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 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 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) 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 145 Cons 145 What will CASE do for your Project? 147 11 Database Security 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 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 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 |
|
