DBMS Fundamentals: Complete Notes

What is a DBMS?

A Database Management System (DBMS) is software that stores, retrieves, secures, and manages data for applications. It abstracts low-level file handling and gives you reliable primitives like transactions, indexing, and concurrency control.

Common DBMS families include relational systems (PostgreSQL, MySQL), document systems (MongoDB), key-value stores (Redis), and graph databases (Neo4j).

Why DBMS over plain files

• Data consistency with constraints and validations.
• Concurrent access by multiple users or services.
• Query language support for rich retrieval.
• Backup, recovery, and security controls.
• Performance optimization via indexes and execution planners.

Entity-Relationship model

ER modeling helps you convert real-world requirements into structured database design. Entities represent objects, attributes represent properties, and relationships represent associations between entities.

Modeling an education platform

• One-to-one: user and profile settings table.
• One-to-many: course and lessons.
• Many-to-many: students and courses via join table.

Entity: Student(student_id, name, email)
Entity: Course(course_id, title, level)
Entity: Enrollment(enrollment_id, student_id, course_id, enrolled_at)

Relationship:
Student 1..N Enrollment N..1 Course

Why normalization exists

Normalization reduces redundancy and update anomalies by organizing data into well-formed tables. It keeps data accurate when inserts, updates, and deletes happen frequently.

From 1NF to 3NF

• 1NF: atomic values, no repeating groups.
• 2NF: remove partial dependency on composite keys.
• 3NF: remove transitive dependencies.

-- Unnormalized style (bad)
-- orders(order_id, customer_name, customer_city, product_name, product_price)

-- Better normalized design
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  customer_city VARCHAR(80)
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  product_price DECIMAL(10, 2)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id)
);

ACID explained

• Atomicity: all operations succeed or none do.
• Consistency: constraints remain valid after commit.
• Isolation: concurrent transactions do not corrupt each other.
• Durability: committed data survives crashes.

Transfer money safely

Financial and inventory systems rely on transactions so partial updates never leak into user-visible state.

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 202;

COMMIT;
-- If anything fails, ROLLBACK;

Concurrency problems

• Dirty read: reading uncommitted data.
• Non-repeatable read: same query returns different row values.
• Phantom read: additional rows appear between repeated scans.
• Lost update: one transaction silently overwrites another.

Isolation levels in practice

Higher isolation gives stronger correctness but may reduce throughput due to locking and conflict retries.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT stock FROM products WHERE product_id = 77;
-- perform business checks
UPDATE products SET stock = stock - 1 WHERE product_id = 77;

COMMIT;

How indexing works

Most relational DBMS engines use B-tree indexes. Instead of scanning every row, the engine navigates index pages to quickly find matching record pointers.

CREATE TABLE enrollments (
  enrollment_id BIGSERIAL PRIMARY KEY,
  student_id BIGINT NOT NULL,
  course_id BIGINT NOT NULL,
  enrolled_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_date ON enrollments(course_id, enrolled_at);

Index trade-offs

• Fast reads, slower writes due to maintenance overhead.
• Composite index column order is crucial.
• Unused indexes increase storage and planning complexity.

Query optimizer role

The optimizer decides join order, access path (index vs scan), and algorithm (hash join, merge join, nested loop). Understanding plan output is key to fixing slow queries.

EXPLAIN ANALYZE
SELECT c.title, COUNT(e.enrollment_id) AS total_enrollments
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.title
ORDER BY total_enrollments DESC;

Optimization playbook

• Filter early to reduce join input size.
• Avoid SELECT * in analytical and API queries.
• Use covering indexes for frequent access paths.
• Rewrite correlated subqueries when planner struggles.
• Track slow-query logs continuously in production.

Project brief

Design a normalized schema for students, courses, lessons, enrollments, and payments. Then create transactions for enrollment checkout and optimize reporting queries for monthly active students and top courses.

Project tasks

• Draw ER diagram with cardinality and keys.
• Apply 3NF and justify denormalized fields, if any.
• Implement transaction-safe enrollment flow.
• Benchmark report query before and after indexing.
• Document locking assumptions and failure recovery behavior.

SELECT
  DATE_TRUNC('month', e.enrolled_at) AS month_start,
  COUNT(DISTINCT e.student_id) AS active_students
FROM enrollments e
GROUP BY DATE_TRUNC('month', e.enrolled_at)
ORDER BY month_start;