Database Design Using Entity-Relationship Diagrams Essential to database design, entity-relationship (ER) diagrams are known for their usefulness in data modeling and mapping out clear database designs. Tey are also well- known for being difcult to master. With Database Design Using Entity-Relationship Diagrams, Tird Edition, database designers, developers, and students preparing to enter the feld can quickly learn the ins and outs of data modeling through ER diagramming. Building on the success of the bestselling frst and second editions, this accessible text includes a new chapter on the relational model and functional dependencies.
It also includes expanded chapters on Enhanced Entity-Relationship (EER) diagrams and reverse mapping. It uses cutting-edge case studies and examples to help readers master database development basics and defnes ER and EER diagramming in terms of requirements (end user requests) and specifcations (designer feedback to those requests), facilitating agile database development. Tis book • Describes a step-by-step approach for producing an ER diagram and developing a relational database from it • Contains exercises, examples, case studies, bibliographies, and summaries in each chapter • Details the rules for mapping ER diagrams to relational databases • Explains how to reverse engineer a relational database back to an entity- relationship model • Includes grammar for the ER diagrams that can be presented back to the user, facilitating agile database development The updated exercises and chapter summaries provide the real-world understanding needed to develop ER and EER diagrams, map them to relational databases, and test the resulting relational database. Complete with a wealth of additional exercises and examples throughout, this edition should be a basic component of any database course.
Its comprehensive nature and easy-to-navigate structure make it a resource that students and professionals will turn to throughout their careers. Database Design Using Entity-Relationship Diagrams Tird Edition Sikha Saha Bagui Richard Walsh Earp Tird Edition published 2023 by CRC Press 6000 Broken Sound Parkway NW, Suite 300, Boca Raton, FL 33487–2742 and by CRC Press 4 Park Square, Milton Park, Abingdon, Oxon, OX14 4RN CRC Press is an imprint of Taylor & Francis Group, LLC © 2023 Sikha Saha Bagui and Richard Walsh Earp First edition published by CRC Press 2003 Second edition published by CRC Press 2011 Reasonable eforts have been made to publish reliable data and information, but the author and publisher cannot assume responsibility for the validity of all materials or the consequences of their use. Te authors and publishers have attempted to trace the copyright holders of all material reproduced in this publication and apologize to copyright holders if permission to publish in this form has not been obtained. If any copyright material has not been acknowledged please write and let us know so we may rectify in any future reprint.
Except as permitted under U. Copyright Law, no part of this book may be reprinted, reproduced, transmitted, or utilized in any form by any electronic, mechanical, or other means, now known or hereafer invented, including photocopying, microflming, and recording, or in any information storage or retrieval system, without written permission from the publishers. For permission to photocopy or use material electronically from this work, access www.com or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA 01923, 978–750–8400.
For works that are not available on CCC please contact mpkbookspermissions@tandf.uk Trademark notice: Product or corporate names may be trademarks or registered trademarks and are used only for identifcation and explanation without intent to infringe.1201/9781003314455 Typeset in Minion by Apex CoVantage, LLC Dedicated to my father, Santosh Saha; mother, Ranu Saha; husband, Subhash; sons, Sumon and Sudip; brother, Pradeep Saha; and nieces, Priyashi and Piyali. Dedicated to my late wife, Brenda, and my children, Beryl, Rich, Gen, and Mary Jo. And to my wife, Anne, who encouraged me to update this work and meticulously edited every word. xxvii Chapter 1 Data, Databases, and the Software Engineering Process .4 What Is the Sofware Engineering Process? .5 Entity-Relationship Diagrams and the Sof ware Engineering Life Cycle .1 Phase 1: Get the Requirements for the Database .2 Phase 2: Specify the Database .3 Phase 3: Design the Database .10 Chapter 2 Data and Data Models .2 Files, Records, and Data Items .3 Moving From 3 × 5 Cards to Computers .1 Te Hierarchical Model .1 Te Hierarchical Model with a Linked List .3 Drawbacks of the Hierarchical Model .5 Te Network Model.
28 vii viii • Contents 2.6 Te Relational Model .32 Chapter 3 The Relational Model and Functional Dependencies .2 Fundamentals of Relational Database .3 Relational Database and Sets .5 Non-1NF to 1NF .6 Te Second Normal Form .2 Non-2NF to 2NF .7 Te Tird Normal Form .8 Te Equijoin Operation .9 Some Functional Dependency Rules .10 Te Boyce–Codd Normal Form .69 Chapter 4 The Basic ER Diagram: A Data Modeling Schema .2 What Is a Data Modeling Schema? .1 What Is an Entity-Relationship Diagram?.3 Defning a Database—Some Defnitions: Entity, Relationship, and Attribute .2 ER Design Methodology.4 A First “Entity-Only” ER Diagram: An Entity with Attributes .5 More about Attributes.1 Te Simple or Atomic Attribute .2 Te Composite Attribute .3 Te Multivalued Attribute.4 Te Derived Attribute .6 English Description of the Entity .2 ER Design Methodology.7 Mapping the Entity Diagram to a Relational Database. 104 Chapter 5 Beyond the First Entity Diagram .2 Examining an Entity: Changing an Attribute to Be an Entity .3 Defning a Relationship for Our New Entity .1 ER Design Methodology.4 A Preliminary Grammar for ER Diagrams .5 Defning a Second Entity .6 Does a Relationship Exist?.7 Attribute or Relationship? .1 ER Design Methodology. 128 x • Contents Chapter 6 Extending Relationships/Structural Constraints .2 Te Cardinality Ratio of a Relationship .1 One to One (1:1) .2 Many to One (M:1) .3 One to Many (1:M) .4 Many to Many (M:N) .3 Participation: Full/Partial .5 Summary of the Patterns and Relationships .6 ER Design Methodology.6 Some Examples of Other Relationships.1 An Example of the One-to-Many Relationship (1:M) .1 Pattern 4–1:M, From the 1 Side, Partial Participation .2 Pattern 2—M(Partial):1, From M Side, Optional Participation .2 An Example of the Many-to-One Relationship (M:1) .1 Pattern 1—M:1, From the M Side, Full Participation .2 Pattern 3–1:M, From the 1 Side, Full Participation .3 An Example of the Many-to-Many Relationship (M:N).1 Pattern 3—M:N, From the M Side, Full Participation .2 Pattern 4—N:M, From the N Side, Partial Participation .7 One Final Example .1 ER Design Methodology.2 Pattern 1—M:1, From the M Side, Full Participation .3 Pattern 3–1:M, From the 1 Side, Full Participation .8 Mapping Relationships to a Relational Database .1 Mapping Binary M:N Relationships .2 Mapping Binary 1:1 Relationships .3 Mapping Binary 1:N Relationships. 178 Chapter 7 The Weak Entity .2 Strong and Weak Entities .3 Weak Entities and Structural Constraints .4 Weak Entities and the Identifying Owner .1 Another Example of a Weak Entity and the Identifying Owner.5 Weak Entities Connected to Other Weak Entities .6 Revisiting the Methodology .7 Weak Entity Grammar .8 Mapping Weak Entities to a Relational Database.
200 Chapter 8 Further Extensions for ER Diagrams with Binary Relationships .2 Attributes of Relationships .3 Relationships Developing into Entities: Te M:N Relationship Revisited .4 More Entities and Relationships .1 More Tan Two Entities .1 Pattern 4—x:y::1:M, From the 1 Side, Partial Participation .2 Pattern 1—x:y::M:1, From the M Side, Full Participation .2 Adding More Attributes Tat Evolve into Entities .5 More Evolution of the Database .6 Attributes Tat Evolve into Entities .1 Recursive Relationships and Structural Constraints .1 One-to-One Recursive Relationship (Partial Participation on Both Sides) .2 One-to-Many Recursive Relationship (Partial Participation on Both Sides) .3 Many-to-Many Recursive Relationship (Partial on Both Sides) .9 Te Derived or Redundant Relationship .10 Optional: An Alternative ER Notation for Specifying Structural Constraints on Relationships .11 Review of the Methodology .1 ER Design Methodology.12 Mapping Rules for Recursive Relationships. 238 Chapter 9 Ternary and Higher-Order ER Diagrams .2 Binary or Ternary Relationship? .3 Structural Constraints for Ternary Relationships .1 Many to Many to Many (M1:M2:M3).4 An Example of an n-ary Relationship .5 n-ary Relationships Do Not Preclude Binary Relationships .6 Methodology and Grammar for the n-ary Relationship .1 A More Exact Grammar .1 Pattern 3—M:N, From the M Side, Full Participation .2 Pattern 3—k:M, from the k Side, Full Participation (k = 1 or N) .3 Pattern 5 (n-ary)—x:y:z::a:b:c, From the a Side, Full/Partial Participation .2Grammar in a Partial Participation, Ternary Relationship with an M1:1:M2 Relationship .7 Ternary Relationships From Relationship-Relationship Situations .8 n-ary Relationships Tat May Be Resolved into Binary Relationships.9 Mapping n-ary Relationships to a Relational Database .10 Review of the Methodology .1 ER Design Methodology. 263 Chapter 10 The Enhanced Entity-Relationship (EER) Model .2 What Is a Generalization or Specialization? .4 Examples of Generalizations or Specializations .5 Methodology and Grammar for Generalization/ Specialization Relationships .6 Mapping Rules for Generalizations and Specializations .7 Subclasses of Subclasses .8 Categories or Union Types .1 Participation Ratios in Categories or Union Types .2 Mapping Categories or Union Types When Superclasses Have the Same Primary Keys .3 Mapping Categories or Union Types When Superclasses Have Diferent Primary Keys .9 Final ER Design Methodology .1 ER Design Methodology. 298 Chapter 11 Relational Mapping and Reverse Engineering ER/EER Diagrams .2 Steps Used to Map ER/EER Diagrams to Relational Databases .1 Reverse Engineering Rule 1.
Develop Strong Entities.2 Reverse Engineering Rule 2. Look for 1:1 and 1:N (1:x) Relationships .3 Reverse Engineering Rule 2a. Check for Attributes of the 1:x Relationship .4 Reverse Engineering Rule 3. Look for Weak Entities and Multivalued Attributes .5 Reverse Engineering Rule 3a.
Checking for Weak Entities .6 Reverse Engineering Rule 3b. Checking for Multivalued Attributes .7 Reverse Engineering Rule 4. Check for M:N and n-ary Relationships .8 Reverse Engineering Rule 4a. Check for the Binary Case .9 Reverse Engineering Rule 4b.
Check for the n-ary Case .10 Reverse Engineering Rule 5. Check for Generalization/Specialization Relationships.11 Reverse Engineering Rule 5a. Check for Generalization/Specialization Relationships with Disjoint or Overlap Relationships with Total or Partial Participation Constraints.12 Reverse Engineering Rule 5b. Check for Disjoint Generalization/Specialization Relationships with Single-Predicate- Defned Attributes .13 Reverse Engineering Rule 5c.
Check for Overlap Generalization/Specialization Relationship with More Tan One Flag.14 Reverse Engineering Rule 6. Check for Shared Subclasses .15 Reverse Engineering Rule 7. Check for Categories or Union Types .324 Chapter 12 A Brief Overview of the Barker/Oracle-Like Model .2 A First “Entity-Only” ER Diagram: An Entity with Attributes .3 Attributes in the Barker/Oracle-Like Model .1 Optional versus Mandatory Attributes .4 Relationships in the Barker/Oracle-Like Model .5 Structural Constraints in the Barker/ Oracle-Like Model .6 Dealing with the Concept of the Weak Entity in the Barker/Oracle-Like Model .7 Dealing with the Concept of Multivalued Attributes in the Barker/Oracle-Like Model .8 Treatment of Foreign Keys .9 Recursive Relationships in the Barker/ Oracle-Like Model. 347 Preface Data modeling and database design have undergone signifcant evolu- tion in recent years.
Today, the relational data model and the relational database system dominate business applications. Te relational model has allowed the database designer to focus on the logical and physical char- acteristics of a database separately. In this book, we concentrate on tech- niques for database design with a very strong bias for relational database systems using the ER (entity-relationship) approach for conceptual model- ing (solely a logical implementation). INTENDED AUDIENCE Tis book is intended to be used for data modeling by database practitio- ners and students.
It is also intended to be used as a supplemental text in database courses, systems analysis and design courses, and other courses that design and implement databases. Many present-day database and sys- tems analysis and design books limit their coverage of data modeling. Tis book not only increases the exposure to data modeling concepts, but also presents a step-by-step approach to designing an ER diagram and devel- oping a relational database from it.