Beginning Database Design Solutions (Wrox Programmer to Programmer)

The vast majority of software applications use relational databases that virtually every application developer must work with. This book introduces you to database design, whether you're a DBA or database developer. You'll discover what databases are, their goals, and why proper design is necessary to achieve those goals. Additionally, you'll master how to structure the database so it gives good performance while minimizing the chance for error. You will learn how to decide what should be in a database to meet the application's requirements. Beginning Database Design Solutions -1 Cover 1 About the Author 9 Credits 11 Acknowledgments 13 Contents 15 Introduction 25 Who This Book Is For 26 What This Book Covers 26 What You Need to Use This Book 26 How This Book Is Structured 27 How to Use This Book 30 Note to Instructors 31 Note to Students 32 Conventions 33 Source Code 34 The Book’s Web Site 34 Errata 35 Contacting the Author 36 Disclaimer 36 Part I: Introduction to Databases and Database Design 39 Chapter 1: Goals of Effective Database Design 41 Understanding the Importance of Design 42 Information Containers 43 Strengths and Weaknesses of Information Containers 45 Desirable Database Features 46 Summary 59 Exercises 60 Chapter 2: Database Types 61 Why Bother? 62 Flat Files 62 Relational Databases 65 Spreadsheets 68 Hierarchical Databases 69 XML 72 Network 78 Object 80 Object-Relational 81 Exotic 82 Summary 83 Exercises 85 Chapter 3: Relational Database Fundamentals 87 Relational Points of View 87 Table, Rows, and Columns 88 Relations, Attributes, and Tuples 90 Keys 90 Indexes 92 Constraints 93 Database Operations 95 Summary 97 Exercises 97 Part II: Database Design Process and Techniques 101 Chapter 4: Understanding User Needs 103 Make a Plan 104 Bring a List of Questions 105 Meet the Customers 107 Learn Who’s Who 108 Pick the Customers’ Brains 111 Walk a Mile in the User’s Shoes 111 Study Current Operations 112 Brainstorm 113 Look to the Future 114 Understand the Customers’ Reasoning 114 Learn What the Customers Really Need 115 Prioritize 116 Verify Your Understanding 117 Write the Requirements Document 118 Make Use Cases 119 Decide Feasibility 123 Summary 123 Exercises 124 Chapter 5: Translating User Needs into Data Models 127 What Are Data Models? 128 User Interface Models 130 Semantic Object Models 134 Entity-Relationship Models 144 Relational Models 151 Summary 155 Exercises 156 Chapter 6: Extracting Business Rules 159 What Are Business Rules? 159 Identifying Key Business Rules 161 Extracting Key Business Rules 166 Multi-Tier Applications 167 Summary 171 Exercises 172 Chapter 7: Normalizing Data 175 What Is Normalization? 175 First Normal Form (1NF) 176 Second Normal Form (2NF) 184 Third Normal Form (3NF) 188 Stopping at Third Normal Form 192 Boyce-Codd Normal Form (BCNF) 192 Fourth Normal Form (4NF) 196 Fifth Normal Form (5NF) 200 Domain/Key Normal Form (DKNF) 203 Essential Redundancy 205 The Best Level of Normalization 206 Summary 207 Exercises 208 Chapter 8: Designing Databases to Support Software Applications 211 Plan Ahead 211 Document Everything 212 Consider Multi-Tier Architecture 213 Convert Domains into Tables 213 Keep Tables Focused 214 Use Three Kinds of Tables 214 Use Naming Conventions 216 Allow Some Redundant Data 217 Don’t Squeeze in Everything 218 Summary 219 Exercises 220 Chapter 9: Common Design Patterns 223 Associations 223 Temporal Data 238 Logging and Locking 241 Summary 243 Exercises 243 Chapter 10: Common Design Pitfalls 245 Lack of Preparation 245 Poor Documentation 246 Poor Naming Standards 246 Thinking Too Small 248 Not Planning for Change 248 Too Much Normalization 251 Insufficient Normalization 251 Insufficient Testing 252 Performance Anxiety 252 Mishmash Tables 253 Not Enforcing Constraints 255 Obsession with IDs 256 Not Defining Natural Keys 258 Summary 259 Exercises 260 Part III: A Detailed Case Study 263 Chapter 11: User Needs and Requirements 265 Meet the Customers 265 Pick the Customers’ Brains 267 Write Use Cases 276 Write the Requirements Document 280 Demand Feedback 281 Summary 282 Exercises 282 Chapter 12: Building a Data Model 283 Semantic Object Modeling 283 Entity-Relationship Modeling 288 Relational Modeling 294 Putting It All Together 297 Summary 298 Exercises 299 Chapter 13: Extracting Business Rules 301 Identifying Business Rules 301 Drawing a New Relational Model 308 Summary 308 Exercises 309 Chapter 14: Normalization and Refinement 311 Improving Flexibility 311 Verifying First Normal Form 313 Verifying Second Normal Form 316 Verifying Third Normal Form 319 Summary 321 Exercises 322 Part IV: Implementing Databases (with Examples in Access and MySQL) 323 Chapter 15: Microsoft Access 325 Understanding Access 325 Getting Started 326 Defining Relationships 331 Creating Field Constraints 334 Creating Table Constraints 336 Creating Queries 339 Summary 346 Exercises 346 Chapter 16: MySQL 351 Installing MySQL 351 Using MySQL Command Line Client 352 Executing SQL Scripts 356 Using MySQL Query Browser 357 Using MySQL Workbench 368 Summary 376 Exercises 376 Part V: Advanced Topics 381 Chapter 17: Introduction to SQL 383 Background 383 Finding More Information 384 Standards 384 Basic Syntax 385 Command Overview 386 CREATE TABLE 388 CREATE INDEX 393 DROP 394 INSERT 395 SELECT 396 UPDATE 402 DELETE 403 Summary 404 Exercises 404 Chapter 18: Building Databases with SQL Scripts 407 Why Bother with Scripts? 407 Script Categories 408 Ordering SQL Commands 409 Summary 414 Exercises 415 Chapter 19: Database Maintenance 417 Backups 417 Data Warehousing 420 Repairing the Database 421 Compacting the Database 421 Performance Tuning 421 Summary 424 Exercises 425 Chapter 20: Database Security 427 The Right Level of Security 427 Passwords 428 Privileges 430 Initial Configuration and Privileges 436 Too Much Security 436 Physical Security 437 Summary 438 Exercises 439 Appendix A: Exercise Solutions 441 Chapter 1 441 Chapter 2 446 Chapter 3 449 Chapter 4 451 Chapter 5 455 Chapter 6 462 Chapter 7 468 Chapter 8 472 Chapter 9 474 Chapter 10 478 Chapter 11 480 Chapter 12 484 Chapter 13 487 Chapter 14 489 Chapter 15 492 Chapter 16 492 Chapter 17 492 Chapter 18 495 Chapter 19 497 Chapter 20 498 Appendix B: Sample Database Designs 505 Books 506 DVD and Movies 508 Music and CDs 509 Documents 510 Customer Orders 510 Employee Shifts and Timesheets 511 Employees, Projects, and Departments 511 Employee Skills and Qualifications 513 Identical Object Rental 514 Distinct Object Rental 514 Students, Courses, and Grades 516 Teams 517 Individual Sports 518 Vehicle Fleets 518 Contacts 521 Passengers 521 Recipes 523 Glossary 525 Index 535 This book is intended for IT professionals and students who want to learn how to design, analyze, and understand databases. The material will benefit those who want a better high-level understanding of databases such as proposal managers, architects, project managers, and even customers. The material will also benefit those who will actually design, build, and work with databases such as database designers, database administrators, and programmers. In many projects, these roles overlap so the same person may be responsible for working on the proposal, managing part of the project, and designing and creating the database. This book is aimed at IT professionals and students of all experience levels. It does not assume that you have any previous experience with databases or programs that use them. It doesn't even assume that you have experience with computers. All you really need is a willingness and desire to learn. This book explains database design. It tells how to plan a database's structure so the database will be robust, resistant to errors, and flexible enough to accommodate a reasonable amount of future change. It explains how to discover database requirements, build data models to study data needs, and refine those models to improve the database's effectiveness. The book solidifies these concepts by working through a detailed example that designs a realistic database. Later chapters explain how to actually build databases using two common database products: Access 2007 and MySQL. The book finishes by describing some of the topics you need to understand to keep a database running effectively such as database maintenance and security. This book explains database design. It tells how to determine what should go in a database and how the database should be structured to give the best results. This book does not focus on actually creating the database. The details of database construction are different for different database tools so, to remain as generally as useful as possible, this book doesn't concentrate on any particular database system. You can apply the techniques described here equally to whatever database tool you use whether it's Access, SQL Server, Oracle, MySQL, or some other database product. Most database products include free editions that you can use for smaller projects. For example, SQL Server Express Edition, Oracle Express Edition, and MySQL Community Server are all free. To remain database neutral, the book does not assume you are using a particular database so you don't need any particular software or hardware. To work through the Exercises, all you really need is a pencil and some paper. You are welcome to type solutions into your computer if you like but you may actually find working with pencil and paper easier than using a graphical design tool to draw pictures, at least until you are comfortable with database design and are ready to pick a computerized design tool. "Goals of Effective Database Design," explains the reasons why people and organizations use databases. It explains a database's purpose and conditions that it must satisfy to be useful. This also describes the basic ACID (Atomicity, Consistency, Isolation, Durability) and CRUD (Create, Read, Update, Delete) features that any good database should have. It explains in high-level general terms what makes a good database and what makes a bad database. "Database Types," explains some of the different types of databases that you might decide to use. These include flat files, spreadsheets, hierarchi... "Databases play a critical role in the business operations of most organizations; they're the central repository for critical information on products, customers, suppliers, sales, and a host of other essential information. It's no wonder that the majority of all business computing involves database applications." "With so much at stake, you'd expect most IT professionals would have a firm understanding of good database design. But in fact most learn through a painful process of trial and error, with predictably poor results." "This book provides readers with proven methods and tools for designing efficient, reliable, and secure databases. Author Rod Stephens explains how a database should be organized to ensure data integrity without sacrificing performance. He shares procedures for designing robust, flexible, and secure databases that provide a solid foundation for all of your database applications. The methods and techniques in this book can be applied to any database environment, including Oracle, Microsoft Access, SQL Server, and MySQL. You'll learn the basics of good database design and ultimately discover how to design a real-world database."--Jacket Beginning Database Design Solutions introduces IT professionals---both DBAs and database developers--to database design. It explains what databases are, their goals, and why proper design is necessary to achieve those goals. It tells how to decide what should be in a database to meet the application's requirements.