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.
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.
By:
Rod Stephens
Imprint: Wrox Press
Country of Publication: United States
Dimensions:
Height: 231mm,
Width: 188mm,
Spine: 33mm
Weight: 726g
ISBN: 9780470385494
ISBN 10: 0470385499
Pages: 560
Publication Date: 24 October 2008
Audience:
Professional and scholarly
,
Undergraduate
Replaced By: 9781394155729
Format: Paperback
Publisher's Status: Active
Introduction xxiii Part I: Introduction to Databases and Database Design Chapter 1: Goals of Effective Database Design 3 Understanding the Importance of Design 4 Information Containers 5 Strengths and Weaknesses of Information Containers 7 Desirable Database Features 8 CRUD 9 Retrieval 9 Consistency 10 Validity 10 Easy Error Correction 11 Speed 11 Atomic Transactions 12 ACID 13 Persistence and Backups 14 Low Cost and Extensibility 16 Ease of Use 16 Portability 16 Security 17 Sharing 18 Ability to Perform Complex Calculations 19 Consequences of Good and Bad Design 19 Summary 21 Exercises 22 Chapter 2: Database Types 23 Why Bother? 24 Flat Files 24 INI Files 26 Windows System Registry 26 Relational Databases 27 Spreadsheets 30 Hierarchical Databases 31 XML 34 XML Basics 34 XML Structures 37 XML Summary 39 Network 40 Object 42 Object-Relational 43 Exotic 44 Document-Oriented 44 Deductive 44 Dimensional 44 Temporal 45 Summary 45 Exercises 47 Chapter 3: Relational Database Fundamentals 49 Relational Points of View 49 Table, Rows, and Columns 50 Relations, Attributes, and Tuples 52 Keys 52 Indexes 54 Constraints 55 Basic Constraints 55 Check Constraints 55 Primary Key Constraints 55 Unique Constraints 56 Foreign Key Constraints 56 Database Operations 57 Summary 59 Exercises 59 Part II: Database Design Process and Techniques Chapter 4: Understanding User Needs 65 Make a Plan 66 Bring a List of Questions 67 Functionality 67 Data Needs 67 Data Integrity 68 Security 68 Environment 69 Meet the Customers 69 Learn Who’s Who 70 Pick the Customers’ Brains 73 Walk a Mile in the User’s Shoes 73 Study Current Operations 74 Brainstorm 75 Look to the Future 76 Understand the Customers’ Reasoning 76 Learn What the Customers Really Need 77 Prioritize 78 Verify Your Understanding 79 Write the Requirements Document 80 Make Use Cases 81 Decide Feasibility 85 Summary 85 Exercises 86 Chapter 5: Translating User Needs into Data Models 89 What Are Data Models? 90 User Interface Models 92 Semantic Object Models 96 Classes and Objects 96 Cardinality 97 Identifiers 97 Putting It Together 97 Semantic Views 99 Class Types 100 Comments and Notes 106 Entity-Relationship Models 106 Entities, Attributes, and Identifiers 107 Relationships 107 Cardinality 108 Inheritance 109 Additional Conventions 112 Comments and Notes 113 Relational Models 113 Converting Semantic Object Models 113 Converting ER Diagrams 116 Summary 117 Exercises 118 Chapter 6: Extracting Business Rules 121 What Are Business Rules? 121 Identifying Key Business Rules 123 Extracting Key Business Rules 128 Multi-Tier Applications 129 Summary 133 Exercises 134 Chapter 7: Normalizing Data 137 What Is Normalization? 137 First Normal Form (1NF) 138 Second Normal Form (2NF) 146 Third Normal Form (3NF) 150 Stopping at Third Normal Form 154 Boyce-Codd Normal Form (BCNF) 154 Fourth Normal Form (4NF) 158 Fifth Normal Form (5NF) 162 Domain/Key Normal Form (DKNF) 165 Essential Redundancy 167 The Best Level of Normalization 168 Summary 169 Exercises 170 Chapter 8: Designing Databases to Support Software Applications 173 Plan Ahead 173 Document Everything 174 Consider Multi-Tier Architecture 175 Convert Domains into Tables 175 Keep Tables Focused 176 Use Three Kinds of Tables 176 Use Naming Conventions 178 Allow Some Redundant Data 179 Don’t Squeeze in Everything 180 Summary 181 Exercises 182 Chapter 9: Common Design Patterns 185 Associations 185 Many-to-Many Associations 186 Multiple Many-to-Many Associations 186 Multiple-Object Associations 188 Repeated Attribute Associations 191 Reflexive Associations 193 Temporal Data 200 Effective Dates 200 Deleted Objects 202 Deciding What to Temporalize 203 Logging and Locking 203 Audit Trails 203 Turnkey Records 204 Summary 205 Exercises 205 Chapter 10: Common Design Pitfalls 207 Lack of Preparation 207 Poor Documentation 208 Poor Naming Standards 208 Thinking Too Small 210 Not Planning for Change 210 Too Much Normalization 213 Insufficient Normalization 213 Insufficient Testing 214 Performance Anxiety 214 Mishmash Tables 215 Not Enforcing Constraints 217 Obsession with IDs 218 Not Defining Natural Keys 220 Summary 221 Exercises 222 Part III: A Detailed Case Study Chapter 11: User Needs and Requirements 227 Meet the Customers 227 Pick the Customers’ Brains 229 Determining What the System Should Do 229 Determining How the Project Should Look 231 Determining What Data Is Needed for the User Interface 232 Determining Where the Data Should Come From 233 Determining How the Pieces of Data Are Related 233 Determining Performance Needs 235 Determining Security Needs 236 Determining Data Integrity Needs 236 Write Use Cases 238 Write the Requirements Document 242 Demand Feedback 243 Summary 244 Exercises 244 Chapter 12: Building a Data Model 245 Semantic Object Modeling 245 Building an Initial Semantic Object Model 245 Improving the Semantic Object Model 248 Entity-Relationship Modeling 250 Building an ER Diagram 250 Building a Combined ER Diagram 252 Improving the Entity-Relationship Diagram 254 Relational Modeling 256 Putting It All Together 259 Summary 260 Exercises 261 Chapter 13: Extracting Business Rules 263 Identifying Business Rules 263 Courses 264 CustomerCourses 266 Customers 267 Pets 267 Employees 267 Orders 267 OrderItems 268 InventoryItems 268 TimeEntries 268 Shifts 269 Persons 269 Phones 269 Vendors 269 Drawing a New Relational Model 270 Summary 270 Exercises 271 Chapter 14: Normalization and Refinement 273 Improving Flexibility 273 Verifying First Normal Form 275 Verifying Second Normal Form 278 Pets 279 TimeEntries 280 Verifying Third Normal Form 281 Summary 283 Exercises 284 Part IV: Implementing Databases (with Examples in Access and MySQL) Chapter 15: Microsoft Access 287 Understanding Access 287 Getting Started 288 Defining Relationships 293 Creating Field Constraints 296 Creating Table Constraints 298 Creating Queries 301 Query Design View 304 SQL View 306 Summary 308 Exercises 308 Chapter 16: MySQL 313 Installing MySQL 313 Using MySQL Command Line Client 314 Executing SQL Scripts 318 Using MySQL Query Browser 319 Executing Queries 320 Editing Data 320 Creating and Modifying Databases 321 Using Scripts 327 Getting Syntax Help 329 Using MySQL Workbench 330 Loading Scripts 330 Creating EER Diagrams 331 Editing Databases 333 Defining Triggers 334 Exporting Scripts 336 Summary 338 Exercises 338 Part V: Advanced Topics Chapter 17: Introduction to SQL 345 Background 345 Finding More Information 346 Standards 346 Basic Syntax 347 Command Overview 348 Create Table 350 Create Index 355 Drop 356 Insert 357 Select 358 Select Clause 358 From Clause 359 Where Clause 362 Group By Clause 362 Order By Clause 363 Update 364 Delete 365 Summary 366 Exercises 366 Chapter 18: Building Databases with SQL Scripts 369 Why Bother with Scripts? 369 Script Categories 370 Database Creation Scripts 370 Basic Initialization Scripts 370 Data Initialization Scripts 370 Cleanup Scripts 371 Saving Scripts 371 Ordering SQL Commands 371 Summary 376 Exercises 377 Chapter 19: Database Maintenance 379 Backups 379 Data Warehousing 382 Repairing the Database 383 Compacting the Database 383 Performance Tuning 383 Summary 386 Exercises 387 Chapter 20: Database Security 389 The Right Level of Security 389 Passwords 390 Single-Password Databases 390 Individual Passwords 390 Operating System Passwords 391 Good Passwords 391 Privileges 392 Initial Configuration and Privileges 398 Too Much Security 398 Physical Security 399 Summary 400 Exercises 401 Appendix A: Exercise Solutions 403 Chapter 1 403 Chapter 2 408 Chapter 3 411 Chapter 4 413 Chapter 5 417 Chapter 6 424 Chapter 7 430 Chapter 8 434 Chapter 9 436 Chapter 10 440 Chapter 11 442 Chapter 12 446 Chapter 13 449 Chapter 14 451 Chapter 15 454 Chapter 16 454 Chapter 17 454 Chapter 18 457 Chapter 19 459 Chapter 20 460 Appendix B: Sample Database Designs 467 Books 468 DVD and Movies 470 Music and CDs 471 Documents 472 Customer Orders 472 Employee Shifts and Timesheets 473 Employees, Projects, and Departments 473 Employee Skills and Qualifications 475 Identical Object Rental 476 Distinct Object Rental 476 Students, Courses, and Grades 478 Teams 479 Individual Sports 480 Vehicle Fleets 480 Contacts 483 Passengers 483 Recipes 485 Glossary 487 Index 497
Rod Stephens is a professional software developer who has built a wide variety of software and database applications in his 20-year career. He is the author of 18 books and more than 250 articles, and is a regular contributor to DevX.com (www.devx.com). Rod's clear, compelling writing makes it easy for less experienced readers to master new material. His current position as an adjunct instructor for ITT Technical institute has given him additional insight into the kinds of discussion, examples, and exercises that students find most helpful.
