What kind of thing?
Customer, order, sensor?
Entities define the objects the system needs to represent.
Side 56
Databases
A study of persistent structured information. Databases turn real-world entities into schemas, queries and transactions while preserving integrity under repeated reads and writes.
data→schema→query→transaction→persistence
06core lenses
05query ideas
04ACID properties
56Side
A database begins by deciding what exists.
Schema design translates a domain into entities, attributes and relationships.
Which properties matter?
Name, time, status, amount?
Attributes encode the facts stored about each entity.
How is identity preserved?
Primary + foreign keys.
Keys connect records without relying on ambiguous descriptive fields.
How do entities connect?
One-to-one, one-to-many, many-to-many?
Relationship structure determines joins and integrity rules.
What states are invalid?
Nullability, uniqueness, references.
Constraints push data correctness into the persistence layer.
Queries are questions over stored structure.
Relational queries describe the result wanted without prescribing every physical step used to obtain it.
Filter rows.
Predicates restrict the result to relevant records.
Choose columns.
Return only the attributes needed for the task.
Combine related tables.
Joins reconstruct relationships distributed across normalized tables.
Summarize groups.
Counts, sums and averages compress many rows into group-level results.
Compute across related rows.
Window functions preserve row detail while adding rank, running totals or comparisons.
The optimizer chooses execution.
Equivalent SQL can have very different physical query plans.
Indexes trade write cost and storage for faster lookup.
An index is an auxiliary structure that helps the database avoid scanning every row.
B-tree
Ordered access.
Supports equality and range queries efficiently.
Hash
Fast equality lookup.
Useful when ordering is not required.
Composite
Several columns in one index.
Column order matters for which predicates can use the structure efficiently.
Covering
Answer from the index alone.
Including required columns can avoid extra table lookups.
Cost
Indexes must be maintained.
Each insert, update and delete may require additional index work.
Transactions make multi-step changes behave coherently.
They protect data from partial updates and concurrent interference.
| Property | Meaning | Failure prevented |
|---|---|---|
| Atomicity | All or nothing | Half-completed update |
| Consistency | Rules remain satisfied | Invalid database state |
| Isolation | Concurrent work is controlled | Interference between transactions |
| Durability | Committed changes survive failure | Lost committed work |
Logical tables eventually become bytes on durable media.
Page layout, caching, logs and recovery connect high-level queries to storage hardware.
Unit of storage transfer.
Databases read and write blocks rather than individual abstract rows.
Cache hot pages in memory.
Memory management strongly affects performance.
Record changes before data pages.
Logs enable crash recovery and durability.
Bound recovery work.
Periodic checkpoints reduce how much log must be replayed after a crash.
Maintain additional copies.
Replication can improve availability and read scale while introducing consistency trade-offs.
Recover from larger loss.
Replication is not a substitute for independent backups.
Good schema design preserves meaning under change.
Normalization reduces contradictory duplication; denormalization can deliberately trade purity for performance.
Normalize
Separate facts so each authoritative fact has a clear home.
Constrain
Encode business invariants with keys and checks where practical.
Index
Design around actual query patterns rather than indexing every field.
Observe
Inspect query plans, latency and storage growth before optimizing.
Evolve
Schema migrations must preserve data and application compatibility across versions.
Database System ConceptsSilberschatz et al. · database foundation
Designing Data-Intensive ApplicationsMartin Kleppmann · storage and distributed data
SQL and Relational TheoryC.J. Date · relational model
Database InternalsAlex Petrov · storage engines