Databases and Data Management

A relational database organizes data into tables, each row representing one record and each column one attribute, with relationships between tables expressed through shared key values rather than nested structure. The Structured Query Language that manipulates this model is formalized by the international standard ISO/IEC 9075, which defines both a data-definition sublanguage for creating and altering table structure (schemas) and a data-manipulation sublanguage for inserting, updating, deleting, and querying rows. Worked example: a course-enrollment system might have a `students` table (columns: `student_id`, `name`) and an `enrollments` table (columns: `student_id`, `course_id`), where `student_id` in `enrollments` is a foreign key referencing `students`; a query joining the two tables on that shared column can answer "which courses is student 42 enrolled in" without duplicating the student's name into every enrollment row.

This normalization — splitting data so each fact is stored exactly once and related through keys rather than repeated — is the relational model's central discipline, and it is what keeps a database consistent as it grows: updating a student's name in one place automatically makes every query that joins against `students` reflect the change, rather than requiring a search-and-replace across thousands of duplicated copies. The schema itself, defined by ISO/IEC 9075's data-definition language, also acts as a contract: because every row in a table must have the same columns with the same declared types, an application reading from the database can rely on the shape of the data it receives without checking case by case.

For AI systems, this matters wherever data has a clear, stable, and structured shape — user accounts, transaction logs, labeled training examples with a fixed set of fields — because SQL's declarative queries let an engineer ask for exactly the slice of data needed ("all labeled examples added in the last week where the label confidence is below 0.6") without writing a custom loop to scan and filter records by hand, and the schema's guarantees mean that slice arrives in a predictable, well-typed shape ready to feed into a training pipeline.

Two properties determine whether a database is fast and trustworthy under real workloads: indexing and transactions. PostgreSQL's official glossary defines an index as "a relation that contains data derived from a table..., its internal structure supports fast retrieval of and access to the original data" — in effect, a separate, ordered lookup structure built on top of one or more columns so that finding rows matching a condition does not require scanning every row in the table (a direct database-level echo of the array-versus-hash-table trade-off from the data structures module). Worked example: without an index on `student_id`, finding a specific student's enrollment records means scanning the entire `enrollments` table; with an index on that column, the database can jump almost directly to the matching rows, the same way a hash table turns a linear scan into a near-constant-time lookup.

A transaction, per the same glossary, is "a combination of commands that must act as a single atomic command: they all succeed or all fail as a single unit," with effects not visible to other sessions until the transaction completes. This guarantee is formalized as ACID — Atomicity (all-or-nothing execution), Consistency (the database always satisfies its declared integrity constraints), Isolation (concurrent transactions do not see each other's uncommitted changes), and Durability (once committed, changes survive even a crash immediately afterward). Worked example: moving a student from one course section to another might require deleting one enrollment row and inserting a new one; wrapped in a single transaction, either both changes happen or, if the insert fails partway through, the delete is rolled back too — without a transaction, a crash between the two steps could leave the student enrolled nowhere.

Indexing and transactions address different failure modes but reinforce each other in practice: an index makes the individual read and write operations inside a transaction fast, while the transaction guarantees that a sequence of those operations either all take effect together or none do, which matters enormously for AI systems logging training runs, updating model version records, or recording feature-store writes, where a partially applied update (a model marked "active" without its weights file reference actually being written) is far worse than a slow one.

Not all data fits neatly into fixed rows and columns, and NoSQL — "not only SQL" — databases relax the relational model's rigid schema to accommodate it. MongoDB's documentation describes several major NoSQL types: document-oriented databases store data as JSON-like documents with nested field-value pairs (useful when related information, like a user's address and list of hobbies, naturally belongs together rather than split across joined tables); key-value stores associate a unique key with a single value, optimized for very fast reads and writes such as caching; wide-column stores allow different rows within the same table to have different sets of columns, suited to sparse data; and graph databases store nodes and the relationships between them directly, built for traversing complex networks of connections. Worked example: a user's profile with a variable-length list of hobbies and a nested address fits naturally as a single MongoDB-style document, avoiding the multi-table join the same data would require in a strictly relational schema.

Vector databases address a different problem entirely: retrieving items by semantic similarity rather than exact match. AWS describes a vector database as one that stores data as high-dimensional numeric vectors — produced by machine learning models that encode text, images, or audio into embeddings capturing meaning and context — and supports efficient nearest-neighbor search using specialized indexing algorithms such as HNSW or IVF, ranking results by a distance function like cosine similarity rather than an equality check. Worked example: a support-ticket assistant embeds every past ticket as a vector; when a new question arrives, it is embedded the same way, and a nearest-neighbor search over the stored vectors retrieves the ten most semantically similar past tickets — a query no relational `WHERE` clause or NoSQL key lookup could express, because the match is based on meaning rather than any shared field value.

This is why the essential question that opens this module has no single right database: a retrieval-augmented AI assistant plausibly needs a relational or document store for exact-match operations (looking up a specific ticket by its ID, respecting foreign-key relationships between tickets and customers) and a vector database layered alongside it for semantic retrieval, with the two often used together in the same system rather than as competitors. Choosing well means recognizing which of a system's access patterns are exact-match and structured (favoring SQL or a document store) versus similarity-based over unstructured content (favoring a vector index) — a judgment this lesson's three storage models, considered together, are meant to equip.