11.3.1: SQL Databases (Relational Databases)

SQL databases are relational database management systems (RDBMS) that use the Structured Query Language (SQL) for defining and manipulating data. They are built on the relational model introduced by E.F. Codd, where data is organized into tables with fixed schemas (columns of defined data types) and rows representing records. Each table row has a primary key, and relationships between tables are established via foreign keys – enabling joins and complex queries across normalized data. SQL databases ensure ACID properties (Atomicity, Consistency, Isolation, Durability) for transactions, meaning they excel at maintaining data integrity and performing multi-step updates safely (e.g., transferring money between bank accounts updates two records or none).

Historically, SQL databases like Oracle, MySQL, PostgreSQL, Microsoft SQL Server have been the backbone of enterprise applications. In the cloud, many of these are available as managed services (e.g., Amazon RDS for MySQL/Postgres/Oracle/SQLServer, Azure SQL Database, Google Cloud SQL) which take care of provisioning, backups, and automatic failover. A newer cloud-born relational database is Amazon Aurora, which is MySQL/PostgreSQL-compatible but designed with a distributed storage subsystem that replicates data across 6 copies in 3 availability zones for high durability and availability. Aurora exemplifies how cloud SQL databases have evolved to address some scalability and reliability concerns by separating storage and compute and offering read replicas, etc.

Use cases for SQL databases: They are ideal for structured data and transactional workloads – financial systems, inventory management, customer records, orders, etc., where data consistency is paramount. If you need complex queries (JOINs across multiple tables, aggregations) or strong referential integrity (ensuring that relationships like “every order must belong to a valid customer” are enforced), relational databases are the go-to choice. For example, an e-commerce application might use a SQL database to maintain product catalogs, user accounts, and orders, since those require transactions and schema-defined relationships.

However, traditional SQL databases often scale vertically (on a single machine) and have limits on handling massive data or very high traffic. They typically can be scaled out with read replicas for reads, but writes usually go to a single master node, which can become a bottleneck. In cloud environments, one can mitigate this by using larger instance sizes or sharding the database (partitioning data across multiple databases by key), but this adds complexity. This limitation in horizontal scalability partly led to the rise of NoSQL systems.

---