-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path02-relational-model.html
More file actions
442 lines (421 loc) · 32.6 KB
/
Copy path02-relational-model.html
File metadata and controls
442 lines (421 loc) · 32.6 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
429
430
431
432
433
434
435
436
437
438
439
440
441
442
<!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>Relational Model — DBMS Illustrated</title>
<link rel="stylesheet" href="../css/style.css">
<style>:root{--topic-color:#8B5CF6}</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>Relational Model</span>
</div>
<div class="topic-header">
<div class="topic-badge">Topic 02</div>
<h1>The <span class="accent">Relational</span> Model</h1>
<p class="subtitle">Every database you have ever used — Gmail, Instagram, your bank — stores data in tables. This topic explains the rules behind those tables: how rows and columns are defined, how tables link to each other, and the mathematical operations that make SQL work.</p>
<div class="company-badges">
<span class="badge">PostgreSQL</span><span class="badge">MySQL</span><span class="badge">Oracle</span>
<span class="badge">SQL Server</span><span class="badge">SQLite</span>
</div>
</div>
<div class="section-label">Core Concepts</div>
<div class="mini-cards">
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Relation (Table)</h4>
<p>Think of a relation as a spreadsheet tab. Each row is one record — in database language, a row is called a tuple. Each column is one piece of data about that record — a column is called an attribute. All rows in the same table share the same columns. There is no "first row" or "last row" — order is not guaranteed, just like a deck of cards you have shuffled.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Keys Hierarchy</h4>
<p>Every row needs a way to be uniquely identified — like a student ID number. Any column (or combination of columns) that can identify a row is called a superkey. If you strip that down to the minimum needed (no extra columns), it becomes a candidate key. The one you officially designate as the row's identifier is the primary key. A foreign key is a column that points to the primary key of a different table — it is how tables link to each other.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Referential Integrity</h4>
<p>If every employee must belong to a department, the database enforces that you cannot delete a department that still has employees. This enforcement is called referential integrity. When the parent row is deleted, you decide: should child rows be deleted too (CASCADE)? Should their link be cleared (SET NULL)? Or should the deletion be blocked until the children are gone first (RESTRICT)? Each option encodes a real business decision.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Relational Algebra</h4>
<p>Before SQL existed, mathematicians described database operations using symbols. Pick only certain rows: sigma. Pick only certain columns: pi. Combine two tables: join. Combine two result sets: union. Everything you write in SQL compiles down to these six basic operations. You do not need to memorize the symbols — but understanding them explains why SQL works the way it does.</p>
</div>
</div>
<div class="section-label">Key Taxonomy</div>
<h2 class="section-title">From Superkey to Foreign Key</h2>
<div class="comparison-grid">
<div class="comparison-card">
<h4>Superkey</h4>
<ul>
<li>Any set of columns uniquely identifying a row</li>
<li>{user_id}, {email}, {user_id,email} are all superkeys</li>
<li>May have redundant columns</li>
<li>Every candidate key is a superkey</li>
</ul>
</div>
<div class="comparison-card">
<h4>Candidate Key</h4>
<ul>
<li>A minimal superkey — no subset also identifies rows</li>
<li>{user_id} and {email} are candidate keys</li>
<li>{user_id, email} is NOT one (user_id alone suffices)</li>
<li>A table may have multiple candidate keys</li>
</ul>
</div>
<div class="comparison-card">
<h4>Primary Key</h4>
<ul>
<li>One chosen candidate key per table</li>
<li>Cannot be NULL (implicit NOT NULL)</li>
<li>Physically creates a clustered B+tree in InnoDB</li>
<li>Other candidate keys become UNIQUE constraints</li>
</ul>
</div>
<div class="comparison-card">
<h4>Foreign Key</h4>
<ul>
<li>Attribute(s) referencing a PK in another table</li>
<li>Value must be NULL or match a referenced PK</li>
<li>Enforces referential integrity across tables</li>
<li>ON DELETE: CASCADE / SET NULL / RESTRICT</li>
</ul>
</div>
</div>
<div class="section-label">Integrity Constraints</div>
<h2 class="section-title">Enforcing Correct Data</h2>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Domain Constraint</h4>
<p>Every column has a declared type and set of legal values — that is its domain. For example, an age column should not allow -5 or the word "banana." Domain constraints let you declare rules like "age must be a non-negative integer" or "status can only be 'active' or 'inactive'." The database rejects any insert or update that violates these rules before the data is ever saved.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Entity Integrity</h4>
<p>Every row must be uniquely identifiable — you cannot have a row with no identity. This means the primary key column can never be empty (NULL) and can never be duplicated. In practice, many tables use a generated ID number (like BIGSERIAL or UUID) as the primary key rather than something like an email address — email addresses can change, be shared, or be left blank, which would break this rule.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Referential Integrity</h4>
<p>When one table has a column pointing to another table's primary key (a foreign key), the database checks that the reference is valid. You cannot have an order row pointing to a customer that does not exist. When you delete the parent (the customer), you choose what happens to the children (the orders): delete them too (CASCADE), blank out the link (SET NULL), or refuse the deletion until you manually remove the children first (RESTRICT).</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>User-Defined Constraints</h4>
<p>Sometimes you need business rules that go beyond basic types and keys. For example, "end date must be after start date," or "each user can only have one review per product." These are user-defined constraints. You can declare them directly in the table definition, and the database will enforce them automatically — no application code needed. For more complex rules (like checking data across multiple tables at once), you can use triggers.</p>
</div>
</div>
</div>
<div class="section-label">How It Works</div>
<h2 class="section-title">Relational Algebra — Six Core Operations</h2>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>sigma — Selection (WHERE)</h4>
<p>This operation picks certain rows and throws away the rest. The symbol sigma (looks like a capital E rotated) represents "filter rows where this condition is true." In SQL, this is your WHERE clause. For example, "give me only employees earning over $80,000." The result has the same columns as before — you have only reduced the number of rows.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>pi — Projection (SELECT cols)</h4>
<p>This operation keeps only certain columns and hides the rest. The symbol pi represents "project columns." In SQL, this is the list of columns after SELECT — for example, "I only want the name and salary columns, not the full employee record." In strict math, projection also removes duplicate rows. In SQL, duplicates are kept by default for speed — add DISTINCT if you want to remove them.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Join — Combining Relations</h4>
<p>A join takes two tables and stitches them together based on a matching column. Think of it like looking up a person's department name by matching the department ID on the employee row with the department ID on the department row. In SQL, you use JOIN ... ON to say exactly which columns should match. Always use an explicit ON condition in real code — letting the database guess (natural join) is risky and error-prone.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>Set Operations (UNION, INTERSECT, EXCEPT)</h4>
<p>Sometimes you want to combine two query results rather than two tables. UNION stacks them together (like combining two lists). INTERSECT gives you only rows that appear in both lists. EXCEPT gives you rows from the first list that do not appear in the second. Both queries must return the same number of columns. UNION removes duplicates by default; use UNION ALL to keep them (which is faster when you know there are no duplicates).</p>
</div>
</div>
</div>
<div class="code-block">
<div class="cb-header"><span class="cb-lang">Relational Algebra and SQL</span></div>
<pre><span class="cmt">Relational algebra: pi(name,dept_name)(sigma(salary>80000)(employees JOIN departments))</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="cmt">Key constraints in DDL</span>
<span class="kw">CREATE TABLE</span> orders (
order_id <span class="fn">BIGSERIAL</span> <span class="kw">PRIMARY KEY</span>,
customer_id <span class="fn">INT</span> <span class="kw">NOT NULL</span>
<span class="kw">REFERENCES</span> customers(customer_id)
<span class="kw">ON DELETE RESTRICT</span>,
status <span class="fn">VARCHAR</span>(<span class="num">20</span>) <span class="kw">NOT NULL</span>
<span class="kw">CHECK</span> (status <span class="kw">IN</span> (<span class="str">'pending'</span>,<span class="str">'paid'</span>,<span class="str">'shipped'</span>)),
total <span class="fn">NUMERIC</span>(<span class="num">10</span>,<span class="num">2</span>) <span class="kw">CHECK</span> (total > <span class="num">0</span>),
created_at <span class="fn">TIMESTAMPTZ</span> <span class="kw">DEFAULT</span> now()
);
<span class="cmt">UNION: customers who ordered OR registered this year</span>
<span class="kw">SELECT</span> customer_id <span class="kw">FROM</span> orders
<span class="kw">UNION</span>
<span class="kw">SELECT</span> id <span class="kw">FROM</span> customers <span class="kw">WHERE</span> created_at >= <span class="str">'2024-01-01'</span>;
<span class="cmt">EXCEPT: customers who registered but never ordered</span>
<span class="kw">SELECT</span> id <span class="kw">FROM</span> customers
<span class="kw">EXCEPT</span>
<span class="kw">SELECT DISTINCT</span> customer_id <span class="kw">FROM</span> orders;</pre>
</div>
<div class="at-a-glance reveal">
<h3>At a Glance</h3>
<ul>
<li><strong>Relation = table. Tuple = row. Attribute = column.</strong> Every value in every cell must be a single, indivisible piece of data — no lists inside a cell.</li>
<li><strong>Primary key</strong> — the unique ID for every row. <strong>Foreign key</strong> — a column that points to the primary key of another table. This is how you link tables together.</li>
<li><strong>INNER JOIN</strong> — only rows that match in both tables. <strong>LEFT JOIN</strong> — all rows from the left table, even when there is no match on the right (missing data shows as NULL).</li>
<li><strong>Relational algebra</strong> — think of it as the math version of SQL. Pick rows (sigma), pick columns (pi), link tables (join). SQL is just a friendlier way to write this.</li>
<li>The key rules you learn here (candidate keys, how data depends on other data) are the foundation of normalization — the next topic.</li>
</ul>
</div>
<div class="section-label">JOIN Types Reference</div>
<div class="comparison-grid">
<div class="comparison-card">
<h4>INNER JOIN</h4>
<ul>
<li>Returns only matching rows from both sides</li>
<li>Non-matching rows are silently dropped</li>
<li>Most common join type in OLTP</li>
<li>Use when you need data from both tables</li>
</ul>
</div>
<div class="comparison-card">
<h4>LEFT JOIN</h4>
<ul>
<li>All rows from left, matching from right</li>
<li>NULLs for right side when no match</li>
<li>Common for optional relationships</li>
<li>Use: orders with or without invoices</li>
</ul>
</div>
<div class="comparison-card">
<h4>FULL OUTER JOIN</h4>
<ul>
<li>All rows from both sides</li>
<li>NULLs where no match on either side</li>
<li>Rare in practice, powerful for reconciliation</li>
<li>Use: finding records missing from either table</li>
</ul>
</div>
<div class="comparison-card">
<h4>CROSS JOIN</h4>
<ul>
<li>Cartesian product: every row times every row</li>
<li>n * m rows — very expensive at scale</li>
<li>Useful for generating test data or combinations</li>
<li>Missing ON clause in INNER = accidental CROSS</li>
</ul>
</div>
</div>
<div class="diagram-box">
<svg viewBox="0 0 700 230" role="img" aria-label="Relational model: two tables joined on a foreign key, producing a result set">
<!-- employees table -->
<rect x="30" y="30" width="180" height="140" rx="8" class="svg-box-c"/>
<rect x="30" y="30" width="180" height="30" rx="8" class="svg-box-c" style="fill:#0EA5E9;opacity:.85"/>
<text x="120" y="51" text-anchor="middle" class="svg-label" style="fill:#fff">employees</text>
<text x="120" y="82" text-anchor="middle" class="svg-soft">PK emp_id</text>
<text x="120" y="100" text-anchor="middle" class="svg-soft">name</text>
<text x="120" y="118" text-anchor="middle" class="svg-soft">FK dept_id</text>
<text x="120" y="136" text-anchor="middle" class="svg-soft">salary</text>
<!-- departments table -->
<rect x="490" y="30" width="180" height="120" rx="8" class="svg-box-p"/>
<rect x="490" y="30" width="180" height="30" rx="8" class="svg-box-p" style="fill:#7C3AED;opacity:.85"/>
<text x="580" y="51" text-anchor="middle" class="svg-label" style="fill:#fff">departments</text>
<text x="580" y="82" text-anchor="middle" class="svg-soft">PK dept_id</text>
<text x="580" y="100" text-anchor="middle" class="svg-soft">dept_name</text>
<text x="580" y="118" text-anchor="middle" class="svg-soft">budget</text>
<!-- FK reference arrow -->
<line x1="210" y1="118" x2="490" y2="82" class="svg-line svg-dash" style="stroke:#F59E0B"/>
<text x="350" y="90" text-anchor="middle" class="svg-soft" style="fill:#F59E0B">FK dept_id = PK dept_id</text>
<!-- result table -->
<rect x="200" y="168" width="300" height="48" rx="8" class="svg-box-g"/>
<text x="350" y="188" text-anchor="middle" class="svg-label">JOIN Result Set</text>
<text x="350" y="206" text-anchor="middle" class="svg-soft">emp_id, name, dept_name, salary</text>
<!-- lines from tables to result -->
<line x1="120" y1="170" x2="280" y2="168" class="svg-line"/>
<line x1="580" y1="150" x2="420" y2="168" class="svg-line"/>
<!-- Animated packets -->
<circle class="pkt" cx="210" cy="118" r="5" fill="#F59E0B"/>
<circle class="pkt" cx="350" cy="90" r="5" fill="#F59E0B"/>
<circle class="pkt" cx="120" cy="140" r="5" fill="#0EA5E9"/>
<circle class="pkt" cx="580" cy="140" r="5" fill="#7C3AED"/>
</svg>
<p class="diagram-caption">A foreign key in employees references the primary key in departments. The JOIN operation walks that reference to stitch the two relations together into a flat result set.</p>
</div>
<div class="demo-section" id="demo-relational">
<div class="demo-header">
<h3>Relational Join Demo</h3>
<div class="demo-controls">
<button class="demo-btn join-btn" data-join="INNER">INNER JOIN</button>
<button class="demo-btn join-btn" data-join="LEFT">LEFT JOIN</button>
<button class="demo-btn join-btn" data-join="RIGHT">RIGHT JOIN</button>
</div>
</div>
<div class="demo-canvas-wrap"></div>
<div class="demo-hint">Matching FK values (dept_id) are highlighted. Result table shows below.</div>
</div>
<div class="info-box">
<h4>Closure Property: Why SQL Subqueries Work</h4>
<p>Every relational algebra operation takes one or more relations as input and produces a relation as output — this is closure. It enables arbitrary composition: the output of a SELECT is itself a relation that can be used as input to another SELECT. This is why subqueries, CTEs, and derived tables all work naturally in SQL. Every query result is a "virtual table" with the same interface as a stored table — that is why you can write <code>SELECT * FROM (SELECT ...) AS sub</code>.</p>
</div>
<div class="section-label">Anti-patterns</div>
<div class="antipatterns">
<div class="antipattern">
<h4>Choosing the wrong join type</h4>
<p>Using INNER JOIN when you need LEFT JOIN silently drops rows. Always verify expected result cardinality with a COUNT(*) sanity check. A missing row in the result is often worse than an error because it is invisible.</p>
</div>
<div class="antipattern">
<h4>Missing referential integrity constraints</h4>
<p>Storing FK values without declaring FOREIGN KEY lets orphaned rows accumulate — child rows pointing to deleted parents. This silent data corruption is expensive to find and fix later, especially across millions of rows.</p>
</div>
<div class="antipattern">
<h4>Using NATURAL JOIN in production</h4>
<p>NATURAL JOIN matches on ALL common column names. Adding a new column to a table can silently change join semantics. Always use explicit ON conditions in production code.</p>
</div>
<div class="antipattern">
<h4>NULL in primary key columns</h4>
<p>NULLs in PK columns violate entity integrity. NULL means "unknown" — two NULLs are not equal in SQL's three-valued logic. Always make PKs NOT NULL. Use surrogate keys (BIGSERIAL, UUID) for stability.</p>
</div>
</div>
<div class="section-label">Quiz</div>
<div class="quiz-section">
<div class="quiz-question">
<div class="quiz-q-num">Question 1 of 5</div>
<div class="quiz-q-text">A relation in the relational model is formally defined as:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="A foreign key is a constraint, not the definition of a relation."><span class="opt-letter">A</span>A foreign key linking two tables</div>
<div class="quiz-option" data-correct="true" data-explanation="A relation is a set of tuples that all conform to the same schema. Order of tuples is undefined — it is a mathematical set."><span class="opt-letter">B</span>A set of tuples sharing the same attribute schema</div>
<div class="quiz-option" data-correct="false" data-explanation="An index is a physical data structure, not a relation."><span class="opt-letter">C</span>An index on a primary key column</div>
<div class="quiz-option" data-correct="false" data-explanation="A query returns a result set, but that is not the definition of a relation."><span class="opt-letter">D</span>A SQL SELECT query result</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 constraint ensures that a foreign key value either matches a primary key in the referenced table or is NULL?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="PRIMARY KEY ensures uniqueness and non-nullability within one table."><span class="opt-letter">A</span>PRIMARY KEY</div>
<div class="quiz-option" data-correct="false" data-explanation="NOT NULL only prevents NULL values; it does not link to another table."><span class="opt-letter">B</span>NOT NULL</div>
<div class="quiz-option" data-correct="true" data-explanation="FOREIGN KEY enforces referential integrity: the referenced value must exist in the parent table or be NULL."><span class="opt-letter">C</span>FOREIGN KEY (referential integrity)</div>
<div class="quiz-option" data-correct="false" data-explanation="UNIQUE ensures no duplicates but does not reference another table."><span class="opt-letter">D</span>UNIQUE</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">The relational algebra pi (projection) operation does what?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Filtering rows is the sigma (selection) operation."><span class="opt-letter">A</span>Filters rows based on a predicate</div>
<div class="quiz-option" data-correct="false" data-explanation="Joining two relations is the join operation."><span class="opt-letter">B</span>Joins two relations on a common attribute</div>
<div class="quiz-option" data-correct="true" data-explanation="pi keeps only specified columns and removes duplicate tuples, corresponding to SQL SELECT DISTINCT col1, col2."><span class="opt-letter">C</span>Keeps only specified columns, removes duplicates</div>
<div class="quiz-option" data-correct="false" data-explanation="Aggregation (COUNT, SUM) is not a basic relational algebra operation — it is an extended operator."><span class="opt-letter">D</span>Computes aggregate functions like COUNT and SUM</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">What distinguishes a candidate key from a superkey?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Both can include multiple columns."><span class="opt-letter">A</span>A candidate key must be a single column</div>
<div class="quiz-option" data-correct="true" data-explanation="A candidate key is a minimal superkey — no proper subset of it is also a superkey. Superkeys may have redundant columns."><span class="opt-letter">B</span>A candidate key is a minimal superkey — no subset also uniquely identifies rows</div>
<div class="quiz-option" data-correct="false" data-explanation="Candidate keys may be nullable in the pure model; PKs (chosen candidate keys) cannot."><span class="opt-letter">C</span>A candidate key cannot contain NULL values</div>
<div class="quiz-option" data-correct="false" data-explanation="A table can have multiple candidate keys."><span class="opt-letter">D</span>A candidate key can only exist once per table</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">ON DELETE CASCADE on a foreign key means:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="RESTRICT prevents deletion of a parent row if child rows exist."><span class="opt-letter">A</span>Prevent deletion of the parent row if child rows exist</div>
<div class="quiz-option" data-correct="false" data-explanation="SET NULL sets the FK column to NULL, it does not cascade the deletion."><span class="opt-letter">B</span>Set the FK column to NULL when the parent is deleted</div>
<div class="quiz-option" data-correct="true" data-explanation="ON DELETE CASCADE automatically deletes child rows when the parent row is deleted. Useful for dependent entities like order_items when an order is deleted."><span class="opt-letter">C</span>Automatically delete child rows when the parent row is deleted</div>
<div class="quiz-option" data-correct="false" data-explanation="SET DEFAULT restores a default value, not cascade deletion."><span class="opt-letter">D</span>Set the FK to a default value when the parent is deleted</div>
</div>
<div class="quiz-feedback"></div>
</div>
</div>
<div class="section-label">Interview Q&A</div>
<div class="qa-section">
<div class="qa-item">
<div class="qa-q">What is the difference between a superkey, candidate key, and primary key?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A <strong>superkey</strong> is any set of attributes that uniquely identifies a tuple. A <strong>candidate key</strong> is a minimal superkey — no proper subset is also a superkey. A <strong>primary key</strong> is the one candidate key chosen by the designer as the principal identifier. Example: in a users table, {user_id}, {email}, and {user_id, email} are all superkeys. {user_id} and {email} are candidate keys (both minimal). The designer picks {user_id} as PK and adds UNIQUE on email.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">Explain referential integrity and ON DELETE options.<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Referential integrity means every non-null FK value must exist as a PK in the referenced table. When the parent row is deleted: <strong>CASCADE</strong> deletes all child rows (useful for orders and items). <strong>SET NULL</strong> sets FK to NULL (useful for optional relationships). <strong>RESTRICT / NO ACTION</strong> rejects the delete if child rows exist (safest default). <strong>SET DEFAULT</strong> sets to a default value. Choose based on the business rule: is a child row meaningful without its parent?</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is the difference between INNER JOIN and CROSS JOIN?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A CROSS JOIN is the Cartesian product — every row in table A combined with every row in table B. If A has 1,000 rows and B has 1,000 rows, the result has 1,000,000 rows. No ON clause. An INNER JOIN is a cross join plus a filter: only tuples where the join predicate is true survive. CROSS JOINs are rarely intentional in production — a missing ON clause in an INNER JOIN produces a CROSS JOIN accidentally. Useful for generating test data or date/product combinations.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What does the relational algebra closure property mean?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Closure means every relational algebra operation takes one or more relations as input and produces a relation as output. This enables composition: you can nest operations arbitrarily, using the output of one as input to the next. SQL exploits this: subqueries, CTEs, and derived tables all rely on closure. Every SQL query produces a "virtual table" that can be treated as a relation for further querying — that is why <code>SELECT * FROM (SELECT ...) AS sub</code> works.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">Why does SQL use DISTINCT? Does not the relational model already eliminate duplicates?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Strictly, yes — a relation is a set, so no duplicate tuples are allowed. But SQL is based on bags (multisets), not sets, for performance: eliminating duplicates requires sorting or hashing, which is expensive. So SELECT without DISTINCT can return duplicates. SELECT DISTINCT adds the deduplication step. This is a deliberate SQL deviation from pure relational theory — the tradeoff is performance over theoretical purity. For aggregations, duplicates are often intentional (you want to COUNT all rows, not just unique ones).</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is entity integrity and why is NULL problematic in PKs?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Entity integrity requires primary key columns be non-null and unique. NULL means "unknown value" — two NULLs are not considered equal (NULL != NULL in SQL's three-valued logic). If a PK column is NULL, the row cannot be uniquely identified. The DBMS rejects NULL in PK columns (NOT NULL is implicit). In practice, use surrogate keys (BIGSERIAL, UUID v7) rather than natural keys that might be null or change over time such as email addresses.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">How is a LEFT JOIN different from a subquery with NOT EXISTS?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Both express "left side with optional right side" but serve different patterns. <strong>LEFT JOIN</strong> retrieves data from both tables — you can SELECT columns from both. Common pattern: "give me all orders and their payments if they exist." <strong>NOT EXISTS</strong> finds rows in one table with no match in another: "give me customers who have never ordered" — you test for absence, not select from the second table. Performance: LEFT JOIN + WHERE right.col IS NULL is equivalent to NOT EXISTS but the optimizer may choose different plans. NOT EXISTS often optimizes better in PostgreSQL; NOT IN can behave unexpectedly if the subquery returns NULLs.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is a self-join and when would you use one?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A self-join joins a table to itself using aliases. Classic use: hierarchical or adjacency list structures. Example: <code>SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id</code>. This retrieves each employee alongside their manager's name from the same table. Other uses: finding pairs of employees in the same department, comparing rows within the same table, or finding records that share a value. The DBMS treats the two aliases as two logical relations — same physical table, two logical names.</div></div>
</div>
</div>
<div class="section-label">Further Reading</div>
<div class="reading-links">
<a class="reading-link" href="https://www.cs.cmu.edu/~christos/COURSES/826.S03/HANDOUTS/codd72.pdf" target="_blank">Codd's Original 1970 Paper</a>
<a class="reading-link" href="https://en.wikipedia.org/wiki/Relational_algebra" target="_blank">Relational Algebra (Wikipedia)</a>
<a class="reading-link" href="https://www.postgresql.org/docs/current/ddl-constraints.html" target="_blank">PostgreSQL Constraints Documentation</a>
</div>
<div class="topic-nav">
<a href="01-intro.html" class="topic-nav-link">
<div class="topic-nav-arrow">←</div>
<div><div class="topic-nav-label">Previous</div><div class="topic-nav-title">Introduction to DBMS</div></div>
</a>
<a href="03-sql.html" class="topic-nav-link next">
<div class="topic-nav-arrow">→</div>
<div><div class="topic-nav-label">Next</div><div class="topic-nav-title">SQL Fundamentals</div></div>
</a>
</div>
</div>
<script src="../js/main.js"></script>
<script src="../js/demos.js"></script>
</body>
</html>