Noesis
4.4.1Databases

Relational model — tables, rows, columns, NULL

1,893 words9 min readdifficulty · medium1 backlinks
🇮🇳 Yeh note Hinglish mein padho →

WHY does the relational model exist?

A relation in math is just a set of tuples drawn from some domains. A "table" is the everyday name for a relation.

WHAT are the pieces?

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

HOW to read a table — worked example

Table Student:

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

NULL — the subtle part (three-valued logic)

Common mistakes (Steel-man → Fix)

Active recall

Recall Quick self-test (hide answers, recall first)
  • Degree vs cardinality? → degree = #columns, cardinality = #rows.
  • What does NULL mean? → value missing/unknown, not 0/''.
  • NULL = NULL returns? → UNKNOWN.
  • How to test for NULL? → IS NULL / IS NOT NULL.
  • FALSE AND NULL? → FALSE. TRUE OR NULL? → TRUE.
  • Does a row survive WHERE if the condition is UNKNOWN? → No, only TRUE survives.
Recall Feynman: explain to a 12-year-old

Imagine a class attendance grid. Each column is a question we ask about a kid (name, age, favourite sport). Each row is one kid's answers. The whole grid is the table. Sometimes a kid forgot to write their age — that empty box isn't "zero years old," it just means we don't know yet. That blank-meaning-unknown is called NULL. And here's the funny rule: if you ask "is this unknown age bigger than 10?" the honest answer is "I can't tell" — so the computer says unknown and just skips that kid from the answer list.

Connections

  • Primary Keys and Uniqueness — how to forbid duplicate rows
  • Foreign Keys and Referential Integrity — linking tables
  • SQL SELECT and WHERE — three-valued logic in action
  • Normalization — organising columns to avoid anomalies
  • Data Types and Domains — what a column may hold
  • COALESCE and NULL handling functions
What is a relation/table in the relational model?
A named set of rows (tuples) all sharing the same columns (attributes).
Degree of a table?
The number of columns.
Cardinality of a table?
The number of rows.
What does NULL represent?
A missing/unknown value — not zero and not the empty string.
Result of NULL = NULL?
UNKNOWN (not TRUE).
Which logic does SQL use because of NULL?
Three-valued logic (TRUE, FALSE, UNKNOWN).
FALSE AND NULL evaluates to?
FALSE (already false regardless of the unknown).
TRUE OR NULL evaluates to?
TRUE (already true regardless of the unknown).
Does a row pass a WHERE clause when its condition is UNKNOWN?
No — only rows where the condition is TRUE are kept.
Correct way to test for a missing value?
Use IS NULL / IS NOT NULL.
Why is row order not guaranteed without ORDER BY?
A relation is mathematically a set, so order isn't part of the data.
What does COALESCE(x, d) do?
Returns x if it isn't NULL, otherwise the default d.

Concept Map

separates

enables

is a set of

has named

restricted by

counted as

counted as

defined by

may hold

means

comparisons give

leads to

Codd 1970 insight

Logical vs physical structure

Declarative SQL queries

Relation / Table

Rows / tuples

Columns / attributes

Domain / data type

Cardinality

Degree

Schema / shape

NULL marker

Value absent or unknown

UNKNOWN value

Three-valued logic

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Relational model ka basic idea ekdum simple hai: saara data tables (grid) ke form mein store hota hai. Har column ek tarah ki information ka naam hai (jaise name, age, major) aur uska ek domain yaani data type hota hai. Har row ek record hai — ek student, ek order, jo bhi. Jitne columns utna degree, jitni rows utni cardinality. Mathematically ye ek set of tuples hai, isliye bina ORDER BY ke row order ki koi guarantee nahi — yaad rakhna.

Ab sabse tricky cheez: NULL. NULL ka matlab hai "value pata hi nahi" — ye zero nahi, empty string '' bhi nahi. Socho Ravi ki age form mein blank reh gayi — iska matlab ye nahi ki Ravi 0 saal ka hai, matlab hai humein nahi pata. Isi wajah se SQL normal TRUE/FALSE ke alawa ek teesri value laata hai: UNKNOWN. Jaise hi NULL ke saath comparison karo (NULL = 20, NULL > 10), answer aata hai UNKNOWN.

Iska practical impact: WHERE clause sirf un rows ko rakhta hai jinki condition TRUE ho. UNKNOWN waali rows chup-chaap drop ho jaati hain. Isliye WHERE age = 20 OR age <> 20 likhne par bhi NULL waala banda gayab ho jaata hai. Fix? NULL check karne ke liye hamesha IS NULL / IS NOT NULL use karo, kyunki yeh asli TRUE/FALSE deta hai. Aur missing value ki jagah default daalna ho to COALESCE(col, default) use karo. Mantra yaad rakho: "NULL = no clue", aur no clue kabhi no clue ke barabar nahi hota.

Go deeper — visual, from zero

D1FoundationsEvery symbol from zero · 1,793 wordsD2Visual walkthroughThe derivation in pictures · 2,371 wordsD3Worked examplesEvery scenario covered · 3,198 wordsD4ExercisesGraded, full solutions · 2,695 wordsD5Question bankConcept traps, quick reveals · 1,682 words

Test yourself — Databases

L1 RecognitionL2 RecallL3 ProductionL4 ApplicationL5 Mastery

Connections

Normalization — 1NF, 2NF, 3NF, BCNF — anomalies each resolvesCoding · 4.4.21Data types — int, float, str, bool, NoneTypeCoding · 1.2.4Keys — primary, candidate, super, foreign, natural vs surrogateCoding · 4.4.2