Database Modeling Step by Step

269 Pages·2020·9.044 MB·\269

by  Gavin Powell (Author)

Database Modeling
Step-by-Step

Database Modeling
Step-by-Step
Gavin Powell
Boca Raton London New York
CRC Press is an imprint of the
Taylor & Francis Group, an informa business
AN AUERBACH BOOK
CRC Press
Taylor & Francis Group
6000 Broken Sound Parkway NW, Suite 300
Boca Raton, FL 33487-2742
© 2020 by Gavin Powell
CRC Press is an imprint of Taylor & Francis Group, an Informa business
No claim to original U.S. Government works
Printed on acid-free paper
International Standard Book Number-13: 978-0-367-42217-2 (Hardback)
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
Trademarks Used in This Publication
DB2 is a registered trademark of International Business Machines Corporation in Armonk, NY.
HACKOLADE is a registered trademark of IntegrIT, naamloze vennootschap, Netherlands.
HBASE is a registered trademark of The Apache Software Foundation in Wakefi eld, MA.
LOOKER is a registered trademark of Looker Data Sciences, Inc. in Santa Cruz, CA.
MONGODB is a registered trademark of Mongodb, Inc. in New York, NY.
NETEZZA is a registered trademark of Ibm International Group BV in Amsterdam.
ORACLE is a registered trademark of Oracle International Corporation in Redwood City, CA.
QLIK is a registered trademark of QlikTech International AB in SE-223 63 Lund.
SNOWFLAKE is a registered trademark of Snowfl ake Inc. in San Mateo, CA.
SQL SERVER is a registered trademark of Microsoft Corporation in Redmond, WA.
TABLEAU is a registered trademark of Tableau Software, Inc. in Seattle, WA.
VERTICA is a registered trademark of EntIT Software LLC in Sunnyvale, CA.
YED is a registered trademark of yWorks GmbH in Tuebingen, 72070.

Contents
Contents vii
List of Figures xi
Preface xvii
About the Author xix
Chapter 1 The Evolution of Relational Database Modeling 1
1.1 F rom File Systems to Object-Relational Databases 2
1.1.1 F ile Systems 3
1.1.2 The Hierarchical Database Model 3
1.1.3 T he Network Database Model 4
1.1.4 T he Relational Database Model 5
1.1.5 The Object Database Model 7
1.1.6 The Object-Relational Database Model 9
1.2 G eneral Types of Database Models 10
1.2.1 Transactional Databases 10
1.2.2 Decision Support Databases 11
1.2.3 Hybrid Databases 13
1.3 C onclusion 14
Chapter 2 The Pieces of the Relational Data Model 15
2.1 Why Discuss the Pieces? 16
2.2 Tables 17
2.2.1 Columns or Fields 18
2.2.2 Rows 30
vii
viii Database Modeling Step by Step
2.3 Relationships 31
2.3.1 Representing Relationships in an ERD 31
2.3.2 K eys to Enforce Referential Integrity 41
2.4 Indexes 51
2.4.1 What is an Index? 51
2.4.2 Alternate Indexing 52
2.4.3 Types of Indexes 53
2.4.4 Different Ways to Build Indexes 56
2.5 Specialized Objects 57
2.6 Conclusion 60
Chapter 3 Intuitive Relational Data Modeling and Normalization 61
3.1 Normalization and Normal Forms 63
3.1.1 Defi ning the Normal Forms 63
3.2 Intuitive Database Modeling 65
3.2.1 D efi ning Data Modeling Intuitively 66
3.2.2 M aster-Detail Relationship 67
3.2.3 D ynamic-Static Relationship 73
3.2.4 A dvanced Relationships 79
3.3 Data Model Design with Normalization 87
3.3.1 A nomalies 88
3.3.2 Dependency and Determinants 89
3.3.3 D efi ning Normal Forms Again 93
3.3.4 1 st Normal Form 94
3.3.5 2nd Normal Form 96
3.3.6 3rd Normal Form 98
3.3.7 Advanced Normalization Beyond 3rd Normal Form 100
3.4 Conclusion 113
Chapter 4 Reading and Writing Relational Data with SQL 115
4.1 W hat is SQL? 116
4.1.1 T he Origins of SQL 117
4.1.2 SQL for Different Databases 118
4.1.3 I ntroducing SQL 118
4.2 Querying a Database Using SELECT 120
4.2.1 Filtering with the WHERE Clause 122
4.2.2 Sorting with the ORDER BY Clause 126
Contents ix
4.2.3 Aggregating with the GROUP BY Clause 127
4.2.4 Join Queries 128
4.2.5 Nested Queries 136
4.2.6 Composite Queries 137
4.3 C hanging Data in a Database 138
4.3.1 U nderstanding Transactions 139
4.4 C hanging Database Metadata 141
4.5 C onclusion 146
Chapter 5 Advanced Relational Database Modeling 147
5.1 Understanding Denormalization 149
5.1.1 Normal Form Defi nitions 149
5.1.2 Intuitive Data Modeling Defi nitions 150
5.1.3 Denormalizing Granularity Created in Chapter 3 151
5.1.4 Denormalization Using Specialized Database Objects 156
5.1.5 Denormalization Tricks 158
5.2 Understanding the Object Database Model 160
5.3 Introducing the Data Warehouse Model 165
5.4 Conclusion 167
Chapter 6 Understanding Data Warehouse Database Modeling 169
6.1 What Is a Data Warehouse? 170
6.1.1 The Relational Database Model and Data Warehouses 172
6.2 T he Dimensional Database Model 174
6.2.1 W hat Is a Star Schema? 175
6.2.2 W hat Is a Snowfl ake Schema? 176
6.2.3 K imball and Inmon on Data Warehousing 181
6.3 D ata Warehouse Modeling 183
6.3.1 Understanding Business Processes 184
6.3.2 Granularity 184
6.3.3 Commonly Occurring Types of Dimension Tables 186
6.3.4 Understanding the Basics of Fact Tables 189
6.4 C onclusion 195
Chapter 7 Modeling for BigData Databases 197
7.1 Dimensional Modeling and Staging Databases in the
Age of BigData 198
7.1.1 The Data Vault Model 199