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

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