Database Design for Mere Mortals®: 25th Anniversary Edition

The #1 Easy, Commonsense Guide to Database Design―Now Update d Foreword by Michelle Poolet, Mount Vernon Data Systems LLC Database Design for Mere Mortals has earned worldwide respect as the simplest way to learn relational database design. Now, this hands-on, software independent tutorial is even clearer and easier to use. Step by step, this new 25th Anniversary Edition shows you how to design modern databases that are soundly structured, reliable, and flexible, even in the latest online applications. Michael Hernandez guides you through everything from planning to defining tables, fields, keys, table relationships, business rules, and views. You will learn practical ways to improve data integrity, how to avoid common mistakes, and when to break the rules. Updated review questions and figures help you learn these techniques more easily and effectively. Understand database types, models, and design terminology Perform interviews to efficiently capture requirements―even if everyone works remotely Set clear design objectives and transform them into effective designs Analyze a current database so you can identify ways to improve it Establish table structures and relationships, assign primary keys, set field specifications, and set up views Ensure the correct level of data integrity for each database Identify and establish business rules Preview and prepare for the future of relational databases Whatever relational database systems you use, Hernandez will help you design databases that are robust and trustworthy. Never designed a database before? Settling for inadequate generic designs? Running existing databases that need improvement? Start here. Cover Page 2 About This eBook 3 Title Page 5 Copyright Page 6 Dedication Page 7 Contents at a Glance 8 Contents 10 Figure Credits 17 Foreword 18 Preface 19 Register Your Book 20 Acknowledgments 21 About the Author 24 Introduction 26 What’s New in the Fourth Edition 27 Who Should Read This Book 28 The Purpose of This Book 28 How to Read This Book 30 How This Book Is Organized 30 A Word about the Examples and Techniques in This Book 32 Part I: Relational Database Design 36 1. The Relational Database 37 Topics Covered in This Chapter 37 What Is a Database? 37 The Relational Database 38 What’s Next? 43 Summary 44 Review Questions 44 2. Design Objectives 46 Topics Covered in This Chapter 46 Why Should You Be Concerned with Database Design? 46 The Importance of Theory 47 The Advantage of Learning a Good Design Methodology 48 Objectives of Good Design 49 Benefits of Good Design 50 Database-Design Methods 50 Normalization 52 Summary 54 Review Questions 54 3. Terminology 56 Topics Covered in This Chapter 56 Why This Terminology Is Important 56 Value-Related Terms 57 Structure-Related Terms 62 Relationship-Related Terms 69 Integrity-Related Terms 76 Summary 77 Review Questions 77 Part II: The Design Process 80 4. Conceptual Overview 81 Topics Covered in This Chapter 81 The Importance of Completing the Design Process 82 Defining a Mission Statement and Mission Objectives 82 Analyzing the Current Database 83 Creating the Data Structures 84 Determining and Establishing Table Relationships 84 Determining and Defining Business Rules 85 Determining and Defining Views 86 Reviewing Data Integrity 86 Summary 87 Review Questions 88 5. Starting the Process 89 Topics Covered in This Chapter 89 Conducting Interviews 90 Defining the Mission Statement 94 Defining the Mission Objectives 98 Summary 102 Review Questions 103 6. Analyzing the Current Database 105 Topics Covered in This Chapter 105 Getting to Know the Current Database 105 Conducting the Analysis 108 Looking at How Data Is Collected 109 Looking at How Information Is Presented 111 Conducting Interviews 114 Interviewing Users 119 Interviewing Management 130 Compiling a Complete List of Fields 133 Summary 144 Review Questions 144 7. Establishing Table Structures 146 Topics Covered in This Chapter 146 Defining the Preliminary Table List 146 Defining the Final Table List 153 Associating Fields with Each Table 162 Refining the Fields 164 Refining the Table Structures 177 Summary 193 Review Questions 194 8. Keys 196 Topics Covered in This Chapter 196 Why Keys Are Important 196 Establishing Keys for Each Table 197 Table-Level Integrity 208 Reviewing the Initial Table Structures 209 Summary 215 Review Questions 215 9. Field Specifications 217 Topics Covered in This Chapter 217 Why Field Specifications Are Important 218 Field-Level Integrity 218 Anatomy of a Field Specification 219 Using Unique, Generic, and Replica Field Specifications 230 Defining Field Specifications for Each Field in the Database 234 Summary 237 Review Questions 238 10. Table Relationships 239 Topics Covered in This Chapter 239 Why Relationships Are Important 240 Types of Relationships 240 Identifying Existing Relationships 256 Establishing Each Relationship 264 Refining All Foreign Keys 279 Establishing Relationship Characteristics 284 Relationship-Level Integrity 292 Summary 296 Review Questions 297 11. Business Rules 299 Topics Covered in This Chapter 299 What Are Business Rules? 299 Categories of Business Rules 303 Defining and Establishing Business Rules 305 Validation Tables 317 Reviewing the Business Rule Specifications Sheets 321 Summary 329 Review Questions 329 12. Views 331 Topics Covered in This Chapter 331 What Are Views? 331 Anatomy of a View 332 Determining and Defining Views 342 Summary 356 Review Questions 356 13. Reviewing Data Integrity 358 Topics Covered in This Chapter 358 Why You Should Review Data Integrity 359 Reviewing and Refining Data Integrity 359 Assembling the Database Documentation 361 Done at Last! 362 Summary 363 Part III: Other Database Design Issues 365 14. Bad Design—What Not to Do 366 Topics Covered in This Chapter 366 “Flat-File” Design 366 Spreadsheet Design 368 Database Design Based on the Database Software 370 A Final Thought 371 Summary 372 15. Bending or Breaking the Rules 373 Topics Covered in This Chapter 373 When May You Bend or Break the Rules? 373 Documenting Your Actions 376 Summary 376 16. In Closing 378 Part IV: Appendixes 381 A: Answers to Review Questions 382 B: Diagram of the Database Design Process 396 C: Design Guidelines 413 D: Documentation Forms 420 E: Database-Design Diagram Symbols 423 F: Sample Designs 425 G: On Normalization 431 H: Recommended Reading 438 Glossary 440 References 449 Index 450 Michael J. Hernandez's best-selling Database Design for Mere Mortals has earned worldwide respect as the simplest way to learn relational database design. Now, he's made this hands-on, software independent tutorial even clearer and easier to use. Step by step, this new 25th Anniversary Edition shows you how to design modern databases that are soundly structured, reliable, and flexible, even in the latest online applications. Hernandez guides you through everything from planning to defining tables, fields, keys, table relationships, business rules, and views. You'll learn practical ways to improve data integrity, how to avoid common mistakes, and when to break the rules. Updated review questions and figures help you learn these techniques more easily and effectively. Understand database types, models, and design terminology Perform interviews to efficiently capture requirements--even if everyone's working remotely Set clear design objectives and transform them into effective designs Analyze a current database so you can identify ways to improve it Establish table structures and relationships, assign primary keys, set field specifications, and set up views Ensure the correct level of data integrity for each database Identify and establish business rules Preview and prepare for the future of relational databases. Whatever relational database systems you use, Hernandez will help you design databases that are robust and trustworthy. Never designed a database before? Settling for inadequate generic designs? Running existing databases that need improvement? Start here Step by step, this new 25th anniversary edition shows you h ow to design modern databases that are soundly structured, reliable, and flexible, even in the latest online applications