Modern Database Management (12th Edition)

data, Database management, Data warehousing Database designThis page intentionally left blankGlobal editionTwelfth editionMODERN DATABASE MANAGEMENTJeffrey A Hofferuniversity of daytonV. RameshIndiana universityHeikki TopiBentley universityPEARSONBoston Columbus Indianapolis New York San franciscoAmsterdam Cape Town Dubai London Madrid Milan Munich Paris Montreal TorontoDelhi Mexico City Sao Paulo Sydney Hong Kong Seoul Singapore Taipei TokyoVice President, Business Publishing: Donna BattistaOperations Specialist: Diane PeiranoEditor-in-Chief: Stephanie wallCreative director: blair brownAcquisitions Editor: Nicole SamSenior Art Director: Janet SlowikProgram Manager Team Lead: Ashley SantoraInterior and Cover Designer: shibu VelayudhanProgram Manager: Denise WeissLumina datamatics ltdEditorial Assistant: Olivia VignoneCover Image: Neale Cousland/ShutterstockVice President, Product Marketing: Maggie MoylanVice President, Director of Digital Strategy &e AssessmentDirector of Marketing, Digital Services and ProductsPaul gentileJeanette KoskinasManager of Learning Applications: Paul DelucaField Marketing Manager: Lenny Ann RaperDigital Editor: Brian SuretteSenior Strategic Marketing Manager: Erin GardnerDigital Studio Manager: Diane LombardoProduct Marketing Assistant: Jessica QuazzaDigital Studio Project Manager: Robin LazrusProject Manager Team Lead: Jeff HolcombDigital Studio Project Manager: Alana ColesProject Manager: Ilene KahnDigital Studio Project Manager: Monique LawrenceAssistant Acquisitions Editor, Global Edition: Ananya SrivastavaDigital Studio Project Manager: Regina DasilvaAssociate Project Editor, Global Edition: Amrita KarFull-Service Project Management and CompositionProject Manager, Global Edition: Nikhil RakshitGeorge Jacob/Integra Software Solutions Pvt., LtdManager, Media Production, Global Edition: Vikram KumarPrinter/Binder: vivarSenior Manufacturing Controller, Production, Global edition:Cover printer: vivarText Font: 10/12 PalatinoLTStd romanCredits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriatepage within text.Microsoft and/ or its respective suppliers make no representations about the suitability of the information contained in the documents andrclated graphics published as part of the services for any purposc. All such documents and rclated graphics are providedwithoutwarranty of any kind. Microsoft and or its respective suppliers hereby disclaim all warranties and conditions with regard to this information,including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title andnon-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damagesor any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action,arising out of or in connection with the usc or performance of information available from the scrviccsT he documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodicallyadded to the information herein. Microsoft and /or its respective suppliers may make improvements and/or changes in the product(s)andy or the program(s)described herein at any time. Partial screen shots may be viewed in full within the software version specifiedTrademarksMicrosoftWindows, and Microsoft Office" are registered trademarks of the Microsoft Corporation in the U.S.A. and other countriesThis book is not sponsored or endorsed by or affiliated with the Microsoft CorporationPearson education LimitedEdinburgh GateEssex CM20 2JEEnglandand Associated Companies throughout the worldVisit us on the world Wide Web atwww.pearsonglobaleditions.comC Pearson Education Limited 2016The rights of Jeff Hoffer, Ramesh Venkataraman and Heikki Topi to be identified as the authors of this work have been asserted by them inaccordance with the Copyright, Designs and Patents Act 1988Authorized adaptation from the lInited states edition, entitled Modern Database Management, 12fht edition ISBN 978-0-13-354461-9, by eff Hoffer.Laimesh Venkataraman and Heikki Topi, published by Pearson Education (o 2076All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any meanselectronic, mechanical, photocopying, recording or otherwise, without either the prior written permission of the publisher or a license permittingrestricted copying in the United Kingdom issued by the Copyright Licensing Agency Ltd, Saffron House, 6-10 Kirby Street, London EC IN STSAll trademarks used herein are the property of their respective owners. The use of any trademark in this text does not vest in the author orpublisher any trademark ownership rights in such trademarks, nor does the use of such trademarks imply any affiliation with or endorsementof this book by such ownersISBN-10.1-292-10185ISBN-13:978-1-292-101859British Library Cataloguing-in-Publication Dataa catalogue record for this book is available from the british library1987654321Typeset by Integra Software Solutions Pvt., LtdPrinted and bound by vivarTo Patty, for her sacrifices, encouragement, and support for more than 30 yearsof being a textbook author widow. To my students and colleagues, for beingreceptive and critical and for challenging me to be a better teacher-.A.H.To Gayathri, for her sacrifices and patience these past 25 years. To my parents, forletting me make the journey abroad, and to my cat, Raju, who was a part of ourfamily for more than 20 years-VRTo Anne-Louise, for her loving support, encouragement, and patienceTo Leila and saara, whose laughter and joy of life continue to teach me aboutwhat is truly important To my teachers, colleagues, and students, from whomI continue to learn every dayH.7This page intentionally left blankBRIEF CONTENTSPart I The Context of Database Management 37Chapter 1 The database Environment and Development Process 38Part II Database Analysis 87Chapter 2 Modeling data in the Organization 89Chapter 3 the Enhanced E-R Model 150Part Ill Database Design 189Chapter 4 Logical Database Design and the relational Model 191Chapter 5 Physical Database Design and Performance 242Part IV Implementation 277Chapter 6 Introduction to SQL 279Chapter 7 Advanced SQL 325Chapter 8 Database Application Development 373Chapter 9 Data Warehousing 410Part V Advanced Database Topics 453Chapter 10 Data Quality and Integration 455Chapter 11 Big Data and Analytics 481Chapter 12 Data and Database Administration 521Glossary of acronyms 570Glossary of Terms 572Index 580AvailableOnlineatwww.pearsonhighered.com/hofferChapter 13 Distributed databases 13-1Chapter 14 Object-Oriented Data Modeling 14-1AppendicesAppendix A Data Modeling Tools and Notation A-37Appendix B Advanced Normal Forms B-37Appendix C Data Structures C-37This page intentionally left blankCONTENTSPreface 25Part I The Context of Database Management 37An verview of part one 37Chapter 1 The Database Environment and Development Process 38Learning objectives 38Data matter! 38Introduction 39Basic Concepts and Definitions 41Data 41Data Versus Information 41Metadata 42Traditional File Processing Systems 43File Processing Systems at Pine valley Furniture Company 44Disadvantages of file processing systems 44PROGRAM-DATA DEPENDENCE 44DUPLICATION OF DATA 45LIMITED DATA SHARING 45LENGTHY DEVELOPMENT TIMES 45EXCESSIVE PROGRAM MAINTENANCE 45The database Approach 45Data models 45ENTITIES 46RELATIONSHIPS 47Relational databases 47Database management Systems 47Advantages of the datPROGRAM-DATA INDEPENDENCE 47PIMPROVED DATA CONSISTENCY 48IMPROVED DATA SHARING 48INCREASED PRODUCTIVITY OF APPLICATION DEVELOPMENT 49ENFORCEMENT OF STANDARDS 49IMPROVED DATA QUALITY 49IMPROVED DATA ACCESSIBILITY AND RESPONSIVENESS 50REDUCED PROGRAM MAINTENANCE 50IMPROVED DECISION SUPPORT 50Cautions about database benefits 50Costs and Risks of the Database Approach 50NEW SPECIALIZED PEINSTALLATION AND MANAGEMENT COST AND COMPLEXITY 51CONVERSION COSTS 51NEED FOR EXPLICIT BACKUP AND RECOVERY 51ORGANIZATIONAL CONFLICT 51Components of the database Environment 51