Preface

Database systems and database design technology have undergone significant evolution in recent years as business applications have been dominated by the relational data model and relational database systems. The relational model has allowed the database designer to separately focus on logical 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, temporal, and multimedia databases, have also had an important impact on database design.

In this fourth edition, we continue to concentrate on techniques 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:

• Database Modeling and Design: Logical Design (4th Edition))
• Physical Database Design

Logical database design is largely the domain of application designers, who design the logical structure of the database 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 companies, these communities may in fact comprise 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 to data requirements specification and conceptual modeling; then, we take a detailed look at another dominating data modeling approach, the Unified Modeling Language (UML). Both approaches are used throughout the text for all 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 and have been thoroughly classroom tested.

Organization

The database life cycle is described in Chapter 1. In Chapter 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 popular method of conceptualizing users’ data requirements. Chapter 3 introduces the UML notation for data modeling. UML (actually 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 concepts in the database design process. Chapter 4 is devoted to the 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 fifth normal form, showing the functional equivalence between the conceptual model (both ER and UML) and the relational model for the higher normal forms.

The case study in Chapter 7 summarizes the techniques presented in Chapters 1 through 6 with a new problem environment.

Chapter 8 describes the major logical database design issues in business intelligence—data warehousing, online analytical processing (OLAP) for decision support systems, and data mining.

Chapter 9 discusses three of the currently most popular software tools for logical design: IBM’s Rational Data Architect, Computer Associates’ AllFusion ERwin Data Modeller, and Sybase 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 with three tables 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 professional, this book offers new insights into database modeling and the ease of transition from the ER 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, or MySQL, 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.

Typographical Conventions

For easy reference, entity and class names (Employee, Department, and so on) are capitalized from Chapter 2 forward. Throughout the book, table names (product, product_count) are set in boldface for readability.

Acknowledgments

We wish to acknowledge colleagues and students that contributed to the technical continuity of this book: James Bean, Mike Blaha, Deb Bolton, Joe Celko, Jarir Chaar, Nauman Chaudhry, David Chesney, 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 Computer Science (EECS) at the University of Michigan for providing computer resources for writing and revising. Finally, thanks to Julie for offering Ludington and her unwavering support (TJT), and thanks for the generosity of my wife and children, who have permitted me the time to work on this text (SL).

Solutions Manual

A solutions manual to all exercises is available. Contact the publisher for further information.