Database Design Using Entity-Relationship Diagrams, Second Edition

362 Pages·2012·2.694 MB·English

by  Bagui, Sikha, Earp, Richard

Information Technology / Database EB
a
a
g
r
Essential to database design, entity-relationship (ER) diagrams are known for pu
i
their usefulness in mapping out clear database designs. They are also well-
known for being difficult to master. With Database Design Using Entity- D
a
Relationship Diagrams, Second Edition, database designers, developers,
t
and students preparing to enter the field can quickly learn the ins and outs of a
b
ER diagramming.
a
s
Building on the success of the bestselling first edition, this accessible text e

includes a new chapter on the relational model and functional dependencies. D
It also includes expanded chapters on Enhanced Entity Relationship (EER) e
s
diagrams and reverse mapping. It uses cutting-edge case studies and examples
i
g
to help readers master database development basics and defines ER and EER
n
diagramming in terms of requirements (end user requests) and specifications

U
(designer feedback to those requests).
s
i
n
• Describes a step-by-step approach for producing an ER diagram
g
and developing a relational database from it

E
• Contains exercises, examples, case studies, bibliographies, n
and summaries in each chapter t
i
t
• Details the rules for mapping ER diagrams to relational databases y
-
• Explains how to reverse engineer a relational database back to an R
entity-relationship model e
l
a
• Includes grammar for the ER diagrams that can be presented back
t
to the user i
o
n
The updated exercises and chapter summaries provide the real-world s
h
understanding needed to develop ER and EER diagrams, map them to
i
relational databases, and test the resulting relational database. Complete with p

a wealth of additional exercises and examples throughout, this edition should D
be a basic component of any database course. Its comprehensive nature and i
a
easy-to-navigate structure make it a resource that students and professionals g
will turn to throughout their careers. r
a
m
s
K12857 ES
de
ISBN: 978-1-4398-96010706-09 itiocon
nd
www.crcpress.com
9 781439 861769
www.auerbach-publications.com
K12857 cvr mech.indd 1 8/9/11 8:53 AM
Database Design
Using Entity-
Relationship
Diagrams
Second Edition

Database Design
Using Entity-
Relationship
Diagrams
Second Edition
Sikha Bagui and Richard Earp
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
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.

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