> ## Documentation Index
> Fetch the complete documentation index at: https://miliracle.online/llms.txt
> Use this file to discover all available pages before exploring further.

# The Relational Model

> Relations, tuples, keys and constraints: a visual guide to the idea behind every SQL database.

export const QUIZ = [{
  q: "STUDENT has 5 attributes and 4 tuples. What is its degree?",
  a: ["4", "5", "20"],
  c: 1,
  why: "Degree is the number of columns (attributes): 5. The 4 rows (tuples) are the cardinality."
}, {
  q: "{ROLL_NO, NAME} uniquely identifies every student. Is it a candidate key?",
  a: ["Yes, it is unique", "No, it is not minimal", "No, keys must be one attribute"],
  c: 1,
  why: "ROLL_NO alone already picks out every student, so NAME is extra. A candidate key has no extra columns."
}, {
  q: "Why does deleting branch CS fail?",
  a: ["CS is a primary key", "Students still point at CS through a foreign key", "BRANCH_NAME is too long"],
  c: 1,
  why: "RAM and RAMESH are in CS. Deleting it would leave them pointing at nothing, so by default the database says no. ON DELETE CASCADE or SET NULL can change that."
}, {
  q: "Which value can never appear in a primary key?",
  a: ["0", "An empty string", "NULL"],
  c: 2,
  why: "A primary key must always have a value. 0 and an empty string are values. NULL means no value at all. This rule is called entity integrity."
}, {
  q: "What does a projection like π ADDRESS (STUDENT) return?",
  a: ["A relation", "A single value", "A list of row numbers"],
  c: 0,
  why: "Every operation gives back a table (a relation). That is why you can feed one result into the next operation."
}];

export const OPS = [{
  label: "Select σ",
  algebra: "σ AGE = 18 (STUDENT)",
  sql: "SELECT * FROM STUDENT WHERE AGE = 18;",
  note: "Selection keeps the rows that match a condition. Same attributes, fewer tuples.",
  run: () => ({
    cols: S_COLS.slice(0, 5),
    rows: STUDENTS.filter(r => r[4] === 18).map(r => r.slice(0, 5))
  })
}, {
  label: "Project π",
  algebra: "π ADDRESS (STUDENT)",
  sql: "SELECT DISTINCT ADDRESS FROM STUDENT;",
  note: "Projection keeps chosen attributes. DELHI appeared twice but a relation is a set, so it appears once. SQL needs DISTINCT to do the same.",
  run: () => ({
    cols: ["ADDRESS"],
    rows: [...new Set(STUDENTS.map(r => r[2]))].map(x => [x])
  })
}, {
  label: "Join ⋈",
  algebra: "STUDENT ⋈ BRANCH",
  sql: "SELECT * FROM STUDENT JOIN BRANCH USING (BRANCH_CODE);",
  note: "A natural join pairs rows whose BRANCH_CODE matches. CV has no students, so it is not in the result. ADDRESS, PHONE and AGE are hidden here to fit.",
  run: () => ({
    cols: ["ROLL_NO", "NAME", "BRANCH_CODE", "BRANCH_NAME"],
    rows: STUDENTS.map(r => [r[0], r[1], r[5], branchName(r[5])])
  })
}, {
  label: "Chain all three",
  algebra: "π NAME, BRANCH_NAME (σ AGE = 18 (STUDENT ⋈ BRANCH))",
  sql: "SELECT NAME, BRANCH_NAME\nFROM STUDENT JOIN BRANCH USING (BRANCH_CODE)\nWHERE AGE = 18;",
  note: "Because each operation returns a relation, the output of one can feed the next. This is called closure.",
  run: () => ({
    cols: ["NAME", "BRANCH_NAME"],
    rows: STUDENTS.filter(r => r[4] === 18).map(r => [r[1], branchName(r[5])])
  })
}];

export const branchName = code => (BRANCHES.find(b => b[0] === code) || [])[1];

export const SQL_KW = /\b(SELECT|DISTINCT|FROM|WHERE|INSERT|INTO|VALUES|DELETE|UPDATE|SET|JOIN|USING|NULL)\b/;

export const attemptSql = a => {
  if (a.kind === "delete") return `DELETE FROM BRANCH\nWHERE BRANCH_CODE = '${a.code}';`;
  const vals = a.row.map(v => v === null ? "NULL" : typeof v === "number" || String(v).startsWith("'") ? v : `'${v}'`);
  return `INSERT INTO STUDENT VALUES\n  (${vals.join(", ")});`;
};

export const ATTEMPTS = [{
  label: "Insert student with branch ME",
  kind: "insert",
  row: [5, "RAJ", "PUNE", null, 19, "ME"],
  checks: {
    domain: "pass",
    entity: "pass",
    key: "pass",
    ref: "fail"
  },
  bad: {
    cell: 5
  },
  why: "BRANCH has no row with BRANCH_CODE 'ME', so the foreign key would point at nothing."
}, {
  label: "Delete branch CS",
  kind: "delete",
  code: "CS",
  checks: {
    domain: "na",
    entity: "na",
    key: "na",
    ref: "fail"
  },
  bad: {
    students: [0, 1],
    branch: "CS"
  },
  why: "RAM and RAMESH are still in CS. By default the database refuses the delete. In SQL this is called NO ACTION or RESTRICT."
}, {
  label: "Delete branch CV",
  kind: "delete",
  code: "CV",
  checks: {
    domain: "na",
    entity: "na",
    key: "na",
    ref: "pass"
  },
  ok: true,
  why: "No student references CV, so nothing is left dangling. The row is gone."
}, {
  label: "Insert student without ROLL_NO",
  kind: "insert",
  row: [null, "RAJ", "PUNE", null, 19, "CS"],
  checks: {
    domain: "pass",
    entity: "fail",
    key: "na",
    ref: "na"
  },
  bad: {
    cell: 0
  },
  why: "ROLL_NO is the primary key, and a primary key can never be NULL."
}, {
  label: "Insert student with ROLL_NO 3",
  kind: "insert",
  row: [3, "RAJ", "PUNE", null, 19, "CS"],
  checks: {
    domain: "pass",
    entity: "pass",
    key: "fail",
    ref: "na"
  },
  bad: {
    cell: 0,
    students: [2]
  },
  why: "SUJIT already has ROLL_NO 3. Two tuples with the same key could no longer be told apart."
}, {
  label: "Insert student with AGE 'abc'",
  kind: "insert",
  row: [5, "RAJ", "PUNE", null, "'abc'", "CS"],
  checks: {
    domain: "fail",
    entity: "na",
    key: "na",
    ref: "na"
  },
  bad: {
    cell: 4
  },
  why: "AGE's domain is whole numbers; 'abc' is not one."
}, {
  label: "Insert a valid student",
  kind: "insert",
  row: [5, "RAJ", "PUNE", null, 19, "CS"],
  checks: {
    domain: "pass",
    entity: "pass",
    key: "pass",
    ref: "pass"
  },
  ok: true,
  why: "Every rule passes, so the DBMS stores the tuple."
}];

export const CHECKS = [["domain", "Domain", "each value fits its attribute's type"], ["entity", "Entity integrity", "primary key is not NULL"], ["key", "Key", "primary key value is unique"], ["ref", "Referential", "every foreign key points at a real row"]];

export const keyVerdict = (sel, rows, cols) => {
  if (!sel.length) return {
    level: 0,
    dup: [],
    tone: "",
    text: "Pick one or more attributes."
  };
  const id = (r, set) => JSON.stringify(set.map(c => r[c]));
  const isUnique = set => new Set(rows.map(r => id(r, set))).size === rows.length;
  const names = set => set.map(c => cols[c]).join(", ");
  if (!isUnique(sel)) {
    const seen = {};
    rows.forEach((r, i) => seen[id(r, sel)] = [...seen[id(r, sel)] || [], i]);
    const dup = Object.values(seen).filter(g => g.length > 1).flat();
    const ids = dup.map(i => rows[i][0]);
    const list = ids.length > 2 ? `${ids.slice(0, -1).join(", ")} and ${ids[ids.length - 1]}` : ids.join(" and ");
    return {
      level: 0,
      dup,
      tone: "is-bad",
      text: `Not a superkey. Rows ${list} share the same ${names(sel)}, so it cannot tell them apart.`
    };
  }
  const extra = sel.find(c => isUnique(sel.filter(x => x !== c)));
  if (extra !== undefined) return {
    level: 1,
    dup: [],
    tone: "",
    text: `Superkey, but not minimal: drop ${cols[extra]} and the rest still identifies every row. So it is not a candidate key.`
  };
  const nullCol = sel.find(c => rows.some(r => r[c] === null));
  if (nullCol !== undefined) return {
    level: 1,
    dup: [],
    tone: "is-bad",
    text: `Unique here, but ${cols[nullCol]} has a NULL (row 4). A primary key may never be NULL, and two students could share a phone anyway.`
  };
  if (sel.length === 1 && sel[0] === 0) return {
    level: 3,
    dup: [],
    tone: "is-ok",
    text: "Candidate key, and the one chosen as primary key. The school assigns ROLL_NO to be unique and never leaves it empty."
  };
  return {
    level: 2,
    dup: [],
    tone: "",
    text: `Unique and minimal in these 4 rows, but only by luck: nothing stops a second ${rows[0][1]} from enrolling. A key is a rule you declare from what the data means, not a pattern you spot in today's rows.`
  };
};

export const KEY_LEVELS = ["Superkey", "Candidate key", "Primary key"];

export const INSTANCE_OPS = [{
  label: "Insert ROLL_NO 5",
  sql: "INSERT INTO STUDENT VALUES (5, 'RAJ', 'PUNE', NULL, 19);",
  can: rows => !rows.some(r => r[0] === 5),
  apply: rows => [...rows, [5, "RAJ", "PUNE", null, 19]],
  note: "One tuple added."
}, {
  label: "Delete ROLL_NO 2",
  sql: "DELETE FROM STUDENT WHERE ROLL_NO = 2;",
  can: rows => rows.some(r => r[0] === 2),
  apply: rows => rows.filter(r => r[0] !== 2),
  note: "One tuple removed."
}, {
  label: "Update RAM's AGE",
  sql: "UPDATE STUDENT SET AGE = 19 WHERE ROLL_NO = 1;",
  can: rows => rows.some(r => r[0] === 1 && r[4] === 18),
  apply: rows => rows.map(r => r[0] === 1 ? [...r.slice(0, 4), 19] : r),
  note: "One value changed; the count did not."
}];

export const TERMS = [{
  n: "Relation",
  def: "The whole table: a named set of tuples that all have the same attributes.",
  eg: "STUDENT",
  on: () => true
}, {
  n: "Attribute",
  def: "A named column. It describes one property of every entity in the relation.",
  eg: "ADDRESS",
  on: (r, c) => c === 2
}, {
  n: "Tuple",
  def: "One row: one entity, with exactly one value per attribute.",
  eg: "(1, RAM, DELHI, 9455123451, 18)",
  on: r => r === 0
}, {
  n: "Domain",
  def: "The set of values an attribute is allowed to hold. Every value in the column must come from it.",
  eg: "AGE: whole numbers",
  on: (r, c) => c === 4
}, {
  n: "Schema",
  def: "The relation's name and its list of attributes. It is the design, not the data.",
  eg: "STUDENT(ROLL_NO, NAME, ADDRESS, PHONE, AGE)",
  on: r => r === -1
}, {
  n: "Degree",
  def: "How many attributes the relation has. Count the highlighted headings.",
  eg: "5",
  on: r => r === -1
}, {
  n: "Cardinality",
  def: "How many tuples the relation holds right now. Count the highlighted rows.",
  eg: "4",
  on: r => r >= 0
}, {
  n: "NULL",
  def: "A marker meaning the value is missing or unknown. It is not zero and not an empty string.",
  eg: "PHONE of ROLL_NO 4",
  on: (r, c) => r === 3 && c === 3
}];

export const student5 = () => STUDENTS.map(r => r.slice(0, 5));

export const BRANCHES = [["CS", "COMPUTER SCIENCE"], ["IT", "INFORMATION TECHNOLOGY"], ["ECE", "ELECTRONICS AND COMMUNICATION ENGINEERING"], ["CV", "CIVIL ENGINEERING"]];

export const B_COLS = ["BRANCH_CODE", "BRANCH_NAME"];

export const STUDENTS = [[1, "RAM", "DELHI", "9455123451", 18, "CS"], [2, "RAMESH", "GURGAON", "9652431543", 18, "CS"], [3, "SUJIT", "ROHTAK", "9156253131", 20, "ECE"], [4, "SURESH", "DELHI", null, 18, "IT"]];

export const S_COLS = ["ROLL_NO", "NAME", "ADDRESS", "PHONE", "AGE", "BRANCH_CODE"];

export const useOnceVisible = (ref, onEnter) => {
  const cb = useRef(onEnter);
  cb.current = onEnter;
  useEffect(() => {
    const el = ref.current;
    if (!el) return;
    if (typeof IntersectionObserver === "undefined") {
      cb.current();
      return;
    }
    const io = new IntersectionObserver(entries => {
      if (entries.some(e => e.isIntersecting)) {
        io.disconnect();
        cb.current();
      }
    }, {
      threshold: 0.35
    });
    io.observe(el);
    return () => io.disconnect();
  }, [ref]);
};

export const useReducedMotion = () => {
  const [reduce, setReduce] = useState(false);
  useEffect(() => {
    const mq = window.matchMedia("(prefers-reduced-motion: reduce)");
    setReduce(mq.matches);
    const on = e => setReduce(e.matches);
    mq.addEventListener("change", on);
    return () => mq.removeEventListener("change", on);
  }, []);
  return reduce;
};

> This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Source: adapted from GeeksforGeeks, ["Relational Model in DBMS"](https://www.geeksforgeeks.org/dbms/relational-model-in-dbms/), with corrections.

In 1970, an IBM researcher named Edgar F. Codd proposed storing data in plain tables and letting the database, not the programmer, work out how to find things. Over fifty years later, MySQL, PostgreSQL, Oracle and SQL Server all still rest on that idea. If you have written a `SELECT`, you have used it.

The **relational model** organizes data into **relations**, which most people call tables. Each row is one entity, such as a student. Each column is one property of that entity, such as a name or an age. Relations connect to each other through shared values called **keys**, and a set of **constraints** stops bad data from getting in.

The model is a theory, and SQL databases are practical implementations of it. They follow it closely but not perfectly; this post points out where they differ.

<Tip>
  **In short**

  * A relation is a table: a fixed set of attributes (columns) and a changing set of tuples (rows).
  * The schema is the design; the instance is the data at one moment.
  * A key identifies each tuple. Primary keys are candidate keys, and candidate keys are minimal superkeys.
  * Constraints (domain, key, entity and referential integrity) are checked on every write.
  * Every relational operation takes relations and returns a relation, so operations chain.
</Tip>

## Anatomy of a relation

The model has its own vocabulary, but every term names something you can point at in a table. We will use one small `STUDENT` relation throughout.

Relation `STUDENT`:

| ROLL\_NO | NAME | ADDRESS | PHONE | AGE |
| - | - | - | - | - |
| 1 | RAM | DELHI | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9652431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | DELHI | NULL | 18 |

| Term | Meaning | In `STUDENT` |
| - | - | - |
| Relation | A named set of tuples that all have the same attributes | `STUDENT` |
| Attribute | A named column; one property of each entity | `ADDRESS` |
| Tuple | One row: one entity, one value per attribute | `(1, RAM, DELHI, 9455123451, 18)` |
| Domain | The set of values an attribute may hold | `AGE`: whole numbers |
| Schema | The relation's name plus its attributes | `STUDENT(ROLL_NO, NAME, ADDRESS, PHONE, AGE)` |
| Degree | Number of attributes | 5 |
| Cardinality | Number of tuples right now | 4 |
| NULL | Marker for a missing or unknown value; not zero, not empty | `PHONE` of ROLL\_NO 4 |

A few rules follow from calling a table a *relation*, a word borrowed from set theory:

* **Values are atomic.** Each cell holds one value from its domain, not a list or a nested table.
* **No two tuples are identical.** A relation is a set, and a set has no duplicates.
* **Order carries no meaning.** Rows have no first or last, and attributes are found by name, not position.

SQL bends the last two: a table without a key can hold duplicate rows, and `ORDER BY` exists because rows come back in no guaranteed order.

Textbooks write a schema as `R(A1, A2, …, An)`, where `n` is the degree. `STUDENT.NAME` names an attribute of a relation, and `t[NAME]` is that attribute's value in tuple `t`.

## Schema vs. instance

The schema and the data change at very different speeds. The schema is decided when the table is designed and rarely changes after that. The data changes with every insert, update and delete. The set of tuples at one moment is called an **instance** (or *relation state*).

| Write | Schema afterwards | Degree | Cardinality |
| - | - | - | - |
| (start) | `STUDENT(ROLL_NO, NAME, ADDRESS, PHONE, AGE)` | 5 | 4 |
| `INSERT INTO STUDENT VALUES (5, 'RAJ', 'PUNE', NULL, 19);` | unchanged | 5 | 5 |
| `DELETE FROM STUDENT WHERE ROLL_NO = 2;` | unchanged | 5 | 4 |
| `UPDATE STUDENT SET AGE = 19 WHERE ROLL_NO = 1;` | unchanged | 5 | 4 |

Each write produces a new instance. Only a schema change (`ALTER TABLE`) changes the degree.

The difference matters because **rules are written against the schema** and must hold for every instance, including ones that do not exist yet. That idea comes back when we choose keys.

## Keys: telling tuples apart

Since a relation has no duplicate tuples, some set of attributes always separates any two rows. Keys name that set. The terms nest inside each other:

* A **superkey** is any set of attributes whose values are different for every tuple.
* A **candidate key** is a *minimal* superkey: remove any attribute and it stops being unique.
* The **primary key** is the one candidate key the designer picks as the main identifier. It can never be NULL.
* A key made of two or more attributes is called a **composite key**. That describes its size; it is not a separate kind of key.

What the figure reports for some attribute sets of `STUDENT`:

| Attributes | Unique in the 4 rows? | Minimal? | Verdict |
| - | - | - | - |
| `ADDRESS` | No (rows 1 and 4 are both DELHI) | — | Not a superkey |
| `AGE` | No (rows 1, 2 and 4 are 18) | — | Not a superkey |
| `ROLL_NO` | Yes | Yes | Candidate key, chosen as primary key |
| `ROLL_NO, NAME` | Yes | No (`ROLL_NO` alone works) | Superkey, not a candidate key |
| `NAME` | Yes | Yes | Unique only by luck; two students can share a name |
| `PHONE` | Yes | Yes | Contains NULL, so it cannot be the primary key |

The `NAME` row is the trap. In these four rows every name is different, so `NAME` *looks* like a key. But the next student could also be called RAM. Data can prove that a set of attributes is **not** a key; it can never prove that one **is**. Keys are declared from what the data means, which is why the school assigns `ROLL_NO`.

Real tables often have more than one candidate key. An `EMPLOYEE` table might have both an employee number and a national ID. Both are unique, and the designer picks one as primary.

## Constraints: rules on every write

Relations do not live alone. Give each student a `BRANCH_CODE` that refers to a row in a `BRANCH` relation, and you have a **foreign key**: an attribute whose values must match a key in another relation (or the same one). `STUDENT` is the *referencing* relation; `BRANCH` is the *referenced* one.

The DBMS checks every insert, update and delete against four kinds of constraint. If any check fails, the whole write is refused.

| Constraint | Rule |
| - | - |
| Domain | Every value is atomic and comes from its attribute's domain (its type and any `CHECK`). |
| Entity integrity | No part of the primary key is NULL. |
| Key | No two tuples share a primary key value (or any other declared unique key). |
| Referential integrity | Every non-NULL foreign key value matches an existing key in the referenced relation. |

Starting data. `STUDENT`:

| ROLL\_NO | NAME | ADDRESS | PHONE | AGE | BRANCH\_CODE |
| - | - | - | - | - | - |
| 1 | RAM | DELHI | 9455123451 | 18 | CS |
| 2 | RAMESH | GURGAON | 9652431543 | 18 | CS |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 | ECE |
| 4 | SURESH | DELHI | NULL | 18 | IT |

`BRANCH`:

| BRANCH\_CODE | BRANCH\_NAME |
| - | - |
| CS | COMPUTER SCIENCE |
| IT | INFORMATION TECHNOLOGY |
| ECE | ELECTRONICS AND COMMUNICATION ENGINEERING |
| CV | CIVIL ENGINEERING |

Each write below is tried against that starting data:

| Write | Result | Constraint | Why |
| - | - | - | - |
| `INSERT INTO STUDENT VALUES (5, 'RAJ', 'PUNE', NULL, 19, 'ME');` | Rejected | Referential | No BRANCH row has code `ME` |
| `DELETE FROM BRANCH WHERE BRANCH_CODE = 'CS';` | Rejected | Referential | RAM and RAMESH are still in `CS` |
| `DELETE FROM BRANCH WHERE BRANCH_CODE = 'CV';` | Accepted | — | No student references `CV` |
| `INSERT INTO STUDENT VALUES (NULL, 'RAJ', 'PUNE', NULL, 19, 'CS');` | Rejected | Entity integrity | Primary key is NULL |
| `INSERT INTO STUDENT VALUES (3, 'RAJ', 'PUNE', NULL, 19, 'CS');` | Rejected | Key | ROLL\_NO 3 already exists |
| `INSERT INTO STUDENT VALUES (5, 'RAJ', 'PUNE', NULL, 'abc', 'CS');` | Rejected | Domain | `AGE` must be a whole number |
| `INSERT INTO STUDENT VALUES (5, 'RAJ', 'PUNE', NULL, 19, 'CS');` | Accepted | — | Every check passes |

Two details are worth knowing.

**1. Deleting a branch that students still use.** RAM and RAMESH are both in branch `CS`. What happens if you delete `CS` from `BRANCH`?

* By default, the database says no. RAM and RAMESH would point at a branch that no longer exists. In SQL this default is called `NO ACTION` or `RESTRICT`.
* You can pick a different rule when you create the foreign key:
  * `ON DELETE CASCADE`: deleting `CS` also deletes RAM and RAMESH.
  * `ON DELETE SET NULL`: RAM and RAMESH stay, but their `BRANCH_CODE` becomes empty.

Deleting a *student* never breaks this rule. The foreign key goes from `STUDENT` to `BRANCH`, so removing a student leaves nothing pointing at nothing.

**2. A foreign key does not have to point at the primary key.** Say `BRANCH_NAME` is marked `UNIQUE`, so no two branches can share a name. Then `STUDENT` could store `'COMPUTER SCIENCE'` instead of `'CS'` and point at that column. A foreign key can point at any column whose values never repeat. In SQL, that means the primary key or any column marked `UNIQUE`.

The order in which the figure runs the checks is a teaching device. A real DBMS may check them in any order; what matters is that one failure rejects the write.

Some tutorials, including the source of this post, call these rejected writes *insertion and deletion anomalies*. In database design, "anomaly" means something else: a problem caused by storing the same fact in several places, which [normalization](https://en.wikipedia.org/wiki/Database_normalization) fixes. A refused write is the constraint doing its job.

## Operations return relations

Codd defined a small set of operations on relations, called **relational algebra**. SQL is built on them. The three you use daily:

* **Selection (σ)** keeps the tuples that match a condition.
* **Projection (π)** keeps only some attributes.
* **Join (⋈)** combines tuples from two relations whose values match.

There are also set operations: union, intersection and difference. They work on two relations with the same attributes.

```sql theme={null}
-- σ AGE = 18 (STUDENT): rows 1, 2 and 4, all 5 attributes
SELECT * FROM STUDENT WHERE AGE = 18;

-- π ADDRESS (STUDENT): DELHI, GURGAON, ROHTAK (DELHI once: a relation has no duplicates)
SELECT DISTINCT ADDRESS FROM STUDENT;

-- STUDENT ⋈ BRANCH: 4 rows; CV has no students, so it does not appear
SELECT * FROM STUDENT JOIN BRANCH USING (BRANCH_CODE);

-- π NAME, BRANCH_NAME (σ AGE = 18 (STUDENT ⋈ BRANCH))
SELECT NAME, BRANCH_NAME
FROM STUDENT JOIN BRANCH USING (BRANCH_CODE)
WHERE AGE = 18;
```

Result of the last query:

| NAME | BRANCH\_NAME |
| - | - |
| RAM | COMPUTER SCIENCE |
| RAMESH | COMPUTER SCIENCE |
| SURESH | INFORMATION TECHNOLOGY |

The important property is **closure**: every operation takes relations and returns a relation. That is why you can nest a query inside another, join the result of a join, or treat a view as if it were a table.

Projection also shows a difference between theory and SQL. In the model, projecting `ADDRESS` returns DELHI once, because a relation is a set. SQL returns it twice unless you write `DISTINCT`.

## Codd's rules

In 1985, Codd published a list of rules that a system must follow to call itself relational. People call them the "12 rules", but they are numbered 0 to 12, so there are thirteen. A few show the spirit of the list:

| Rule | Name | Meaning |
| - | - | - |
| 0 | Foundation | The system must manage its data entirely through relational means. |
| 1 | Information | All data, including the catalog of tables, is stored as values in tables. |
| 2 | Guaranteed access | Every value can be reached by table name, primary key value and column name. |
| 3 | Systematic NULLs | Missing data is represented the same way everywhere, separately from any real value. |
| 5 | Comprehensive data sublanguage | One language covers defining data, querying it, changing it and setting constraints. |
| 8–9 | Physical and logical data independence | Applications keep working when storage or table structure changes. |

No mainstream product meets every rule completely. The list is better read as a description of the goal than as a pass/fail test.

## Strengths and limits

| Strengths | Limits |
| - | - |
| Simple model: everything is a table | Fixed schemas need migrations when the shape of the data changes |
| Declarative queries: say *what*, the DBMS decides *how* | Deep hierarchies and graph-like data take many joins; graph or document models can fit better |
| The DBMS enforces integrity, not each application | Highly normalized schemas need joins, which cost time without good indexes |
| Data independence: storage can change under the applications | Spreading joins and transactions across many machines is hard (distributed SQL systems exist, at a cost) |

Most relational systems also offer ACID transactions. That comes from the DBMS implementation rather than from the model itself, but the two are rarely seen apart.

## Check yourself

Questions this document answers:

* What is the difference between degree and cardinality? `STUDENT` has 5 columns and 4 rows. Degree is the number of columns (5). Cardinality is the number of rows (4).
* What is the difference between a schema and an instance? The schema is the table's name and column list, such as `STUDENT(ROLL_NO, NAME, ADDRESS, PHONE, AGE)`. The instance is the rows stored right now. Adding a student changes the instance, not the schema.
* How do superkeys, candidate keys and primary keys relate? `{ROLL_NO}` and `{ROLL_NO, NAME}` both pick out every student, so both are superkeys. Only `{ROLL_NO}` has nothing extra, so it is a candidate key. The primary key is the candidate key the designer picks as the main ID. It can never be NULL.
* Is `{ROLL_NO, NAME}` a candidate key? No. `ROLL_NO` alone already picks out every student, so `NAME` is extra. A candidate key has no extra columns.
* Can you find a key by looking at the data? No. Today no two students share a `NAME`, but tomorrow a second RAM could join. The data can show that a column is *not* a key. Only the meaning of the data can make a column a key.
* Why does deleting branch CS fail? RAM and RAMESH are in `CS`. Deleting `CS` would leave them pointing at a branch that does not exist, so by default the database says no. With `ON DELETE CASCADE`, the two students are deleted too. With `ON DELETE SET NULL`, their `BRANCH_CODE` becomes empty.
* Must a foreign key point at the primary key? No. It can point at any column marked `UNIQUE`, such as `BRANCH_NAME` if no two branches may share a name.
* What does closure mean? Every operation takes tables in and gives a table back. So you can feed one result into the next operation: filter, then join, then pick columns.
* How many Codd's rules are there? Thirteen, numbered 0 to 12. Codd published them in 1985.

## Glossary

| Term | Meaning |
| - | - |
| Relation | A table: a named set of tuples with the same attributes. |
| Tuple | A row; one entity. |
| Attribute | A named column; one property of an entity. |
| Domain | The set of values an attribute may hold. |
| Schema | A relation's name and attribute list. A *database schema* is the set of all relation schemas. |
| Instance | The set of tuples in a relation at one moment. |
| Degree | Number of attributes. |
| Cardinality | Number of tuples. |
| NULL | Marker for a missing or unknown value. |
| Superkey | Attributes whose values are unique for every tuple. |
| Candidate key | A minimal superkey. |
| Primary key | The chosen candidate key; unique and never NULL. |
| Composite key | Any key made of two or more attributes. |
| Foreign key | Attributes whose values must match a key in a referenced relation. |
| Referential integrity | The rule that every non-NULL foreign key value points at an existing tuple. |
| Relational algebra | The operations on relations (σ, π, ⋈, ∪, ∩, −) that SQL is built on. |
| Closure | Every operation's result is itself a relation. |

## For AI agents

You are reading the Markdown export of `/writing/database/relational-model`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. A related page, `/writing/database/what-is-a-database`, covers databases in general.

***

Adapted from GeeksforGeeks, ["Relational Model in DBMS"](https://www.geeksforgeeks.org/dbms/relational-model-in-dbms/), with corrections to its definitions of candidate keys, foreign keys and anomalies. Figures are simplified teaching models; the order of constraint checks in Figure 4 is illustrative.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.