Teorey J., Lightstone S., Nadeau T. Database Modeling and Design: Logical Design

Подождите немного. Документ загружается.

DATABASE MODELING

AND DESIGN

The Morgan Kaufmann Series in Data Management Systems (Selected Titles)

Joe Celko’s Data, Measurements and

Standards in SQL

Joe Celko

Information Modeling and Relational

Databases, 2

Edition

Terry Halpin, Tony Morgan

Joe Celko’s Thinking in Sets

Joe Celko

Business Metadata

Bill Inmon, Bonnie O’Neil, Lowell Fryman

Unleashing Web 2.0

Gottfried Vossen, Stephan Hagemann

Enterprise Knowledge Management

David Loshin

Business Process Change, 2

Edition

Paul Harmon

IT Manager’s Handbook, 2

Edition

Bill Holtsnider & Brian Jaffe

Joe Celko’s Puzzles and Answers, 2

Edition

Joe Celko

Architecture and Patterns for IT Service

Management, Resource Planning, and

Governance

Charles Betz

Joe Celko’s Analytics and OLAP in SQL

Joe Celko

Data Preparation forData Mining Using SAS

Mamdouh Refaat

Querying XML: XQuery, XPath, and SQL/

XML in Context

Jim Melton and Stephen Buxton

Data Mining: Concepts and Techniques,

Edition

Jiawei Han and Micheline Kamber

Database Modeling and Design: Logical

Design, 5

Edition

Toby J, Teorey, Sam S. Lightstone,

Thomas P. Nadeau, and H. V. Jagadish

Foundations of Multidimensional and

Metric Data Structures

Hanan Samet

Joe Celko’s SQL for Smarties: Advanced

SQL Programming, 4

Edition

Joe Celko

Moving Objects Databases

Ralf Hartmut Gu

ting and Markus Schneider

Joe Celko’s SQL Programming Style

Joe Celko

Data Mining, Second Edition: Concepts

and Techniques

Jiawei Han, Micheline Kamber, Jian Pei

Fuzzy Modeling and Genetic Algorithms

for Data Mining and Exploration

Earl Cox

Data Modeling Essentials, 3

Edition

Graeme C. Simsion and Graham C. Witt

Developing High Quality Data Models

Matthew West

Location-Based Services

Jochen Schiller and Agne

s Voisard

Managing Time in Relational Databases:

How to Design, Update and Query

Temporal Data

Tom Johnston and Randall Weis

Database Modeling with Microsoft

Visio

for Enterprise Architects

Terry Halpin, Ken Evans, Patrick Hallock,

Bill Maclean

Designing Data-Intensive W eb Applications

Stephano Ceri, Piero Fraternali, Aldo

Bongio, Marco Brambilla, Sara Comai,

Maristella Matera

Mining the Web: Discovering Knowledge

from Hypertext Data

Soumen Chakrabarti

Advanced SQL: 1999—Understanding

Object-Relational and Other Advanced

Features

Jim Melton

Database Tuning: Principles, Experiments,

and Troubleshooting Techniques

Dennis Shasha, Philippe Bonnet

SQL: 1999—Understanding Relational

Language Components

Jim Melton, Alan R. Simon

Information Visualization in Data Mining

and Knowledge Discovery

Edited by Usama Fayyad, Georges G.

Grinstein, Andreas Wierse

Transactional Information Systems

Gerhard Weikum and Gottfried Vossen

Spatial Databases

Philippe Rigaux, Michel Scholl, and

Agnes Voisard

Managing Reference Data in Enterprise

Databases

Malcolm Chisholm

Understanding SQL and Java Together

Jim Melton and Andrew Eisenberg

Database: Principles, Programming, and

Performance, 2

Edition

Patrick and Elizabeth O’Neil

The Object Data Standard

Edited by R. G. G. Cattell, Douglas Barry

Data on the Web: From Relations to

Semistructured Data and XML

Serge Abiteboul, Peter Buneman, Dan Suciu

Data Mining, Third Edition Practical

Machine Learning Tools and Techniques

with Java Implementations

Ian Witten, Eibe Frank, and Mark A. Hall

Joe Celko’s Data and Databases: Concepts

in Practice

Joe Celko

Developing Time-Oriented Database

Applications in SQL

Richard T. Snodgrass

Web Farming for the Data Warehouse

Richard D. Hackathorn

Management of Heterogeneous and

Autonomous Database Systems

Edited by Ahmed Elmagarmid, Marek

Rusinkiewicz, Amit Sheth

Object-Relational DBMSs: 2

Edition

Michael Stonebraker and Paul Brown,

with Dorothy Moore

Universal Database Management: A Guide

to Object/Relational Technology

Cynthia Maro Saracco

Readings in Database Systems, 3

Edition

Edited by Michael Stonebraker, Joseph M.

Hellerstein

Understanding SQL’s Stored Procedures: A

Complete Guide to SQL/PSM

Jim Melton

Principles of Multimedia Database

Systems

V. S. Subrahmanian

Principles of Database Query Processing

for Advanced Applications

Clement T. Yu, Weiyi Meng

Advanced Database Systems

Carlo Zaniolo, Stefano Ceri, Christos

Faloutsos, Richard T. Snodgrass, V. S.

Subrahmanian, Roberto Zicari

Principles of Transaction Processing,

Edition

Philip A. Bernstein, Eric Newcomer

Using the New DB2: IBMs Object-Relational

Database System

Don Chamberlin

Distributed Algorithms

Nancy A. Lynch

Active Database Systems: Triggers

and Rules For Advanced Database

Processing

Edited by Jennifer Widom, Stefano Ceri

Migrating Legacy Systems: Gateways,

Interfaces, & the Incremental Approach

Michael L. Brodie, Michael Stonebraker

Atomic Transactions

Nancy Lynch, Michael Merritt, William

Weihl, Alan Fekete

Query Processing for Advanced Database

Systems

Edited by Johann Christoph Freytag,

David Maier, Gottfried Vossen

Transaction Processing

Jim Gray, Andreas Reuter

Database Transaction Models for

Advanced Applications

Edited by Ahmed K. Elmagarmid

A Guide to Developing Client/Server SQL

Applications

Setrag Khoshafian, Arvola Chan, Anna

Wong, Harry K. T. Wong

DATABASE MODELING

AND DESIGN

Logical Design

Fifth Edition

TOBY TEOREY

SAM LIGHTSTONE

TOM NADEAU

H. V. JAGADISH

AMSTERDAM • BOSTON • HEIDELBERG • LONDON

NEW YORK • OXFORD • PARIS • SAN DIEGO

SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO

Mor

an Kaufmann Publishers is an imprint of Elsevier

Acquiring Editor: Rick Adams

Development Editor: David Bevans

Project Manager: Sarah Binns

Designer: Joanne Blank

Morgan Kaufmann Publishers is an imprint of Elsevier.

30 Corporate Drive, Suite 400, Burlington, MA 01803, USA

This book is printed on acid-free paper.

No part of this publication may be reproduced or transmitted in any form or by any means,

electronic or mechanical, including photocopying, recording, or any information storage and

retrieval system, without permission in writing from the publisher. Details on how to seek

permission, further information about the Publisher’s permissions policies and our arrangements

with organizations such as the Copyright Clearance Center and the Copyright Licensing Agency,

can be found at our website: www.elsevier.com/permissions.

This book and the individual contributions contained in it are protected under copyright by the

Publisher (other than as may be noted herein).

Notices

Knowledge and best practice in this field are constantly changing. As new research and experience

broaden our understanding, changes in research methods, professional practices, or medical

treatment may become necessary.

Practitioners and researchers must always rely on their own experience and knowledge in

evaluating and using any information, methods, compounds, or experiments described herein. In

using such information or methods they should be mindful of their own safety and the safety of

others, including parties for whom they have a professional responsibility.

To the fullest extent of the law, neither the Publisher nor the authors, contributors, or editors,

assume any liability for any injury and/or damage to persons or property as a matter of products

liability, negligence or otherwise, or from any use or operation of any methods, products,

instructions, or ideas contained in the material herein.

Library of Congress Cataloging-in-Publication Data

Database modeling and design : logical design / Toby Teorey ... [et al.]. – 5th ed.

p. cm.

Rev. ed. of: Database modeling & design / Tobey Teorey, Sam Lightstone, Tom Nadeau. 4th ed. 2005.

ISBN 978-0-12-382020-4

1. Relational databases. 2. Database design. I. Teorey, Toby J. Database modeling & design.

QA76.9.D26T45 2011

005.75

6–dc22

2010049921

British Library Cataloguing-in-Publication Data

A catalogue record for this book is available from the British Library.

For information on all Morgan Kaufmann publications,

visit our Web site at www.mkp.com or www.elsevierdirect.com

Printed in the United States of America

1112131415 54321

To Julie, for her wonderful support

—Toby Teorey

To my wife and children, Elisheva, Hodaya, and Avishai

—Sam Lightstone

To Carol, Paula, Mike, and Lagi

—Tom Nadeau

To Aradhna, Siddhant, and Kamya

—H V Jagadish

PREFACE

Database design technology has undergone significant

evolution in recent years, although business applications

continue to be dominated by the relational data model

and relational database systems. The relational model has

allowed the database designer to separately focus on logi-

cal design (defining the data relationships and tables) and

physical design (efficiently storing data onto and retrieving

data from physical storage). Other new technologies such

as data warehousing, OLAP, and data mining, as well as

object-oriented, spatial, and Web-based data access, have

also had an important impact on database design.

In this fifth edition, we continue to concentrate on tech-

niques for database design in relational database systems.

However, because of the vast and explosive changes in

new physical database design techniques in recent years, we

have reorganized the topics into two separate books: Data-

base Mod eling and Design: Logical Design (5

Edition) and

Physical Database Design: The Database Professional’s Guide

Edition)

Logical database design is largely the domain of applica-

tion designers, who design the logical structure of the data-

base to suit application requirements for data manipulation

and structured queries. The definition of database tables for

a particular vendor is considered to be within the domain

of logical design in this book, although many database

practitioners refer to this step as physical design.

Physical database design, in the context of these two

books, is performed by the implementers of the database

servers, usually database administrators (DBAs) who must

decide how to structure the database for a particular

machine (server), and optimize that structure for system

performance and system administration. In smaller com-

panies these communities may in fact be the same people,

but for large enterprises they are very distinct.

We start the discussion of logical database design with the

entity-relationship (ER) approach for data requirements

specification and conceptual modeling. We then take a

detailed look at another dominating data modeling approach,

the Unified Modeling Language (UML). Both approaches are

used throughout the text for all the data modeling examples,

so the user can select either one (or both) to help follow the

logical design methodology. The discussion of basic

principles is supplemented with common examples that are

based on real-life experiences.

Organization

The database life cycle is described in Chapter 1. In Chap-

ter 2, we present the most fundamental concepts of data

modeling and provide a simple set of notational constructs

(the Chen notation for the ER model) to represent them.

The ER model has traditionally been a very popular method

of conceptualizing users’ data requirements. Chapter 3

introduces the UML notation for data modeling. UML (actu-

ally UML-2) has become a standard method of modeling

large-scale systems for object-oriented languages such as

C++ and Java, and the data modeling component of UML

is rapidly becoming as popular as the ER model. We feel it

is important for the reader to understand both notations

and how much they have in common.

Chapters 4 and 5 show how to use data modeling con-

cepts in the database design process. Chapter 4 is devoted

to direct application of conceptual data modeling in logical

database design. Chapter 5 explains the transformation of

the conceptual model to the relational model, and to

Structured Query Language (SQL) syntax specifically.

Chapter 6 is devoted to the fundamentals of database

normalization through third normal form and its variation,

Boyce-Codd normal form, showing the functional equiva-

lence between the conceptual model (both ER and UML)

and the relational model for third normal form.

The case study in Chapter 7 summarizes the techniques

presented in Chapters 1 through 6 with a new problem

environment.

Chapter 8 illustrates the basic features of object-oriented

database systems and how they differ from relational data-

base systems. An “impedance mismatch” problem often

arises due to data being moved between tables in a

x PREFACE

relational database and objects in an application program.

Extensions made to relational systems to handle this prob-

lem are described.

Chapter 9 looks at Web technologies and how they

impact databases and database design. XML is perhaps

the best known Web technology. An overview of XML is

given, and we explore database design issues that are spe-

cific to XML.

Chapter 10 describes the major logical database design

issues in business intelligence - data warehousing, online

analytical processing (OLAP) for decision support systems,

and data mining.

Chapter 11 discusses three of the currently most popu-

lar software tools for logical design: IBM’s Rational Data

Architect, Computer Associates’ AllFusion ERwin Data

Modeler, and Sybase’s PowerDesigner. Examples are given

to demonstrate how each of these tools can be used to

handle complex data modeling problems.

The Appendix contains a review of the basic data definition

and data manipulation components of the relational database

query language SQL (SQL-99) for those readers who lack

familiarity with database query languages. A simple example

database is used to illustrate the SQL query capability.

The database practitioner can use this book as a guide

to database modeling and its application to database

design for business and office environments and for well-

structured scientific and engineering databases. Whether

you are a novice database user or an experienced profes-

sional, this book offers new insights into database modeling

and the ease of transition from the ER model or UML

model to the relational model, including the building of

standard SQL data definitions. Thus, no matter whether

you are using IBM’s DB2, Oracle, Microsoft’s SQL Server,

Access, or MySQL for example, the design rules set forth

here will be applicable. The case studies used for the

examples throughout the book are from real-life databases

that were designed using the principles formulated here.

This book can also be used by the advanced undergraduate

or beginning graduate student to supplement a course

textbook in introductory database management, or for a

stand-alone course in data modeling or database design.

PREFACE xi

Typographical Conventions

For easy reference, entity and class names (Employee,

Department, and so on) are capitalized from Chapter 2 for-

ward. Throughout the book, relational table names (pro-

duct, product_count) are set in boldface for readability.

Acknowledgments

We wish to acknowledge colleagues that contributed to the

technical continuity of this book: James Bean, Mike Blaha,

Deb Bolton, Joe Celko, Jarir Chaar, Nauman Chaudhry, David

Chesney, David Childs, Pat Corey, John DeSue, Yang

Dongqing, Ron Fagin, Carol Fan, Jim Fry, Jim Gray, Bill Grosky,

Wei Guangping, Wendy Hall, Paul Helman, Nayantara Kalro,

John Koenig, Ji-Bih Lee, Marilyn Mantei Tremaine, Bongki

Moon, Robert Muller, Wee-Teck Ng, Dan O’Leary, Kunle

Olukotun, Dorian Pyle, Dave Roberts, Behrooz Seyed-

Abbassi, Dan Skrbina, Rick Snodgrass, Il-Yeol Song, Dick

Spencer, Amjad Umar, and Susanne Yul. We also wish to

thank the Department of Electrical Engineering and Com-

puter Science (EECS), especially Jeanne Patterson, at the Uni-

versity of Michigan for providing resources for writing and

revising. Finally, thanks for the generosity of our wives and

children that has permitted us the time to work on this text.

Solutions Manual

A solutions manual to all exercises is available. Contact

the publisher for further information.

xii PREFACE