DBMS Deep Dive | Resources by Shumbul Arifa

Keys: how rows are identified and linked

Keys are how a database uniquely identifies rows and connects tables.

Primary key - uniquely identifies each row (e.g. student_id). One per table, never null.
Candidate key - any column(s) that could be the primary key. The primary key is chosen from these.
Foreign key - a column that points to another table's primary key. This is how relationships work.
Composite key - a primary key made of two or more columns together.

Analogy: a primary key is your unique roll number. A foreign key on a "marks" table stores that roll number to link each mark back to the right student, without repeating the student's name and details everywhere.

Normalization

Normalization organizes tables to reduce duplicate data and prevent update anomalies. You split data so each fact lives in exactly one place.

Form	Rule (informally)
1NF	Each cell holds a single value; no repeating groups or lists.
2NF	1NF + every non-key column depends on the whole key (no partial dependency).
3NF	2NF + no non-key column depends on another non-key column (no transitive dependency).

Analogy: instead of writing your full address on every single letter you send (and having to update all of them when you move), you keep the address in one place and reference it. Change it once, everything stays correct. That is what normalization does for data.

Most real apps aim for 3NF. Sometimes teams deliberately denormalize (add controlled duplication) to speed up reads, a trade-off, not a mistake.

ACID & transactions

A transaction is a group of operations treated as one unit. ACID is the set of guarantees that keep transactions reliable:

Atomicity - all steps succeed, or none do (no half-done transactions).
Consistency - the database moves from one valid state to another (rules stay satisfied).
Isolation - concurrent transactions do not corrupt each other's view.
Durability - once committed, changes survive crashes and power loss.

Analogy: a bank transfer. Debiting your account and crediting your friend's must both happen or neither (atomicity). Money can never vanish midway (consistency), two transfers at once must not double-spend (isolation), and once done, it stays done even if the server crashes (durability).

Indexing

An index is a separate data structure (often a B-tree) that lets the database find rows fast without scanning the whole table. Add indexes to columns you frequently filter (WHERE) or join on.

Analogy: the index at the back of a textbook. To find "photosynthesis" you do not read every page, you jump to the index, get the page number, and go straight there. Without it, you would scan the whole book (a full table scan).

Trade-off:

Indexes make reads much faster.
They make writes slightly slower (the index must be updated too) and use extra storage.
So index the columns you search on, not every column.

SQL vs NoSQL

	SQL (relational)	NoSQL
Structure	Fixed schema, tables & rows	Flexible (documents, key-value, graph)
Best for	Structured, related data; strong consistency	Flexible or huge-scale, fast-changing data
Examples	PostgreSQL, MySQL	MongoDB, Redis, Cassandra
Guarantees	Strong ACID	Often favors availability/scale (BASE)

Analogy: SQL is a spreadsheet with strict columns everyone must follow, great when structure matters. NoSQL is a box of labeled sticky notes, each can hold different fields, great when things vary or scale massively.

Final quiz

Ten questions across keys, normalization, ACID, indexing, and SQL vs NoSQL. Aim for 7+.

What to do next

🗄️ SQL & Databases 🧠 CS Fundamentals 🖥️ OS Deep Dive 🌐 Networks Deep Dive 🧩 OOP Deep Dive

🌱 How to use this

Read each section, then take its quiz to lock it in. The final quiz scores you out of 10. Pairs perfectly with the hands-on SQL & Databases guide and the CS Fundamentals guide. Confused by a term? Tap ✦ Ask AI.