Database Design: Know It All

366 Pages·2008·7.75 MB·English

by  Toby J. Teorey

Database Design
This page intentionally left blank
Database Design
Know It All
Stephen Buxton Thomas P. Nadeau
Lowell Fryman Bonnie O’Neil
Ralf Hartmut Güting Elizabeth O’Neil
Terry Halpin Patrick O’Neil
Jan L. Harrington Markus Schneider
William H. Inmon Graeme Simsion
Sam S. Lightstone Toby J. Teorey
Jim Melton Graham Witt
Tony Morgan
AMSTERDAM (cid:129) BOSTON (cid:129) HEIDELBERG (cid:129) LONDON
NEW YORK (cid:129) OXFORD (cid:129) PARIS (cid:129) SAN DIEGO
SAN FRANCISCO (cid:129) SINGAPORE (cid:129) SYDNEY (cid:129) TOKYO
Morgan Kaufmann is an imprint of Elsevier
Morgan Kaufmann Publishers is an imprint of Elsevier.
30 Corporate Drive, Suite 400
Burlington, MA 01803
This book is printed on acid-free paper.
Copyright © 2009 by Elsevier Inc. All rights reserved.
Designations used by companies to distinguish their products are often claimed as
trademarks or registered trademarks. In all instances in which Morgan Kaufmann
Publishers is aware of a claim, the product names appear in initial capital or all capital
letters. Readers, however, should contact the appropriate companies for more complete
information regarding trademarks and registration.
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,
scanning, or otherwise, without prior written permission of the publisher.
Permissions may be sought directly from Elsevier’s Science & Technology Rights
Department in Oxford, UK: phone: [phone], fax: [phone], e-mail:
[email protected]. You may also complete your request on-line via the Elsevier
homepage (http://elsevier.com), by selecting “Support & Contact” then “Copyright and
Permission” and then “Obtaining Permissions.”
Library of Congress Cataloging-in-Publication Data
Teorey, Toby J.
Database design : know it all / Toby Teorey et al.
p. cm. — (Morgan Kaufmann know it all series)
Includes index.
ISBN 978-0-12-374630-6 (alk. paper)
1. Database design. I. Title.
QA76.9.D26T42 2008
005.74—dc22 2008040366
For information on all Morgan Kaufmann publications,
visit our Website at www.mkp.com or www.books.elsevier.com
Printed in the United States
08 09 10 11 12 10 9 8 7 6 5 4 3 2 1
Working together to grow
libraries in developing countries
www.elsevier.com | www.bookaid.org | www.sabre.org
Contents
About This Book ........................................................................... ix
Contributing Authors .................................................................... xi
CHAPTER 1 Introduction ..................................................................... 1
1.1 Data and Database Management ........................................ 1
1.2 The Database Life Cycle .................................................... 2
1.3 Conceptual Data Modeling ................................................ 7
1.4 Summary ............................................................................ 9
1.5 Literature Summary ............................................................ 9
CHAPTER 2 Entity–Relationship Concepts ..................................... 11
2.1 Introduction to ER Concepts ............................................. 13
2.2 Further Details of ER Modeling ......................................... 20
2.3 Additional ER Concepts ..................................................... 29
2.4 Case Study .......................................................................... 32
2.5 Normalization: Preliminaries .............................................. 36
2.6 Functional Dependencies .................................................. 41
2.7 Lossless Decompositions ................................................... 57
2.8 Normal Forms .................................................................... 65
2.9 Additional Design Considerations...................................... 80
2.10 Suggestions for Further Reading........................................ 83
CHAPTER 3 Data Modeling in UML ................................................. 85
3.1 Introduction ....................................................................... 85
3.2 Object Orientation ............................................................. 88
3.3 Attributes............................................................................ 91
3.4 Associations ........................................................................ 97
3.5 Set-Comparison Constraints ............................................... 105
3.6 Subtyping ........................................................................... 113
3.7 Other Constraints and Derivation Rules ............................ 118
3.8 Mapping from ORM to UML .............................................. 132
3.9 Summary ............................................................................ 136
3.10 Literature Summary ............................................................ 138
vi Contents
CHAPTER 4 Requirements Analysis and Conceptual
Data Modeling ................................................................ 141
4.1 Introduction ....................................................................... 141
4.2 Requirements Analysis ....................................................... 142
4.3 Conceptual Data Modeling ................................................ 143
4.4 View Integration ................................................................ 152
4.5 Entity Clustering for ER Models ........................................ 160
4.6 Summary ............................................................................ 165
4.7 Literature Summary ............................................................ 167
CHAPTER 5 Logical Database Design ............................................. 169
5.1 Introduction ....................................................................... 169
5.2 Overview of the Transformations Required ...................... 170
5.3 Table Specifi cation ............................................................. 172
5.4 Basic Column Defi nition .................................................... 181
5.5 Primary Key Specifi cation .................................................. 187
5.6 Foreign Key Specifi cation .................................................. 189
5.7 Table and Column Names ................................................. 200
5.8 Logical Data Model Notations ............................................ 201
5.9 Summary ............................................................................ 203
CHAPTER 6 Normalization ................................................................. 205
6.1 Translating an ER Diagram into Relations ......................... 205
6.2 Normal Forms .................................................................... 206
6.3 First Normal Form .............................................................. 207
6.4 Second Normal Form ......................................................... 212
6.5 Third Normal Form ............................................................ 214
6.6 Boyce-Codd Normal Form ................................................. 216
6.7 Fourth Normal Form .......................................................... 217
6.8 Normalized Relations and Database Performance ............. 219
6.9 Further Reading ................................................................. 224
CHAPTER 7 Physical Database Design ........................................... 225
7.1 Introduction ....................................................................... 225
7.2 Inputs to Database Design ................................................. 226
7.3 Options Available to the Database Designer ..................... 228
7.4 Design Decisions that Do Not Affect Program Logic ........ 229
7.5 Crafting Queries to Run Faster .......................................... 237
7.6 Logical Schema Decisions .................................................. 238
7.7 Views .................................................................................. 247
7.8 Summary ............................................................................ 250
Contents vii
CHAPTER 8 Denormalization ............................................................. 251
8.1 Basics of Normalization ..................................................... 251
8.2 Common Types of Denormalization ................................. 255
8.3 Table Denormalization Strategy ......................................... 259
8.4 Example of Denormalization ............................................. 260
8.5 Summary ............................................................................ 267
8.6 Further Reading ................................................................. 267
CHAPTER 9 Business Metadata Infrastructure ............................. 269
9.1 Introduction ....................................................................... 269
9.2 Types of Business Metadata ............................................... 269
9.3 The Metadata Warehouse .................................................. 271
9.4 Delivery Considerations ..................................................... 273
9.5 Integration .......................................................................... 275
9.6 Administrative Issues ......................................................... 279
9.7 Metadata Repository: Buy or Build? ................................... 280
9.8 The Build Considerations................................................... 281
9.9 The Third Alternative: Use a Preexisting Repository ........ 281
9.10 Summary ............................................................................ 282
CHAPTER 10 Storing: XML and Databases ...................................... 283
10.1 Introduction ....................................................................... 283
10.2 The Need for Persistence .................................................. 284
10.3 SQL/XML’s XML Type ........................................................ 293
10.4 Accessing Persistent XML Data .......................................... 294
10.5 XML “On the Fly”: Nonpersistent XML Data..................... 295
10.6 Summary ............................................................................ 297
CHAPTER 11 Modeling and Querying Current Movement ............ 299
11.1 Location Management ........................................................ 299
11.2 MOST—A Data Model for Current and
Future Movement ............................................................... 301
11.3 FTL—A Query Language Based on Future
Temporal Logic .................................................................. 306
11.4 Location Updates—Balancing Update Cost
and Imprecision ................................................................. 317
11.5 The Uncertainty of the Trajectory of
a Moving Object ................................................................ 323
11.6 Practice .............................................................................. 333
11.7 Literature Notes ................................................................. 335
Index ........................................................................................... 337
This page intentionally left blank
About This Book
All of the elements about database design are here together in a single resource
written by the best and brightest experts in the fi eld! Databases are the main
repository of a company’ s historical data— its corporate memory — and they
contain the raw material for management’ s decision support system. The increas-
ing volume of data in modern business calls for the continual refi nement of data-
base design methodology. Database Design: Know It All expertly combines the
fi nest database design material from the Morgan Kaufmann portfolio into a single
book, making it a defi nitive, one-stop-shopping opportunity so that readers can
have the information they need available to quickly retrieve, analyze, transform,
and load data— the very processes that more and more organizations use to
differentiate themselves. Each chapter is authored by a leading expert in the
fi eld; the book consolidates introductory and advanced topics ranging from ER
and UML techniques to storing XML and querying moving objects. In this way,
what is here is an invaluable resource for anyone working in today’ s fast-paced,
data-centric environment.