Grokking Relational Database Design (Qiang Hao, Michail Tsikerdekis) (z-library.sk, 1lib.sk, z-lib.sk)

📄 File Format: PDF

💾 File Size: 21.7 MB

📖 Read Online ⬇️ Download

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

(This page has no text content)

📄 Page 2

       Grokking Relational Database Design   Qiang Hao and Michail Tsikerdekis   To comment go to liveBook     Manning Shelter Island   For more information on this and other Manning titles go to www.manning.com  

📄 Page 3

Copyright For online information and ordering of this and other Manning books, please visit www.manning.com. The publisher offers discounts on these books when ordered in quantity. For more information, please contact    Special Sales Department Manning Publications Co. 20 Baldwin Road PO Box 761 Shelter Island, NY 11964 Email: orders@manning.com    ©2025 by Manning Publications Co. All rights reserved.    No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning

📄 Page 4

Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps. ♾ Recognizing the importance of preserving what has been written, it is Manning’s policy to have the books we publish printed on acid-free paper, and we exert our best efforts to that end. Recognizing also our responsibility to conserve the resources of our planet, Manning books are printed on paper that is at least 15 percent recycled and processed without the use of elemental chlorine. The author and publisher have made every effort to ensure that the information in this book was correct at press time. The author and publisher do not assume and hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause, or from any usage of the information herein.        Manning Publications Co. 20 Baldwin Road Technical PO Box 761 Shelter Island, NY 11964    Development editor:   Elesha Hyde Technical editor:   Louis Davidson Review editors:   Aleksandar Dragosavljević and Kishor Rit Production editor:   Andy Marinkovich Copy editor:   Keir Simpson

📄 Page 5

Proofreader:   Keri Hales Technical proofreader:   Ross Turner Typesetter:   Dennis Dalinnik Illustrator:   Cody Pham Cover designer:   Marija Tudor       ISBN: 9781633437418

📄 Page 6

dedication To our family and friends, for their endless support and encouragement. And to all those who believe in the power of curiosity and persistence, may this book serve as a companion on your journey of learning.

📄 Page 7

contents           Front matter preface acknowledgments about this book about the authors    Part 1.  Get started   1   Introducing databases and SQL Overview Your first SQL query The basics of SQL queries Table and data management Recap   2   Related tables and more SQL Overview Related tables Revisit table and data management Learn more SQL on your own Recap   3   Overview of database design

📄 Page 8

Goals of database design Overview of the design process Key phases of database design Recap Part 2.  Master database design   4   Entities and attributes Overview Entities and attributes Keys Data types Recap   5   Relationships Overview Entity-relationship diagrams& Connect related entities Cardinality Strong and weak entities Recap   6   Normalization and implementation Overview Normalization Implementation Recap

📄 Page 9

  7   Security and optimization Overview Security Storage considerations Indexing Denormalization Recap Part 3.  Database design and AI   8   Database design in the age of generative AI Overview Requirements analysis Entities and attributes Relationships among entities Implementation& Recap           index

📄 Page 10

front matter preface For years, we have taught courses on database management systems, and one recurring challenge we’ve noticed is the overwhelming difficulty students have with conventional textbooks. These materials, while comprehensive, often feel tedious and painful for students to chew through, making the learning process more daunting than it needs to be. Our teaching experiences led us to envision a different kind of resource—one that makes the principles of relational database design more approachable and engaging. That opportunity came when Dr. Daniel Zingaro, an editor at Manning and a colleague from the University of Toronto Mississauga, reached out to us. Daniel asked whether we would be interested in writing a book that could bring these concepts to life in a way that learners could better grasp and enjoy. The result is Grokking Relational Database Design, a book that focuses not just on the technicalities but also on how to teach them effectively, emphasizing clarity, real-world relevance, and a step-by-step approach. Whether you’re new to the field or seeking a refresher on concepts you learned years ago, this book is designed for you.

📄 Page 11

In today’s world, where AI and machine learning are rapidly transforming industries, the role of relational databases remains fundamental. Although new technologies such as NoSQL databases and Big Data solutions have emerged, relational databases continue to be the backbone of data storage, especially in environments that require structured, reliable, scalable data solutions. Moreover, the rise of AI and machine learning has only increased the demand for clean, organized data—something that relational databases excel at providing. By mastering relational database design, you’ll not only gain a crucial skill but also position yourself to harness the full potential of AI and machine learning, ensuring the integrity and accessibility of the data that powers these technologies. We hope that this book becomes a valuable resource for anyone venturing into this critical area of technology and helps bridge the gap between foundational database principles and their applications in the rapidly evolving tech landscape. acknowledgments We would like to sincerely thank the following people for their invaluable contributions to this book: Cody Pham—Cody created all the illustrations for this book. His vivid visuals greatly enhance its readability, making complex concepts easier to grasp.

📄 Page 12

Dr. Daniel Zingaro—Dr. Zingaro was instrumental in helping us define the scope of the book. He offered insightful guidance on structuring the content across chapters. Elesha Hyde—Elesha provided invaluable support through her detailed suggestions and feedback, which were essential to the editing process. Louis Davidson—Louis offered insightful suggestions and feedback that strengthened the robustness and clarity of each chapter. Jonathan Gennick—Jonathan contributed useful suggestions to the revision of this book. The team at Manning Publications—We are grateful to the team at Manning for their expertise and dedication in helping bring this book to life. All the reviewers who took the time to provide their valuable feedback—David Spenn, Ganesh Falak, Ganesh Swaminathan, Grant Colley, Heng Zhang, Jim Amrhein, José Alberto Reyes Quevedo, Marcus Geselle, Maxim Volgin, Nadir Doctor, Oliver Korten, Orlando Méndez Morales, Patrick Regan, Peter A. Schott, Potito Coluccelli, Radhakrishna MV, Sasha Sankova, Trevoir Williams, Valerie Parham-Thompson, Víctor Durán, Vojta Tuma, William Jamir Silva, and Yilun Zhang. Your suggestions helped improve the book. about this book

📄 Page 13

Grokking Relational Database Design teaches the art of database design through hands-on projects, insightful illustrations, and practical, action-oriented learning. Unlike many introductory books that focus primarily on SQL syntax and formal database theory, this book emphasizes a foundational approach to relational design thinking. You’ll learn how to structure databases from the ground up, creating systems that are both efficient and intuitive— databases that stand the test of time and are a pleasure to work with long after they’re built. Who should read this book? This book is written primarily for those who are new to database design or are seeking a refresher on concepts they may have learned years ago. Each chapter is crafted to be both accessible and thorough, providing practical guidance on every key concept essential to designing effective relational databases. How this book is organized: A road map This book is organized in three parts covering eight chapters:

📄 Page 14

Part 1, Get started—The first part introduces the essentials. In chapters 1 and 2, you gain foundational knowledge of Structured Query Language (SQL), which is essential for effective database design. Chapter 3 provides a comprehensive overview of the principles and processes that define successful database design. Part 2, Master database design—Spanning four chapters, this part delves into the core skills needed to excel at database design: Entities and attributes—Learn how to identify and define entities and their attributes based on requirements analysis, laying the groundwork for a well-structured database. Relationships—Discover how to establish meaningful relationships among entities, guided by design principles that ensure consistency and usability. Normalization and implementation—Explore the process of normalizing your design to minimize data anomalies during insertion, updates, and deletions. You’ll also learn how to translate your design into SQL code, bringing your database to life. Security and optimization—Gain practical insights into securing and optimizing your database, addressing performance, and protecting data integrity.

📄 Page 15

Part 3, Database design and AI—In this final part, you see how generative AI can accelerate the design process, helping you streamline workflows and enhance efficiency in database design. You walk through the full process of designing a database from scratch with the help of ChatGPT. About the code You can get executable snippets of code from the liveBook (online) version of this book at https://livebook.manning.com/book/grokking-relational- database-design. The complete code for the examples in the book is available for download from the Manning website at www.manning.com and from GitHub at https://github.com/Neo-Hao/grokking-relational-database- design. Each chapter has a dedicated folder in the repository, where you’ll find scripts and step-by-step instructions that align with the content of that chapter. liveBook discussion forum Purchase of Grokking Relational Database Design includes free access to liveBook, Manning’s online reading platform. Using liveBook’s exclusive discussion features, you can attach comments to the book globally or to specific sections or paragraphs. It’s a snap to make notes for yourself, ask and answer technical questions, and receive help from the authors and other users. To access the forum, go to https://livebook.manning.com/book/grokking-relational-

📄 Page 16

database-design/discussion. You can also learn more about Manning’s forums and the rules of conduct at https://livebook.manning.com/discussion. Manning’s commitment to our readers is to provide a venue where meaningful dialogue between individual readers and between readers and authors can take place. It is not a commitment to any specific amount of participation on the part of the authors, whose contributions to the forum remain voluntary (and unpaid). We suggest you try asking the authors some challenging questions lest their interest stray! The forum and the archives of previous discussions will be accessible on the publisher’s website as long as the book is in print. about the authors

📄 Page 17

DR. QIANG HAO is an Associate Professor of Computer Science at Western Washington University. His research in computing education is widely recognized and cited within the academic community. Dr. Hao has extensive experience teaching a broad range of courses, such as database systems, data structures, and mobile development. His innovative approach to teaching has earned him high praise from both students and colleagues. DR. MICHAIL TSIKERDEKIS is an Associate Professor of Computer Science at Western Washington University. His research in cybersecurity has been featured in many top-tier journals and conferences. Over the years, he has taught a variety of courses in various departments such as computer science, information science, and sociology, including database systems and database concepts. He is an IEEE Senior Member and a 24/25 U.S. Fulbright Scholar.

📄 Page 18

Part 1. Get started Welcome to the journey into relational database design! In this part, you’ll gain foundational skills in Structured Query Language (SQL) that are essential for designing efficient and effective databases. By mastering these basics, you’ll be prepared to tackle advanced database concepts with confidence. This part is the “laying the foundation before building the structure” phase. Whether you’re new to SQL or need a refresher, this section will guide you through the essentials. Chapter 1 begins with the basics of SQL that you need for database design. Think of this chapter as your “Hello, World!” moment in database exploration; you’ll write your first SQL queries and begin interacting with data in meaningful ways. Chapter 2 dives a bit deeper into SQL by focusing on expressing the relationships between tables. By the end, you’ll have everything you need to use SQL for database design. Chapter 3 offers a bird’s-eye view of database design. You’ll explore the full process of good database design and what matters at each step. Prepare your tool—a notepad, laptop, or database application—and settle into a focused space. Let’s dive into the exciting world of relational database design!

📄 Page 19

1 Introducing databases and SQL In this chapter You get a foundation for the rest of the book. You learn the basics of relational databases. You peek into database design. You write your first SQL query and learn more about the basics of SQL. What you need to know As you read this chapter, you will find some code snippets. If you want to execute those code snippets or see what changes need to be made to the code for different relational database management systems (RDBMSs), check the GitHub repository that accompanies this book (https://github.com/Neo-Hao/grokking-relational-database- design). You can find the scripts for this chapter in the chapter_01 folder; follow the instructions in the README.md file to run the scripts. Overview

📄 Page 20

Database design is a critical yet easily neglected step in software development. Nearly every application requires data storage and management to some extent, but not every application has a well-designed database. If you design a database without knowing the principles of effective database design, your application may suffer from problems you weren’t expecting, such as disorganized data or queries that take too long and too many resources to run. These problems can lead to bugs and a bad user experience. By contrast, effective database design can serve as a solid foundation for effective software development. Effective database design makes sure that an application’s data is well organized and structured, which in turn supports efficient data querying and manipulation that contributes to