Definition
An online shop records millions of orders a day, from thousands of customers at once, and must never lose a payment halfway through. A folder of CSV files could not cope. What does?
A database is an organised collection of data managed by software called a database management system (DBMS). Relational databases, such as PostgreSQL, MySQL and SQLite, store data in linked tables and are queried with SQL. They guarantee reliable transactions (the ACID properties), enforce rules on the data and allow many users at once. Non-relational (NoSQL) databases store documents, key-value pairs or graphs for other needs.
In simple words
A database is like a well-run library with a careful librarian. Books have fixed places, every borrowing is recorded properly even if the power fails mid-way, many people can search at once, and nobody can shelve a book without a catalogue number.
Overview
- A database is an organised store of data managed by software called a database management system (DBMS).
- Databases handle huge data, many users at once, security and recovery after crashes.
- Relational databases store tables linked by keys and are queried with SQL.
- A primary key uniquely identifies each row; a foreign key links to another table's primary key.
- Transactions follow ACID properties: atomic, consistent, isolated and durable.
- AI systems read training data from databases and write predictions back to them.
Why it matters
Most data that AI systems learn from lives in databases, and SQL is one of the most requested skills in data jobs. Understanding how databases organise and protect data helps you extract it correctly and design systems that store predictions safely.
Key terms
- DBMS
- Database management system: the software that stores, protects and queries data.
- Schema
- The design of a database: its tables, columns, types and relationships.
- Transaction
- A group of operations that succeed or fail together, such as debiting and crediting accounts.
- ACID
- Atomicity, consistency, isolation, durability: the guarantees that make transactions reliable.
- Index
- A structure that makes searching a column fast, like a book's index.
- NoSQL
- Databases that do not use the relational table model, such as document or key-value stores.
Types
Databases come in several types, each organising data differently. These are the main types in use today.
1. Relational (SQL) databases
Data stored in tables of rows and columns with a fixed schema, linked by keys.
- How it works: SQL queries select, join and aggregate tables; the DBMS enforces types, keys and constraints; ideas from Edgar Codd's 1970 relational model.
- Example: MySQL, PostgreSQL, Oracle and SQLite; a bank's accounts and transactions tables.
- When to use it: Use them for structured data where accuracy and relationships matter: banking, orders, school records.
2. Document databases
Data stored as flexible JSON-like documents rather than fixed tables.
- How it works: Each document can have different fields and nested parts; queries find documents by field values.
- Example: MongoDB storing product listings whose attributes differ by category.
- When to use it: Use them when records vary in shape or change often, as in content and app data.
3. Key-value stores
The simplest type: each value is stored and fetched by a unique key.
- How it works: Lookups by key are extremely fast, often from memory, but complex queries are not supported.
- Example: Redis caching user sessions or the latest cricket score.
- When to use it: Use them for caches, sessions and counters where speed matters more than querying.
4. Graph databases
Data stored as nodes (things) and edges (relationships).
- How it works: Queries follow relationships directly, so questions like "friends of friends" are fast.
- Example: Neo4j used to detect fraud rings by linking accounts, phones and addresses.
- When to use it: Use them for networks: social graphs, recommendations, knowledge graphs and fraud. They are also used to store knowledge graphs that give AI assistants structured facts.
5. Column-family and analytical (warehouse) databases
Databases that store data by column, built for scanning and summarising huge tables.
- How it works: Reading only the needed columns and compressing them makes aggregations over billions of rows fast.
- Example: Google BigQuery or Apache Cassandra summarising a year of app clicks.
- When to use it: Use them for analytics, dashboards and logs at large scale.
6. Vector databases
Databases that store embeddings and find the most similar vectors quickly.
- How it works: Approximate nearest-neighbour indexes such as HNSW return the closest vectors to a query embedding in milliseconds.
- Example: Storing embeddings of a company's documents so a RAG chatbot can retrieve the relevant ones.
- When to use it: Use them for semantic search, recommendations and retrieval-augmented generation.
Explained in detail
Why not just files?
| Need | Files | Database |
|---|---|---|
| Many users writing at once | Conflicting edits, corrupted files | Concurrency control keeps data consistent |
| Safety if power fails mid-update | Half-written data | Transactions complete fully or not at all |
| Finding one record among millions | Read the whole file | Indexes find it almost instantly |
| Rules such as "every order must have a customer" | Not enforced | Constraints and foreign keys enforce them |
| Controlling who sees what | Whole-file permissions | Fine-grained user permissions |
ACID in one example
Transferring ₹1,000 from Asha's account to Ravi's needs two steps: subtract from one, add to the other. Atomicity means both happen or neither does, so money is never lost if the system crashes between them. Consistency means the rules, such as "balances cannot go negative", always hold. Isolation means two transfers happening at the same moment do not interfere. Durability means that once the bank confirms the transfer, it survives even a power cut. These guarantees, formalised in the early 1980s, are why banks, railway reservation systems and hospitals rely on relational databases.
Relational and NoSQL
- Relational databases (PostgreSQL, MySQL, SQLite, Oracle) suit structured data with clear relationships and strong consistency needs.
- Document databases (such as MongoDB) store flexible JSON-like records, suited to data whose structure varies.
- Key-value stores (such as Redis) give extremely fast lookups, often used for caching and sessions.
- Vector databases store embeddings and find the most similar ones quickly; they power semantic search and RAG.
- Data warehouses (such as BigQuery or Snowflake) are built for analysing huge historical data, the usual source of training data for business AI.
For learning and small projects, SQLite is ideal: it needs no server, stores everything in one file and comes with Python. The next page covers the SQL you use to query it, and the same SQL works, with small differences, on PostgreSQL, MySQL and data warehouses used in industry.
Databases also matter after a model is built. Predictions, user feedback and monitoring logs need to be stored reliably so that the model can be audited and retrained. A team that designs its database well from the start finds it far easier to answer questions such as "which version of the model made this decision?" and "how often were its predictions overridden by staff?", questions that responsible AI practice increasingly requires.
How it works
- Identify the entities, such as customers, orders and products, and give each a table.
- Choose a primary key for each table.
- Link tables with foreign keys instead of repeating data.
- Add indexes on columns you search or join often.
- Group related changes into transactions.
In code
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")
conn.executescript("""
CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0));
INSERT INTO accounts (owner, balance) VALUES ('Asha', 5000), ('Ravi', 1000);
""")
def transfer(amount, src, dst):
try:
with conn: # one transaction: both updates or neither
conn.execute("UPDATE accounts SET balance = balance + ? WHERE owner = ?", (amount, dst))
conn.execute("UPDATE accounts SET balance = balance - ? WHERE owner = ?", (amount, src))
print(f"Transferred {amount}")
except sqlite3.IntegrityError as err:
print(f"Transfer of {amount} rolled back: {err}")
transfer(1000, "Asha", "Ravi")
transfer(9000, "Asha", "Ravi") # would make Asha's balance negative
print(conn.execute("SELECT owner, balance FROM accounts").fetchall())Output
Transferred 1000
Transfer of 9000 rolled back: CHECK constraint failed: balance >= 0
[('Asha', 4000), ('Ravi', 2000)]Try it yourself
The transfer that failed safely
Asha tries to send ₹9,000 to Ravi but has only ₹4,000.
Which kind of database?
Choose the best fit for each need.
Caching the results of frequent requests
Bank accounts and transfers
Students, courses and enrolments
Finding documents similar in meaning to a question
Common mistakes
- Storing shared, frequently updated data in spreadsheets or CSV files.
- Running related updates separately instead of in one transaction.
- Loading entire tables into pandas instead of filtering in the database.
Check yourself
Summary
- Databases organise, protect and share data better than files.
- Relational databases use linked tables, SQL and ACID transactions.
- NoSQL, vector databases and warehouses serve other needs, including AI.
Finished reading?
Sources: SQLite documentation · Python for Data Analysis, 3rd edition (Wes McKinney)