Relations, Tuples, and Attributes

A relation is a set of tuples drawn from the Cartesian product of its attribute domains, and that single definition — “a set, not a list” — is the mechanism that forces every property you will rely on for the next twenty years of SQL: rows have no order, columns have no order, and no two rows can be identical. Everything below (arity, cardinality, schema vs. instance, why a key is mandatory) is a consequence of that one set-theoretic fact, not an arbitrary rule a vendor invented.

The mechanism: a relation is a subset of a Cartesian product

Fix three domains — the universes of legal values for each attribute:

• dom(RollNo) = the positive integers
• dom(Name) = strings up to 60 chars
• dom(CGPA) = the reals in [0.0, 10.0], plus the special marker NULL (“unknown / not applicable”)

The Cartesian product dom(RollNo) × dom(Name) × dom(CGPA) is the set of every conceivable 3-tuple — billions of them, almost all nonsense. A relation is simply a chosen subset of that product: the handful of tuples that are true facts right now. A tuple is one element of that subset (one row, one fact). An attribute is a named position in the tuple, and its name — not its left-to-right index — is how you address it. Because the container is a mathematical set, two structural guarantees come for free:

1. No duplicate tuples. Sets cannot contain the same element twice, so two byte-identical rows are the same row.
2. No inherent order of rows or of columns. {a, b} = {b, a}; the relation is unchanged by reordering.

Hold onto guarantee #1 — it is the reason a relation needs a key, and the reason the SQL tables you actually use quietly break this model.

Worked example: watch the set semantics bite

Start with the four true facts above. Schema and instance are different things, so name them precisely:

• Schema (the blueprint, fixed): Student(RollNo: int, Name: varchar(60), CGPA: decimal(3,1) NULL). Its arity / degree is 3 — a property of the schema, so it never changes as rows come and go.
• Instance (the data right now, changes every write): the set of four tuples. Its cardinality is 4 — a property of the instance.

Now run three operations and track what the set rules do at each step:

1. Insert a fact that is already present. Attempt to add (102, 'Ben', 7.4) again. In the pure model this is a no-op: the element is already in the set, so cardinality stays 4. The information content did not increase, because asserting a true fact twice tells you nothing new.
2. Reorder the rows. List Dev first, then Asha, Chen, Ben. The relation is identical — same set, same cardinality 4. Any query result that depended on this physical order would be reading something the model does not promise.
3. Reason about the NULL. Ask “how many students have CGPA < 8.0?” Ben (7.4) qualifies. Chen is NULL — unknown — so NULL < 8.0 evaluates to unknown, not true, and Chen is excluded. Answer: 1, not 2. The NULL is not zero and not a low value; it is the absence of a value, and comparisons against it produce a third truth value.

Three operations, three model-level facts: duplicates collapse, order is meaningless, and NULL infects comparisons. None of these were taught as rules — they fell out of “a relation is a set.”

Why the textbook model and your real table disagree

Here is the trap that bites working engineers: a SQL table is a bag (multiset), not a relation. The standard relational model forbids duplicate rows; SQL permits them. If you create a table with no primary key and no unique constraint, this is legal and the second row survives:

CREATE TABLE student (
  roll_no INT,
  name    VARCHAR(60),
  cgpa    DECIMAL(3,1)      -- NULL allowed by default
);
INSERT INTO student VALUES (102, 'Ben', 7.4);
INSERT INTO student VALUES (102, 'Ben', 7.4);   -- succeeds. Two identical rows.
SELECT COUNT(*) FROM student;                   -- 2, not 1

Why the naive version is wrong: “A relation can’t have duplicates, so my table can’t either” is false. Nothing in SQL enforces set semantics for you — the engine only enforces what you declare. The fix is to add the constraint that makes the table behave like a true relation:

CREATE TABLE student (
  roll_no INT PRIMARY KEY,            -- now the engine rejects duplicate roll_no
  name    VARCHAR(60) NOT NULL,
  cgpa    DECIMAL(3,1)               -- still NULL-able, on purpose
);

The primary key is what closes the gap between “a set of unique tuples” (the model) and “a heap of rows” (an unconstrained table). A relation is a set by definition; a table is a set only by discipline.

Pitfalls

• Assuming rows come back in insert order. SELECT * FROM student with no ORDER BY may return any order, and the order can change after a vacuum, an index rebuild, or a parallel scan. Code that pages or compares results without ORDER BY is relying on a property the model explicitly denies — it works in dev and breaks in prod.
• Treating NULL as a value. WHERE cgpa = NULL returns nothing (it evaluates to unknown, never true); you must write WHERE cgpa IS NULL. Likewise cgpa <> 8.0 silently drops the NULL rows. Aggregates compound this: AVG(cgpa) skips NULLs entirely, so the denominator is the count of non-null rows, not the row count.
• Counting NULL with COUNT. COUNT(*) counts rows; COUNT(cgpa) counts non-null cgpa values. On the four-row instance these give 4 and 3 — a classic off-by-one in reports.
• Forgetting the key, then deduping by hand. A table with no unique constraint silently accumulates duplicate facts; engineers then write SELECT DISTINCT everywhere to paper over it. DISTINCT is a query-time band-aid; the real fix is a constraint at write time, which also lets the planner reason about uniqueness.
• Confusing arity with cardinality. Arity (column count) is a schema property and is stable; cardinality (row count) is an instance property and changes on every write. Saying “the relation grew to degree 5” when you added rows is a vocabulary error that signals a shaky mental model.

Takeaways

• A relation is a set of tuples over a Cartesian product of domains; “set” is the engine behind unique rows, unordered rows, and unordered columns — they are consequences, not rules.
• Schema vs. instance maps onto arity vs. cardinality: arity (columns) is fixed by the schema, cardinality (rows) changes with the data.
• SQL tables are bags, not relations — duplicates and arbitrary order are allowed until a primary key / UNIQUE constraint pulls the table back to true set semantics.
• NULL is a third truth value, not a value: compare it with IS NULL, and remember it quietly drops out of =, <>, ordinary aggregates, and COUNT(col).

Sources: E. F. Codd, “A Relational Model of Data for Large Shared Data Banks,” Communications of the ACM (1970), for the set-theoretic definition of a relation; Silberschatz, Korth & Sudarshan, Database System Concepts (7th ed.), ch. 2, on schema vs. instance, arity, cardinality, and domains; Date, An Introduction to Database Systems, on the relation-vs-table (set vs. bag) distinction; the ISO/IEC 9075 SQL standard and the PostgreSQL documentation for NULL three-valued logic and the COUNT(*) vs COUNT(col) behavior. Re-authored and deepened for this guide: replaced three auto-imported placeholder figures with one hand-authored diagram of the Cartesian-product mechanism, added a traced worked example, the bag-vs-relation pitfall with a runnable fix, and an engineering pitfalls section.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Relations, Tuples, and Attributes

Why this concept exists (judgment chain)

The relational model won because set semantics give identity, uniqueness, and order-independence for free — you do not invent them per app. A relation is a subset of a Cartesian product of domains; SQL tables are bags until PRIMARY KEY/UNIQUE reimpose set rules. Without that mental model, engineers treat heap order as a contract and treat NULL as zero.

Worked example with numbers or traced steps

Schema Student(roll_no PK, name, cgpa NULL); instance 4 rows including Chen with cgpa NULL.
Insert (102,'Ben',7.4) twice without PK → bag cardinality 2; with PK → second insert rejected.
WHERE cgpa < 8.0: Ben only (NULL < 8 is UNKNOWN → Chen dropped).
COUNT(*) = 4; COUNT(cgpa) = 3; AVG(cgpa) skips NULL in the denominator.
Arity = 3 (schema); cardinality = 4 (instance).

When NOT to use / named alternative

Do not force set semantics with SELECT DISTINCT at every read when the fix is a write-time UNIQUE constraint. Do not assume heap scan order when ORDER BY is omitted. Prefer a surrogate/natural key only after naming the uniqueness rule you actually need.

Failure / ops fingerprint

Symptom: intermittent pagination skip/dupe after vacuum/index rebuild because code relied on physical order. Symptom: reports off-by-one because COUNT(col) vs COUNT(*). Ops: alert on tables missing PK in production schemas; fail CI if CREATE TABLE has no uniqueness constraint.

Hostile-panel Q&As (model answers)

Q1. Why does SQL allow duplicate rows if Codd's model forbids them?
Model answer: SQL tables are multisets by design for implementation pragmatism; uniqueness is opt-in via constraints. The model is recovered only when you declare PK/UNIQUE.

Q2. Is NULL a value in the domain?
Model answer: No — it is a marker of unknown/not-applicable producing three-valued logic. Comparisons yield UNKNOWN; use IS NULL / IS NOT NULL.

Q3. Defend PRIMARY KEY vs SELECT DISTINCT for dedupe.
Model answer: PK rejects bad writes once; DISTINCT is a read-time band-aid that costs sorts/hashes and never stops bad data from landing.