-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path01-intro.html
More file actions
428 lines (406 loc) · 33.1 KB
/
Copy path01-intro.html
File metadata and controls
428 lines (406 loc) · 33.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script>try{var t=localStorage.getItem("dbms-theme")||"dark";document.documentElement.setAttribute("data-theme",t)}catch(e){document.documentElement.setAttribute("data-theme","dark")}</script>
<title>Introduction to DBMS — DBMS Illustrated</title>
<link rel="stylesheet" href="../css/style.css">
<style>:root{--topic-color:#0EA5E9}</style>
</head>
<body>
<div class="reading-progress" id="reading-progress"></div>
<nav class="navbar">
<div class="navbar-inner">
<a class="navbar-brand" href="../index.html"><span class="brand-icon" style="color:var(--primary-soft)">◆</span>DBMS Illustrated</a>
<div class="navbar-links">
<a href="../index.html#topics">All Topics</a>
</div>
<div class="navbar-actions">
<button class="btn-icon" id="theme-toggle" title="Toggle theme">☼</button>
</div>
</div>
</nav>
<div class="container">
<div class="breadcrumb">
<a href="../index.html">Home</a><span class="breadcrumb-sep">›</span>
<a href="../index.html#topics">Course</a><span class="breadcrumb-sep">›</span>
<span>Introduction to DBMS</span>
</div>
<div class="topic-header">
<div class="topic-badge">Topic 01</div>
<h1>Introduction to <span class="accent">DBMS</span></h1>
<p class="subtitle">Imagine storing all your app's data in a spreadsheet. It works fine until two people try to edit it at the same time, the file gets corrupted, or you need to answer complex questions about the data. A Database Management System (DBMS) exists to solve exactly those problems.</p>
<div class="company-badges">
<span class="badge">MySQL</span><span class="badge">PostgreSQL</span><span class="badge">SQLite</span>
<span class="badge">Oracle DB</span><span class="badge">MariaDB</span><span class="badge">IBM Db2</span>
</div>
</div>
<div class="at-a-glance reveal">
<h3>At a Glance</h3>
<ul>
<li><strong>DBMS vs. flat files</strong> — a spreadsheet breaks when two people edit it at once; a DBMS handles that automatically, plus enforces data rules and recovers from crashes.</li>
<li><strong>Three-schema architecture</strong> — think of it like a building: visitors see the lobby (external views), architects see the floor plan (logical schema), and engineers see the pipes (physical storage). Change the pipes without redrawing the floor plan.</li>
<li><strong>ACID</strong> — four promises a database makes. Think of a bank transfer: the money leaves one account AND enters the other (Atomicity), rules are never broken (Consistency), two people don't interfere (Isolation), and once it's done it stays done even if power dies (Durability).</li>
<li><strong>Query pipeline</strong> — your SQL is like a recipe: first it's read and understood (Parse), then the best cooking method is chosen (Optimize), then the food is actually made (Execute).</li>
<li><strong>Write-Ahead Log (WAL)</strong> — before changing any data, the database writes a note in a journal. If the power goes out, it re-reads the journal and picks up where it left off.</li>
</ul>
</div>
<div class="section-label">Core Concepts</div>
<div class="mini-cards">
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Data Independence</h4>
<p>Imagine your app reads data from a filing cabinet. If you reorganize the cabinet (add dividers, switch folders), your app should not need to be rewritten. That is data independence. Physical independence means you can change how data is stored on disk without touching your queries. Logical independence means you can restructure your tables without breaking existing code.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>ACID Properties</h4>
<p>Think about transferring $100 between bank accounts. Atomicity: if the debit succeeds but the credit fails, the whole thing is cancelled — money never disappears into thin air. Consistency: the database only allows valid states, so your balance can never go negative if you set that rule. Isolation: two people booking the same last seat see each other's changes correctly. Durability: once the bank says "transfer complete," it stays complete even if the server crashes a second later.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Three-Schema Architecture</h4>
<p>Think of a hospital. Nurses see patient records (their external view). The hospital's records department sees all tables and relationships (the conceptual schema). The IT department worries about hard drives and backups (the internal schema). Each group sees only what they need. If the IT department upgrades the servers, the nurses' screens do not change. That separation is the three-schema architecture.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Concurrency Control</h4>
<p>Imagine two people editing the same Google Doc at the same time. Databases face a harder version of this problem: two people buying the last ticket, or two bank transfers hitting the same account. The DBMS coordinates these automatically. One approach is locking (like putting a "do not disturb" sign on a row — called Two-Phase Locking or 2PL). Another is keeping multiple versions of data so readers never wait on writers (called MVCC). You do not need to implement this yourself — the database handles it.</p>
</div>
</div>
<div class="section-label">How It Works</div>
<h2 class="section-title">DBMS Architecture</h2>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Query Parser</h4>
<p>When you type a SQL query, the first thing the database does is read it like a teacher reading an essay — checking for spelling mistakes (syntax errors), confirming the table names exist, and verifying you have permission to access that data. If anything is wrong here, you get an error message and nothing else happens yet.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Query Rewriter</h4>
<p>Before the database decides how to run your query, it cleans it up. If you queried a view (a saved named query), it replaces the view name with the actual query behind it. If there are security rules (for example, "this user can only see their own rows"), those get baked in here. You write one thing; the database quietly rewrites it into a complete, safe version before proceeding.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Query Optimizer</h4>
<p>This is the brain of the operation. The optimizer looks at your query and thinks about all the ways it could be answered. Should it scan every row? Use an index to jump straight to the right rows? Which table should it read first? It estimates how much work each approach would take, using statistics about how many rows are in each table and how the data is distributed, then picks the fastest plan. A good plan can be 1000x faster than a bad one for the same query.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>Execution Engine</h4>
<p>The execution works like an assembly line. The plan is a chain of steps — filter rows, join tables, sort results. Each step asks the step below it "give me the next row," processes it, then passes it up. This pull-based design is called the Iterator model (sometimes called the Volcano model). The engine fetches the actual data from a RAM cache called the buffer pool, which is much faster than reading from disk every time.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">5</div>
<div>
<h4>Transaction Manager</h4>
<p>Every query runs inside a transaction — a unit of work that either fully completes or fully undoes. The Transaction Manager keeps track of what is in-progress. Before any data is changed, it writes a note to a journal (the Write-Ahead Log, or WAL) saying what is about to happen. It also manages locks so that two queries do not corrupt the same row at the same time. When you run COMMIT, the transaction is sealed; ROLLBACK throws it all away.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">6</div>
<div>
<h4>Recovery Manager</h4>
<p>When the database crashes (power outage, server restart), the Recovery Manager takes over. It works like reading a journal after a disaster: first it figures out where things stood at the time of the crash, then it replays all the completed work from the journal (called the WAL) to restore it, and finally it cancels any work that was still in progress. This process is called ARIES recovery. The key insight is that because every change was logged before it happened, nothing is ever truly lost.</p>
</div>
</div>
</div>
<div class="code-block">
<div class="cb-header"><span class="cb-lang">SQL</span></div>
<pre><span class="cmt">One SQL statement, six internal system interactions</span>
<span class="kw">SELECT</span> e.name, d.dept_name
<span class="kw">FROM</span> employees e
<span class="kw">JOIN</span> departments d <span class="kw">ON</span> e.dept_id = d.dept_id
<span class="kw">WHERE</span> e.salary > <span class="num">80000</span>
<span class="kw">ORDER</span> <span class="kw">BY</span> e.name;
<span class="cmt">Step 1: Parser validates table names and column types
Step 2: Optimizer chooses IndexScan on salary vs SeqScan
Step 3: Execution engine fetches pages via Buffer Manager
Step 4: Lock Manager grants shared locks on scanned rows
Step 5: Join algorithm executes (hash join or nested loop)
Step 6: Sort node materializes result, applies ORDER BY</span>
<span class="cmt">ACID in action: a bank transfer</span>
<span class="kw">BEGIN</span>;
<span class="kw">UPDATE</span> accounts <span class="kw">SET</span> balance = balance - <span class="num">100</span> <span class="kw">WHERE</span> id = <span class="num">1</span>;
<span class="kw">UPDATE</span> accounts <span class="kw">SET</span> balance = balance + <span class="num">100</span> <span class="kw">WHERE</span> id = <span class="num">2</span>;
<span class="kw">COMMIT</span>; <span class="cmt">atomic: both succeed or both rollback</span></pre>
</div>
<div class="section-label">DBMS Families</div>
<h2 class="section-title">Choosing the Right Engine</h2>
<div class="comparison-grid">
<div class="comparison-card">
<h4>Relational (RDBMS)</h4>
<ul>
<li>SQL query language</li>
<li>ACID transactions</li>
<li>Schema enforced by DDL</li>
<li>Normalized data model</li>
<li>PostgreSQL, MySQL, Oracle, SQL Server</li>
</ul>
</div>
<div class="comparison-card">
<h4>Document Store</h4>
<ul>
<li>JSON/BSON documents</li>
<li>Flexible, schema-optional</li>
<li>Denormalized by design</li>
<li>Tunable consistency</li>
<li>MongoDB, CouchDB, Firestore</li>
</ul>
</div>
<div class="comparison-card">
<h4>Key-Value Store</h4>
<ul>
<li>O(1) get/set operations</li>
<li>Often in-memory</li>
<li>Extremely low latency</li>
<li>No query language</li>
<li>Redis, DynamoDB, etcd</li>
</ul>
</div>
<div class="comparison-card">
<h4>Wide-Column (Column-Family)</h4>
<ul>
<li>Sparse rows, column families</li>
<li>Write-optimized (LSM tree)</li>
<li>Horizontal scalability</li>
<li>Tunable consistency (AP by default)</li>
<li>Cassandra, HBase, Bigtable</li>
</ul>
</div>
</div>
<div class="section-label">Write-Ahead Logging</div>
<h2 class="section-title">Durability via WAL</h2>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Log Before Disk</h4>
<p>Before the database changes any data, it first writes a description of that change to a journal on disk (called the Write-Ahead Log, or WAL). Think of it like a chef writing down a recipe step before actually cooking it. If the kitchen catches fire mid-recipe, you still know exactly what was being done and can restart from the right point. The log always stays one step ahead of the actual data — that is the "write-ahead" guarantee.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>In-Memory Modification</h4>
<p>After logging, the database makes the change in memory (in the buffer pool — its RAM workspace). The updated data does not go to disk right away. This is intentional: writing to RAM is thousands of times faster than writing to disk. If the server crashes at this point, no data is lost — the WAL journal already has a record of every change, so recovery can reconstruct the correct state.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Commit Flushes Log</h4>
<p>When you run COMMIT, the database forces its journal entries to physical storage before telling you "success." This is the moment durability is guaranteed. The actual data pages in RAM can still be updated lazily in the background — but the log is already safe. In PostgreSQL, setting <code>synchronous_commit = off</code> skips this final safety step, making writes faster but risking the loss of a few recent commits if the server crashes.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>Checkpoint and Recovery</h4>
<p>Periodically the database takes a "checkpoint" — it flushes all in-memory changes to disk and marks a safe starting point in the journal. Think of it like saving your progress in a game. After a crash, recovery does not replay the entire journal from the beginning — just from the last checkpoint. The ARIES recovery process then replays completed work (redo), and cancels anything that was still in-progress when the crash happened (undo). More frequent checkpoints mean faster recovery.</p>
</div>
</div>
</div>
<div class="info-box">
<h4>Buffer Pool: RAM Cache Between Your Query and Disk</h4>
<p>The buffer pool is a fixed-size pool of RAM pages managed by the DBMS. When a disk page is needed, the DBMS checks the buffer pool first (buffer hit — fast) or fetches from disk (buffer miss — slow, 0.1ms vs 100ns). InnoDB buffer pool is typically set to 70-80% of server RAM. PostgreSQL's <code>shared_buffers</code> is set to 25% of RAM, relying on the OS page cache for the rest. A buffer pool hit rate above 95% is normal for OLTP workloads — below 90% indicates insufficient RAM or a missing index forcing full scans.</p>
</div>
<div class="section-label">Architecture Diagram</div>
<h2 class="section-title">Three-Schema Architecture</h2>
<p class="section-desc">Every DBMS provides three layers of abstraction. Queries flow from the external view through the logical schema down to physical storage — and results travel back up.</p>
<div class="diagram-box">
<svg viewBox="0 0 700 220" role="img" aria-label="Three-schema architecture: external views, conceptual schema, internal schema, physical storage">
<!-- External layer -->
<rect x="20" y="20" width="140" height="48" rx="8" class="svg-box-c"/>
<text x="90" y="40" text-anchor="middle" class="svg-label">External View 1</text>
<text x="90" y="58" text-anchor="middle" class="svg-soft">HR: salaries</text>
<rect x="280" y="20" width="140" height="48" rx="8" class="svg-box-c"/>
<text x="350" y="40" text-anchor="middle" class="svg-label">External View 2</text>
<text x="350" y="58" text-anchor="middle" class="svg-soft">Dev: user IDs</text>
<rect x="540" y="20" width="140" height="48" rx="8" class="svg-box-c"/>
<text x="610" y="40" text-anchor="middle" class="svg-label">External View 3</text>
<text x="610" y="58" text-anchor="middle" class="svg-soft">Analyst: reports</text>
<!-- Conceptual layer -->
<rect x="200" y="100" width="300" height="48" rx="8" class="svg-box-p"/>
<text x="350" y="120" text-anchor="middle" class="svg-label">Conceptual Schema</text>
<text x="350" y="137" text-anchor="middle" class="svg-soft">Tables, relations, constraints</text>
<!-- Internal layer -->
<rect x="230" y="170" width="240" height="40" rx="8" class="svg-box-g"/>
<text x="350" y="186" text-anchor="middle" class="svg-label">Internal Schema</text>
<text x="350" y="202" text-anchor="middle" class="svg-soft">B+trees, pages, files</text>
<!-- Lines: external -> conceptual -->
<line x1="90" y1="68" x2="280" y2="100" class="svg-line"/>
<line x1="350" y1="68" x2="350" y2="100" class="svg-line"/>
<line x1="610" y1="68" x2="420" y2="100" class="svg-line"/>
<!-- Lines: conceptual -> internal -->
<line x1="350" y1="148" x2="350" y2="170" class="svg-line"/>
<!-- Animated packets flowing down -->
<circle class="pkt" cx="90" cy="68" r="5" fill="#0EA5E9"/>
<circle class="pkt" cx="610" cy="40" r="5" fill="#0EA5E9"/>
<circle class="pkt" cx="350" cy="148" r="5" fill="#7C3AED"/>
</svg>
<p class="diagram-caption">Logical independence: change the conceptual schema without rewriting external queries. Physical independence: reorganize storage without touching the conceptual layer.</p>
</div>
<div class="section-label">Interactive Demo</div>
<h2 class="section-title">ER Diagram Explorer</h2>
<p class="section-desc">Click any entity to highlight its relationships and attributes. The ER diagram models a university registration system.</p>
<div class="demo-section" id="demo-intro">
<div class="demo-header">
<h3>Entity-Relationship Diagram</h3>
<div class="demo-controls">
<span style="font-size:.78rem;color:var(--text-muted)">Click entity to explore</span>
</div>
</div>
<div class="demo-canvas-wrap"></div>
<div class="demo-hint">Click STUDENT, COURSE, or ENROLLMENT to highlight connected elements</div>
</div>
<div class="section-label">Anti-patterns</div>
<div class="antipatterns">
<div class="antipattern">
<h4>Using flat files instead of a DBMS</h4>
<p>CSV files have no atomicity, no query language, no concurrent access control, and no recovery. Fine for scripts; catastrophic for production data with multiple writers or concurrent readers.</p>
</div>
<div class="antipattern">
<h4>Ignoring normalization from day one</h4>
<p>Denormalized schemas feel easy initially but create update anomalies, data redundancy, and painful migrations as the application grows. Start normalized; denormalize deliberately when access patterns demand it.</p>
</div>
<div class="antipattern">
<h4>Treating the DBMS as a dumb storage layer</h4>
<p>Not using constraints (FK, NOT NULL, CHECK) shifts validation to application code — the worst of both worlds. Constraints in the DB enforce invariants across all callers simultaneously.</p>
</div>
<div class="antipattern">
<h4>Not monitoring buffer pool hit rate</h4>
<p>A dropping hit rate is the first warning sign of memory pressure or new table scans. Check <code>pg_stat_bgwriter</code> in PostgreSQL or <code>SHOW ENGINE INNODB STATUS</code> in MySQL regularly.</p>
</div>
<div class="antipattern">
<h4>Skipping EXPLAIN ANALYZE before deploying queries</h4>
<p>A query that runs in 10ms on your dev database with 1,000 rows can take 30 seconds on production with 10 million rows. Always verify the execution plan against production-scale data volumes before deploying.</p>
</div>
</div>
<div class="section-label">Quiz</div>
<h2 class="section-title">Check Your Understanding</h2>
<div class="quiz-section">
<div class="quiz-question">
<div class="quiz-q-num">Question 1 of 5</div>
<div class="quiz-q-text">What is the primary advantage of a DBMS over a file system?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="File systems can be faster for simple sequential reads of large files."><span class="opt-letter">A</span>Faster file I/O in all cases</div>
<div class="quiz-option" data-correct="true" data-explanation="A DBMS provides data independence, integrity constraints, concurrent access, and crash recovery — none of which file systems offer."><span class="opt-letter">B</span>Data independence, integrity, concurrency, and recovery</div>
<div class="quiz-option" data-correct="false" data-explanation="DBMS adds significant complexity compared to flat files."><span class="opt-letter">C</span>Simpler code for all use cases</div>
<div class="quiz-option" data-correct="false" data-explanation="Not all DBMS systems support full ACID transactions."><span class="opt-letter">D</span>Built-in transactions in all storage solutions</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 2 of 5</div>
<div class="quiz-q-text">Which DBMS component generates multiple execution strategies and picks the cheapest?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Buffer Manager handles page caching, not plan selection."><span class="opt-letter">A</span>Buffer Manager</div>
<div class="quiz-option" data-correct="true" data-explanation="The Query Optimizer generates candidate plans, estimates costs using statistics, and picks the minimum-cost plan."><span class="opt-letter">B</span>Query Optimizer</div>
<div class="quiz-option" data-correct="false" data-explanation="Transaction Manager handles locking and WAL, not plan optimization."><span class="opt-letter">C</span>Transaction Manager</div>
<div class="quiz-option" data-correct="false" data-explanation="The Recovery Manager handles crash recovery using the WAL."><span class="opt-letter">D</span>Recovery Manager</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 3 of 5</div>
<div class="quiz-q-text">In the three-schema architecture, the "conceptual schema" represents:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Physical storage details are the internal schema."><span class="opt-letter">A</span>The physical storage layout on disk</div>
<div class="quiz-option" data-correct="true" data-explanation="The conceptual (logical) schema describes tables, relationships, constraints — independent of storage details or user views."><span class="opt-letter">B</span>The logical structure of all data — tables, relationships, constraints</div>
<div class="quiz-option" data-correct="false" data-explanation="User-specific views are the external schema."><span class="opt-letter">C</span>User-specific views and permissions</div>
<div class="quiz-option" data-correct="false" data-explanation="Query execution plans are produced by the optimizer at runtime."><span class="opt-letter">D</span>The query execution plan at runtime</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 4 of 5</div>
<div class="quiz-q-text">In Write-Ahead Logging (WAL), when is the log record guaranteed to be durable?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Dirty pages remain in the buffer pool after a write — only the WAL record is flushed on commit."><span class="opt-letter">A</span>When the dirty data page is flushed to disk</div>
<div class="quiz-option" data-correct="false" data-explanation="Locks are released after the transaction, not when the log is written."><span class="opt-letter">B</span>When transaction locks are released</div>
<div class="quiz-option" data-correct="true" data-explanation="The WAL record for a committed transaction is flushed (fsync) at COMMIT time. This is what guarantees durability — even if the buffer pool is lost in a crash, the log enables redo."><span class="opt-letter">C</span>At COMMIT time — the WAL record is fsynced to durable storage</div>
<div class="quiz-option" data-correct="false" data-explanation="Checkpoints flush dirty data pages, but WAL is flushed on commit."><span class="opt-letter">D</span>At the next checkpoint when dirty pages are flushed</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 5 of 5</div>
<div class="quiz-q-text">Which isolation property guarantees that a committed transaction's changes survive a server crash?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Atomicity ensures all-or-nothing execution, not crash survival."><span class="opt-letter">A</span>Atomicity</div>
<div class="quiz-option" data-correct="false" data-explanation="Consistency ensures constraints are satisfied, not crash survival."><span class="opt-letter">B</span>Consistency</div>
<div class="quiz-option" data-correct="false" data-explanation="Isolation prevents interference between concurrent transactions."><span class="opt-letter">C</span>Isolation</div>
<div class="quiz-option" data-correct="true" data-explanation="Durability guarantees that committed data survives crashes. Implemented via WAL — after COMMIT returns, the log is on durable storage and recovery will replay it."><span class="opt-letter">D</span>Durability</div>
</div>
<div class="quiz-feedback"></div>
</div>
</div>
<div class="section-label">Interview Q&A</div>
<h2 class="section-title">Common Interview Questions</h2>
<div class="qa-section">
<div class="qa-item">
<div class="qa-q">What is the difference between a DBMS and a file system?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A file system provides raw byte storage organized in files and directories — no query language, no constraints, no transactions, no concurrent access control. A DBMS adds: a data model (relational, document, etc.), a declarative query language (SQL), integrity constraints (FK, NOT NULL), ACID transactions, concurrent multi-user access with locking/MVCC, and crash recovery via WAL. Use a file system for blobs and configuration; use a DBMS for structured, transactional data.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">Explain the three-schema architecture.<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Three levels: (1) <strong>External schema</strong> — user views, each showing a subset of data tailored to a role (HR sees salaries, developers see user IDs). (2) <strong>Conceptual schema</strong> — the global logical description of all tables, relationships, and constraints, independent of storage. (3) <strong>Internal schema</strong> — physical storage details: file organization, index structures, page sizes, compression. Mappings between levels enable independence: change disk layout without altering the logical schema, and vice versa.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What are the four ACID properties? Give a real-world example of each.<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner"><strong>Atomicity:</strong> A bank transfer deducts $100 from Account A and credits Account B — if the credit fails, the debit is rolled back. No partial state. <strong>Consistency:</strong> A foreign key constraint ensures you can never have an order referencing a non-existent customer. The DB moves from one valid state to another. <strong>Isolation:</strong> Two users booking the last seat on a flight don't both succeed — the second sees the updated state after the first commits. <strong>Durability:</strong> After a COMMIT returns, the transaction survives a power failure. WAL ensures committed data is on durable storage.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is data independence and why does it matter?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Data independence means application code doesn't need to change when storage or schema changes. <strong>Physical independence:</strong> you can switch from a heap file to a clustered B+tree index without modifying any SQL. The optimizer adapts. <strong>Logical independence:</strong> you can add a column or split a table (providing a view) without breaking existing queries. This matters because storage decisions and schema evolution happen constantly; coupling them to application code creates a maintenance disaster.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is the role of a DBA in a production system?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A Database Administrator is responsible for: schema design and evolution (DDL migrations), performance tuning (index selection, query optimization, buffer pool sizing), backup and recovery planning (RPO/RTO targets, WAL archiving, PITR), security (user roles, column-level encryption, audit logging), capacity planning (storage growth, replication lag), and HA setup (primary/replica failover). In cloud-native environments the DBA role increasingly overlaps with SRE and data engineering.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">How does MVCC differ from lock-based concurrency control?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Lock-based (2PL): readers block writers, writers block readers. High contention on hot rows. MVCC (Multi-Version Concurrency Control): each transaction sees a consistent snapshot of the database at its start time. Readers never block writers; writers never block readers. The DB keeps multiple versions of each row (xmin/xmax in PostgreSQL). Old versions are garbage-collected by VACUUM. PostgreSQL and Oracle use MVCC. MySQL InnoDB uses MVCC for reads but 2PL for writes. The tradeoff: MVCC needs periodic cleanup (VACUUM) and uses more storage for row versions.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is a checkpoint in database recovery?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A checkpoint is a point at which all dirty pages in the buffer pool are flushed to disk and a checkpoint record is written to the WAL. On crash recovery, the DBMS only needs to replay WAL records <em>after</em> the last checkpoint — reducing recovery time. Without checkpoints, recovery would need to replay the entire WAL from the beginning. In PostgreSQL, <code>checkpoint_completion_target</code> and <code>checkpoint_timeout</code> control frequency. Frequent checkpoints = faster recovery but more I/O. Infrequent checkpoints = slower recovery but less write amplification.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">When would you choose a NoSQL database over PostgreSQL?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Choose NoSQL when: (1) <strong>Schema flexibility</strong> is critical — document stores handle evolving, heterogeneous data structures without migrations. (2) <strong>Extreme scale</strong> with simple access patterns — Cassandra handles millions of writes per second by sacrificing joins and ACID. (3) <strong>Low-latency key lookups</strong> — Redis at sub-millisecond latency for caching, sessions, leaderboards. (4) <strong>Graph relationships</strong> — Neo4j handles multi-hop traversals more efficiently than SQL joins. PostgreSQL with JSONB handles most document-store use cases; start with PostgreSQL and switch NoSQL when you hit a specific limitation.</div></div>
</div>
</div>
<div class="section-label">Further Reading</div>
<div class="reading-links">
<a class="reading-link" href="https://www.postgresql.org/docs/current/tutorial.html" target="_blank">PostgreSQL Official Tutorial</a>
<a class="reading-link" href="https://use-the-index-luke.com/" target="_blank">Use The Index, Luke</a>
<a class="reading-link" href="https://db.cs.cmu.edu/courses/" target="_blank">CMU Database Courses</a>
<a class="reading-link" href="https://www.databricks.com/glossary/acid-transactions" target="_blank">ACID Transactions Explained</a>
</div>
<div class="topic-nav">
<div></div>
<a href="02-relational-model.html" class="topic-nav-link next">
<div class="topic-nav-arrow">→</div>
<div><div class="topic-nav-label">Next Topic</div><div class="topic-nav-title">Relational Model</div></div>
</a>
</div>
</div>
<footer class="site-footer" style="margin-top:0">
<p class="footer-sub"><a href="../index.html" style="color:var(--primary-soft)">Back to Course</a> — DBMS Illustrated</p>
</footer>
<script src="../js/main.js"></script>
<script src="../js/demos.js"></script>
</body>
</html>