How to use this handbook
Work through the modules in order for a complete foundation, or jump directly to the operational topic you need. The examples use portable SQL concepts; always verify syntax and behavior against your database engine.
Relational vs Non-Relational Databases
Database selection should follow the shape of the data, consistency requirements, query patterns, and operational constraints. Relational databases organize records into tables connected by keys and are a strong default for transactional systems. Non-relational systems include document, key-value, wide-column, and graph databases that optimize for different access patterns and scale profiles.
SQL and relational use-cases
Choose SQL for financial transactions, inventory, enrollment, billing, and other domains that need joins, constraints, strong consistency, and flexible analytical queries.
NoSQL use-cases
Document and key-value stores suit rapidly changing records, high-volume lookups, event data, content catalogs, and workloads where horizontal scale or denormalized reads are central.
ACID properties
Atomicity makes a transaction all-or-nothing. Consistency preserves valid rules. Isolation controls concurrent visibility. Durability preserves committed data after failure.
BASE model
Basically Available, Soft state, and Eventual consistency favor availability and distributed scale where replicas may converge after a write.
-- A relational transaction preserves an enrollment invariant
BEGIN;
INSERT INTO enrollments (student_id, course_id) VALUES (42, 7);
UPDATE course_seats SET remaining = remaining - 1
WHERE course_id = 7 AND remaining > 0;
COMMIT;Database Modeling & Normalization
Modeling translates business rules into entities, attributes, relationships, and constraints. Start with an entity-relationship diagram: identify entities such as Student, Course, Enrollment, and Payment; choose stable primary keys; then mark one-to-one, one-to-many, and many-to-many relationships.
Normalization sequence
- 1NF: Make every field atomic and remove repeating groups.
- 2NF: Reach 1NF and remove partial dependencies on part of a composite key.
- 3NF: Reach 2NF and remove transitive dependencies between non-key attributes.
- BCNF: Ensure every determinant is a candidate key, resolving stronger functional-dependency anomalies.
Normalization reduces update anomalies and duplicated facts. Denormalization can be appropriate for read-heavy reporting, but make the trade-off explicit and protect derived values with a reliable refresh strategy.
-- Separate facts into entities instead of repeating course data
CREATE TABLE courses (
course_id BIGINT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
seats INT NOT NULL CHECK (seats >= 0)
);
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);Advanced SQL Querying
Advanced SQL is about composing precise relational operations. Understand the cardinality of every join, filter early when it is semantically safe, aggregate only after choosing the correct grain, and use window functions when you need row-level detail alongside calculations over a related set.
JOINs
INNER JOIN returns matches. LEFT JOIN preserves every row from the left side. Use explicit join predicates and inspect one-to-many multiplication.
Subqueries
Use correlated or scalar subqueries for focused lookups, but consider a join or CTE when the planner can reuse a result more efficiently.
Aggregations
GROUP BY defines the output grain. HAVING filters groups after aggregation; WHERE filters rows before it.
WITH course_totals AS (
SELECT course_id, COUNT(*) AS enrollment_count
FROM enrollments
GROUP BY course_id
)
SELECT c.title,
COALESCE(t.enrollment_count, 0) AS enrollment_count,
RANK() OVER (ORDER BY COALESCE(t.enrollment_count, 0) DESC) AS popularity_rank
FROM courses AS c
LEFT JOIN course_totals AS t ON t.course_id = c.course_id
ORDER BY popularity_rank;Query review habit
Before shipping a query, state its intended grain in one sentence, test empty and duplicate cases, inspect the plan, and verify that the result count matches the business question.
Indexing & Query Optimization
An index is an additional data structure that helps the engine locate rows without scanning the entire table. Indexes improve reads at the cost of storage and write maintenance, so index the access patterns you actually measure rather than every column.
B-tree indexes
Balanced trees support equality, range predicates, ordered scans, and prefix-oriented access. They are the general-purpose choice for most relational columns.
Hash indexes
Hash structures are optimized for exact equality lookups. They do not naturally support ordering or range scans, and engine support varies.
Use EXPLAIN or EXPLAIN ANALYZE to compare estimated and actual work. Look for sequential scans on large tables, incorrect row estimates, expensive sorts, repeated nested loops, and filters that prevent index usage.
CREATE INDEX idx_enrollments_course_student
ON enrollments (course_id, student_id);
EXPLAIN ANALYZE
SELECT student_id
FROM enrollments
WHERE course_id = 7
ORDER BY student_id;Prefer indexes that match common filter and join prefixes. Consider composite indexes, covering indexes, partial indexes, or filtered indexes when supported. Remove unused duplicates, monitor bloat, and remember that an index on a low-selectivity boolean may not help.
Database Security & User Roles
Database security combines identity, authorization, safe query construction, encryption, monitoring, and least privilege. Separate application roles by responsibility: read-only reporting, transactional application access, migration ownership, and tightly controlled administration.
RBAC
Assign permissions to roles, then roles to identities. Review grants regularly and avoid sharing a superuser credential with an application.
Prepared statements
Bind untrusted values as parameters. Never concatenate user input into SQL strings, including search terms, filters, or sort values.
Encryption
Use encrypted connections in transit, encrypted storage or managed disks at rest, and carefully protect keys outside the database.
-- Conceptual application code: values are bound separately
const query = `SELECT student_id, email
FROM students
WHERE email = $1`;
const result = await db.query(query, [email]);
-- Never build: "... WHERE email = '" + email + "'"Security checklist
Use strong authentication, least privilege, secret rotation, audit logs, patching, network restrictions, backup encryption, and tested incident-response procedures.
Backup, Recovery & Maintenance
A backup is useful only when it can be restored within the required recovery point objective (RPO) and recovery time objective (RTO). Document who responds, where backups live, how credentials are retrieved, and how the team validates a restore.
Logical vs physical backups
Logical backups export schema and data into portable statements or records. Physical backups copy database files or storage snapshots and are often faster for large systems but more engine-specific.
Point-in-time recovery
PITR combines a base backup with write-ahead logs or equivalent change streams, allowing recovery to a timestamp before an accidental delete or corruption event.
-- PostgreSQL-style examples; adapt to your engine
pg_dump --format=custom --file=academy.dump academy_db
pg_restore --clean --dbname=academy_restore academy.dump
-- Maintenance concepts
VACUUM (ANALYZE) enrollments;
REINDEX TABLE enrollments;Schedule maintenance according to workload: refresh statistics, reclaim or compact storage when needed, rotate logs, archive cold records, check replication lag, test backups, and watch disk capacity. Never run destructive maintenance without a verified backup and rollback plan.
Recovery drill
Restore into an isolated environment, validate row counts and critical workflows, measure elapsed time, record gaps, and update the runbook. A tested recovery path is part of the database design.