Big Data and AI

  Chapter 14: Transactions and Concurrency In a multiuser environment, concurrent access to the same data can lead to inconsistencies, lost updates, and other anomalies. Transactions and concurrency control ensure that your database remains accurate, reliable, and performant even under heavy load. In this chapter you’ll explore: The ACID properties: Atomicity, Consistency, Isolation, Durability Transaction control commands and best practices Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) Techniques for detecting and resolving deadlocks Strategies for building robust, concurrent applications 1. The ACID Properties ACID defines the four guarantees every transaction must uphold: Atomicity A transaction is an all-or-nothing unit. Either every operation succeeds, or all effects are rolled back on failure. Consistency A transaction transforms the database from one valid state to another, respecting all schema constraints, triggers, and business r...

  Chapter 13: Views, Stored Procedures, and Functions As your database needs grow more sophisticated, embedding business logic and reusable patterns directly into the database saves time, reduces errors, and simplifies application code. In this chapter, you’ll learn how to: Define views to encapsulate complex queries and standardize data access Write stored procedures that automate multi-step tasks and maintain transactional integrity Create user-defined functions for reusable calculations and transformations Manage parameters , implement error handling , and assign permissions for safe, controlled execution Harnessing these programmability features turns your database from a simple data store into a powerful, self-documenting service layer. 1. Defining Views for Reusable Query Patterns A view is a virtual table—essentially a named SELECT statement—that you can query as if it were a real table. Views simplify queries, enforce security, and provide a stable interface when unde...

  By now, you’ve mastered the foundations of SQL—from basic queries to table design. In this final module, we’ll dive into three advanced pillars that elevate your database skills: Views, Stored Procedures, and Functions Transactions and Concurrency Performance Tuning & Best Practices These topics empower you to encapsulate logic, ensure data integrity under load, and optimize queries for real-world workloads. Views, Stored Procedures, and Functions Encapsulating complex logic in the database makes your applications cleaner and more maintainable. Views A view is a virtual table defined by a SQL query. Use views to: Simplify joins and aggregations into a single, reusable object Enforce data security by exposing only selected columns Provide backward compatibility when underlying schemas change Example: sql CREATE VIEW sales_summary AS SELECT product_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(quantity * price) AS total_revenue FROM sales GROUP BY product_id,...

  Chapter 12: Keys and Indexes Designing a robust schema goes beyond defining tables and columns. The right keys and indexes enforce data integrity, prevent anomalies, and turbocharge query performance. In this chapter, you’ll learn how to: Distinguish and implement primary keys and surrogate keys Configure foreign keys to uphold referential integrity Create the most effective indexes for your queries Balance speed gains against storage and maintenance costs By the end, you’ll know how to architect schemas that both safeguard your data and serve queries at lightning speed. 1. Primary Keys and Surrogate Keys A table’s primary key uniquely identifies each row. Choosing the right key type sets the foundation for joins, lookups, and integrity. 1.1 Natural vs. Surrogate Keys Natural Key An existing attribute or set of attributes that uniquely describes the entity (e.g., a country’s ISO code, a user’s email). • Pros: No extra column needed; human-readable. • Cons: Subject to business-...

  Part V: Designing Your Schema Chapter 11: Creating and Altering Tables Building a robust database schema starts with thoughtfully created tables and evolves with carefully applied modifications. In this chapter, you’ll learn how to: Define tables using CREATE TABLE Choose appropriate data types and sizes for each column Enforce data-quality rules through constraints ( NOT NULL , UNIQUE , CHECK ) Evolve schemas safely with ALTER TABLE operations By following these best practices, you’ll lay the foundation for reliable, scalable databases that adapt to changing business needs. 1. Defining Tables with CREATE TABLE 1.1 Core Syntax The CREATE TABLE statement introduces a new table and its columns to the database. Always specify columns explicitly along with their data types and constraints: sql CREATE TABLE table_name ( column1_name data_type [constraints], column2_name data_type [constraints], …, columnN_name data_type [constraints] ); table_name : your chosen table identif...