ebook img

Database Design: Know It All PDF

366 Pages·2008·7.75 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Database Design: Know It All

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: (+44) 1865 843830, fax: (+44) 1865 853333, 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.

Description:
Boyce-Codd Normal Form . or attribute; a record is called a row or tuple; and a file is called a table. A database is a more . has a vested interest in the database design process. The ER model has two
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.