Database Design Using Entity-Relationship Diagrams (Foundations of Database Design)

388 Pages·2022·10.915 MB·English

by  Sikha Saha Bagui, Richard Walsh Earp

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. T ey are also well-
known for being dif cult to master. With D atabase Design Using Entity-Relationship
Diagrams, T ird Edition , database designers, developers, and students preparing to enter
the f eld can quickly learn the ins and outs of data modeling through ER diagramming.
Building on the success of the bestselling f rst 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 def nes ER and EER diagramming in terms
of requirements (end user requests) and specif cations (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.

Database Design Using
Entity-Relationship
Diagrams
T ird Edition
Sikha Saha Bagui
Richard Walsh Earp
T ird Edition published 2023
b y CRC Press
6 000 Broken Sound Parkway NW, Suite 300, Boca Raton, FL 33487–2742
a nd by CRC Press
4 Park Square, Milton Park, Abingdon, Oxon, OX14 4RN
C RC Press is an imprint of Taylor & Francis Group, L LC
© 2023 Sikha Saha Bagui and Richard Walsh Earp
F irst edition published by CRC Press 2003
S econd edition published by CRC Press 2011
Reasonable ef orts 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. T e 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.
E xcept 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 hereaf er invented, including photocopying, microf lming, and
recording, or in any information storage or retrieval system, without written permission
from the publishers.
F or permission to photocopy or use material electronically from this work, access w ww.
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
[email protected]
T rademark notice: Product or corporate names may be trademarks or registered trademarks
and are used only for identif cation and explanation without intent to infringe.
ISBN: 978-1-032-01718-1 (hbk)
I SBN: 978-1-032-32321-3 (pbk)
I SBN: 978-1-003-31445-5 (ebk)
DOI: 10.1201/9781003314455
T ypeset in Minion
b y 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.
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.

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
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
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