Database Design Using Entity-Relationship Diagrams, Second Edition (Bagui, Sikha Earp, Richard) (Z-Library)

📄 File Format: PDF

💾 File Size: 3.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

ISBN: 978-1-4398-6176-9 9 781439 861769 90000 Information Technology / Database Essential to database design, entity-relationship (ER) diagrams are known for their usefulness in mapping out clear database designs. They are also well- known for being difficult to master. With Database Design Using Entity- Relationship Diagrams, Second Edition, database designers, developers, and students preparing to enter the field can quickly learn the ins and outs of ER diagramming. Building on the success of the bestselling first edition, 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 defines ER and EER diagramming in terms of requirements (end user requests) and specifications (designer feedback to those requests). • 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 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 Diagram s S e co n d E d itio n B a g u i E a rp www.auerbach-publications.com K12857 www.crcpress.com K12857 cvr mech.indd 1 8/9/11 8:53 AM

📄 Page 2

Database Design Using Entity- Relationship Diagrams Second Edition

📄 Page 3

(This page has no text content)

📄 Page 4

Database Design Using Entity- Relationship Diagrams Second Edition Sikha Bagui and Richard Earp

📄 Page 5

CRC Press Taylor & Francis Group 6000 Broken Sound Parkway NW, Suite 300 Boca Raton, FL 33487-2742 © 2012 by Taylor & Francis Group, LLC CRC Press is an imprint of Taylor & Francis Group, an Informa business No claim to original U.S. Government works Version Date: 20110510 International Standard Book Number-13: 978-1-4398-6177-6 (eBook - PDF) This book contains information obtained from authentic and highly regarded sources. Reasonable efforts 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. The 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, transmit- ted, or utilized in any form by any electronic, mechanical, or other means, now known or hereafter invented, including photocopying, microfilming, 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, please access www.copyright. com (http://www.copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA 01923, 978-750-8400. CCC is a not-for-profit organization that provides licenses and registration for a variety of users. For organizations that have been granted a photocopy license by the CCC, a separate system of payment has been arranged. Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are used only for identification and explanation without intent to infringe. Visit the Taylor & Francis Web site at http://www.taylorandfrancis.com and the CRC Press Web site at http://www.crcpress.com

📄 Page 6

Dedicated to my late father-in-law, Paresh C. Bagui; late mother-in-law, Khodan B. Bagui; father, Santosh Saha; mother, Ranu Saha, and husband, Subhash Bagui S.B. Dedicated to my wife, Brenda, and my children, Beryl, Rich, Gen, and Mary Jo R.E.

📄 Page 7

(This page has no text content)

📄 Page 8

vii Contents Preface...................................................................................................xix Acknowledgments............................................................................. xxiii Introduction......................................................................................... xxv 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.Software.Engineering.Process?................3 1.5. Entity.Relationship.Diagrams.and.the.Software. Engineering.Life.Cycle...................................................6 1.5.1. Phase.1:.Get.the.Requirements.for.the. Database............................................................7 1.5.2. Phase.2:.Specify.the.Database........................7 1.5.3. Phase.3:.Design.the.Database........................8 1.6. Chapter.Summary...........................................................8 Chapter.1.Exercises......................................................................8 Bibliography..................................................................................9 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.......................................................... 20 2.4.1. The.Hierarchical.Model............................... 20 2.4.1.1. The.Hierarchical.Model.with. a.Linked.List...................................25 2.4.1.2. Relationship.Terminology............27 2.4.1.3. Drawbacks.of.the. Hierarchical.Model....................... 28 2.5. The.Network.Model......................................................29

📄 Page 9

viii  •  Contents 2.6. The.Relational.Model....................................................32 2.7. Chapter.Summary.........................................................33 Bibliography................................................................................33 Chapter 3 The.Relational.Model.and.Functional.Dependencies..... 35 3.1. Introduction...................................................................35 3.2. Fundamental.Relational.Database..............................35 3.3. Relational.Database.and.Sets.......................................38 3.4. Functional.Dependency...............................................39 3.5. Non-1NF.to.1NF.......................................................... 40 3.6. The.Second.Normal.Form.......................................... 43 3.6.1. Anomalies.......................................................47 3.6.2. Non-2NF.to.2NF........................................... 48 3.7. The.Third.Normal.Form..............................................50 3.8. The.Equijoin.Operation.............................................. 54 3.9. Some.Functional.Dependency.Rules......................... 56 3.10. The.Boyce.Codd.Normal.Form...................................63 3.11. Chapter.Summary.........................................................65 Chapter.3.Exercises....................................................................65 Bibliography............................................................................... 66 Chapter 4 The.Basic.ER.Diagram:.A.Data.Modeling.Schema......... 67 4.1. Introduction...................................................................67 4.2. What.Is.a.Data.Modeling.Schema?.............................67 4.2.1. So,.What.Is.an.Entity.Relationship. Diagram?....................................................... 68 4.3. Defining.a.Database—.Some.Definitions:. Entity,.Relationship,.Attribute....................................69 4.3.1. A.Beginning.Methodology...........................70 4.3.2. ER.Design.Methodology..............................71 4.4. A.First.“Entity-Only”.ER.Diagram:.An.Entity. with.Attributes..............................................................72 4.5. More.about.Attributes..................................................74 4.5.1. The.Simple.or.Atomic.Attribute..................75 4.5.2. The.Composite.Attribute..............................76 4.5.3. The.Multivalued.Attribute...........................77 4.5.4. The.Derived.Attribute...................................78 4.5.5. Keys.................................................................78

📄 Page 10

Contents  •  ix 4.6. English.Description.of.the.Entity...............................83 4.6.1. The.Method....................................................83 4.6.1.1. The.Entity........................................83 4.6.1.2. The.Attributes.................................83 4.6.1.3. The.Keys......................................... 84 4.6.2. ER.Design.Methodology............................. 84 4.6.3. Examples........................................................ 84 4.6.3.1. Figure 4.3.Example....................... 84 4.6.3.2. Figure 4.4.Example........................85 4.6.3.3. Figure 4.5a.Example..................... 86 4.6.3.4. Figure 4.6.Example........................87 4.6.3.5. Figure 4.7.Example....................... 88 4.7. Mapping.the.Entity.Diagram.to.a.Relational. Database.........................................................................89 4.8. Chapter.Summary.........................................................94 Chapter.4.Exercises....................................................................94 Bibliography................................................................................95 Case.Study.................................................................................. 96 Chapter 5 Beyond.the.First.Entity.Diagram................................... 101 5.1. Introduction.................................................................101 5.2. Examining.an.Entity:.Changing.an.Attribute. to.Be.an.Entity.............................................................102 5.3. Defining.a.Relationship.for.Our.New.Entity..........103 5.3.1. ER.Design.Methodology............................104 5.4. A.Preliminary.Grammar.for.the.ER.Diagrams......105 5.4.1. The.Relationship..........................................105 5.5. Defining.a.Second.Entity...........................................105 5.6. Does.a.Relationship.Exist?.........................................111 5.7. Attribute.or.Relationship?.........................................111 5.7.1. ER.Design.Methodology............................112 5.8. Chapter.Summary.......................................................113 Chapter.5.Exercises..................................................................113 Bibliography..............................................................................114 Case.Study.................................................................................114 Chapter 6 Extending.Relationships/Structural.Constraints......... 123 6.1. Introduction................................................................ 123 6.2. The.Cardinality.Ratio.of.a.Relationship................. 123

📄 Page 11

x  •  Contents 6.2.1. One.to.One.(1:1).......................................... 124 6.2.2. Many.to.One.(M:1)......................................125 6.2.3. One.to.Many.(1:M)......................................125 6.2.4. Many.to.Many.(M:N)..................................125 6.3. Participation:.Full/Partial......................................... 128 6.4. English.Descriptions..................................................130 6.5. Tighter.English............................................................131 6.5.1. Pattern.1—x:y::k:1........................................132 6.5.2. Pattern.2—x:y::k:1.......................................133 6.5.3. Pattern.3—x:y::k:M.....................................133 6.5.4. Pattern.4—x:y::k:M.................................... 134 6.5.5. Summary.of.the.Patterns.and. Relationships................................................135 6.5.5.1. Pattern.1........................................135 6.5.5.2. Pattern.1........................................135 6.5.5.3. Pattern.2........................................136 6.5.5.4. Pattern.2........................................136 6.5.5.5. Pattern.3........................................138 6.5.5.6. Pattern.3........................................138 6.5.5.7. Pattern.4........................................138 6.5.5.8. Pattern.4........................................140 6.5.6. ER.Design.Methodology............................141 6.6. Some.Examples.of.Other.Relationships...................141 6.6.1. An.Example.of.the.One-to-Many. Relationship.(1:M).......................................141 6.6.1.1. Pattern.4—1:M,.from.the.1. Side,.Partial.Participation...........143 6.6.1.2. Pattern.2—M(Partial):1,. from.M.Side,.Optional. Participation................................143 6.6.2. An.Example.of.the.Many-to-One. Relationship.(M:1).......................................144 6.6.2.1. Pattern.1—M:1,.from.the.M. Side,.Full.Participation................144 6.6.2.2. Pattern.3—1:M,.from.the.1. Side,.Full.Participation................145 6.6.3. An.Example.of.the.Many-to-Many. Relationship.(M:N).....................................145

📄 Page 12

Contents  •  xi 6.6.3.1. Pattern.3—M:N,.from.the.M. Side,.Full.Participation................146 6.6.3.2. Pattern.4—M:N,.from.the.M. Side,.Partial.Participation...........146 6.7. One.Final.Example.....................................................147 6.7.1. ER.Design.Methodology............................148 6.7.1.1. The.Entity......................................149 6.7.1.2. The.Entity......................................150 6.7.2. Pattern.1—M:1,.from.the.M.Side,.Full. Participation.................................................152 6.7.3. Pattern.3—1:M,.from.the.1.Side,.Full. Participation.................................................152 6.8. Mapping.Relationships.to.a.Relational. Database.......................................................................153 6.8.1. Mapping.Binary.M:N.Relationships.........153 6.8.2. Mapping.Binary.1:1.Relationships............155 6.8.3. Mapping.Binary.1:N.Relationships...........160 6.9. Chapter.Summary.......................................................161 Chapter.6.Exercises..................................................................162 Bibliography..............................................................................165 Case.Study.................................................................................165 Chapter 7 The.Weak.Entity............................................................. 171 7.1. Introduction.................................................................171 7.2. Strong.and.Weak.Entities..........................................171 7.3. Weak.Entities.and.Structural.Constraints..............176 7.4. Weak.Entities.and.the.Identifying.Owner..............176 7.4.1. Another.Example.of.a.Weak.Entity. and.the.Identifying.Owner........................178 7.5. Weak.Entities.Connected.to.Other.Weak. Entities..........................................................................179 7.6. Revisiting.the.Methodology......................................180 7.7. Weak.Entity.Grammar...............................................181 7.7.1. The.Keys........................................................181 7.8. Mapping.Weak.Entities.to.a.Relational. Database.......................................................................182 7.9. Chapter.Summary.......................................................184

📄 Page 13

xii  •  Contents Chapter.7.Exercises..................................................................185 Bibliography..............................................................................185 Case.Study.................................................................................186 Chapter 8 Further.Extensions.for.ER.Diagrams.with.Binary. Relationships................................................................... 193 8.1. Introduction.................................................................193 8.2. Attributes.of.Relationships........................................194 8.2.1. The.Attributes..............................................195 8.3. Relationships.Developing.into.Entities:.The. M:N.Relationship.Revisited.......................................196 8.3.1. The.Entity.....................................................198 8.3.1.1. The.Attributes...............................198 8.3.1.2. The.Keys........................................198 8.4. More.Entities.and.Relationships...............................198 8.4.1. More.than.Two.Entities..............................199 8.4.1.1. Pattern.4—x:y::1:M,.from.the. 1.Side,.Partial.Participation........199 8.4.1.2. Pattern.1—x:y::M:1,.from.the. M.Side,.Full.Participation.......... 200 8.4.2. Adding.More.Attributes.that.Evolve. into.Entities..................................................201 8.5. More.Evolution.of.the.Database............................... 203 8.6. Attributes.that.Evolve.into.Entities......................... 205 8.7. Recursive.Relationships............................................ 208 8.7.1. Recursive.Relationships.and. Structural.Constraints................................210 8.7.1.1. One-to-One.Recursive. Relationship.(Partial. Participation.on.Both.Sides).......210 8.7.1.2. One-to-Many.Recursive. Relationship.(Partial. Participation.on.Both.Sides).......211 8.7.1.3. Many-to-Many.Recursive. Relationship.(Partial.on.Both. Sides)..............................................211 8.8. Multiple.Relationships................................................213

📄 Page 14

Contents  •  xiii 8.9. The.Derived.or.Redundant.Relationship.................216 8.10. Optional:.An.Alternative.ER.Notation. for.Specifying.Structural.Constraints.on. Relationships.............................................................. 218 8.11. Review.of.the.Methodology.......................................221 8.11.1. ER.Design.Methodology............................221 8.11.2. The.Entity.................................................... 222 8.11.2.1. The.Attributes.............................. 222 8.11.2.2. The.Keys....................................... 222 8.12. Mapping.Rules.for.Recursive.Relationships........... 223 8.13. Chapter.Summary...................................................... 224 Chapter.8.Exercises................................................................. 225 Bibliography............................................................................. 226 Case.Study................................................................................ 226 Chapter 9 Ternary.and.Higher-Order.ER.Diagrams..................... 229 9.1. Introduction................................................................ 229 9.2. Binary.or.Ternary.Relationship?.............................. 230 9.3. Structural.Constraints.for.Ternary.Relationships.... 233 9.3.1. Many.to.Many.to.Many.(M:M:M)............233 9.4. An.Example.of.an.n-ary.Relationship......................235 9.5. n-ary.Relationships.Do.Not.Preclude.Binary. Relationships............................................................... 236 9.6. Methodology.and.Grammar.for.the.n-ary. Relationship.................................................................237 9.6.1. A.More.Exact.Grammar............................ 238 9.6.1.1. Pattern.3—M:N,.from.the.M. Side,.Full.Participation................239 9.6.1.2. Pattern.3—k:M,.from.the.k. Side,.Full.Participation.(k.=.1. or.M)..............................................239 9.6.1.3. Pattern.5.(n-ary)—x:y:z::a:b:c,. from.the.a.Side,.Full/Partial. Participation.................................239 9.6.2. Grammar.in.a.Partial.Participation,. Ternary.Relationship.with.an.M:1:M. Relationship................................................. 240

📄 Page 15

xiv  •  Contents 9.7. Ternary.Relationships.from. Relationship-Relationship.Situations...................... 242 9.8. n-ary.Relationships.that.May.Be.Resolved.into. Binary.Relationships.................................................. 244 9.9. Mapping.n-ary.Relationships.to.a.Relational. Database...................................................................... 247 9.10. Review.of.the.Methodology...................................... 249 9.10.1. ER.Design.Methodology........................... 249 9.11. Chapter.Summary...................................................... 250 Chapter.9.Exercises................................................................. 250 Bibliography..............................................................................251 Chapter 10 The.Enhanced.Entity.Relationship.(EER).Model......... 253 10.1. Introduction.................................................................253 10.2. What.Is.a.Generalization.or.Specialization?...........253 10.3. Variants........................................................................255 10.4. Examples.of.Generalizations.or.Specializations.... 256 10.5. Methodology.and.Grammar.for. Generalization/Specialization.Relationships......... 262 10.6. Mapping.Rules.for.Generalizations. and.Specializations.................................................... 264 10.6.1. Mapping.Rule.15......................................... 265 10.6.2. Mapping.Rule.16......................................... 267 10.6.3. Mapping.Rule.17......................................... 268 10.6.4. Mapping.Rule.18......................................... 269 10.7. Subclasses.of.Subclasses.............................................270 10.7.1. Mapping.Rule.19..........................................273 10.8. Categories.or.Union.Types........................................274 10.8.1. Participation.Ratios.in.Categories.or. Union.Types.................................................276 10.8.2. Mapping.Categories.or.Union.Types. When.Superclasses.Have.the.Same. Primary.Keys...............................................278 10.8.3. Mapping.Categories.or.Union.Types. When.Superclasses.Have.Different. Primary.Keys...............................................279

📄 Page 16

Contents  •  xv 10.9. Final.ER.Design.Methodology................................. 280 10.9.1. ER.Design.Methodology........................... 280 10.10. Chapter.Summary.......................................................281 Chapter.10.Exercises............................................................... 282 Bibliography............................................................................. 282 Case.Study................................................................................ 283 Chapter 11 Relational.Mapping.and.Reverse.Engineering. ER/EER.Diagrams.......................................................... 287 11.1. Introduction................................................................ 287 11.2. Steps.Used.to.Map.ER/EER.Diagrams.to. Relational.Databases.................................................. 287 11.3. Reverse.Engineering...................................................293 11.3.1. Reverse.Engineering.Rule.1..Develop. Strong.Entities............................................. 294 11.3.2. Reverse.Engineering.Rule.2..Look.for. 1:1.and.1:N.(1:x).Relationships..................295 11.3.3. Reverse.Engineering.Rule.2a..Check. for.Attributes.of.the.1:x.Relationship...... 297 11.3.4. Reverse.Engineering.Rule.3..Look. for.Weak.Entities.and.Multivalued. Attributes....................................................298 11.3.5. Reverse.Engineering.Rule.3a.. Checking.for.Weak.Entities...................... 298 11.3.6. Reverse.Engineering.Rule.3b.. Checking.for.Multivalued.Attributes...... 300 11.3.7. Reverse.Engineering.Rule.4..Check.for. M:N.and n-ary.Relationships................... 302 11.3.8. Reverse.Engineering.Rule.4a..Check. for.the.Binary.Case..................................... 302 11.3.9. Reverse.Engineering.Rule.4b..Check. for.the.n-ary.Case....................................... 303 11.3.10. Reverse.Engineering.Rule.5..Check. for.Generalization/Specialization. Relationships............................................... 303

📄 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.......................... 304 11.3.12. Reverse.Engineering.Rule.5b.. Check.for.Disjoint.Generalization/ Specialization.Relationships.with. Single-Predicate-Defined.Attributes....... 306 11.3.13. Reverse.Engineering.Rule.5c.. Check.for.Overlap.Generalization/ Specialization.Relationship.with.More. than.One.Flag.............................................. 306 11.3.14. Reverse.Engineering.Rule.6..Check. for.Shared.Subclasses................................. 306 11.3.15. Reverse.Engineering.Rule.7..Check. for.Categories.or.Union.Types.................. 307 11.4. Chapter.Summary...................................................... 307 Chapter.11.Exercises............................................................... 308 Bibliography............................................................................. 309 Chapter 12 A.Brief.Overview.of.the.Barker/Oracle-Like.Model......311 12.1. Introduction.................................................................311 12.2. A.First.“Entity-Only”.ER.Diagram:.An.Entity. with.Attributes............................................................312 12.3. Attributes.in.the.Barker/Oracle-Like.Model...........313 12.3.1. Optional.versus.Mandatory.Attributes....313 12.4. Relationships.in.the.Barker/Oracle-Like.Model.....314 12.5. Structural.Constraints.in.the. Barker/Oracle-Like.Model.........................................315 12.6. Dealing.with.the.Concept.of.the.Weak.Entity. in.the.Barker/Oracle-Like.Model..............................318 12.7. Dealing.with.the.Concept.of.Multivalued. Attributes.in.the.Barker/Oracle-Like.Model...........319 12.8. Treatment.of.Foreign.Keys........................................321 12.9. Recursive.Relationships.in.the. Barker/Oracle-Like.Model.........................................322

📄 Page 18

Contents  •  xvii 12.10. Mapping.M:N.Relationships.....................................323 12.11. Chapter.Summary.......................................................323 Chapter.12.Exercises............................................................... 326 Bibliography............................................................................. 326 Glossary............................................................................................... 327

📄 Page 19

(This page has no text content)

📄 Page 20

xix Preface Data. modeling. and. database. design. have. undergone. significant. evolu- tion. in.recent.years..Today,. the.relational.data.model.and. the.relational. database.system.dominate.business.applications..The.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.mod- eling.(solely.a.logical.implementation). INTENDED AUDIENCE This.book.is.intended.to.be.used.for.data.modeling.by.database.practition- ers.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..This. 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 This.book.focuses.on.presenting.(a).an.ER design methodology.for.develop- ing.an.ER.diagram;.(b).a.grammar.for.the.ER.diagrams.that.can.be.pre- sented.back.to.the.user;.and.(c).mapping rules.to.map.the.ER.diagram.to. a.relational.database..The.steps.for.the.ER.design.methodology,.the.gram- mar.for.the.ER.diagrams,.as.well.as.the.mapping.rules.are.developed.and. presented.in.a.systematic.step-by-step.manner.throughout.the.book..Also,. several.examples.of.“sample.data”.have.been.included.with.relational.data- base.mappings.to.give.a.“realistic”.feeling.