A developer's guide to data modeling for SQL server covering SQL server 2005 and 2008

303 Pages·2008·3.535 MB·English

by  Johnson, Eric;Jones, Joshua

Praise for A Developer’s Guide to Data Modeling
for SQL Server
“Eric and Joshua do an excellent job explaining the importance of data modeling and how
to do it correctly. Rather than relying only on academic concepts, they use real-world ex-
amples to illustrate the important concepts that many database and application develop-
ers tend to ignore. The writing style is conversational and accessible to both database
design novices and seasoned pros alike. Readers who are responsible for designing, imple-
menting, and managing databases will benefit greatly from Joshua’s and Eric’s expertise.”
—Anil Desai, Consultant, Anil Desai, Inc.
“Almost every IT project involves data storage of some kind, and for most that means a
relational database management system (RDBMS). This book is written for a database-
centric audience (database modelers, architects, designers, developers, etc.). The authors
do a great job of showing us how to take a project from its initial stages of requirements
gathering all the way through to implementation. Along the way we learn how to handle
some of the real-world design issues that typically surface as we go through the process.
“The bottom line here is simple. This is the book you want to have just finished read-
ing when your boss says ‘We have a new project I would like your help with.’”
—Ronald Landers, Technical Consultant, IT Professionals, Inc.
“The Data Model is the foundation of the application. I’m pleased to see additional books
being written to address this critical phase. This book presents a balanced and pragmatic
view with the right priorities to get your SQL server project off to a great start and a long
life.”
—Paul Nielsen, SQL Server MVP, SQLServerBible.com
“This is a truly excellent introduction to the database design methodology that will work
for both novices and advanced designers. The authors do a good job at explaining the ba-
sics of relational database modeling and how they fit into modern business architecture.
This book teaches us how to identify the business problems that have to be satisfied by a
database and then proceeds to explain how to build a solid solution from scratch.”
—Alexzander N. Nepomnjashiy, Microsoft SQL Server DBA,
NeoSystems North-West, Inc.
“A Developer’s Guide to Data Modeling for SQL Server explains the concepts and prac-
tice of data modeling with a clarity that makes the technology accessible to anyone build-
ing databases and data-driven applications.
“Eric Johnson and Joshua Jones combine a deep understanding of the science of data
modeling with the art that comes with years of experience. If you’re new to data model-
ing, or find the need to brush up on its concepts, this book is for you.”
—Peter Varhol, Executive Editor, Redmond Magazine
This page intentionally left blank
A Developer’s Guide
to Data Modeling
for SQL Server
C SQL S
OVERING ERVER
2005 2008
AND
This page intentionally left blank
A Developer’s Guide
to Data Modeling
for SQL Server
C SQL S
OVERING ERVER
2005 2008
AND
Eric Johnson
Joshua Jones
Upper Saddle River, NJ • Boston • Indianapolis • San Francisco
New York • Toronto • Montreal • London • Munich • Paris • Madrid
Capetown • Sydney • Tokyo • Singapore • Mexico City
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks.
Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations
have been printed with initial capital letters or in all capitals.
The authors and publisher have taken care in the preparation of this book, but make no expressed or implied war-
ranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or
consequential damages in connection with or arising out of the use of the information or programs contained herein.
The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales,
which may include electronic versions and/or custom covers and content particular to your business, training goals,
marketing focus, and branding interests. For more information, please contact:
U.S. Corporate and Government Sales
[phone]
[email protected]
For sales outside the United States please contact:
International Sales
[email protected]
This Book Is Safari Enabled
®
The Safari Enabled icon on the cover of your favorite technology book means the book is available
through Safari Bookshelf. When you buy this book, you get free access to the online edition for 45 days.
Safari Bookshelf is an electronic reference library that lets you easily search thousands of technical books, find code
samples, download chapters, and access technical information whenever and wherever you need it.
To gain 45-day Safari Enabled access to this book:
• Go to informit.com/onlineedition
• Complete the brief registration form
• Enter the coupon code BZG7-GAAI-IAAQ-R6IB-DCI8
If you have difficulty registering on Safari Bookshelf or accessing the online edition, please e-mail
[email protected].
Visit us on the Web: informit.com/aw
Library of Congress Cataloging-in-Publication Data
Johnson, Eric, 1978–
A developer’s guide to data modeling for SQL server : covering SQL server
2005 and 2008 / Eric Johnson and Joshua Jones. — 1st ed.
p. cm.
Includes index.
ISBN 978-0-321-49764-2 (pbk. : alk. paper)
1. SQL server. 2. Database design. 3. Data structures (Computer science)
I. Jones, Joshua, 1975- II. Title.
QA76.9.D26J65 2008
005.75'85—dc22 2008016668
Copyright©2008 Pearson Education, Inc.
All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permis-
sion must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or trans-
mission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information
regarding permissions, write to:
Pearson Education, Inc.
Rights and Contracts Department
501 Boylston Street, Suite 900
Boston, MA 02116
Fax [phone]
ISBN-13: 978-0-321-49764-2
ISBN-10: 0-321-49764-3
Text printed in the United States on recycled paper at Courier in Stoughton, Massachusetts.
First printing, June 2008
For Michelle and Evan—Eric
To my wife and children; I have time to play now—Josh
This page intentionally left blank
C
ONTENTS
Preface xv
Acknowledgments xvii
About the Authors xix
PART I Data Modeling Theory. . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 1 Data Modeling Overview. . . . . . . . . . . . . . . . . . . . . . . . . 3
Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Relational Database Management Systems. . . . . . . . . . . . . . . . . . . . . . . 5
Why a Sound Data Model Is Important . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Meeting Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Easy Data Retrieval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Performance Tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
The Process of Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Modeling Theory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Building the Logical Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Building the Physical Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Chapter 2 Elements Used in Logical Data Models . . . . . . . . . . . . . . 23
Entities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Domains. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Single-Valued and Multivalued Attributes. . . . . . . . . . . . . . . . . . . . . . . 32
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
ix