Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN XXXXXXXXXX
Database
Design Process
• Data analysis and requirements
• Entity Relationship modeling and normalization
• Data model verification
• Distributed database design*
• Determine end-user views, outputs and transaction requirements
• Define entities, attributes, domains and relationships
• Draw ER diagrams; normalize entity attributes
• Identify ER modules and validate insert, update, and delete rules
• Validate reports, queries, views, integrity, access, and security
• Define the fragmentation and allocation strategy
DBMS and Hardware Independent
DBMS Dependent
Hardware Dependent
• Determine DBMS and data model to use
• Define tables, columns, relationships, and constraints
• Normalized set of tables
• Ensure entity and referential integrity; define column constraints
• Ensure the model supports user requirements
• Define tables, indexes, and views’ physical organization
• Define users, security groups, roles, and access controls
• Define database and query execution parameters
• Map conceptual model to logical model components
• Validate logical model using normalization
• Validate logical modeling integrity constraints
• Validate logical model against user requirements
Conceptual
Design
DBMS
Selection Select the DBMS
Logical
Design
Section Stage Steps Activities
Physical
Design
9-5
9-4
9-6
9-7
* See Chapter 12, Distributed Database Management Systems
+ See Chapter 11, Database Performance Tuning and Query Optimization
• Define data storage organization
• Define integrity and security measures
• Determine performance measures+
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
BUSINESS RULES
Properly document and verify all business rules with the end users.
Ensure that all business rules are written precisely, clearly, and simply. The business rules
must help identify entities, attributes, relationships, and constraints.
Identify the source of all business rules, and ensure that each business rule is justified,
dated, and signed off by an approving authority.
DATA MODELING
Naming Conventions: All names should be limited in length (database-dependent size).
ENTITY NAMES:
Should be nouns that are familiar to business and should be short and meaningful
Should document a
eviations, synonyms, and aliases for each entity
Should be unique within the model
For composite entities, may include a combination of a
eviated names of the entities
linked through the composite entity
ATTRIBUTE NAMES:
Should be unique within the entity
Should use the entity a
eviation as a prefix
Should be descriptive of the characteristic
Should use suffixes such as _ID, _NUM, or _CODE for the PK attribute
Should not be a reserved word
Should not contain spaces or special characters such as @, !, or &
RELATIONSHIP NAMES:
Should be active or passive ve
s that clearly indicate the nature of the relationship
Entities:
Each entity should represent a single subject.
Each entity should represent a set of distinguishable entity instances.
All entities should be in 3NF or higher. Any entities below 3NF should be justified.
The granularity of the entity instance should be clearly defined.
The PK is clearly defined and supports the selected data granularity.
Attributes:
Should be simple and single-valued (atomic data)
Should document default values, constraints, synonyms, and aliases
Derived attributes should be clearly identified and include source(s)
Should not be redundant unless they are justified for transaction accuracy,
performance, or maintaining a history
Nonkey attributes must be fully dependent on the PK attribute
Relationships:
Should clearly identify relationship participants
Should clearly define participation, connectivity, and document cardinality
ER Model:
Should be validated against expected processes: inserts, updates, and deletes
Should evaluate where, when, and how to maintain a history
Should not contain redundant relationships except as required (see Attributes)
Should minimize data redundancy to ensure single-place updates
Should conform to the minimal data rule: “All that is needed is there and
all that is there is needed.”
Data Modeling Checklist
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
cengage.com/mindtap
Fit your coursework
into your hectic life.
Make the most of your time by learning
your way. Access the resources you need to
succeed wherever, whenever.
• �Get�more�from�your�time�online�with�an�easy-to-follow�
five-step�learning�path.
• �Stay�focused�with�an�all-in-one-place,�integrated�
presentation�of�course�content.
• �Get�the�free�MindTap�Mobile�App�and�learn�
wherever you are.
Break limitations. Create your
own�potential,�and�be�unstoppable�
with�MindTap.
MINDTAP. POWERED BY YOU.
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
DATABASE SYSTEMS
Carlos Coronel | Steven Mo
is
Design, Implementation,
and Management
13e
Australia • Brazil • Mexico • Singapore • United Kingdom • United States
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
This is an electronic version of the print textbook. Due to electronic rights restrictions,
some third party content may be suppressed. Editorial review has deemed that any suppressed
content does not materially affect the overall learning experience. The publisher reserves the right
to remove content from this title at any time if subsequent rights restrictions require it. Fo
valuable information on pricing, previous editions, changes to cu
ent editions, and alternate
formats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for
materials in your areas of interest.
Important Notice: Media content referenced within the product description or the product
text may not be available in the eBook version.
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN XXXXXXXXXX
© 2019, 2015 Cengage Learning, Inc.
ALL RIGHTS RESERVED. No part of this work covered by the copyright herein
may be reproduced or distributed in any form or by any means, except as
permitted by U.S. copyright law, without the prior written permission of the
copyright owner.
For product information and technology assistance, contact us at
Cengage Learning Customer & Sales Support, XXXXXXXXXX
For permission to use material from this text or product, submit all
equests online at www.cengage.com/permissions
Further permissions questions can be emailed to
XXXXXXXXXX
Screenshots for this book were created using Microsoft Access®, Excel®,
and Visio® and were used with permission from Microsoft. Microsoft and
the Office logo are either registered trademarks or trademarks of Microsoft
Corporation in the United States and/or other countries.
Oracle is a registered trademark, and Oracle12 c and MySQL are trademarks
of Oracle Corporation.
iPhone, iPad, iTunes, and iPod are registered trademarks of Apple Inc.
Li
ary of Congress Control Number: XXXXXXXXXX
Student Edition ISBN: XXXXXXXXXX
Loose Leaf Edition ISBN: XXXXXXXXXX
Cengage
20 Channel Center Street
Boston, MA 02210
USA
Cengage Learning is a leading provider of customized learning solutions
with employees residing in nearly 40 different countries and sales in more
than 125 countries around the world. Find your local representative at
www.cengage.com.
Cengage Learning products are represented in Canada by
Nelson Education, Ltd.
To learn more about Cengage, visit www.cengage.com
Purchase any of our products at your local college store or at our
prefe
ed online store www.cengage
ain.com.
Database Systems: Design, Implementation,
and Management, 13th Edition
Carlos Coronel and Steven Mo
is
SVP, GM Skills: Jonathan Lau
Product Director: Lauren Murphy
Product Team Manager: Kirstin McNary
Associate Product Manager: Kate Mason
Executive Director of Development: Marah
Bellegarde
Senior Content Development Manager: Leigh
Hefferon
Content Developer: Maria Garguilo
Product Assistant: Jake Toth
VP, Marketing for Science, Technology, & Math:
Jason Sakos
Marketing Director: Michele McTighe
Marketing Manager: Stephanie Al
acht
Production Director: Patty Stephan
Content Project Manager: Michele Stulga
Art Director: Diana Graham
Cover Designer: Roycroft Design
(roycroftdesign.com)
Cover Image: iStock.com/liuzishan
Printed in the United States of America
Print Number: 01 XXXXXXXXXXPrint Year: 2017
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
iii
Dedication
To the treasures in my life: To Victoria, for 28 wonderful years. Thank you for your
unending support and for being my angel, my sweetie, and, most importantly, my best
friend. To Carlos Anthony, who has become a remarkable man, pride of his father, and
husband to our beautiful, sweet, and smart daughter-in-law, Jered. Thank you for your
words of wisdom, hard-working attitude, and for giving us reasons