Databases — study guide
The concept's fragments, read in order.
More than a pile of files
A flat file is a fine place to keep data right up to the moment you need to ask it a question. Search it, and you read the whole thing top to bottom. Let two programs write it at once, and they clobber each other. Lose power mid-write, and you may be left with half a record and no way to tell. Structured data can live in a file, but a file gives you nowhere to stand once the data starts to matter.
A database is the layer that takes that standing. It is structured storage with three things a flat file cannot offer: a way to query, to ask for exactly the records you want without reading everything; a way to let many clients read and write at once without corrupting each other; and durability, the guarantee that once a change is accepted it survives a crash. You give up the plainness of a file and get those guarantees in return.
Everything here is those guarantees and their price: how data is shaped into tables, how a query language lets you describe results instead of fetching them by hand, how an index makes a lookup fast, what it means for a change to be all-or-nothing, and how the strict relational model differs from the looser stores that trade some of it away.
Rows, columns, and keys
The relational model gives data one shape: the table. A table is a named collection of rows, and every row has the same set of named columns, each column holding one type of value, a number or a piece of text or a date. That fixed set of columns and their types is the table's schema, the agreed structure every row must fit.
The schema is the discipline. Because every row in a table has the same columns of the same types, the database can store them compactly, check that new data fits before accepting it, and reason about the whole table at once. A row that tried to add a stray field, or to store text where a number belongs, is simply rejected.
One column, or a small group of them, is singled out as the primary key: the value that uniquely identifies each row. Two rows may share a name or a city, but no two share a primary key, and none may leave it empty. That unique handle is what lets a single row be found, updated, or referred to from another table without ambiguity.
Say what you want, not how
Most programming tells the machine how to do something, step by step. A query language like SQL (Structured Query Language) works the other way around: you describe the result you want, and the database's engine decides how to produce it. It is declarative, a statement of the what, not a recipe for the how.
Three verbs carry most of the weight. SELECT names the data to retrieve. WHERE attaches a condition that restricts the result to the rows you care about, discarding the rest. JOIN combines rows from two or more tables into one result, matching them on a shared value, a primary key in one table lining up with a reference to it in another.
Because you state only the goal, the engine is free to choose the fastest way to reach it, whether that means scanning a table, using an index, or reordering the work, and to change that choice as the data grows, all without a single word of your query changing.
Finding a row without reading them all
Ask a table for one row by some column's value and, with no help, the database has only one move: scan the whole table, checking every row until it finds the match. On a large table that is a great deal of wasted reading to answer a small question.
An index removes the waste. It is a separate, sorted structure that maps values to the rows holding them, kept in an order the database can search by jumping rather than scanning like the index at the back of a book, which sends you straight to the right page instead of reading every page to find a term. The usual form is a B-tree, a shallow branching structure where finding a value takes only a few steps down from the top instead of a walk across every row. A full-table scan becomes a fast seek.
Nothing is free. The index is extra data that costs storage, and because it must stay in step with the table, every insert, update, or delete now has to change the index too, so writes get slower to make reads faster. That trade is the whole decision: index the columns you search on often, and leave the rest unindexed rather than pay for lookups you never make.
All of it, or none of it
Some changes only make sense together. Move money between two accounts and you have two writes, one debit and one credit, that must both happen or neither must. A transaction is the database's way to bundle several changes into one unit that either takes effect completely or not at all like a money transfer that must either complete in full or leave everything exactly as it was, never half done. If anything fails partway, the whole group is rolled back and the database is left exactly as it was.
That guarantee is the first letter of ACID, the four properties that make transactions trustworthy. Atomicity is the all-or-nothing rule just named. Consistency means a transaction moves the database from one valid state to another, never leaving broken invariants behind. Isolation means concurrent transactions do not see each other's half-finished work, so the result is as if they had run one after another. Durability means that once a transaction commits, its changes survive, even a crash a moment later.
Together they let you reason about a complex change as a single, safe step. You issue the group, and the database promises that the dangerous middle, the half-done state, is never something another client or a power failure gets to observe.
One shape does not fit all
The relational model's strengths, a fixed schema, joins across tables, and full ACID transactions, are also its constraints. Every row must fit the table's columns, and holding to that while spreading data across many machines is hard. For some workloads the constraints cost more than they are worth.
NoSQL is the umbrella for stores that drop some of those rules in exchange for scale or flexibility like rigid pre-printed forms with fixed fields versus free-form notes that each carry their own shape. They come in families: key-value stores, which map a key straight to a blob of value; document stores, where each record is a self-describing nested document with its own shape; wide-column stores, which group very large tables by families of columns; and graph stores, built for data that is mostly connections. What they share is a willingness to relax the strict schema, the joins, or the immediate consistency that relational databases insist on.
The most common thing they relax is consistency. Rather than guarantee every reader sees the latest write at once, many NoSQL systems offer eventual consistency, the promise that copies converge given a little time, often summarized as BASE against the relational ACID. It buys scale and availability, and asks you to tolerate a brief window where two readers might disagree. Neither shape is better; they answer different questions.
Many writers at once
A database rarely serves one client at a time. Many connections read and write the same tables at once, and without care their operations interleave in ways that corrupt data: two transactions read the same value, each updates it, and one silently overwrites the other.
Isolation levels are the dial that governs how much concurrent transactions are allowed to affect each other. The SQL standard defines four, from read uncommitted, which can see others' unfinished work, up to serializable, which behaves as if transactions ran one at a time. Tighter isolation is safer and slower; looser isolation is faster and admits more anomalies. Choosing the level is choosing where on that scale a workload should sit.
Underneath, databases enforce these levels by one of two strategies. Locking makes a transaction claim a row or table so others must wait, trading throughput for safety. Multi-version concurrency control (MVCC) instead keeps several versions of a value, so a reader sees a consistent snapshot while a writer prepares the next version, and reading need not block writing. Either way the goal is the same: let many clients work at once as if each had the database to itself.
Where your app's state really lives
Nearly every application that outlives a single run has to keep its state somewhere, and that somewhere is almost always a database. A team tool holds its users, projects, and permissions in one; an agent pipeline records its tasks, results, and history the same way. The database is where the application's real, durable state lives, and what the application can reliably do later is bounded by what that store guarantees.
The choice of shape is load-bearing. Pick a relational database and you get schemas and joins and transactions: easy to ask questions that span your data, hard to change the shape of that data on a whim. Pick a document store and you get flexibility up front: easy to evolve each record, harder to enforce consistency across many. The decision is cheap to make on day one and expensive to reverse once real data has piled up in the chosen shape.
So the database is not a detail bolted on at the end. It is the ground the rest of the system stands on, and the guarantees you pick, durability, transactions, the fixed schema or the loose one, are the ones your whole application inherits.