Beginning Database Design- P1

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:20

0
69
lượt xem
9
download

Beginning Database Design- P1

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Beginning Database Design- P1:This book focuses on the relational database model from a beginning perspective. The title is, therefore, Beginning Database Design. A database is a repository for data. In other words, you can store lots of information in a database. A relational database is a special type of database using structures called tables. Tables are linked together using what are called relationships. You can build tables with relationships between those tables, not only to organize your data, but also to allow later retrieval of information from the database....

Chủ đề:
Lưu

Nội dung Text: Beginning Database Design- P1

  1. Beginning Database Design Gavin Powell
  2. Beginning Database Design
  3. Beginning Database Design Gavin Powell
  4. Beginning Database Design Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana Published by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN-13: 978-0-7645-7490-0 ISBN-10: 0-7645-7490-6 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 1B/RV/RR/QV/IN Library of Congress Control Number is available from the publisher. No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permis- sion of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http:// www.wiley.com/go/permissions. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HERE- FROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAP- PEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
  5. This book is dedicated to Jacqueline — my fondest pride and joy.
  6. About the Author Gavin Powell has a Bachelor of Science degree in Computer Science, with numerous professional accreditations and skills (including Microsoft Word, PowerPoint, Excel, Windows 2000, ERWin, and Paintshop, as well as Microsoft Access, Ingres, and Oracle relational databases, plus a multitude of application development languages). He has almost 20 years of contracting, consulting, and hands-on educating experience in both software development and database administration roles. He has worked with all sorts of tools and languages, on various platforms over the years. He has lived, studied, and worked on three different continents, and is now scratching out a living as a writer, musician, and family man. He can be contacted at oracledbaexpert@earthlink.net or info@oracledbaexpert.com. His Web site at http://www.oracledbaexpert.com offers information on database modeling, database software, and many development languages. Other titles by this author include Oracle Data Warehouse Tuning for 10g (Burlington, MA: Digital Press, 2005), Oracle 9i: SQL Exam Cram 2 (1Z0-007) (Indianapolis: Que, 2004), Oracle SQL: Jumpstart with Examples (Burlington, MA: Digital Press, 2004), Oracle Performance Tuning for 9i and 10g (Burlington, MA: Digital Press, 2003), ASP Scripting (Stephens City, VA: Virtual Training Company, 2005), Oracle Performance Tuning (Stephens City, VA: Virtual Training Company, 2004), Oracle Database Administration Fundamentals II (Stephens City, VA: Virtual Training Company, 2004), Oracle Database Administration Fundamentals I (Stephens City, VA: Virtual Training Company, 2003), and Introduction to Oracle 9i and Beyond: SQL & PL/SQL (Stephens City, VA: Virtual Training Company, 2003).
  7. Credits Senior Acquisitions Editor Vice President and Publisher Jim Minatel Joseph B. Wikert Development Editor Project Coordinator Kevin Shafer Michael Kruzil Technical Editor Graphics and Production Specialists David Mercer Jonelle Burns Carrie A. Foster Production Editor Denny Hager Pamela Hanley Joyce Haughey Jennifer Heleine Copy Editor Alicia B. South Susan Hobbs Quality Control Technicians Editorial Manager Laura Albert Mary Beth Wakefield Leeann Harney Joe Niesen Production Manager Tim Tate Proofreading and Indexing TECHBOOKS Production Services Vice President & Executive Group Publisher Richard Swadley
  8. Contents Introduction xvii Part I: Approaching Relational Database Modeling 1 Chapter 1: Database Modeling Past and Present 3 Grasping the Concept of a Database 4 Understanding a Database Model 5 What Is an Application? 5 The Evolution of Database Modeling 6 File Systems 7 Hierarchical Database Model 8 Network Database Model 8 Relational Database Model 9 Relational Database Management System 11 The History of the Relational Database Model 11 Object Database Model 12 Object-Relational Database Model 14 Examining the Types of Databases 14 Transactional Databases 15 Decision Support Databases 15 Hybrid Databases 16 Understanding Database Model Design 16 Defining the Objectives 17 Looking at Methods of Database Design 20 Summary 21 Chapter 2: Database Modeling in the Workplace 23 Understanding Business Rules and Objectives 24 What Are Business Rules? 25 The Importance of Business Rules 26 Incorporating the Human Factor 27 People as a Resource 27 Talking to the Right People 29 Getting the Right Information 30
  9. Contents Dealing with Unfavorable Scenarios 32 Computerizing a Pile of Papers 32 Converting Legacy Databases 33 Homogenous Integration of Heterogeneous Databases 33 Converting from Spreadsheets 33 Sorting Out a Messed-up Database 34 Summary 34 Chapter 3: Database Modeling Building Blocks 35 Information, Data and Data Integrity 37 Understanding the Basics of Tables 37 Records, Rows, and Tuples 39 Fields, Columns and Attributes 40 Datatypes 42 Simple Datatypes 42 Complex Datatypes 46 Specialized Datatypes 47 Constraints and Validation 47 Understanding Relations for Normalization 48 Benefits of Normalization 49 Potential Normalization Hazards 49 Representing Relationships in an ERD 49 Crows Foot 50 One-to-One 51 One-to-Many 52 Many-to-Many 53 Zero, One, or Many 55 Identifying and Non-Identifying Relationships 57 Understanding Keys 58 Primary Keys 59 Unique Keys 59 Foreign Keys 60 Understanding Referential Integrity 63 Understanding Indexes 64 What Is an Index? 65 Alternate Indexing 65 Foreign Key Indexing 65 Types of Indexes 66 Different Ways to Build Indexes 68 Introducing Views and Other Specialized Objects 69 Summary 70 Exercises 70 x
  10. Contents Part II: Designing Relational Database Models 71 Chapter 4: Understanding Normalization 73 What Is Normalization? 74 The Concept of Anomalies 74 Dependency, Determinants, and Other Jargon 76 Defining Normal Forms 80 Defining Normal Forms the Academic Way 80 Defining Normal Forms the Easy Way 81 1st Normal Form (1NF) 82 1NF the Academic Way 82 1NF the Easy Way 83 2nd Normal Form (2NF) 89 2NF the Academic Way 89 2NF the Easy Way 89 3rd Normal Form (3NF) 96 3NF the Academic Way 96 3NF the Easy Way 97 Beyond 3rd Normal Form (3NF) 103 Why Go Beyond 3NF? 104 Beyond 3NF the Easy Way 104 One-to-One NULL Tables 104 Beyond 3NF the Academic Way 107 Boyce-Codd Normal Form (BCNF) 108 4th Normal Form (4NF) 111 5th Normal Form (5NF) 116 Domain Key Normal Form (DKNF) 121 Summary 122 Exercises 122 Chapter 5: Reading and Writing Data with SQL 123 Defining SQL 124 The Origins of SQL 125 SQL for Different Databases 125 The Basics of SQL 126 Querying a Database Using SELECT 127 Basic Queries 127 Filtering with the WHERE Clause 130 Precedence 132 Sorting with the ORDER BY Clause 134 xi
  11. Contents Aggregating with the GROUP BY Clause 135 Join Queries 137 Nested Queries 141 Composite Queries 143 Changing Data in a Database 144 Understanding Transactions 144 Changing Database Metadata 145 Summary 148 Exercises 149 Chapter 6: Advanced Relational Database Modeling 151 Understanding Denormalization 152 Reversing Normal Forms 152 Denormalizing Beyond 3NF 153 Denormalizing 3NF 157 Denormalizing 2NF 160 Denormalizing 1NF 161 Denormalization Using Specialized Database Objects 162 Denormalization Tricks 163 Understanding the Object Model 165 Introducing the Data Warehouse Database Model 167 Summary 169 Exercises 170 Chapter 7: Understanding Data Warehouse Database Modeling 171 The Origin of Data Warehouses 172 The Relational Database Model and Data Warehouses 173 Surrogate Keys in a Data Warehouse 174 Referential Integrity in a Data Warehouse 174 The Dimensional Database Model 175 What Is a Star Schema? 176 What Is a Snowflake Schema? 178 How to Build a Data Warehouse Database Model 182 Data Warehouse Modeling Step by Step 183 How Long to Keep Data in a Data Warehouse? 183 Types of Dimension Tables 184 Understanding Fact Tables 190 Summary 191 Exercises 192 xii
  12. Contents Chapter 8: Building Fast-Performing Database Models 193 The Needs of Different Database Models 194 Factors Affecting OLTP Database Model Tuning 194 Factors Affecting Client-Server Database Model Tuning 195 Factors Affecting Data Warehouse Database Model Tuning 196 Understanding Database Model Tuning 197 Writing Efficient Queries 198 The SELECT Command 200 Filtering with the WHERE Clause 202 The HAVING and WHERE Clauses 204 Joins 205 Auto Counters 206 Efficient Indexing for Performance 206 Types of Indexes 207 How to Apply Indexes in the Real World 207 When Not to Use Indexes 209 Using Views 210 Application Caching 211 Summary 212 Exercises 213 Part III: A Case Study in Relational Database Modeling 215 Chapter 9: Planning and Preparation Through Analysis 217 Steps to Creating a Database Model 219 Step 1: Analysis 219 Step 2: Design 220 Step 3: Construction 220 Step 4: Implementation 220 Understanding Analysis 221 Analysis Considerations 222 Potential Problem Areas and Misconceptions 224 Normalization and Data Integrity 224 More Normalization Leads to Better Queries 224 Performance 224 Generic and Standardized Database Models 225 Putting Theory into Practice 225 Putting Analysis into Practice 225 Company Objectives 226 xiii
  13. Contents Case Study: The OLTP Database Model 229 Establishing Company Operations 229 Discovering Business Rules 232 Case Study: The Data Warehouse Model 243 Establishing Company Operations 244 Discovering Business Rules 248 Project Management 253 Project Planning and Timelines 253 Budgeting 255 Summary 256 Exercises 257 Chapter 10: Creating and Refining Tables During the Design Phase 259 A Little More About Design 260 Case Study: Creating Tables 262 The OLTP Database Model 262 The Data Warehouse Database Model 265 Case Study: Enforcing Table Relationships 269 Referential Integrity 269 Primary and Foreign Keys 270 Using Surrogate Keys 271 Identifying versus Non-Identifying Relationships 272 Parent Records without Children 272 Child Records with Optional Parents 273 The OLTP Database Model with Referential Integrity 274 The Data Warehouse Database Model with Referential Integrity 279 Normalization and Denormalization 282 Case Study: Normalizing an OLTP Database Model 283 Denormalizing 2NF 284 Denormalizing 3NF 285 Denormalizing 1NF 286 Denormalizing 3NF Again 287 Deeper Normalization Layers 289 Case Study: Backtracking and Refining an OLTP Database Model 295 Example Application Queries 298 Case Study: Refining a Data Warehouse Database Model 308 Summary 316 Exercises 317 xiv
  14. Contents Chapter 11: Filling in the Details with a Detailed Design 319 Case Study: Refining Field Structure 320 The OLTP Database Model 320 The Data Warehouse Database Model 323 Understanding Datatypes 329 Simple Datatypes 329 ANSI (American National Standards Institute) Datatypes 330 Microsoft Access Datatypes 331 Specialized Datatypes 331 Case Study: Defining Datatypes 332 The OLTP Database Model 332 The Data Warehouse Database Model 336 Understanding Keys and Indexes 338 Types of Indexes 339 What, When, and How to Index 342 When Not to Create Indexes 342 Case Study: Alternate Indexing 343 The OLTP Database Model 343 The Data Warehouse Database Model 345 Summary 352 Exercises 352 Chapter 12: Business Rules and Field Settings 353 What Are Business Rules Again? 354 Classifying Business Rules in a Database Model 355 Normalization, Normal Forms, and Relations 355 Classifying Relationship Types 356 Explicitly Declared Field Settings 357 Storing Code in the Database 358 Stored Procedure 360 Stored Function 362 Event Trigger 363 External Procedure 364 Macro 364 Case Study: Implementing Field Level Business Rules in a Database Model 364 Table and Relation Level Business Rules 364 Individual Field Business Rules 364 Field Level Business Rules for the OLTP Database Model 364 Field Level Business Rules for the Data warehouse Database Model 370 xv
  15. Contents Encoding Business Rules 373 Encoding Business Rules for the OLTP Database Model 373 Encoding Business Rules for the Data Warehouse Database Model 374 Summary 379 Part IV: Advanced Topics 381 Chapter 13: Advanced Database Structures and Hardware Resources 383 Advanced Database Structures 384 What and Where? 384 Views 384 Materialized Views 384 Indexes 385 Clusters 385 Auto Counters 385 Partitioning and Parallel Processing 385 Understanding Views 386 Understanding Materialized Views 387 Understanding Types of Indexes 390 BTree Index 391 Bitmap Index 392 Hash Keys and ISAM Keys 393 Clusters, Index Organized Tables, and Clustered Indexes 393 Understanding Auto Counters 393 Understanding Partitioning and Parallel Processing 393 Understanding Hardware Resources 396 How Much Hardware Can You Afford? 396 How Much Memory Do You Need? 396 Understanding Specialized Hardware Architectures 396 RAID Arrays 397 Standby Databases 397 Replication 399 Grids and Computer Clustering 400 Summary 401 Glossary 403 Appendix A: Exercise Answers 421 Appendix B: Sample Databases 435 Index 443 xvi
  16. Introduction This book focuses on the relational database model from a beginning perspective. The title is, therefore, Beginning Database Design. A database is a repository for data. In other words, you can store lots of infor- mation in a database. A relational database is a special type of database using structures called tables. Tables are linked together using what are called relationships. You can build tables with relationships between those tables, not only to organize your data, but also to allow later retrieval of information from the database. The process of relational database model design is the method used to create a relational database model. This process is mathematical in nature, but very simple, and is called normalization. With the process of normalization are a number of distinct steps called Normal Forms. Normal Forms are: 1st Normal Form (1NF), 2nd Normal Form (2NF), 3rd Normal Form (3NF), Boyce-Codd Normal Form (BCNF), 4th Normal Form (4NF), 5th Normal Form (5NF), and Domain Key Normal Form (DKNF). That is quite a list. This book presents the technical details of normalization and Normal Forms, in addition to presenting a lay- man’s version of normalization. Purists would argue that this approach is sacrilegious. The problem with normalization is that it is so precise by attempting to cater to every possible scenario. The result is that normalization is often misunderstood and quite frequently ignored. The result is poorly designed relational database models. A simplified version tends to help bridge a communication gap, and perhaps prepare the way for learning the precise definition of normalization, hopefully lowering the incline of the learning curve. Traditionally, relational database model design (and particularly the topic of normalization), has been much too precise for commercial environments. There is an easy way to interpret normalization, and this book contains original ideas in that respect. You should read this book because these ideas on relational database model design and normalization techniques will help you in your quest for perhaps even just a little more of an understanding as to how your database works. The objective here is to teach you to make much better use of that wonderful resource you have at your fingertips — your personal or company database. Who This Book Is For People who would benefit from reading this book would be anyone involved with database technology, from the novice all the way through to the expert. This includes database administrators, developers, data modelers, systems or network administrators, technical managers, marketers, advertisers, forecasters, planners — anyone. This book is intended to explain to the people who actually make use of database data (such as in a data warehouse) to make forecasting predictions for market research and otherwise. This book is intended for everyone. If you wanted some kind of clarity as to the funny diagrams you find in your Microsoft Access database (perhaps built for you by a programmer), this book will do it for you. If you want to know what on earth is all that stuff in the company SQL-Server or Oracle database, this book is a terrific place to start — giving just enough understanding without completely blowing your mind with too much techno-geek-speak.
  17. Introduction Current Head To find further information, the easiest place to search is the Internet. Search for a term such as “first normal form,” or “1st normal form,” or “1NF,” in search engines such as http://www.yahoo.com. Be aware that not all information will be current and might be incorrect. Verify by crosschecking between multiple references. If no results are found using Yahoo, try the full detailed listings on http://www.google .com. Try http://www.amazon.com and http://www.barnesandnoble.com where other relational database modeling titles can be found. What This Book Covers The objective of this book is to provide an easy to understand, step-by-step, simple explanation of designing and building relational database models. Plenty of examples are offered, and even a multiple chapter case study scenario is included, really digging into and analyzing all the details. All the scary, deep-level technical details are also here—hopefully with enough examples and simplistic explanatory detail to keep you hooked and absorbed, from cover to cover. As with all of the previous books by this author, this book presents something that appears to be immensely complex in a simplistic and easy to understand manner. The profligate use of examples and step-by-step explanations builds the material into the text. Note that the content of this book is made available “as is.” The author assumes no responsibility or liability for any mishaps as a result of using this information, in any form or environment. How This Book Is Structured This book is divided into four parts. Each part contains chapters with related material. The book begins by describing the basics behind relational database modeling. It then progresses onto the theory with which relational database models are built. The third part performs a case study across four entire chap- ters, introducing some new concepts, as the case study progresses. In Part IV, new concepts described in the case study chapters are not directly related to relational database modeling theory. The last part describes some advanced topics. It is critical to read the parts in the order in which they appear in the book. Part I examines historical aspects, describing why the relational database model became necessary. Part II goes through all the the- ory grounding relational database modeling. You need to know why the relational database model was devised (from Part I), to fully understand theory covered in Part II. After all the history and theories are understood, you can begin with the case study in Part III. The case study applies all that you have learned from Part I and Part II, particularly Part II. Part IV contains detail some unusual information, related to previous chapters by expanding into rarely used database structures and hardware resource usage. xviii
Đồng bộ tài khoản