Database Development For Dummies®
Published by
Wiley Publishing, Inc.
111 River St.
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2001 Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
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 permission 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-8700. 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-4447, e-mail: permcoordinator@ wiley.com.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, 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.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE USED THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A PROFESSIONAL WHERE APPROPRIATE. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.
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 877-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.
Library of Congress Control Number: 00-103400
ISBN: 0-7645-0752-4
Manufactured in the United States of America
10 9 8 7 6 5 4
10/TQ/RS/QV/IN
Allen G. Taylor is a 28-year veteran of the computer industry and the author of 17 computer-related books, including SQL For Dummies. In addition to writing, he is a database consultant and seminar leader in database design and application development. Allen lives with his family on a small farm outside of Oregon City, Oregon. You can contact Allen at agt@transport.com.
I have received help from many quarters in this book. I am especially indebted to Keith Taylor, Heath Schweitzer, Joshua McKinney, Sue Preston, and Ernest Argetsinger.
Thanks to my editor, John Pont, and all the folks at Wiley Publishing who helped make this book possible, including Debra Williams Cauley, Kristy Nash, Nancee Reeves, Angie Hunckler, and Constance Carlisle.
Thanks once again to my agent, Matt Wagner of Waterside Productions, who continues to help me advance my writing career.
Thanks to Patrick J. McGovern, who built a structure that gave me my first trip to China as well as the opportunity to write this book.
Thanks to Joyce, Jenny, Valerie, Melody, Neil, Rob, and Sam for sacrificing some of their time with me so that I could write.
This book is dedicated to my wife, Joyce C. Taylor, who continues to encourage me, even though she believes I spend too much time staring into a computer monitor.
We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Project Editor: John W. Pont
Acquisitions Editor: Debra Williams Cauley
Proof Editor: Teresa Artman
Technical Editor: Ernest Argetsinger
Editorial Manager: Constance Carlisle, Sarah Shupert, Candace Nicholson, Amanda Foxworth
Production
Project Coordinator: Nancee Reeves
Layout and Graphics: Amy Adrian, Karl Brandt, John Greenough, LeAndra Johnson, Jill Piscitelli, Heather Pope, Brian Torwelle
Proofreaders: John Bitter, Nancy Price, Marianne Santy, York Production Services, Inc.
Indexer: York Production Services, Inc.
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Title
Introduction
About This Book
Who Should Read This Book?
Foolish Assumptions
How This Book Is Organized
Conventions Used in This Book
Icons Used in This Book
Where to Go From Here
Part I : Basic Concepts
Chapter 1: Database Processing
The Different Classes of Databases
So Much Data, So Little Time
What Is Database Processing?
Chapter 2: Database Development
What Is a Database?
Developing a Database
Resisting the Urge to Build a Database Right Now
Some Development Tools May Be Too User Friendly
Part II : Data Modeling: What Should the Database Represent?
Chapter 3: The Users’ Model
Who Are the Interested Parties, and What Are They Thinking?
What Should the System Be?
Chapter 4: The Entity-Relationship Model
Exploring the Structure of the E-R Model
Creating Entity-Relationship Diagrams
Refining the E-R Model
It’s Time to Look at Some E-R Examples
Chapter 5: The Semantic Object Model
Examining the SOM Structure
Different Types of Objects Model Different Situations
Building a Semantic Object Model, an Example
Comparing the E-R Model to the SOM
Chapter 6: Determining What You Are Going to Do
Upgrading an Existing System
Building a New System from Scratch
What Will Matter Five Years from Now?
Determining the Project’s Scope
Part III : Database Design
Chapter 7: The Relational Model
Relations, Attributes, and Tuples
Problems with Your Relations
Fixing Problems through Normalization
Using Functional Dependencies to Build Relations
Can a Database be too Normalized?
Trading Off Data Integrity Against Performance
Chapter 8: Using an Entity-Relationship Model to Design a Database
Capturing the User’s Model with an E-R Model
Converting an E-R Model into a Relational Design
Chapter 9: Using a Semantic Object Model to Design a Database
Converting an SOM into a Relational Design
An Example
Part IV : Implementing a Database
Chapter 10: Using DBMS Tools to Implement a Database
Translating a Relational Model into a Database
Access 2000
Chapter 11: Addressing Bigger Problems with SQL Server 2000
Getting to Know SQL Server 2000
Translating Your Relational Model into a SQL Server Database
Chapter 12: Using SQL to Implement a Database
The Evolution of SQL
ANSI Standard SQL
SQL Variants
Creating a Database with SQL
Part V : Implementing a Database Application
Chapter 13: Using DBMS Tools to Implement a Database Application
Building a Forms-based Application with Access
Using VBA to Create a More Sophisticated Application
Chapter 14: SQL and Database Applications
Programs and Procedural Languages
SQL and Set-at-a-Time Operations
Combining the Procedural and the Nonprocedural
Using SQL without a Host Language
Part VI : Using Internet Technology with Database
Chapter 15: Database on Networks
The Architecture and Functions of Client/Server Systems
The Internet Changes Everything
Serving Up Information Over the Organizational Intranet
Chapter 16: Database Security and Reliability
Maintaining Database Security
Controlling Concurrent Access
Database Crash and Recovery
Part VII : The Part of Tens
Chapter 17: Ten Rules to Remember When Creating a Database
Databases, Even Huge Ones Such as NASA’s Bill of Materials for the Space Shuttle, Can Be Designed, Built, and Maintained
Build Your Database Model to Accurately Reflect the Users’ Data Model
Be Sure to Budget Enough Time to Do Justice to Every One of the Phases of Development
Build a Conceptual Model of Your Proposed Database
Make Your Model as Detailed as It Needs to Be, but Not More So
Build Flexibility into Your Systems So They Will Adapt Easily When Requirements Change
Accurately Assess the Project’s Scope at the Beginning
Make Sure All the Relations in Your Model Deal With Only One Idea
Sometimes, for Performance Reasons, You Will Want to Denormalize Relations So They Deal With More than One Idea
Reduce Any Many-to-Many Relationships in a Model to Multiple One-to-Many Relationships
Chapter 18: Ten Rules to Remember When Creating a Database Application
Talk to Your System’s Future Users a Lot
Document Every Phase of the Development Process
Test Your Application Frequently
Be a Consensus Builder
Pick the Right Tools for the Job
Database Applications Communicate with Databases Using SQL
Use Standard SQL Wherever Possible
Optimize the Server for Fast Data Transfer
Configure the Client for Lowest Cost
Pump Some Life into Web-based Applications
Glossary
Because you are reading this, I assume that you have recently become interested in database. Perhaps you would like to impress your friends by casually tossing out some big words that they have never heard before — words such as semantic object model, denormalization, or maybe even tuple. Perhaps your boss at work has just informed you that your department will be computerizing its records and that you have been assigned to build the database. Whatever your motivation, this book will get you started down the path to becoming a true database guru. When you reach that exalted level, impressing your friends with big words will pale in significance compared to what you will be able to do with your organization’s most important information.
Ever since computers became powerful enough to support them, databases have been at the core of both commercial and scientific data processing. The domain of database processing includes any problem or task that must deal with large amounts of data. Most database systems in existence today, and practically all new systems being implemented, make use of relational database technology, the subject of this book.
This book takes you step by step through the conceptualization, design, development, and maintenance of relational database systems. It gives you a solid grounding in database theory and then shows how to reduce that theory to practice using two of the more popular database management systems in use today: Microsoft Access and Microsoft SQL Server. Major topics covered include
Understanding database architecture and how it has evolved
Recognizing how database technology affects everyday life
Using a structured approach to database development
Creating an appropriate data model
Creating a reliable relational design
Implementing a relational design
Keeping a database secure
Putting your database on the Internet
My objective with this book is to give you the information you need to build a robust database system that will do what you want it to do. When designed correctly, a database system will give you the performance, flexibility, and reliability to meet your needs, both now and in the future.
Anyone tasked with the development of a software system that incorporates a database element, or anyone managing the people who do such development, should read this book. Any person in any organization that uses database technology (that should be just about anybody who works anywhere) can benefit from understanding the concepts I explain in this book.
Databases have penetrated every nook and cranny of our highly connected, information-intensive society. The more you understand about how they function and the differences between well-designed and poorly designed databases, the better you will be able to decide the best way to use your organization’s database resources.
In order to write this book, I had to make some assumptions about who would be reading it and what their level of expertise would be. Based on feedback I have received from readers of SQL For Dummies , I know that accurately targeting readership is incredibly difficult. I expect that some readers will be gaining their first exposure to databases, while others will be professional database developers. I have tried to make the book understandable to the first group, while at the same time making it a useful guide to the second group.
This book contains seven major parts. Each part consists of several chapters. It makes sense to read the book from beginning to end because later material builds on an understanding of what has gone before. You may decide to skip either the Access chapter or the SQL Server chapter if they do not apply to you. However, the implementation details that I describe in those chapters will be similar to what you will encounter in other development environments, and thus will probably be valuable to you anyway.
Part I establishes the context for the rest of the book. It describes the position of data and databases in the world today and then describes how to systematically design and develop a database system incorporating a database and one or more applications that operate on that database. This part also describes challenges that often arise in the course of a database development project, and how you can best address them.
In any database development project, you must address a few key questions — for example: What exactly should the database represent, and to what level of detail? Answers to these questions come from finding out who will use the proposed system and how they will use it. Finding out the needs and expectations of the users, and then transforming those needs and expectations into a formal, structured data model forms the core of Part II. Getting this part right is absolutely critical to the successful completion of a development project.
After you have a model of the proposed system that is satisfactory to everyone concerned, you need to convert that model into a database design. In order for your design to be reliable as well as functional, you need to decide how best to transform complex relationships among data items into simpler relationships that are not subject to the data corruption problems that often accompany complexity. Part III highlights the complexities you are likely to encounter, and in each case describes how best to transform them into a simpler form that eliminates the problems.
Part IV starts with a database design, developed using the techniques that I explain in Part III, and shows step by step how to convert that design into a database using some of the more popular database development tools available today. First, I cover the process using Microsoft Access 2000. Then, I show you how to implement the same design using the SQL Server 2000 DBMS. Finally, I explain how to implement the design using straight SQL code, without the help of any fancy development tools. I clearly delineate the strengths and weaknesses of each approach as I describe each method.
The application is the part of a database system that the users see and interact with. It is the application that answers whatever questions the users pose to the database.
The implementation of a database application can differ greatly from one development environment to another. On the one hand, Access gives developers an integrated forms wizard and report writer and the ability to create a complete application without writing a single line of procedural code. On the other, a developer can write a database application using only procedural code with embedded SQL statements, without the aid of a DBMS such as Access. SQL Server falls somewhere in the middle. You can use external forms generator and report writer packages along with procedural code to operate on an SQL Server database. You can also employ a hybrid approach in which you use some or all of these facilities. The ability to use all these tools gives you the ultimate in flexibility, but also requires the highest level of expertise.
Databases are most useful when resident on networks available to multiple people. That usefulness is multiplied when the number of users increases, as it does when the database is accessible over the Internet or a large organizational intranet. In Part VI, I discuss network architectures, the kinds of threats to data integrity that network operation causes, and the particular threats that are peculiar to the Internet. In general, good countermeasures to these threats exist, but developers and database administrators must be aware of the threats so they can apply the countermeasures effectively.
Part VII distills the messages of the preceding six parts, providing concise summaries of the main things to keep in mind when designing and building systems based on relational database technology. If you keep these principles in mind, you can’t go too far wrong.
In this book, I use several typographical conventions. I use monofont type for code that appears within a regular paragraph of text — for example, to tell you about an access denied error message. I use command arrows (⇒) to present menu commands in the most concise manner possible. For example, if I didn’t use command arrows, I would have to give you instructions like this: “In the menu bar, choose File. Then, in the resulting menu, choose Open.” With the command arrow, all that verbiage boils down to this: “Choose File⇒Open.”
Throughout the pages of this book, I use these icons to highlight particularly helpful information.
.jpg)
Enough preliminaries! Dig into the real meat of this book — what databases are and how to build them. Understanding those two things is rapidly becoming a requisite for just about anyone involved in commerce, science, or anything else that involves the storage and processing of data. Start with Chapter 1. It gives you the perspective you need to understand where database technology came from and where it stands today.
In this part . . .
In Part I, I give you the background information you need in order to build high-quality databases and database applications. I describe the different classes of databases and what makes them different. I also describe the critical role that databases play in our data-saturated world, including the so-called “new economy.” I offer a brief history of data processing and the advent of database systems, leading up to coverage of what databases and database applications are, followed by a structured approach to building them. I also describe some of the major pitfalls of database development, and explain how to avoid them.