Database Design and Modeling with PostgreSQL and MySQL Build Efficient and Scalable Databases for Modern Applications Using Open Source Databases
Become well-versed with database modeling and SQL optimization, and gain a deep understanding of transactional systems through practical examples and exercises Key Features Get to grips with fundamental-to-advanced database design and modeling concepts with PostgreSQL and MySQL Explore database inte...
| Main Author: | |
|---|---|
| Other Authors: | , |
| Format: | eBook |
| Language: | Inglés |
| Published: |
Birmingham :
Packt Publishing, Limited
2024.
|
| Edition: | 1st ed |
| Subjects: | |
| See on Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009841737206719 |
Table of Contents:
- 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.
