Relations, Tuples and Domains

Jr Codex DBMS Notes

Level: Beginner Prerequisites: Module 1, Chapter 4: ACID Time to complete: ~15 minutes

Table of Contents

1. The Formal Vocabulary
2. A Relation Is a Set
3. The Four Properties That Follow
4. Schema vs Instance
5. NULL — the Awkward Value
6. Summary & Next Steps

1. The Formal Vocabulary

The formal terms map onto the everyday ones, and both are used constantly — including in error messages.

Named
─────────────────────────────────────────
  students
  ┌────────┬──────────┬──────────┬────────┐
  │  id    │  name    │  city    │  marks │  ← attributes
  ├────────┼──────────┼──────────┼────────┤
  │  1     │  Asha    │  Pune    │   88   │  ← a tuple
  │  2     │  Ravi    │  Delhi   │   74   │
  │  3     │  Meera   │  Pune    │   91   │
  └────────┴──────────┴──────────┴────────┘

  degree = 4          cardinality = 3
  domain of marks = integers 0-100
─────────────────────────────────────────

2. A Relation Is a Set

This is the definition everything else follows from.

The Definition
─────────────────────────────────────────
  Given domains D1, D2, ... Dn, a RELATION is a
  SUBSET of the Cartesian product D1 × D2 × ... × Dn.

  In plain terms: a relation is a SET of tuples,
  where every tuple has one value from each domain.

  The operative word is SET — not list, not
  sequence, not file.
─────────────────────────────────────────

# A relation, modelled directly in Python. Note the set, and the frozen tuples.
students = {
    (1, "Asha",  "Pune",  88),
    (2, "Ravi",  "Delhi", 74),
    (3, "Meera", "Pune",  91),
}
 
# Being a set has consequences you can observe:
students.add((1, "Asha", "Pune", 88))
print(len(students))        # still 3 — duplicates are impossible by definition

3. The Four Properties That Follow

Because a relation is a set of tuples, four things are true automatically. Each has a practical consequence.

1. NO DUPLICATE TUPLES
─────────────────────────────────────────
  Sets contain distinct elements. Two identical
  rows are one row.

  CONSEQUENCE: every relation needs something that
  distinguishes its tuples — a KEY (Chapter 2).

2. TUPLE ORDER IS MEANINGLESS
─────────────────────────────────────────
  Sets are unordered. "The third row" is not a
  concept.

  CONSEQUENCE: a query with no ORDER BY may return
  rows in ANY order, and that order may change
  between runs when the plan changes. Relying on
  it is one of the most common real bugs in
  application code.

3. ATTRIBUTE ORDER IS MEANINGLESS
─────────────────────────────────────────
  Attributes are named, not positioned.

  CONSEQUENCE: SELECT * returns columns in schema
  order, which changes when someone adds a column.
  Name your columns explicitly in anything that
  ships.

4. VALUES ARE ATOMIC
─────────────────────────────────────────
  Each cell holds ONE indivisible value from its
  domain — not a list, not a nested record.

  CONSEQUENCE: this is FIRST NORMAL FORM, and it is
  why "phone numbers" as a comma-separated string
  is a design error (Module 4).
─────────────────────────────────────────

The Theory-vs-Practice Caveat
─────────────────────────────────────────
  SQL tables are BAGS, not sets — they permit
  duplicate rows unless a key forbids it, which is
  why SELECT DISTINCT exists at all.

  This is SQL departing from the model, covered in
  Chapter 4. The set-based reasoning still governs
  how you should DESIGN, even where SQL is looser.
─────────────────────────────────────────

4. Schema vs Instance

The Distinction
─────────────────────────────────────────
  SCHEMA     the DESIGN — names, attributes,
             domains, constraints.
             Changes rarely, by migration.

    students(id INT, name TEXT, city TEXT, marks INT)

  INSTANCE   the DATA in it at one moment.
             Changes constantly.

    the three rows above

  Same distinction as a class and its objects
  (Python Notes, Module 4), or a struct and its
  values.
─────────────────────────────────────────

-- The schema: written once.
CREATE TABLE students (
    id    INTEGER PRIMARY KEY,
    name  TEXT    NOT NULL,
    city  TEXT,
    marks INTEGER CHECK (marks BETWEEN 0 AND 100)     -- the DOMAIN, enforced
);
 
-- The instance: changes all day.
INSERT INTO students VALUES (1, 'Asha', 'Pune', 88);

Why the Distinction Matters
─────────────────────────────────────────
  Constraints are properties of the SCHEMA, so they
  hold for every possible instance — past, present
  and future.

  "No student has marks above 100" declared as a
  CHECK is true forever. The same rule enforced in
  application code is true only for the code paths
  someone remembered.
─────────────────────────────────────────

5. NULL — the Awkward Value

What NULL Means
─────────────────────────────────────────
  NULL is not zero, and not an empty string. It
  means "no value here", covering at least three
  distinct situations:

    UNKNOWN        the student has a city; we do
                   not know it
    NOT APPLICABLE the attribute does not apply
    WITHHELD       known, deliberately not stored

  SQL uses one marker for all three, which is the
  root of most confusion about it.
─────────────────────────────────────────

Three-Valued Logic
─────────────────────────────────────────
  Comparisons with NULL yield UNKNOWN, not TRUE or
  FALSE.

    NULL = NULL        ──► UNKNOWN  (not TRUE!)
    NULL <> 5          ──► UNKNOWN
    marks > 50         ──► UNKNOWN when marks is NULL

  WHERE keeps only TRUE rows, so UNKNOWN rows are
  silently dropped.
─────────────────────────────────────────

SELECT * FROM students WHERE city = NULL;      -- returns NOTHING, ever
SELECT * FROM students WHERE city IS NULL;     -- the correct form
 
-- The trap that catches people:
SELECT * FROM students WHERE city <> 'Pune';
-- Excludes students whose city is NULL — they are neither Pune nor not-Pune.
SELECT * FROM students WHERE city IS DISTINCT FROM 'Pune';   -- includes NULLs

Practical Guidance
─────────────────────────────────────────
  - Use NOT NULL by default; allow NULL only where
    "no value" is genuinely meaningful
  - Never use NULL as a sentinel for zero, empty,
    or "not yet"
  - Remember aggregates SKIP nulls: AVG(marks)
    divides by the count of NON-NULL marks
  - Test any WHERE clause with a NULL present
─────────────────────────────────────────

6. Summary & Next Steps

Key Takeaways

• A relation is formally a set of tuples, and four practical properties follow directly: no duplicates, no row order, no column order, and atomic values.
• Because row order is meaningless, a query without ORDER BY may return rows in any order — and that order can change when the plan changes.
• The schema is the design and the instance is the data; constraints belong to the schema, which is why they hold for every future instance.
• NULL means "no value", comparisons against it yield UNKNOWN rather than TRUE or FALSE, and WHERE silently drops UNKNOWN rows.

Concept Check

1. Your application reads "the first row" of an unordered query and it works for months, then breaks after someone adds an index. Explain why, using this chapter's second property.
2. Why is a comma-separated list of phone numbers in one column a violation of the relational model itself, not just a stylistic preference?
3. WHERE city <> 'Pune' returns fewer rows than expected. What is happening, and what is the fix?

Next Chapter

→ Chapter 2: Keys and Constraints

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index