Exercises — Relational model — tables, rows, columns, NULL

WHAT we do: apply the two definitions — count columns for degree, count rows for cardinality.

• Columns are id, name, age, major, gpa → 5 columns → degree = 5.
• Rows are Asha, Ravi, Mira, Dev → 4 rows → cardinality = 4.

WHY it works: degree is the grid's width and cardinality is its height. A NULL cell still counts — the row and column exist; only the value is missing.

The marker is NULL. It does not equal 0 (a known number) and does not equal '' (a known empty string) — those are values, NULL is the absence of one.

The NULL-vs-NULL subtlety: SQL does not say NULL = NULL is FALSE. It says the comparison is UNKNOWN (printed as NULL): you cannot prove two unknowns equal because you don't know either one. Because = never returns TRUE for NULL, the only reliable test for "is this cell missing?" is IS NULL (or IS NOT NULL), which returns a real TRUE/FALSE.

WHAT we do: test the condition gpa = 3.1 on every row; keep a row only if the result is TRUE.

• Asha: 3.8 = 3.1 → FALSE → drop.
• Ravi: NULL = 3.1 → UNKNOWN → drop (not TRUE).
• Mira: 3.1 = 3.1 → TRUE → keep.
• Dev: 3.1 = 3.1 → TRUE → keep.

Answer: Mira, Dev. WHY Ravi drops: comparing a known number to an unknown gives UNKNOWN, and WHERE only keeps TRUE.

WHAT COALESCE does: COALESCE(x, d) returns x if x is not NULL, else d.

• Asha: 3.8 (not NULL) → 3.8
• Ravi: NULL → default → 0.0
• Mira: 3.1 → 3.1
• Dev: 3.1 → 3.1

Result list: [3.8, 0.0, 3.1, 3.1]. WHY use it: it turns "unknown" into a chosen stand-in so later arithmetic doesn't collapse to NULL.

Treat NULL (call it U) as "TRUE or FALSE, can't tell." The figure below is a colour-coded truth table — read it like this:

How to read the figure (alt text): it has two blocks. The left block, "AND", lists three rows: FALSE AND U = FALSE (orange result), TRUE AND U = U (magenta result), U AND U = U. The right block, "OR", lists TRUE OR U = TRUE (violet result), FALSE OR U = U, U OR U = U. Colours: violet = TRUE, orange = FALSE, magenta = UNKNOWN. The caption reminds you: the known side wins whenever it already forces the answer.

Now the five parts:

• (a) FALSE AND U: AND is TRUE only if both TRUE. One side is already FALSE → whole thing FALSE no matter what U is → FALSE.
• (b) TRUE OR U: OR is TRUE if either is TRUE. One side is already TRUE → TRUE.
• (c) TRUE AND U: result depends entirely on U — could be TRUE or FALSE → UNKNOWN.
• (d) U OR U: both unknown, could go either way → UNKNOWN.
• (e) NOT U: NOT flips TRUE↔FALSE, but if we can't tell whether U is TRUE or FALSE, we equally can't tell what its opposite is → UNKNOWN. (This is why NOT (age = 20) still drops Ravi: flipping an unknown stays unknown.)

WHY: whenever the known side already forces the answer, the unknown can't change it. Otherwise — including any NOT applied to an unknown — the answer is genuinely unknown.

WHAT we do: evaluate the condition per row.

• Asha (20): 20>21 FALSE OR 20<=21 TRUE → TRUE → keep.
• Ravi (NULL): NULL>21=U OR NULL<=21=U → U OR U = UNKNOWN → drop.
• Mira (22): 22>21 TRUE → TRUE → keep.
• Dev (19): 19<=21 TRUE → TRUE → keep.

Answer: 3 rows (Asha, Mira, Dev). It looks like it should return all 4 ("every age is either >21 or ≤21"), but Ravi's unknown age makes both halves UNKNOWN, so he's excluded. Fix to get all 4: append OR age IS NULL.

Rule: COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL.

• (a) COUNT(*) = 4 rows → 4.
• (b) COUNT(age): ages are 20, NULL, 22, 19 → 3 non-NULL → 3.
• (c) COUNT(gpa): 3.8, NULL, 3.1, 3.1 → 3 non-NULL → 3.
• (d) COUNT(major): CS, Physics, NULL, CS → 3 non-NULL → 3.

WHY the difference: * means "just tell me how many records exist," while COUNT(col) means "how many records have a known value in that column."

Plain AVG(gpa): non-NULL gpas are 3.8, 3.1, 3.1.

• Sum = 3.8 + 3.1 + 3.1 = 10.0. Count = 3.
• Average = 10.0 ÷ 3 = 3.333… ≈ 3.333.

After COALESCE: now the values are 3.8, 0.0, 3.1, 3.1 — four known numbers.

• Sum = 3.8 + 0.0 + 3.1 + 3.1 = 10.0. Count = 4.
• Average = 10.0 ÷ 4 = 2.5.

WHY they differ: plain AVG treats Ravi's gpa as "not there" (dropped from both sum and count). COALESCE claims Ravi scored 0.0, dragging the mean down. Same data, two different questions — choose deliberately.

(a) Classical relational model: Codd's pure model in first normal form requires every attribute to hold exactly one atomic value from its domain — it does not permit missing values at all; NULL was a later, controversial addition precisely because pure theory has no slot for "no value." So strictly, a row with age=NULL is not a well-formed tuple of the pure relation; the whole three-valued-logic machinery exists only because practical SQL bolted NULL on top. (Even setting NULL aside, a relation is a set, so the separate rule that forbids duplicate rows would still apply.) (b) Real SQL with PK on id: a PRIMARY KEY forces id to be unique and NOT NULL. Row with id=1 already exists → the insert is rejected. This is why keys matter: real tables are multisets and would otherwise permit two different students sharing id=1, breaking the "one id ↔ one student" promise. Constraint that fixes it: declare id PRIMARY KEY (or UNIQUE), which forbids duplicate identifiers. See Foreign Keys and Referential Integrity for why other tables can then safely point at id.

Semantics: major = 'CS' is UNKNOWN for Mira (NULL vs a known string), so WHERE drops her — this is correct behaviour, not a bug. If we want to include unknowns we must say so. Condition: WHERE major = 'CS' OR major IS NULL. Row-by-row:

• Asha (CS): TRUE OR FALSE = TRUE → keep.
• Ravi (Physics): FALSE OR FALSE = FALSE → drop.
• Mira (NULL): UNKNOWN OR TRUE = TRUE (OR with a TRUE is TRUE) → keep.
• Dev (CS): TRUE OR FALSE = TRUE → keep.

Answer: Asha, Mira, Dev (3 rows). WHY it works: IS NULL returns a real TRUE for Mira, and TRUE OR anything = TRUE, rescuing her from the vanishing-row trap.

Degree & cardinality of Student? ::: 5 and 4. COUNT(*) vs COUNT(age) here? ::: 4 vs 3. Rows returned by WHERE age > 21 OR age <= 21? ::: 3 (Ravi's NULL age escapes both). AVG(gpa) vs AVG after COALESCE(gpa,0)? ::: 3.333 vs 2.5. FALSE AND NULL / TRUE OR NULL / NOT NULL? ::: FALSE / TRUE / UNKNOWN. One condition for CS-or-unknown majors? ::: major='CS' OR major IS NULL.