
Database Design and Modeling with PostgreSQL and MySQL
Description
Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.
Database Design and Modeling with PostgreSQL and MySQL will equip you with the knowledge and skills you need to architect, build, and optimize efficient databases using two of the most popular open-source platforms. As you progress through the chapters, you'll gain a deep understanding of data modeling, normalization, and query optimization, supported by hands-on exercises and real-world case studies that will reinforce your learning. You'll explore topics like concurrency control, backup and recovery strategies, and seamless integration with web and mobile applications. These advanced topics will empower you to tackle complex database challenges confidently and effectively. Additionally, you'll explore emerging trends, such as NoSQL databases and cloud-based solutions, ensuring you're well-versed in the latest developments shaping the database landscape. By embracing these cutting-edge technologies, you'll be prepared to adapt and innovate in today's ever-evolving digital world. By the end of this book, you'll be able to understand the technologies that exist to design a modern and scalable database for developing web applications using MySQL and PostgreSQL open-source databases.
All prices
More details
Other editions
Additional editions

Content
- Cover
- Copyright
- Foreword
- Contributors
- Table of Contents
- Preface
- Part 1: Introduction to Databases
- Chapter 1: SQL and NoSQL Databases: Characteristics, Design, and Trade-Offs
- Understanding databases and data models
- Exploring the relational data model (SQL databases)
- Tables, rows, and columns
- Normalization
- Structured Query Language (SQL)
- ACID transactions
- Navigating the document data model (NoSQL databases)
- Data models in NoSQL
- Types of NoSQL databases
- Key-value stores
- Document stores
- Column-family stores
- Graph databases
- Applying the CAP theorem and NoSQL design choices
- Consistency
- Availability
- Partition tolerance
- Consistency models in NoSQL databases
- NoSQL design choices and use cases
- Managing transaction management and concurrency control in NoSQL
- BASE transactions in NoSQL databases
- Reasons for the BASE model in NoSQL databases
- Implications for data integrity and concurrency control
- Analyzing the advantages and disadvantages of NoSQL databases
- Advantages of NoSQL databases
- Disadvantages of NoSQL databases
- Summary
- Chapter 2: Building a Strong Foundation for Database Design
- The importance of a solid foundation in database design
- Key terms and data models
- Understanding the relational model in detail
- Identifying entities, attributes, and relationships in database design
- Creating an ER diagram
- Advanced database design
- Normalization - reducing redundancy and improving data integrity
- Achieving normal forms - 1NF, 2NF, and 3NF
- Ensuring database validity and integrity
- Concluding thoughts
- Summary
- Part 2: Practical Implementation
- Chapter 3: Getting Your Hands Dirty with PostgreSQL and MySQL
- Understanding the sample database
- EDA and preprocessing
- Database schema
- MySQL
- Concluding thoughts
- Summary
- Part 3: Core Concepts in Database Design
- Chapter 4: Mastering the Building Blocks of Database Design and Modeling
- Understanding database objects
- Understanding data types and constraints
- Keys and how to use them
- Database checks and constraints
- Check constraint
- Default constraint
- Not null constraint
- Checking data quality with constraints
- No date in the future
- Value within a specific range
- How to avoid redundancy
- Database consistency and beyond
- Transactions - ensuring data integrity
- Concurrency control - managing multiple users
- PostgreSQL
- MySQL
- Summary
- Part 4: Advanced Database Techniques
- Chapter 5: Advanced Techniques for Advanced Databases
- Creating custom views of your data
- Understanding the purpose of views
- The advantages of using views in database management
- Indexing - how to find data faster
- Understanding indexing
- Types of indexes
- How indexing works
- PostgreSQL indexes
- MySQL indexes
- Stored procedures - reusable code for your database
- UDFs
- The essence of UDFs
- Efficiency through reusability - the role of UDFs in code optimization
- UDFs and performance optimization in database operations
- Using UDFs to enrich SQL queries for expressive interactions
- Practical insights into UDFs in MySQL and PostgreSQL
- Understanding CTEs
- The role of CTEs in code modularity
- Components and integration of CTEs
- Guidelines for efficient CTE usage
- Advanced strategies with CTEs
- Case studies in action - real-world examples of CTE transformations
- Summary
- Chapter 6: Understanding Database Scalability
- Introducing database scaling
- Challenges in database scaling
- Primary methods of database scaling
- Vitess - a horizontal scaling solution for MySQL
- Citus - a horizontal scaling solution for PostgreSQL
- Vertical scaling - enhancing capacity within existing infrastructure
- InnoDB Cluster for MySQL
- Sharding and resharding
- Future trends and emerging challenges in database scaling
- Serverless databases
- Summary
- Part 5: Best Practices and Future Trends
- Chapter 7: Best Practices for Building and Maintaining Your Database
- Designing for performance - optimizing database efficiency
- Schema design
- Query optimization
- Advanced query optimization techniques
- Using subqueries and JOIN clauses wisely
- Utilizing temporary tables
- Optimizing aggregates and GROUP BY clauses
- Using an index to assist GROUP BY clauses
- Iterative process and analysis
- Understanding database scaling
- Vertical scaling
- Horizontal scaling
- Replication in PostgreSQL
- MySQL replication
- Ensuring database security
- Access control
- Implementing robust authentication mechanisms
- Permission models
- PostgreSQL permission model
- Roles and privileges
- Example setup in PostgreSQL
- MySQL permission model
- Privileges and levels
- Example setup in MySQL
- Management and flexibility
- Exploring data encryption
- Protecting data at rest
- Protecting data in transit
- Monitoring and auditing
- Data quality and governance - maintaining high standards
- Learning about data cleaning
- Data governance frameworks
- Compliance
- Collaboration and documentation
- Effective communication
- Documentation practices
- Advanced topics in database management
- Backup and recovery
- Tools for monitoring PostgreSQL
- MySQL performance monitoring tools
- Strategies for regular performance analysis
- Summary
- Chapter 8: The Future of Databases and Their Designs
- Understanding vectorized search
- MySQL enhancements and innovations
- MySQL HeatWave
- Prospects of use cases of HeatWave
- TiDB as a MySQL drop-in replacement for distributed systems
- PostgreSQL - expanding horizons
- pgEdge - fully distributed PostgreSQL optimized for the network edge
- Multi-master replication in pgEdge
- Simplified cluster management
- Enhanced backup and recovery
- Advanced monitoring and alerting
- EnterpriseDB - elevating PostgreSQL for the enterprise
- Choosing your EDB deployment method
- Introduction to columnar databases with ClickHouse
- What is ClickHouse?
- Choosing the right alternative
- Summary
- Index
- Other Books You May Enjoy
System requirements
File format: ePUB
Copy protection: Adobe-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Install the free reader Adobe Digital Editions prior to download (see eBook Help).
- Tablet/smartphone (Android; iOS): Install the free app Adobe Digital Editions or the app PocketBook before downloading (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (not Kindle).
The file format ePub works well for novels and non-fiction books – i.e., „flowing” text without complex layout. On an e-reader or smartphone, line and page breaks automatically adjust to fit the small displays.
This eBook uses Adobe-DRM, a „hard” copy protection. If the necessary requirements are not met, unfortunately you will not be able to open the eBook. You will therefore need to prepare your reading hardware before downloading.
Please note: We strongly recommend that you authorise using your personal Adobe ID after installation of any reading software.
For more information, see our ebook Help page.
File format: ePUB
Copy protection: without DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Use a reader that can handle the file format ePUB, such as Adobe Digital Editions or FBReader – both free (see eBook Help).
- Tablet/Smartphone (Android; iOS): Install the free app Adobe Digital Editions or the app PocketBook (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (not Kindle).
The file format ePUB works well for novels and non-fiction books – i.e., 'flowing' text without complex layout. On an e-reader or smartphone, line and page breaks automatically adjust to fit the small displays.
This eBook does not use copy protection or Digital Rights Management
For more information, see our eBook Help page.