Digital Library

SQL and NoSQL Databases (Michael Kaufmann, Andreas Meier)（Z-Library）

Michael Kaufmann, Andreas Meier

Share E-Book
Read Without Registration

SQL and NoSQL Databases (Michael Kaufmann, Andreas Meier)（Z-Library）

Author Michael Kaufmann, Andreas Meier

Database
Language English

This textbook offers a comprehensive introduction to relational (SQL) and non-relational (NoSQL) databases. The authors thoroughly review the current state of database tools and techniques and examine upcoming innovations. In the first five chapters, the authors analyze in detail the management, modeling, languages, security, and architecture of relational databases, graph databases, and document databases. Moreover, an overview of other SQL- and NoSQL-based database approaches is provided. In addition to classic concepts such as the entity and relationship model and its mapping in SQL database schemas, query languages or transaction management, other aspects for NoSQL databases such as non-relational data models, document and graph query languages (MQL, Cypher), the Map/Reduce procedure, distribution options (sharding, replication) or the CAP theorem (Consistency, Availability, Partition Tolerance) are explained. This 2nd English edition offers a new in-depth introduction to document databases with a method for modeling document structures, an overview of the document-oriented MongoDB query language MQL as well as security and architecture aspects. The topic of database security is newly introduced as a separate chapter and analyzed in detail with regard to data protection, integrity, and transactions. Texts on data management, database programming, and data warehousing and data lakes have been updated. In addition, the book now explains the concepts of JSON, JSON schema, BSON, index-free neighborhood, cloud databases, search engines and time series databases. The book includes more than 100 tables, examples and illustrations, and each chapter offers a list of resources for further reading. It conveys an in-depth comparison of relational and non-relational approaches and shows how to undertake development for big data applications. This way, it benefits students and practitioners working across the broad field of data science and a

Format PDF
Size 7.4 MB
20
Views
0
Downloads
0.00
Total Donations
(First 20 pages)

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

Page 1
SQL and NoSQL Databases Modeling, Languages, Security and Architectures for Big Data Management Second Edition Michael Kaufmann Andreas Meier
Page 2
Michael Kaufmann • Andreas Meier SQL and NoSQL Databases Modeling, Languages, Security and Architectures for Big Data Management Second Edition
Page 3
Michael Kaufmann Informatik Hochschule Luzern Rotkreuz, Switzerland Andreas Meier Institute of Informatics Universität Fribourg Fribourg, Switzerland ISBN 978-3-031-27907-2 ISBN 978-3-031-27908-9 (eBook) https://doi.org/10.1007/978-3-031-27908-9 # The Editor(s) (if applicable) and The Author(s), under exclusive license to Springer Nature Switzerland AG 2023 The first edition of this book was published by Springer Vieweg in 2019 This work is subject to copyright. All rights are solely and exclusively licensed by the Publisher, whether the whole or part of the material is concerned, specifically the rights of reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. The use of general descriptive names, registered names, trademarks, service marks, etc. in this publication does not imply, even in the absence of a specific statement, that such names are exempt from the relevant protective laws and regulations and therefore free for general use. The publisher, the authors, and the editors are safe to assume that the advice and information in this book are believed to be true and accurate at the date of publication. Neither the publisher nor the authors or the editors give a warranty, expressed or implied, with respect to the material contained herein or for any errors or omissions that may have been made. The publisher remains neutral with regard to jurisdictional claims in published maps and institutional affiliations. This Springer imprint is published by the registered company Springer Nature Switzerland AG The registered company address is: Gewerbestrasse 11, 6330 Cham, Switzerland
Page 4
Foreword The term database has long since become part of people’s everyday vocabulary, for managers and clerks as well as students of most subjects. They use it to describe a logically organized collection of electronically stored data that can be directly searched and viewed. However, they are generally more than happy to leave the whys and hows of its inner workings to the experts. Users of databases are rarely aware of the immaterial and concrete business values contained in any individual database. This applies as much to a car importer’s spare parts inventory as the IT solution containing all customer depots at a bank or the patient information system of a hospital. Yet failure of these systems, or even cumulative errors, can threaten the very existence of the respective company or institution. For that reason, it is important for a much larger audience than just the “database specialists” to be well-informed about what is going on. Anyone involved with databases should understand what these tools are effectively able to do and which conditions must be created and maintained for them to do so. Probably the most important aspect concerning databases involves (a) the dis- tinction between their administration and the data stored in them (user data) and (b) the economic magnitude of these two areas. Database administration consists of various technical and administrative factors, from computers, database systems, and additional storage to the experts setting up and maintaining all these components— the aforementioned database specialists. It is crucial to keep in mind that the administration is by far the smaller part of standard database operation, constituting only about a quarter of the entire efforts. Most of the work and expenses concerning databases lie in gathering, maintaining, and utilizing the user data. This includes the labor costs for all employees who enter data into the database, revise it, retrieve information from the database, or create files using this information. In the above examples, this means warehouse employees, bank tellers, or hospital personnel in a wide variety of fields—usually for several years. In order to be able to properly evaluate the importance of the tasks connected with data maintenance and utilization on the one hand and database administration on the other hand, it is vital to understand and internalize this difference in the effort required for each of them. Database administration starts with the design of the database, which already touches on many specialized topics such as determining the v
Page 5
consistency checks for data manipulation or regulating data redundancies, which are as undesirable on the logical level as they are essential on the storage level. The development of database solutions is always targeted on their later use, so ill-considered decisions in the development process may have a permanent impact on everyday operations. Finding ideal solutions, such as the golden mean between too strict and too flexible when determining consistency conditions, may require some experience. Unduly strict conditions will interfere with regular operations, while excessively lax rules will entail a need for repeated expensive data repairs. vi Foreword To avoid such issues, it is invaluable for anyone concerned with database development and operation, whether in management or as a database specialist, to gain systematic insight into this field of computer sciences. The table of contents gives an overview of the wide variety of topics covered in this book. The title already shows that, in addition to an in-depth explanation of the field of conventional databases (relational model, SQL), the book also provides highly educational infor- mation about current advancements and related fields, the keywords being NoSQL and Big Data. I am confident that the newest edition of this book will once again be well-received by both students and professionals—its authors are quite familiar with both groups. Professor Emeritus for Databases ETH Zürich Zürich, Switzerland Carl August Zehnder
Page 6
Preface It is remarkable how stable some concepts are in the field of databases. Information technology is generally known to be subject to rapid development, bringing forth new technologies at an unbelievable pace. However, this is only superficially the case. Many aspects of computer science do not essentially change. This includes not only the basics, such as the functional principles of universal computing machines, processors, compilers, operating systems, databases and information systems, and distributed systems, but also computer language technologies such as C, TCP/IP, or HTML that are decades old but in many ways provide a stable fundament of the global, earth-spanning information system known as the World Wide Web. Like- wise, the SQL language (Structured Query Language) has been in use for almost five decades and will remain so in the foreseeable future. The theory of relational database systems was initiated in the 1970s by Codd (relation model) and Chamberlin and Boyce (SEQUEL). However, these technologies have a major impact on the practice of data management today. Especially, with the Big Data revolution and the widespread use of data science methods for decision support, relational databases and the use of SQL for data analysis are actually becoming more important. Even though sophisticated statistics and machine learning are enhancing the possibilities for knowledge extraction from data, many if not most data analyses for decision support rely on descriptive statistics using SQL for grouped aggrega- tion. SQL is also used in the field of Big Data with MapReduce technology. In this sense, although SQL database technology is quite mature, it is more relevant today than ever. Nevertheless, the developments in the Big Data ecosystem brought new technologies into the world of databases, to which we pay enough attention too. Non-relational database technologies, which find more and more fields of applica- tion under the generic term NoSQL, differ not only superficially from the classical relational databases but also in the underlying principles. Relational databases were developed in the twentieth century with the purpose of tightly organized, operational forms of data management, which provided stability but limited flexibility. In contrast, the NoSQL database movement emerged in the beginning of the new century, focusing on horizontal partitioning, schema flexibility, and index-free neighborhood with the goal of solving the Big Data problems of volume, variety, and velocity, especially in Web-scale data systems. This has far-reaching vii
Page 7
consequences and leads to a new approach in data management, which deviate significantly from the previous theories on the basic concept of databases: the way data is modeled, how data is queried and manipulated, how data consistency is handled, and how data is stored and made accessible. That is why in all chapters we compare these two worlds, SQL and NoSQL databases. viii Preface In the first five chapters, we analyze in detail the management, modeling, languages, security, and architecture of SQL databases, graph databases, and, in the second English edition, new document databases. In Chaps. 6 and 7, we provide an overview of other SQL- and NoSQL-based database approaches. In addition to classic concepts such as the entity and relationship model and its mapping in SQL or NoSQL database schemas, query languages, or transaction management, we explain aspects for NoSQL databases such as the MapReduce procedure, distribution options (fragments, replication), or the CAP theorem (con- sistency, availability, partition tolerance). In the second English edition, we offer a new in-depth introduction to document databases with a method for modeling document structures, an overview of the database language MQL, as well as security and architecture aspects. The new edition also takes into account new developments in the Cypher language. The topic of database security is newly introduced as a separate chapter and analyzed in detail with regard to data protection, integrity, and transactions. Texts on data management, database programming, and data warehousing and data lakes have been updated. In addition, the second English edition explains the concepts of JSON, JSON Schema, BSON, index-free neighborhood, cloud databases, search engines, and time series databases. We have launched a Website called sql-nosql.org, where we share teaching and tutoring materials such as slides, tutorials for SQL and Cypher, case studies, and a workbench for MySQL and Neo4j, so that language training can be done either with SQL or with Cypher, the graph-oriented query language of the NoSQL database Neo4j. We thank Alexander Denzler and Marcel Wehrle for the development of the workbench for relational and graph-oriented databases. For the redesign of the graphics, we were able to work with Thomas Riediker. We thank him for his tireless efforts. He has succeeded in giving the pictures a modern style and an individual touch. In the ninth edition, we have tried to keep his style in our new graphics. For the further development of the tutorials and case studies, which are available on the website sql-nosql.org, we thank the computer science students Andreas Waldis, Bettina Willi, Markus Ineichen, and Simon Studer for their contributions to the tutorial in Cypher, respectively, to the case study Travelblitz with OpenOffice Base and with Neo4J. For the feedback on the manuscript, we thank Alexander Denzler, Daniel Fasel, Konrad Marfurt, Thomas Olnhoff, and Stefan Edlich for their willing- ness to contribute to the quality of our work with reading our manuscript and with providing valuable feedback. A heartfelt thank you goes out to Michael Kaufmann’s wife Melody Reymond for proofreading our manuscript. Special thanks to Andy
Page 8
Oppel of the University of California, Berkeley, for grammatical and technological review of the English text. A big thank goes to Leonardo Milla of Springer, who has supported us with patience and expertise. Preface ix Rotkreuz, Switzerland Michael Kaufmann Fribourg, Switzerland Andreas Meier October 2022
Page 9
Contents 1 Database Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Information Systems and Databases . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 SQL Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2.1 Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2.2 Structured Query Language SQL . . . . . . . . . . . . . . . . . . . 6 1.2.3 Relational Database Management System . . . . . . . . . . . . . 8 1.3 Big Data and NoSQL Databases . . . . . . . . . . . . . . . . . . . . . . . . . 10 1.3.1 Big Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 1.3.2 NoSQL Database Management System . . . . . . . . . . . . . . . 12 1.4 Graph Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 1.4.1 Graph-Based Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 1.4.2 Graph Query Language Cypher . . . . . . . . . . . . . . . . . . . . 15 1.5 Document Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.5.1 Document Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.5.2 Document-Oriented Database Language MQL . . . . . . . . . . 19 1.6 Organization of Data Management . . . . . . . . . . . . . . . . . . . . . . . . 21 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2 Database Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.1 From Requirements Analysis to Database . . . . . . . . . . . . . . . . . . . 25 2.2 The Entity-Relationship Model . . . . . . . . . . . . . . . . . . . . . . . . . . 28 2.2.1 Entities and Relationships . . . . . . . . . . . . . . . . . . . . . . . . 28 2.2.2 Associations and Association Types . . . . . . . . . . . . . . . . . 29 2.2.3 Generalization and Aggregation . . . . . . . . . . . . . . . . . . . . 32 2.3 Implementation in the Relational Model . . . . . . . . . . . . . . . . . . . . 35 2.3.1 Dependencies and Normal Forms . . . . . . . . . . . . . . . . . . . 35 2.3.2 Mapping Rules for Relational Databases . . . . . . . . . . . . . . 42 2.4 Implementation in the Graph Model . . . . . . . . . . . . . . . . . . . . . . . 47 2.4.1 Graph Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 2.4.2 Mapping Rules for Graph Databases . . . . . . . . . . . . . . . . . 51 2.5 Implementation in the Document Model . . . . . . . . . . . . . . . . . . . . 55 2.5.1 Document-Oriented Database Modeling . . . . . . . . . . . . . . 55 xi
Page 10
xii Contents 2.5.2 Mapping Rules for Document Databases . . . . . . . . . . . . . . 59 2.6 Formula for Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 3 Database Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 3.1 Interacting with Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 3.2 Relational Algebra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3.2.1 Overview of Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3.2.2 Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 3.2.3 Relation Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 3.2.4 Relationally Complete Languages . . . . . . . . . . . . . . . . . . . 80 3.3 Relational Language SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 3.3.1 Creating and Populating the Database Schema . . . . . . . . . . 81 3.3.2 Relational Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 3.3.3 Built-In Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 3.3.4 Null values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 3.4 Graph-Based Language Cypher . . . . . . . . . . . . . . . . . . . . . . . . . . 91 3.4.1 Creating and Populating the Database Schema . . . . . . . . . . 92 3.4.2 Relation Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 3.4.3 Built-In Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 3.4.4 Graph Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 3.5 Document-Oriented Language MQL . . . . . . . . . . . . . . . . . . . . . . 98 3.5.1 Creating and Filling the Database Schema . . . . . . . . . . . . . 98 3.5.2 Relation Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 3.5.3 Built-In Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 3.5.4 Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 3.6 Database Programming with Cursors . . . . . . . . . . . . . . . . . . . . . . 106 3.6.1 Embedding of SQL in Procedural Languages . . . . . . . . . . . 106 3.6.2 Embedding Graph-Based Languages . . . . . . . . . . . . . . . . . 109 3.6.3 Embedding Document Database Languages . . . . . . . . . . . . 109 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 4 Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 4.1 Security Goals and Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 4.2 Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 4.2.1 Authentication and Authorization in SQL . . . . . . . . . . . . . 113 4.2.2 Authentication in Cypher . . . . . . . . . . . . . . . . . . . . . . . . . 118 4.2.3 Authentication and Authorization in MQL . . . . . . . . . . . . . 121 4.3 Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 4.3.1 Relational Integrity Constraints . . . . . . . . . . . . . . . . . . . . . 127 4.3.2 Integrity Constraints for Graphs in Cypher . . . . . . . . . . . . 129 4.3.3 Integrity Constraints in Document Databases with MQL . . . 132 4.4 Transaction Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 4.4.1 Multi-user Operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 4.4.2 ACID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Page 11
Contents xiii 4.4.3 Serializability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 4.4.4 Pessimistic Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 4.4.5 Optimistic Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 4.4.6 Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 4.5 Soft Consistency in Massive Distributed Data . . . . . . . . . . . . . . . . 144 4.5.1 BASE and the CAP Theorem . . . . . . . . . . . . . . . . . . . . . . 144 4.5.2 Nuanced Consistency Settings . . . . . . . . . . . . . . . . . . . . . 146 4.5.3 Vector Clocks for the Serialization of Distributed Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 4.5.4 Comparing ACID and BASE . . . . . . . . . . . . . . . . . . . . . . 149 4.6 Transaction Control Language Elements . . . . . . . . . . . . . . . . . . . . 151 4.6.1 Transaction Control in SQL . . . . . . . . . . . . . . . . . . . . . . . 151 4.6.2 Transaction Management in the Graph Database Neo4J and in the Cypher Language . . . . . . . . . . . . . . . . . . . . . . . 153 4.6.3 Transaction Management in MongoDB and MQL . . . . . . . 155 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 5 System Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 5.1 Processing of Homogeneous and Heterogeneous Data . . . . . . . . . . 159 5.2 Storage and Access Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 5.2.1 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 5.2.2 Tree Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 5.2.3 Hashing Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 5.2.4 Consistent Hashing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 5.2.5 Multi-dimensional Data Structures . . . . . . . . . . . . . . . . . . 168 5.2.6 Binary JavaScript Object Notation BSON . . . . . . . . . . . . . 171 5.2.7 Index-Free Adjacency . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 5.3 Translation and Optimization of Relational Queries . . . . . . . . . . . . 175 5.3.1 Creation of Query Trees . . . . . . . . . . . . . . . . . . . . . . . . . . 175 5.3.2 Optimization by Algebraic Transformation . . . . . . . . . . . . 178 5.3.3 Calculation of Join Operators . . . . . . . . . . . . . . . . . . . . . . 180 5.3.4 Cost-Based Optimization of Access Paths . . . . . . . . . . . . . 182 5.4 Parallel Processing with MapReduce . . . . . . . . . . . . . . . . . . . . . . 184 5.5 Layered Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 5.6 Use of Different Storage Structures . . . . . . . . . . . . . . . . . . . . . . . 187 5.7 Cloud Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 6 Post-relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 6.1 The Limits of SQL and What Lies Beyond . . . . . . . . . . . . . . . . . . 193 6.2 Federated Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 6.3 Temporal Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 6.4 Multi-dimensional Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 6.5 Data Warehouse and Data Lake Systems . . . . . . . . . . . . . . . . . . . 204 6.6 Object-Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 6.7 Knowledge Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Page 12
xiv Contents 6.8 Fuzzy Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220 7 NoSQL Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 7.1 Development of Non-relational Technologies . . . . . . . . . . . . . . . . 223 7.2 Key-Value Stores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 7.3 Column-Family Stores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227 7.4 Document Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 7.5 XML Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233 7.6 Graph Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237 7.7 Search Engine Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 7.8 Time Series Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Page 13
https://doi.org/10.1007/978-3-031-27908-9_1 Database Management 1 1.1 Information Systems and Databases The evolution from the industrial society via the service society to the information and knowledge society is represented by the assessment of information as a factor in production. The following characteristics distinguish information from material goods: • Representation: Information is specified by data (signs, signals, messages, or language elements). • Processing: Information can be transmitted, stored, categorized, found, or converted into other representation formats using algorithms and data structures (calculation rules). • Combination: Information can be freely combined. The origin of individual parts cannot be traced. Manipulation is possible at any point. • Age: Information is not subject to physical aging processes. • Original: Information can be copied without limit and does not distinguish between original and copy. • Vagueness: Information can be imprecise and of differing validity (quality). • Medium: Information does not require a fixed medium and is therefore indepen- dent of location. These properties clearly show that digital goods (information, software, multime- dia, etc.), i.e., data, are vastly different from material goods in both handling and economic or legal evaluation. A good example is the loss in value that physical products often experience when they are used—the shared use of information, on the other hand, may increase its value. Another difference lies in the potentially high production costs for material goods, while information can be multiplied easily and at significantly lower costs (only computing power and storage medium). This causes difficulties in determining property rights and ownership, even though digital watermarks and other privacy and security measures are available. # The Author(s), under exclusive license to Springer Nature Switzerland AG 2023 M. Kaufmann, A. Meier, SQL and NoSQL Databases, 1
Page 14
2 1 Database Management Information System Communication network or WWW Application Software User guidance Dialog design Business logic Data querying Data manipulation Access permissions Data protection Request Response User Database Storage Database Management Database System Fig. 1.1 Architecture and components of information systems Considering data as the basis of information as a production factor in a company has significant consequences: • Basis for decision-making: Data allows well-informed decisions, making it vital for all organizational functions. • Quality level: Data can be available from different sources; information quality depends on the availability, correctness, and completeness of the data. • Need for investments: Data gathering, storage, and processing cause work and expenses. • Degree of integration: Fields and holders of duties within any organization are connected by informational relations, meaning that the fulfillment of the said duties largely depends on the degree of data integration. Once data is viewed as a factor in production, it must be planned, governed, monitored, and controlled. This makes it necessary to see data management as a task for the executive level, inducing a major change within the company. In addition to the technical function of operating the information and communication infrastructure (production), planning and design of data flows (application portfolio) is crucial. As shown in Fig. 1.1, an information system enables users to store and connect information interactively, to ask questions, and to get answers. Depending on the type of information system, the acceptable questions may be limited. There are, however, open information systems and online platforms in the World Wide Web that use search engines to process arbitrary queries. The computer-based information system in Fig. 1.1 is connected to a communi- cation network such as the World Wide Web in order to allow for online interaction
Page 15
and global information exchange in addition to company-specific analyses. Any information system of a certain size uses database systems to avoid the necessity to redevelop database management, querying, and analysis every time it is used. 1.2 SQL Databases 3 Database systems are software for application-independently describing, storing, and querying data. All database systems contain a storage and a management component. The storage component called the database includes all data stored in organized form plus their description. The management component called the database management system (DBMS) contains a query and data manipulation language for evaluating and editing the data and information. This component not only does serve the user interface but also manages all access and editing permissions for users and applications. SQL databases (SQL = Structured Query Language, cf. Sect. 1.2) are the most common in practical use. However, providing real-time Web-based services referencing heterogeneous data sets is especially challenging (cf. Sect. 1.3 on Big Data) and has called for new solutions such as NoSQL approaches (cf. Sect. 1.4). When deciding whether to use relational or non-relational technologies, pros and cons have to be considered carefully—in some use cases, it may even be ideal to combine different technologies (cf. operating a Web shop in Sect. 5.6). Modern hybrid DBMS approaches combine SQL with non-relational aspects, either by providing NoSQL features in relational databases or by exposing an SQL querying interface to non-relational databases. Depending on the database architecture of choice, data management within the company must be established and developed with the support of qualified experts (Sect. 1.5). Further reading is listed in Sect. 1.6. 1.2 SQL Databases 1.2.1 Relational Model One of the simplest and most intuitive ways to collect and present data is in a table. Most tabular data sets can be read and understood without additional explanations. To collect information about employees, a table structure as shown in Fig. 1.2 can be used. The all-capitalized table name EMPLOYEE refers to the entire table, while the individual columns are given the desired attribute names as headers, for example, the employee number “E#,” the employee’s name “Name,” and their city of resi- dence “City.” An attribute assigns a specific data value from a predefined value range called domain as a property to each entry in the table. In the EMPLOYEE table, the attribute E# allows to uniquely identify individual employees, making it the key of the table. To mark key attributes more clearly, they will be written in italics in the table headers throughout this book.1 The attribute City is used to label the respective 1 Some major works of database literature mark key attributes by underlining.
Page 16
places of residence and the attribute Name for the names of the respective employees (Fig. 1.3). 4 1 Database Management E# Name City EMPLOYEE Table name Attribute Key attribute Fig. 1.2 Table structure for an EMPLOYEE table E# Name City EMPLOYEE Column (or attribute) E19 E4 E1 E7 Stewart Bell Murphy Howard Stow Kent Kent Cleveland Data value Record (row or tuple) Fig. 1.3 EMPLOYEE table with manifestations The required information of the employees can now easily be entered row by row. In the columns, values may appear more than once. In our example, Kent is listed as the place of residence of two employees. This is an important fact, telling us that both employee Murphy and employee Bell are living in Kent. In our EMPLOYEE table, not only cities but also employee names may exist multiple times. For that reason, the aforementioned key attribute E# is required to uniquely identify each employee in the table.
Page 17
1.2 SQL Databases 5 Identification Key An identification key or just key of a table is one attribute or a minimal combination of attributes whose values uniquely identify the records (called rows or tuples) within the table. If there are multiple keys, one of them can be chosen as the primary key. This short definition lets us infer two important properties of keys: • Uniqueness: Each key value uniquely identifies one record within the table, i.e., different tuples must not have identical keys. • Minimality: If the key is a combination of attributes, this combination must be minimal, i.e., no attribute can be removed from the combination without eliminating the unique identification. The requirements of uniqueness and minimality fully characterize an identifica- tion key. However, keys are also commonly used to reference tables among themselves. Instead of a natural attribute or a combination of natural attributes, an artificial attribute can be introduced into the table as key. The employee number E# in our example is an artificial attribute, as it is not a natural characteristic of the employees. While we are hesitant to include artificial keys or numbers as identifying attributes, especially when the information in question is personal, natural keys often result in issues with uniqueness and/or privacy. For example, if a key is constructed from parts of the name and the date of birth, it may not necessarily be unique. Moreover, natural or intelligent keys divulge information about the respec- tive person, potentially infringing on their privacy. Due to these considerations, artificial keys should be defined application-inde- pendent and without semantics (meaning, informational value). As soon as any information can be deduced from the data values of a key, there is room for interpretation. Additionally, it is quite possible that the originally well-defined principle behind the key values changes or is lost over time. Table Definition To summarize, a table is a set of rows presented in tabular form. The data records stored in the table rows, also called tuples, establish a relation between singular data values. According to this definition, the relational model considers each table as a set of unordered tuples. Tables in this sense meet the following requirements: • Table name: A table has a unique table name. • Attribute name: All attribute names are unique within a table and label one specific column with the required property. • No column order: The number of attributes is not set, and the order of the columns within the table does not matter. • No row order: The number of tuples is not set, and the order of the rows within the table does not matter.
Page 18
6 1 Database Management E# Name City EMPLOYEE E19 Stewart Stow E4 Bell Kent E1 Murphy Kent E7 Howard Cleveland Example query: “Select the names of the employees living in Kent.” Formulation with SQL: SELECT Name FROM EMPLOYEE WHERE City = 'Kent' Results table: Name Bell Murphy Fig. 1.4 Formulating a query in SQL • Identification key: Strictly speaking, tables represent relations in the mathemati- cal sense only if there are no duplicate rows. Therefore, one attribute or a combination of attributes can uniquely identify the tuples within the table and is declared the identification key. 1.2.2 Structured Query Language SQL As explained, the relational model presents information in tabular form, where each table is a set of tuples (or records) of the same type. Seeing all the data as sets makes it possible to offer query and manipulation options based on sets. The result of a selective operation, for example, is a set, i.e., each search result is returned by the database management system as a table. If no tuples of the scanned table show the respective properties, the user gets a blank result table. Manipulation operations similarly target sets and affect an entire table or individual table sections. The primary query and data manipulation language for tables is called Structured Query Language, usually shortened to SQL (see Fig. 1.4). It was standardized by
Page 19
ANSI (American National Standards Institute) and ISO (International Organization for Standardization).2 1.2 SQL Databases 7 SQL is a descriptive language, as the statements describe the desired result instead of the necessary computing steps. SQL queries follow a basic pattern as illustrated by the query from Fig. 1.4: “SELECT the attribute Name FROM the EMPLOYEE table WHERE the city is Kent.” A SELECT-FROM-WHERE query can apply to one or several tables and always generates a table as a result. In our example, the query would yield a results table with the names Bell and Murphy, as desired. The set-based method offers users a major advantage, since a single SQL query can trigger multiple actions within the database management system. Relational query and data manipulation languages are descriptive. Users get the desired results by merely setting the requested properties in the SELECT expression. They do not have to provide the procedure for computing the required records. The database management system takes on this task, processes the query or manipulation with its own search and access methods, and generates the results table. With procedural database languages on the other hand, the methods for retrieving the requested information must be programmed by the user. In that case, each query yields only one record, not a set of tuples. With its descriptive query formula, SQL requires only the specification of the desired selection conditions in the WHERE clause, while procedural languages require the user to specify an algorithm for finding the individual records. As an example, let us take a look at a query language for hierarchical databases (see Fig. 1.5): For our initial operation, we use GET_FIRST to search for the first record that meets our search criteria. Next, we access all other corresponding records individually with the command GET_NEXT until we reach the end of the file or a new hierarchy level within the database. Overall, we can conclude that procedural database management languages use record-based or navigating commands to manipulate collections of data, requiring some experience and knowledge of the database’s inner structure from the users. Occasional users basically cannot independently access and use the contents of a database. Unlike procedural languages, relational query and manipulation languages do not require the specification of access paths, processing procedures, or naviga- tional routes, which significantly reduces the development effort for database utilization. If database queries and analyses are to be done by end users instead of IT professionals, the descriptive approach is extremely useful. Research on descriptive database interfaces has shown that even occasional users have a high probability of successfully executing the desired analyses using descriptive language elements. Figure 1.5 also illustrates the similarities between SQL and natural language. In 2 ANSI is the national standards organization of the USA. The national standardization organizations are part of ISO.
Page 20
fact, there are modern relational database management systems that can be accessed with natural language. 8 1 Database Management Natural language: “Select the names of the employees living in Kent.” Descriptive language: SELECT Name FROM EMPLOYEE WHERE City = 'Kent' Procedural language: get first EMPLOYEE while status = 0 do begin end if City = 'Kent' then print(Name) get next EMPLOYEE Fig. 1.5 The difference between descriptive and procedural languages 1.2.3 Relational Database Management System Databases are used in the development and operation of information systems in order to store data centrally, permanently, and in a structured manner. As shown in Fig. 1.6, relational database management systems are integrated systems for the consistent management of tables. They offer service functionalities and the descriptive language SQL for data description, selection, and manipulation. Every relational database management system consists of a storage and a man- agement component. The storage component stores both data and the relationships between pieces of information in tables. In addition to tables with user data from various applications, it contains predefined system tables necessary for database operation. These contain descriptive information and can be queried, but not manipulated, by users. The management component’s most important part is the language SQL for relational data definition, selection, and manipulation. This component also contains service functions for data restoration after errors, for data protection, and for backup. Relational database management systems (RDBMS) have the following properties:
The above is a preview of the first 20 pages. Register to read the complete e-book.

Recommended for You

Loading recommended books...
Failed to load, please try again later

Tip the Site

Scan the WeChat Pay or Alipay code to tip. No login required.

WeChat Pay
Alipay
← Back to List