A relational database management system (RDBMS) persists structured data in related tables. It handles storage, updates, and relationships so application code does not have to maintain those concerns itself. section 10.3 section 10.9
Tables resemble two-dimensional CSV data, but an RDBMS adds durable relationships through unique identifiers. Separating data into tables reduces redundancy; primary and foreign keys reconnect the related records. section 10.3.2
An RDBMS supports the core data operations applications need:
- Create, read, update, and delete tables and their stored data. section 10.3
- Connect records across tables with primary keys, foreign keys, auto-incrementing IDs, or UUIDs. section 10.3.2
- Preserve one-to-many and many-to-many relationships without repeating every piece of information. section 10.9 section 10.3.2
Querying related data
SQL provides a standardized, declarative interface: SELECT * FROM customer; returns every customer row, while a JOIN can combine customers, orders, addresses, items, and products into one result. section 10.3.4
SELECT c.name, a.street, a.zipcode, COUNT(c.name) AS 'Times Used' FROM CUSTOMER c JOIN 'order' o ON o.customer_id = c.customer_id JOIN address a ON a.address_id = o.address_id GROUP BY a.street ORDER BY c.name; name street zipcode Times Used ---------- ---------- ---------- ---------- Joe 12 Main St 12345 2 Mary 127 Margol 40322 1 Mary 41 Orange 40321 1 Sue 212 Grove 34213 1
Inspect a multi-table query that joins customers, orders, and addresses, then groups and sorts the results. section 10.3.4
Choosing an RDBMS involves balancing structure, consistency, query performance, and application needs. SQLite can support a small application, while larger systems may contain millions of rows. Document-oriented NoSQL databases offer a different model, though modern systems increasingly overlap. section 12.3.4
