Worked examples — Relational model — tables, rows, columns, NULL

This page is a drill through every case the relational model can throw at you. We do not learn a new idea here — we take the ideas from the parent note (tables, rows, columns, and the tricky NULL) and grind them against every situation, one cell of a matrix at a time.

---

The scenario matrix

Every question about a relational table falls into one of these case classes. Read the whole grid once; each worked example below is tagged with the cell it covers.

#	Case class	The situation	Example that covers it
C1	Counting shape	degree vs cardinality, incl. an empty table	Ex 1
C2	Comparison with a known value	col = v, col <> v where col is present	Ex 2
C3	Comparison touching NULL	NULL = v, NULL <> v → UNKNOWN	Ex 3
C4	Boolean combine: AND	TRUE/FALSE/UNKNOWN AND UNKNOWN	Ex 4
C5	Boolean combine: OR	TRUE/FALSE/UNKNOWN OR UNKNOWN	Ex 4
C6	Arithmetic with NULL	NULL + 5, NULL * 0 — degenerate zero case	Ex 5
C7	Aggregates over NULL	COUNT, COUNT(col), SUM, AVG skipping NULLs	Ex 6
C8	Set identity: duplicates & order	is a table a set or a bag? no ORDER BY	Ex 7
C9	Word problem (real world)	a survey with missing answers	Ex 8
C10	Exam twist	the = OR <> "returns everyone?" trap + the fix	Ex 9

We will reuse this small table throughout (call it Student):

id	name	age	major
1	Asha	20	CS
2	Ravi	NULL	Physics
3	Mira	22	NULL

Here "NULL" means, as the parent note said, value absent / unknown — not zero, not empty text.

The figure below draws this exact grid. Look at it now: the cyan header boxes are the columns (kinds of fact), each white row is a record, and the two amber boxes mark the NULL cells — the ones we will keep colliding with throughout this page. Notice degree (4) counts across the top, cardinality (3) counts down the side.

---

Ex 1 — Counting shape, including the empty table (C1)

Steps

1. Degree = number of columns. Student has columns id, name, age, major → degree $=4$. Why this step? Degree describes the shape (the schema), which is fixed regardless of how many rows exist.
2. Cardinality = number of rows. Student shows 3 records → cardinality $=3$. Why this step? Cardinality describes the content — how many facts are stored right now.
3. Empty table Log: it has 3 declared columns, so degree $=3$, even with zero rows. Cardinality $=0$. Why this step? This is the degenerate cell: shape and content are independent. A table can have shape but no content.

Verify: Student degree$+$cardinality $=4+3=7$; Log $=3+0=3$. Counts are non-negative integers ✓.

---

Ex 2 — Comparison with a known value (C2)

Steps

1. age = 20: Asha has $20$ → TRUE. Mira has $22$ → $22=20$ is FALSE. Why this step? Both ages are present and known, so ordinary two-valued logic applies — every comparison is cleanly TRUE or FALSE.
2. Result of age = 20: {Asha} — WHERE keeps only the TRUE row. Why this step? A WHERE clause returns exactly the rows whose test is TRUE, so only Asha survives while Mira's FALSE row is dropped.
3. age <> 20 (the <> means "not equal"): Asha $20\ne 20$ FALSE; Mira $22\ne 20$ TRUE. Why this step? We flip the test to "not equal" to check the complementary set; since both ages are known, each again resolves cleanly to TRUE or FALSE.
4. Result of age <> 20: {Mira} — the one TRUE row. Why this step? Again WHERE keeps only TRUE rows, so Mira survives and Asha's FALSE row is dropped — the exact opposite of step 2, as expected for a not-equal test.

Verify: Asha and Mira are split into disjoint sets {Asha} and {Mira}; together they cover both known-age rows, exactly what "equal or not-equal" should do when no NULL is involved ✓.

---

Ex 3 — Comparison touching NULL (C3)

Steps

1. age = 20 for Ravi: age is unknown, so "is the unknown equal to 20?" → we cannot honestly say. Result $=$ UNKNOWN. Why this step? Any comparison against an unknown value yields UNKNOWN, because you cannot test something you do not know.
2. age <> 20 for Ravi: same reasoning → UNKNOWN.
3. age = NULL: now both sides are unknown. $\text{NULL}=\text{NULL}\Rightarrow \text{UNKNOWN}$. This is not TRUE. Why this step? Two unknowns cannot be proven equal — you don't know either of them.

The figure shows Ravi's age as an amber ? box, with all three tests arrowing out to the same amber verdict UNKNOWN. Look at the arrows: every one lands on UNKNOWN — visual proof that no ordinary comparison can escape the unknown box.

Verify: all three land on UNKNOWN, matching the parent formula $\text{NULL}=\text{NULL}\Rightarrow \text{UNKNOWN}$ ✓.

---

Ex 4 — Boolean combine: AND & OR with UNKNOWN (C4, C5)

We write U for UNKNOWN. Think of U as "TRUE or FALSE, we can't tell."

Steps

1. T AND U: AND is TRUE only if both are TRUE. One side is TRUE, the other could be TRUE or FALSE → the whole could still go either way → U. Why this step? The known TRUE does not "dominate" AND — it still needs the other side to also be TRUE, and that side is unknown, so the result stays unknown.
2. F AND U: AND is FALSE if either side is FALSE. One side is already FALSE → result is FALSE regardless of the unknown → FALSE. Why this step? The known FALSE "dominates" AND, so the unknown never matters.
3. T OR U: OR is TRUE if either side is TRUE. One side is already TRUE → result TRUE regardless → TRUE. Why this step? The known TRUE "dominates" OR.
4. F OR U: OR is TRUE only via a TRUE somewhere; here one side is FALSE and the other unknown → could go either way → U. Why this step? The known FALSE does not "dominate" OR — OR still needs some TRUE to fire, and the only candidate is unknown, so the result stays unknown.

The figure shows two 3×3 grids, AND on the left and OR on the right, with each cell coloured by outcome (cyan T, white F, amber U). Look at the amber cells: they cluster exactly where no dominating value is present — the geometry of the mnemonic below.

Verify: F AND U = FALSE, T OR U = TRUE, T AND U = U, F OR U = U — the two collapse cases match the parent's truth tables ✓.

---

Ex 5 — Arithmetic with NULL, and the zero degenerate (C6)

Steps

1. NULL + 5: unknown plus 5 is still unknown (you don't know the starting number) → NULL. Why this step? Adding a known amount to an unknown quantity leaves the total unknown — shifting an unknown by 5 does not reveal what it was, so the result must stay NULL.
2. NULL - NULL: two separate unknowns subtracted — you don't know either, so the difference is unknown → NULL. (It is not 0!) Why this step? NULLs are not "the same value"; they are independent absences.
3. NULL * 0: in ordinary maths anything times 0 is 0. But SQL treats any arithmetic touching NULL as NULL → NULL. Why this step? This is the degenerate zero case. SQL propagates NULL structurally; it does not simplify unknown * 0 to 0.

Verify (concept): all three results are NULL, consistent with $\text{NULL}+5\Rightarrow \text{NULL}$ from the parent ✓.

---

Ex 6 — Aggregates skip NULL (C7)

Steps

1. COUNT(*): counts rows, regardless of NULLs → $3$. Why this step? The * form is about existence of rows, so all three rows count.
2. COUNT(age): counts non-NULL values in age. Ages present: Asha $20$, Mira $22$; Ravi is NULL and is skipped → $2$. Why this step? COUNT(col) ignores NULLs — this is the whole point of the C7 cell.
3. SUM(age): adds present ages only: $20+22=42$. Why this step? SUM, like every aggregate except COUNT(*), silently drops NULL inputs before adding — you cannot add an unknown, so Ravi's row contributes nothing to the total.
4. AVG(age): SUM over the count of non-NULL values $=42/2=21$, not $42/3$. Why this step? AVG divides by how many values it actually summed (2), so a missing age does not drag the average toward 0.

Verify: $\text{SUM}=42$, non-NULL count $=2$, $\text{AVG}=42/2=21$. Compare with the wrong method $42/3=14$ — the difference proves NULL was correctly excluded ✓.

---

Ex 7 — Set vs bag: duplicates and order (C8)

Steps

1. Pure relation is a set: duplicates collapse. {CS, CS, Physics} as a set $=\{CS,Physics\}$ → 2 distinct rows. Why this step? A set has no repeated elements — that is the mathematical definition the parent gave.
2. Real SQL is a multiset (bag): an actual SQL table may keep both CS rows unless a PRIMARY KEY/UNIQUE forbids it. So theory says 2, practice may store 3. Why this step? The steel-man from the parent — real tables relax the "no duplicates" rule.
3. Order: without ORDER BY, row order is not guaranteed. You must not assume CS, Physics, NULL. Why this step? A set has no intrinsic order, so the engine may return rows in any order.

Verify: distinct count of {CS, CS, Physics} $=2$ ✓.

---

Ex 8 — Word problem: a survey with missing answers (C9)

Steps

1. Separate absent from zero. Two NULLs = "customer left the question blank" (unknown). The 0 = a known answer: "I tipped nothing." These are different! Why this step? This is the parent's core distinction: NULL $\ne$ 0. The 0 is real data; the NULLs are missing data.
2. Average tip = AVG(tip): sum of known values $=50+0+30=80$; count of non-NULL values $=3$ → $80/3\approx 26.67$. Why this step? AVG skips the 2 NULLs but includes the known 0, because 0 is a value.
3. "Tipped above 0": count rows where tip > 0. Known values $50,0,30$: those $>0$ are $50$ and $30$ → $2$ people. The two NULL rows give NULL > 0 → UNKNOWN → not counted. Why this step? A WHERE/filter keeps a row only when the test is TRUE; UNKNOWN rows are dropped (parent's rule).

Verify: $\text{SUM}=80$, non-NULL count $=3$, $\text{AVG}=80/3\approx 26.6\overline{6}$; people with tip>0 $=2$ ✓.

---

Ex 9 — Exam twist: the "returns everyone?" trap and its fix (C10)

Steps

1. Asha (age 20): 20 = 20 TRUE OR 20 <> 20 FALSE → TRUE OR FALSE = TRUE → kept. Why this step? Asha's age is known, so both branches resolve to definite truth values and the OR fires TRUE — she must survive the filter, establishing the "expected" behaviour before the trap appears.
2. Mira (age 22): 22 = 20 FALSE OR 22 <> 20 TRUE → FALSE OR TRUE = TRUE → kept. Why this step? Again a known age gives two definite branches; the OR fires TRUE via the not-equal branch, confirming every non-NULL row does behave like the tautology suggests.
3. Ravi (age NULL): NULL = 20 → U OR NULL <> 20 → U → U OR U = U. WHERE keeps only TRUE rows → Ravi is silently dropped. Why this step? The tautology breaks precisely because the "either/or" never becomes TRUE when both branches are UNKNOWN.
4. Result: {Asha, Mira} — not everyone. Ravi vanished. Why this step? Collecting the per-row verdicts (TRUE, TRUE, UNKNOWN) and applying the "only TRUE survives" rule shows the query returns 2 of 3 rows — making the silent loss of the NULL row concrete and countable.
5. Fix — handle NULL explicitly with IS NULL, which returns real TRUE/FALSE: SELECT name FROM Student WHERE age = 20 OR age <> 20 OR age IS NULL; Now Ravi's branch age IS NULL → TRUE → kept. Result {Asha, Ravi, Mira}. Why this step? IS NULL is the only correct way to test for absence, escaping three-valued logic back into two-valued TRUE/FALSE.

The figure lays out all three rows with an arrow to each verdict: Asha and Mira arrow to cyan TRUE / kept, while Ravi arrows to amber UNKNOWN / DROPPED. Look at the amber row: it is the visual of a row silently falling out of the result, and the caption line shows the IS NULL fix that rescues it.

Verify: buggy query returns 2 names {Asha, Mira}; fixed query returns 3 names {Asha, Ravi, Mira}. The difference (1 row) is exactly the NULL row ✓.

---

Active recall

Recall Which cell was which?

• Empty table degree? ::: Equal to its declared column count (rows don't matter) — here 3.
• NULL - NULL? ::: NULL (two independent unknowns), never 0.
• COUNT(age) on 3 rows with 1 NULL? ::: 2.
• AVG of {20, NULL, 22}? ::: 21 = 42/2, dividing by non-NULL count.
• F AND U? ::: FALSE. T OR U? ::: TRUE.
• Does age = 20 OR age <> 20 return NULL-age rows? ::: No — UNKNOWN OR UNKNOWN drops them.
• Correct fix to include them? ::: Add OR age IS NULL.

---

Connections

• SQL SELECT and WHERE — where three-valued logic actually filters rows
• COALESCE and NULL handling functions — substitute defaults for NULL before aggregating
• Primary Keys and Uniqueness — forbids the duplicate rows of Ex 7
• Data Types and Domains — why 0, '', and NULL live in different worlds
• Foreign Keys and Referential Integrity · Normalization