Database Design Using Entity-Relationship Diagrams (S. Bagui, R. Earp) (Z-Library)

📄 File Format: PDF

💾 File Size: 5.1 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

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, T ird 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. T is 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.

📄 Page 3

(This page has no text content)

📄 Page 4

Database Design Using Entity-Relationship Diagrams T ird Edition Sikha Saha Bagui Richard Walsh Earp

📄 Page 5

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.S. 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. copyright.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.co.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. ISBN: 978-1-032-01718-1 (hbk) ISBN: 978-1-032-32321-3 (pbk) ISBN: 978-1-003-31445-5 (ebk) DOI: 10.1201/9781003314455 Typeset in Minion by Apex CoVantage, LLC

📄 Page 6

Dedicated to my father, Santosh Saha; mother, Ranu Saha; husband, Subhash; sons, Sumon and Sudip; brother, Pradeep Saha; and nieces, Priyashi and Piyali. S.S.B. 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. R.W.E.

📄 Page 7

(This page has no text content)

📄 Page 8

Contents Preface ..................................................................................................xix Acknowledgments ............................................................................ xxiii Authors ................................................................................................ xxv Introduction ..................................................................................... xxvii Chapter 1 Data, Databases, and the Software Engineering Process ........................................................... 1 1.1 Introduction ......................................................................1 1.2 Data .....................................................................................1 1.3 Building a Database ..........................................................2 1.4 What Is the Sof ware Engineering Process? .....................3 1.5 Entity-Relationship Diagrams and the Sof ware Engineering Life Cycle ....................................7 1.5.1 Phase 1: Get the Requirements for the Database .........................................................8 1.5.2 Phase 2: Specify the Database ............................8 1.5.3 Phase 3: Design the Database ............................9 1.6 Chapter Summary ............................................................9 Chapter 1 Exercises ...................................................................10 Bibliography ...............................................................................10 Chapter 2 Data and Data Models ..................................................... 11 2.1 Introduction ....................................................................11 2.2 Files, Records, and Data Items ......................................11 2.3 Moving From 3 × 5 Cards to Computers ....................14 2.4 Database Models .............................................................19 2.4.1 T e Hierarchical Model ................................... 20 2.4.1.1 T e Hierarchical Model with a Linked List ..............................24 2.4.1.2 Relationship Terminology ................26 2.4.1.3 Drawbacks of the Hierarchical Model ............................27 2.5 T e Network Model ....................................................... 28 vii

📄 Page 9

viii • Contents 2.6 T e Relational Model .....................................................31 2.7 Chapter Summary ..........................................................32 Bibliography ...............................................................................32 Chapter 3 The Relational Model and Functional Dependencies .................................................................... 33 3.1 Introduction ....................................................................33 3.2 Fundamentals of Relational Database .........................33 3.3 Relational Database and Sets ........................................36 3.4 Functional Dependency .................................................38 3.5 Non-1NF to 1NF ............................................................ 40 3.6 T e Second Normal Form ............................................ 44 3.6.1 Anomalies .......................................................... 48 3.6.2 Non-2NF to 2NF ................................................49 3.7 T e T ird Normal Form ................................................52 3.8 T e Equijoin Operation .................................................57 3.9 Some Functional Dependency Rules ...........................59 3.10 T e Boyce–Codd Normal Form ...................................65 3.11 Chapter Summary ......................................................... 68 Chapter 3 Exercises ...................................................................68 Bibliography ...............................................................................69 Chapter 4 The Basic ER Diagram : A Data Modeling Schema ........ 71 4.1 Introduction ....................................................................71 4.2 What Is a Data Modeling Schema? ..............................71 4.2.1 What Is an Entity-Relationship Diagram?.............................................................72 4.3 Def ning a Database—Some Def nitions: Entity, Relationship, and Attribute ..............................73 4.3.1 A Beginning Methodology ...............................74 4.3.2 ER Design Methodology ...................................75 4.4 A First “Entity-Only” ER Diagram: An Entity with Attributes ..............................................76 4.5 More about Attributes ....................................................79 4.5.1 T e Simple or Atomic Attribute ......................79 4.5.2 T e Composite Attribute ................................. 80 4.5.3 T e Multivalued Attribute ................................81

📄 Page 10

Contents • ix 4.5.4 T e Derived Attribute .......................................82 4.5.5 Keys .....................................................................82 4.6 English Description of the Entity .................................87 4.6.1 T e Method ........................................................87 4.6.1.1 T e Entity ............................................87 4.6.1.2 T e Attributes .....................................87 4.6.1.3 T e Keys ............................................. 88 4.6.2 ER Design Methodology .................................. 88 4.6.3 Examples ............................................................ 88 4.6.3.1 Figure 4.3 Example ............................89 4.6.3.2 Figure 4.4 Example ........................... 90 4.6.3.3 Figure 4.5a Example ..........................91 4.6.3.4 Figure 4.6 Example ............................91 4.6.3.5 Figure 4.7 Example ............................92 4.7 Mapping the Entity Diagram to a Relational Database ........................................................94 4.8 Chapter Summary ......................................................... 99 Chapter 4 Exercises ...................................................................99 Case Study ................................................................................ 100 Notes ......................................................................................... 104 Bibliography ............................................................................ 104 Chapter 5 Beyond the First Entity Diagram .................................. 107 5.1 Introduction ..................................................................107 5.2 Examining an Entity: Changing an Attribute to Be an Entity .............................................108 5.3 Def ning a Relationship for Our New Entity ............109 5.3.1 ER Design Methodology .................................111 5.4 A Preliminary Grammar for ER Diagrams ..............112 5.4.1 T e Relationship ..............................................112 5.5 Def ning a Second Entity .............................................112 5.6 Does a Relationship Exist? ...........................................117 5.7 Attribute or Relationship? ...........................................117 5.7.1 ER Design Methodology .................................118 5.8 Chapter Summary ........................................................119 Chapter 5 Exercises ................................................................ 119 Case Study ................................................................................ 120 Bibliography ............................................................................ 128

📄 Page 11

x • Contents Chapter 6 Extending Relationships/Structural Constraints ..................................................................... 129 6.1 Introduction ..................................................................129 6.2 T e Cardinality Ratio of a Relationship ....................129 6.2.1 One to One (1:1) ...............................................131 6.2.2 Many to One (M:1) ..........................................131 6.2.3 One to Many (1:M) ..........................................132 6.2.4 Many to Many (M:N) ......................................132 6.3 Participation: Full/Partial .......................................... 134 6.4 English Descriptions ....................................................135 6.5 Tighter English ..............................................................137 6.5.1 Pattern 1—x:y::k:1 ............................................139 6.5.2 Pattern 2—x:y::k:1 ...........................................139 6.5.3 Pattern 3—x:y::k:M .........................................140 6.5.4 Pattern 4—x:y::k:M .........................................140 6.5.5 Summary of the Patterns and Relationships ....................................................141 6.5.5.1 Pattern 1 ............................................141 6.5.5.2 Pattern 1 ............................................141 6.5.5.3 Pattern 2 ............................................142 6.5.5.4 Pattern 2 ............................................143 6.5.5.5 Pattern 3 ............................................143 6.5.5.6 Pattern 3 ............................................143 6.5.5.7 Pattern 4 ............................................144 6.5.5.8 Pattern 4 ............................................145 6.5.6 ER Design Methodology .................................146 6.6 Some Examples of Other Relationships .....................147 6.6.1 An Example of the One-to-Many Relationship (1:M) ...........................................147 6.6.1.1 Pattern 4–1:M, From the 1 Side, Partial Participation ............148 6.6.1.2 Pattern 2—M(Partial):1, From M Side, Optional Participation .....................................149 6.6.2 An Example of the Many-to-One Relationship (M:1) ...........................................150 6.6.2.1 Pattern 1—M:1, From the M Side, Full Participation ...............150

📄 Page 12

Contents • xi 6.6.2.2 Pattern 3–1:M, From the 1 Side, Full Participation .................151 6.6.3 An Example of the Many-to-Many Relationship (M:N) ..........................................151 6.6.3.1 Pattern 3—M:N, From the M Side, Full Participation ...............152 6.6.3.2 Pattern 4—N:M, From the N Side, Partial Participation ...........152 6.7 One Final Example .......................................................153 6.7.1 ER Design Methodology ................................ 154 6.7.1.1 T e Entity ..........................................155 6.7.1.2 T e Entity ..........................................156 6.7.2 Pattern 1—M:1, From the M Side, Full Participation .............................................158 6.7.3 Pattern 3–1:M, From the 1 Side, Full Participation .............................................159 6.8 Mapping Relationships to a Relational Database ......................................................159 6.8.1 Mapping Binary M:N Relationships .............159 6.8.2 Mapping Binary 1:1 Relationships ................161 6.8.3 Mapping Binary 1:N Relationships ...............167 6.9 Chapter Summary ........................................................168 Cha pter 6 Exercises ................................................................ 168 Case Study ................................................................................ 172 Note .......................................................................................... 178 Bibliography ............................................................................ 178 Chapter 7 The Weak Entity ............................................................. 179 7.1 Introduction ..................................................................179 7.2 Strong and Weak Entities ............................................179 7.3 Weak Entities and Structural Constraints ................184 7.4 Weak Entities and the Identifying Owner ................184 7.4.1 Another Example of a Weak Entity and the Identifying Owner .................186 7.5 Weak Entities Connected to Other Weak Entities ................................................................186 7.6 Revisiting the Methodology ........................................189 7.7 Weak Entity Grammar ............................................... 190

📄 Page 13

xii • Contents 7.7.1 T e Keys ........................................................... 190 7.8 Mapping Weak Entities to a Relational Database ......................................................191 7.9 Chapter Summary ........................................................193 Chapter 7 Exercises ................................................................ 194 Case Study ................................................................................ 194 Bibliography ............................................................................ 200 Chapter 8 Further Extensions for ER Diagrams with Binary Relationships ............................................. 201 8.1 Introduction ..................................................................201 8.2 Attributes of Relationships ..........................................201 8.2.1 T e Attributes ................................................. 203 8.3 Relationships Developing into Entities: T e M:N Relationship Revisited ................................ 204 8.3.1 T e Entity ........................................................ 205 8.3.1.1 T e Attributes .................................. 206 8.3.1.2 T e Keys ........................................... 206 8.4 More Entities and Relationships ................................ 206 8.4.1 MoreT an Two Entities ................................ 206 8.4.1.1 Pattern 4—x:y::1:M, From the 1 Side, Partial Participation .................................... 207 8.4.1.2 Pattern 1—x:y::M:1, From the M Side, Full Participation .................................... 207 8.4.2 Adding More Attributes T at Evolve into Entities ................................ 209 8.5 More Evolution of the Database .................................213 8.6 Attributes T at Evolve into Entities ...........................213 8.7 Recursive Relationships ...............................................216 8.7.1 Recursive Relationships and Structural Constraints ....................................219 8.7.1.1 One-to-One Recursive Relationship (Partial Participation on Both Sides) ...........219 8.7.1.2 One-to-Many Recursive Relationship (Partial Participation on Both Sides) .......... 220

📄 Page 14

Contents • xiii 8.7.1.3 Many-to-Many Recursive Relationship (Partial on Both Sides) ....................................... 220 8.8 Multiple Relationships ................................................ 222 8.9 T e Derived or Redundant Relationship .................. 224 8.10 Optional: An Alternative ER Notation for Specifying Structural Constraints on Relationships ........................................................... 228 8.11 Review of the Methodology ....................................... 230 8.11.1 ER Design Methodology ................................ 230 8.11.2 T e Entity .........................................................231 8.11.2.1 T e Attributes ...................................231 8.11.2.2 T e Keys ............................................232 8.12 Mapping Rules for Recursive Relationships .............232 8.13 Chapter Summary ....................................................... 234 Chapter 8 Exercises ................................................................ 234 Case Study ................................................................................ 235 Bibliography ............................................................................ 238 Chapter 9 Ternary and Higher-Order ER Diagrams ..................... 239 9.1 Introduction ..................................................................239 9.2 Binary or Ternary Relationship? ............................... 240 9.3 Structural Constraints for Ternary Relationships ................................................................ 243 9.3.1 Many to Many to Many (M1:M2:M3) ............. 243 9.4 An Example of an n -ary Relationship ...................... 245 9.5 n -ary Relationships Do Not Preclude Binary Relationships ................................................... 246 9.6 Methodology and Grammar for the n -ary Relationship ....................................................... 247 9.6.1 A More Exact Grammar ................................ 249 9.6.1.1 Pattern 3—M:N, From the M Side, Full Participation ....... 249 9.6.1.2 Pattern 3—k:M, from the k Side, Full Participation (k = 1 or N) ...................................... 249 9.6.1.3 Pattern 5 ( n -ary)—x:y:z::a:b:c, From the a Side, Full/Partial Participation .................................... 250

📄 Page 15

xiv • Contents 9.6.2 Grammar in a Partial Participation, Ternary Relationship with an M1:1:M2 Relationship ......................................................251 9.7 Ternary Relationships From Relationship-Relationship Situations .........................252 9.8 n-a ry Relationships T at May Be Resolved into Binary Relationships ........................... 254 9.9 Mapping n -ary Relationships to a Relational Database ......................................................259 9.10 Review of the Methodology ....................................... 260 9.10.1 ER Design Methodology ................................ 260 9.11 Chapter Summary ........................................................261 Chapter 9 Exercises ................................................................ 262 Bibliography ............................................................................ 263 Chapter 10 The Enhanced Entity-Relationship (EER) Model ................................................................... 265 10.1 Introduction................................................................. 265 10.2 What Is a Generalization or Specialization? ............... 265 10.3 Variants ........................................................................ 267 10.4 Examples of Generalizations or Specializations ............................................................. 268 10.5 Methodology and Grammar for Generalization/ Specialization Relationships .......................................274 10.6 Mapping Rules for Generalizations and Specializations ......................................................276 10.6.1 Mapping Rule 15 ............................................. 277 10.6.2 Mapping Rule 16 ............................................. 280 10.6.3 Mapping Rule 17 ..............................................281 10.6.4 Mapping Rule 18 ............................................. 282 10.7 Subclasses of Subclasses ............................................. 283 10.7.1 Mapping Rule 19 ............................................. 285 10.8 Categories or Union Types ........................................ 286 10.8.1 Participation Ratios in Categories or Union Types ............................ 288 10.8.2 Mapping Categories or Union Types When Superclasses Have the Same Primary Keys ..................................291

📄 Page 16

Contents • xv 10.8.3 Mapping Categories or Union Types When Superclasses Have Dif erent Primary Keys ...................................291 10.9 Final ER Design Methodology .................................. 292 10.9.1 ER Design Methodology .................................293 10.10 Chapter Summary ...................................................... 294 Cha pter 10 Exercises .............................................................. 294 Case Study ................................................................................ 295 Bibliography ............................................................................ 298 Chapter 11 Relational Mapping and Reverse Engineering ER/EER Diagrams .................................... 299 11.1 Introduction ................................................................. 299 11.2 Steps Used to Map ER/EER Diagrams to Relational Databases ............................................... 299 11.3 Reverse Engineering .................................................... 306 11.3.1 Reverse Engineering Rule 1. Develop Strong Entities ................................ 307 11.3.2 Reverse Engineering Rule 2. Look for 1:1 and 1:N (1:x) Relationships .............. 308 11.3.3 Reverse Engineering Rule 2a. Check for Attributes of the 1:x Relationship ..........311 11.3.4 Reverse Engineering Rule 3. Look for Weak Entities and Multivalued Attributes ........................................................311 11.3.5 Reverse Engineering Rule 3a. Checking for Weak Entities ..........................314 11.3.6 Reverse Engineering Rule 3b. Checking for Multivalued Attributes .............314 11.3.7 Reverse Engineering Rule 4. Check for M:N and n -ary Relationships .................316 11.3.8 Reverse Engineering Rule 4a. Check for the Binary Case ............................316 11.3.9 Reverse Engineering Rule 4b. Check for the n -ary Case ..............................316 11.3.10 Reverse Engineering Rule 5. Check for Generalization/Specialization Relationships ...................................................318

📄 Page 17

xvi • Contents 11.3.11 Reverse Engineering Rule 5a. Check for Generalization/Specialization Relationships with Disjoint or Overlap Relationships with Total or Partial Participation Constraints ............319 11.3.12 Reverse Engineering Rule 5b. Check for Disjoint Generalization/Specialization Relationships with Single-Predicate- Def ned Attributes ........................................ 320 11.3.13 Reverse Engineering Rule 5c. Check for Overlap Generalization/Specialization Relationship with More T an One Flag ........ 321 11.3.14 Reverse Engineering Rule 6. Check for Shared Subclasses .....................................321 11.3.15 Reverse Engineering Rule 7. Check for Categories or Union Types .....................321 11.4 Chapter Summary ........................................................322 Cha pter 11 Exercises ................................................................323 Bibliography ..............................................................................324 Chapter 12 A Brief Overview of the Barker/Oracle-Like Model .............................................................................. 325 12.1 Introduction..................................................................325 12.2 A First “Entity-Only” ER Diagram: An Entity with Attributes .......................................... 326 12.3 Attributes in the Barker/Oracle-Like Model .............................................................................327 12.3.1 Optional versus Mandatory Attributes ..........................................................327 12.4 Relationships in the Barker/Oracle-Like Model .............................................................................329 12.5 Structural Constraints in the Barker/ Oracle-Like Model .......................................................329 12.6 Dealing with the Concept of the Weak Entity in the Barker/Oracle-Like Model ...................332 12.7 Dealing with the Concept of Multivalued Attributes in the Barker/Oracle-Like Model ............333 12.8 Treatment of Foreign Keys .........................................336

📄 Page 18

Contents • xvii 12.9 Recursive Relationships in the Barker/ Oracle-Like Model .......................................................336 12.10 Mapping M:N Relationships ......................................336 12.11 Chapter Summary .......................................................340 Chapter 12 Exercises ................................................................340 Bibliography ..............................................................................341 Glossary ............................................................................................... 343 Index .................................................................................................... 347

📄 Page 19

(This page has no text content)

📄 Page 20

Preface Data modeling and database design have undergone signif cant 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. T is 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. BOOK HIGHLIGHTS Tis book focuses on data modeling using entity-relationship (ER) dia- grams, presenting (a) an Entity-Relationship (ER) design methodology for developing an ER diagram; (b) a grammar for the ER diagrams that can be presented back to the user, facilitating agile database development; and (c) mapping rules to map the ER diagram to a relational database. T e steps for the ER design methodology, the grammar for the ER diagrams, as well as the mapping rules are developed and presented in a system- atic step-by-step manner throughout the book. Also, several examples of xix