PR I NC I P L E S O F D A TABA S E MANAGEMENT THE PRACT I CAL GU I D E TO S TOR I NG , MANAG I NG AND A NALYZ I NG B I G A ND SMALL D A TA P R I N C I P L E S O F D A T A B A S E M ANAGEMENT PROV I D E S S T UDENT S W I TH THE COMPREHEN S I V E D A TABA S E MANAGEMENT I N FORMAT I ON TO U NDER S TAND A ND A P P LY THE FUNDAMENTAL CONCE P T S O F D A TABA S E DE S I GN A ND MODEL I NG , D A TABA S E S Y S T EMS , D A TA S TORAGE A ND THE E VOLV I NG WORLD O F DATA WAREHOU S I NG , GOVERNANCE A ND MORE . D E S I GNED F OR THO S E S T UDY I NG D A TABA S E MANAGEMENT F OR I N FORMAT I ON MANAGEMENT OR COMPUTER S C I E NCE , T H I S I L LU S TRATED T EXTBOOK HA S A WELL - B A LANCED THEORY – PRACT I C E F OCU S A ND COVER S THE E S S ENT I A L TO P I C S , F ROM E S TABL I S H ED D A TABA S E T ECHNOLOG I E S U P T O RECENT T R END S L I K E B I G D A TA , N O SQL A ND ANALYT I C S . O NGO I NG C A S E S T UD I E S , D R I L L - DOWN BOXE S T HAT R EVEAL D E E P ER I N S I GHT S ON K EY TO P I C S , R E T ENT I ON QUE S T I ON S A T THE E ND O F E V ERY S E C T I ON O F A CHA P T ER , A ND CONNECT I ON S BOXE S T HAT S HOW THE RELAT I ON SH I P B E TWEEN CONCE P T S THROUGHOUT THE T EXT A R E I N C LUDED TO PROV I D E THE P RACT I CAL TOOL S TO G E T S TARTED I N D A TABA S E MANAGEMENT . Key features include: Full-color illustrations throughout the text. Extensive coverage of important trending topics, including data warehousing, business intelligence, data integration, data quality, data governance, Big Data and analytics. An online playground with diverse environments, including MySQL for querying; MongoDB; Neo4j Cypher; and a tree structure visualization environment. Hundreds of examples to illustrate and clarify the concepts discussed that can be reproduced on the book’s companion online playground. Case studies, review questions, problems and exercises in every chapter. Additional cases, problems and exercises in the appendix. “Although there have been a series of classical textbooks on database systems, the new dramatic advances call for an updated text covering the latest significant topics, such as Big Data analytics, NoSQL and much more. Fortunately, this is exactly what this book has to offer. It is highly desirable for training the next generation of data management professionals.” – Jian Pei, Simon Fraser University “I haven’t seen an as up-to-date and comprehensive textbook for database management as this one in many years. Principles of Database Management combines a number of classical and recent topics concerning data modeling, relational databases, object-oriented databases, XML, distributed data management, NoSQL and Big Data in an unprecedented manner. The authors did a great job in stitching these topics into one coherent and compelling story that will serve as an ideal basis for teaching both introductory and advanced courses.” – Martin Theobald, University of Luxembourg “This is a very timely book with outstanding coverage of database topics and excellent treatment of database details. It not only gives very solid discussions of traditional topics such as data modeling and relational databases, but also contains refreshing contents on frontier topics such as XML databases, NoSQL databases, Big Data and analytics. For those reasons, this will be a good book for database professionals, who will keep using it for all stages of database studies and works.” – J. Leon Zhao, City University of Hong Kong “This accessible, authoritative book introduces the reader the most important fundamental concepts of data management, while providing a practical view of recent advances. Both are essential for data professionals today.” – Foster Provost, New York University, Stern School of Business “This guide to big and small data management addresses both fundamental principles and practical deployment. It reviews a range of databases and their relevance for analytics. The book is useful to practitioners because it contains many case studies, links to open-source software, and a very useful abstraction of analytics that will help them choose solutions better. It is important to academics because it promotes database principles which are key to successful and sustainable data science.” – Sihem Amer-Yahia, Laboratoire d’Informatique de Grenoble; Editor-in- Chief, The VLDB Journal (International Journal on Very Large DataBases) “This book covers everything you will need to teach in a database implementation and design class. With some chapters covering Big Data, analytic models/methods and NoSQL, it can keep our students up to date with these new technologies in data management-related topics.” – Han-fen Hu, University of Nevada, Las Vegas PRINCIPLES OF DATABASE MANAGEMENT The Practical Guide to Storing, Managing and Analyzing Big and Small Data Wilfried Lemahieu KU Leuven, Belgium Seppe vanden Broucke KU Leuven, Belgium Bart Baesens KU Leuven, Belgium; University of Southampton, United Kingdom University Printing House, Cambridge CB2 8BS, United Kingdom One Liberty Plaza, 20th Floor, New York, NY 10006, USA 477 Williamstown Road, Port Melbourne, VIC 3207, Australia 314–321, 3rd Floor, Plot 3, Splendor Forum, Jasola District Centre, New Delhi – 110025, India 79 Anson Road, #06–04/06, Singapore 079906 Cambridge University Press is part of the University of Cambridge. It furthers the University’s mission by disseminating knowledge in the pursuit of education, learning, and research at the highest international levels of excellence. www.cambridge.org Information on this title: www.cambridge.org/9781107186125 DOI: 10.1017/9781316888773 © Wilfried Lemahieu, Seppe vanden Broucke, and Bart Baesens 2018 This publication is in copyright. Subject to statutory exception and to the provisions of relevant collective licensing agreements, no reproduction of any part may take place without the written permission of Cambridge University Press. First published 2018 Printed and bound in Great Britain by Clays Ltd, Elcograf S.p.A. A catalog record for this publication is available from the British Library. Library of Congress Cataloging-in-Publication Data Names: Lemahieu, Wilfried, 1970– author. | Broucke, Seppe vanden, 1986– author. | Baesens, Bart, author. Title: Principles of database management : the practical guide to storing, managing and analyzing big and small data / Wilfried Lemahieu, Katholieke Universiteit Leuven, Belgium, Seppe vanden Broucke, Katholieke Universiteit Leuven, Belgium, Bart Baesens, Katholieke Universiteit Leuven, Belgium. Leuven, Belgium. Description: First edition. | New York, NY : Cambridge University Press, 2018. | Includes bibliographical references and index. Identifiers: LCCN 2018023251 | ISBN 9781107186125 (hardback : alk. paper) Subjects: LCSH: Database management. Classification: LCC QA76.9.D3 L454 2018 | DDC 005.74–dc23 LC record available at https://lccn.loc.gov/2018023251 ISBN 978-1-10718612-5 Hardback Additional resources for this publication at www.cambridge.org/Lemahieu Cambridge University Press has no responsibility for the persistence or accuracy of URLs for external or third-party internet websites referred to in this publication and does not guarantee that any content on such websites is, or will remain, accurate or appropriate. Brief Contents About the Authors Preface Sober: 1000‰ Driven by Technology Part I Databases and Database Design 1 Fundamental Concepts of Database Management 2 Architecture and Categorization of DBMSs 3 Conceptual Data Modeling Using the (E)ER Model and UML Class Diagram 4 Organizational Aspects of Data Management Part II Types of Database Systems 5 Legacy Databases 6 Relational Databases: The Relational Model 7 Relational Databases: Structured Query Language (SQL) 8 Object-Oriented Databases and Object Persistence 9 Extended Relational Databases 10 XML Databases 11 NoSQL Databases Part III Physical Data Storage, Transaction Management, and Database Access 12 Physical File Organization and Indexing 13 Physical Database Organization 14 Basics of Transaction Management 15 Accessing Databases and Database APIs 16 Data Distribution and Distributed Transaction Management Part IV Data Warehousing, Data Governance, and (Big) Data Analytics 17 Data Warehousing and Business Intelligence 18 Data Integration, Data Quality, and Data Governance 19 Big Data 20 Analytics Appendix Using the Online Environment