Database Fundamentals
A database is a managed collection of data with a query language, integrity rules, and a storage engine.
Level: beginner
What is it?
A database system stores data, defines its structure, enforces constraints, accepts queries, coordinates concurrent access, and recovers from failures. A DBMS is the software providing these capabilities; the database is the managed data and metadata.
Why does it matter?
Applications need durable state that remains coherent across requests, concurrent users, and process restarts. A DBMS reduces the amount of custom code needed for indexing, transactions, backup, access control, and query execution.
Mental model
Separate the logical model from the physical engine. A query expresses which rows or values are wanted; the optimizer chooses a plan, and the storage engine reads and modifies the underlying pages.
How it works
- Schema describes data structures and constraints; data are the current stored values.
- Queries are declarative in systems such as SQL: they describe results rather than an exact sequence of operations.
- Indexes accelerate selected access patterns but consume storage and add write work.
- Transactions group changes and provide guarantees about failure and concurrency.
- Backups, logs, replication, access control, and monitoring are part of operating a real database.
Steps
- A client submits a parameterized query.
- The parser validates syntax and resolves referenced tables and columns.
- The optimizer compares candidate plans using statistics and estimated cost.
- The executor runs the chosen plan against buffers/storage and returns rows or an error.
Real-world usage
- Selecting a persistence layer for an application.
- Diagnosing slow queries and understanding why an index may or may not help.
- Explaining the difference between DBMS, schema, query, transaction, and storage engine.
Common mistakes
- Assuming a database is only a collection of tables; logs, indexes, constraints, and metadata matter.
- Building SQL through string concatenation with untrusted input instead of parameters.
- Confusing an accepted write with a durable write when transaction and storage guarantees matter.
Interview questions
What responsibilities does a DBMS provide beyond storing bytes?
It provides a data model, query execution, constraints, concurrency control, recovery, security, and operational tools such as backups and indexing.
Why might two equivalent SQL queries have different performance?
The optimizer may choose different access paths or join orders based on query shape, statistics, indexes, and estimated row counts.