15 Key Terms in Databases Everyone Should Know
Introduction
Databases do much more than hold application data. They organize information, enforce rules, support concurrent users, execute queries, recover from failures, and provide different trade-offs for consistency, performance, and scale. Understanding database systems therefore requires vocabulary from data modeling, querying, transactions, indexing, replication, and distributed storage. The following 15 terms span those areas and provide a practical foundation for discussing how database-backed applications store and retrieve information reliably.
1. Database
A database is an organized collection of data managed by software that supports storage, retrieval, update, and control. Database systems provide structures and rules that are more reliable and searchable than keeping application data in ordinary files.
For example, an online store can keep customers, products, and orders in a database that supports concurrent application requests.
2. Schema
A schema defines how data is structured in a database. In relational systems it includes tables, columns, data types, constraints, and relationships; other database models may use more flexible structures.
For example, an Orders table may define order_id, customer_id, order_date, and total_amount columns.
3. Primary Key
A primary key uniquely identifies each record in a table or collection. It allows the database and applications to refer to one specific item without ambiguity.
For example, customer_id can uniquely identify one customer even when several customers have the same name.
4. Foreign Key
A foreign key links a record in one table to a related record in another and can enforce referential integrity. It helps relational databases represent relationships between business entities.
For example, orders.customer_id can reference customers.customer_id so an order points to a valid customer.
5. Index
An index is an additional data structure that helps the database locate rows efficiently without scanning the entire table. Indexes improve selected read patterns but consume storage and add work to inserts and updates.
For example, an index on email_address can make account lookup much faster than scanning millions of customer rows.
6. Query
A query asks the database to retrieve, aggregate, insert, update, or delete data. Query languages such as SQL let users describe the desired result while the database decides how to execute the request.
For example, a query can return all orders placed by one customer during the last 30 days.
7. Query Plan
A query plan is the execution strategy chosen by the database for a query. It may include index lookups, scans, joins, sorting, and aggregation. The optimizer estimates which plan is likely to be efficient.
Get Bishal Goutam’s stories in your inbox
Join Medium for free to get updates from this writer.
For example, a slow query may improve when a suitable index allows the plan to replace a full table scan with an index lookup.
8. Transaction
A transaction groups related database operations into a logical unit of work. It should either complete according to the database’s transaction rules or avoid leaving partial changes that violate consistency.
For example, transferring money may require decreasing one balance and increasing another as one transaction.
9. ACID
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important guarantees provided by many transactional databases regarding all-or-nothing changes, valid state, concurrent operations, and persistence after commit.
For example, after a committed bank transfer, the new balances should remain stored even if the database server restarts.
10. Isolation Level
An isolation level controls how strongly one transaction is separated from concurrent transactions. Stronger isolation can prevent more anomalies but may reduce concurrency depending on the database and workload.
For example, a reporting query may see different behavior under read committed and serializable isolation.
11. Lock
A lock is a mechanism used to coordinate concurrent access to data. Locks can protect consistency, but excessive or conflicting locks can create waiting and reduce throughput.
For example, two transactions trying to update the same row may cause one transaction to wait for the other.
12. Replication
Replication keeps copies of data on multiple database instances or nodes. It can support availability, read scaling, disaster recovery, or geographic distribution, depending on how replication is configured.
For example, a primary database can send changes to read replicas used for reporting traffic.
13. Sharding
Sharding divides a dataset across multiple database nodes according to a shard key. It can increase storage and write capacity but makes routing, rebalancing, and cross-shard operations more complex.
For example, customer data may be distributed across shards based on customer_id.
14. NoSQL
NoSQL is a broad category of non-relational databases that includes document, key-value, wide-column, and graph models. These systems often optimize for particular access patterns, scale models, or schema flexibility.
For example, a document database can store different product categories with varying attributes in flexible JSON-like documents.
15. Backup and Recovery
Backup and recovery are the processes used to preserve data and restore it after accidental deletion, corruption, or major failure. Backups are different from replicas because a replica can copy an unwanted change as well.
For example, point-in-time recovery may restore a database to the moment just before an incorrect bulk update.
