Database Fundamentals — How to Manage Data Systematically

What Is a Database?

A system for storing, managing, and retrieving data in a structured way.

DBMS (Database Management System):

Software that manages a database
Oracle, MySQL, PostgreSQL, MS SQL Server, SQLite

Why use a database instead of plain files?

Handles concurrent access (multiple simultaneous users)
Guarantees data integrity
Fast search via indexes
Ensures atomicity via transactions

Relational Database (RDBMS)

Core concepts:

Table: rows (records) + columns (fields/attributes)
Primary Key (PK): uniquely identifies each row
Foreign Key (FK): references a PK in another table → expresses relationships
Example: Orders table OrderID (PK) | CustomerID (FK) | ProductID (FK) | OrderDate · | Amount 001 · | C001 · | P005 · | 2026-01-01 | $500

SQL Basics

-- Retrieve data (SELECT)
SELECT name, age FROM customers
WHERE age >= 30
ORDER BY age DESC
LIMIT 10;

-- Insert data (INSERT)
INSERT INTO customers (name, age, email)
VALUES ('Jane Smith', 30, 'jane@example.com');

-- Update data (UPDATE)
UPDATE customers
SET age = 31
WHERE customer_id = 'C001';

-- Delete data (DELETE)
DELETE FROM customers
WHERE customer_id = 'C001';

-- Join tables (JOIN)
SELECT orders.order_id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.amount > 1000;

Normalization

A design principle to reduce redundancy and improve data integrity:

1NF (First Normal Form): each column holds atomic values (no arrays)
2NF (Second Normal Form): remove partial functional dependencies
3NF (Third Normal Form): remove transitive functional dependencies
Example: a de-normalized orders table OrderID | CustomerName | CustomerAddress | ProductName | ProductPrice
Split customer and product info into their own tables
Benefits of normalization: eliminates redundancy, ensures consistency
Benefits of denormalization: fewer joins → better read performance (trade-off)

Index

Purpose: fast lookup on specific columns (like an index in a book)

B-Tree Index (most common):

Balanced tree structure
Search, insert, delete: O(log n)

When to use an index:

Columns frequently used in WHERE clauses
Foreign keys used in JOINs
Columns used in ORDER BY

Watch out:

Degrades write (INSERT/UPDATE/DELETE) performance
Uses additional disk space
Indexing every column causes more harm than good

Transaction (ACID)

Atomicity:

All-or-nothing: no partial success
Bank transfer: debit succeeds + credit succeeds, or both are rolled back

Consistency:

Data integrity is maintained before and after the transaction
Total balance remains unchanged

Isolation:

Concurrent transactions minimize interference with each other

Durability:

Committed data persists even after a system failure

SQL transaction example:

BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = ‘A’; UPDATE accounts SET balance = balance + 1000 WHERE account_id = ‘B’; COMMIT; — or ROLLBACK on error

NoSQL vs. Relational DB

Relational DB: structured data, fixed schema, complex relationships, ACID
NoSQL: unstructured/semi-structured, flexible schema, horizontal scaling

NoSQL types:

Document DB: MongoDB — stores JSON documents (flexible schema)
Key-Value DB: Redis — cache, session storage (ultra-fast)
Column DB: Cassandra — large-scale time-series data
Graph DB: Neo4j — social networks, recommendation engines

When to choose:

Clear data structure with complex relationships: RDBMS
Rapid scaling, flexible schema: NoSQL
In practice: hybrid usage is common

Key Takeaways

SQL CRUD: SELECT (read), INSERT (create), UPDATE (modify), DELETE (remove) ACID: Atomicity, Consistency, Isolation, Durability — the four guarantees of a transaction Index: O(log n) reads, but write performance trade-off NoSQL: flexible schema, horizontal scaling, suited for unstructured data