Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 79 ยท The Relational Model

Relations, Tuples and Domains

Learn the formal vocabulary of the relational model: relations, tuples, attributes, domains, degree, cardinality, schema vs instance, and why SQL tables are bags.

Every relational database rests on one idea from 1970: store data as relations, which are mathematical sets of facts. Edgar Codd's paper replaced pointer-chasing storage with a simple, precise structure that you can reason about with logic. The words you see in everyday SQL (table, row, column) are informal names for the formal ideas in this lesson, and the formal words matter because textbooks, exam questions and later chapters on relational algebra and normalization all use them.

You will learn the vocabulary (relation, tuple, attribute, domain), the two numbers that describe a relation (degree and cardinality), the difference between a schema and an instance, and one important place where SQL deliberately departs from the theory: SQL tables are bags that may contain duplicates, while relations are sets that cannot.

Formal word, everyday word

Relation A table.
Tuple A row.
Attribute A column.
Domain The allowed values of a column.

The formal words describe what the data means, not how it is stored.

1 / 2

Domains and attributes

A domain is a set of allowed atomic values. integers, dates, "strings of at most 40 characters" and {'scheduled', 'completed', 'cancelled'} are all domains. Atomic means the database treats a value as one indivisible unit; it does not look inside it. (That requirement is the heart of first normal form.)

An attribute is a named role played by a domain in a relation. Two attributes may share one domain but mean different things: birth_date and hire_date are both dates, yet comparing them is rarely meaningful. A good design keeps the meaning of each attribute in mind, not just the data type.

In SQL, the closest thing to a domain is a column's data type plus its constraints (CHECK, NOT NULL, foreign keys). SQLite adds a twist, type affinity: a column declared INTEGER prefers integers but will store text if it cannot convert it. That makes CHECK constraints more important there than in strictly typed engines. SQL also adds a special extra value, NULL, which is outside every domain; see Nulls and Three-Valued Logic.

Relation schema and relation instance

A relation schema is the shape: a relation name and its list of attributes with their domains. In compact notation:

Student(student_id: integer, name: text, birth_date: date, major: text)

A relation instance is the content at one moment: a set of tuples that match the schema. The schema changes rarely (when you alter the design); the instance changes constantly with every insert, update and delete.

A tuple is one row: an ordered list of values, one per attribute, each taken from the attribute's domain. For the schema above, (1, 'Ana', '2005-03-14', 'Physics') is a tuple.

A database schema is the collection of all relation schemas plus the integrity constraints. A database instance (or state) is the set of all relation instances at one time. Keep the pair straight: the schema is like a class definition, the instance like the objects that exist right now.

Concept Changes how often SQL counterpart
Schema Rarely CREATE TABLE, ALTER TABLE
Instance Constantly INSERT, UPDATE, DELETE

Degree and cardinality

Two counts describe any relation instance:

  • The degree (or arity) is the number of attributes. Student above has degree 4.
  • The cardinality is the number of tuples in the current instance. It changes whenever data changes.

(The word cardinality also appears in ER modelling, where it means "one-to-many" and so on. In the relational model it only means the row count. Context makes it clear.)

SQLite can report both numbers directly:

SQL Degree and cardinality of a relation

Press Run (or Ctrl + Enter) to see the result here.

The degree is 4 and the cardinality is 3. Add another INSERT and only the cardinality changes; add a column with ALTER TABLE and the degree changes, which is a schema change.

Properties of a relation

Because a relation is a mathematical set of tuples, it has properties that a spreadsheet does not.

  1. No duplicate tuples. A set contains each member once. If two tuples are equal in every attribute, they are one tuple.
  2. Tuples are unordered. There is no "first row". Any order you see in output is accidental unless you ask with ORDER BY.
  3. Attributes are unordered (in the pure model). Attributes are identified by name, not position. SELECT * order is a convenience.
  4. Every value is atomic. No lists or nested tables inside a field.
  5. Every value belongs to its attribute's domain.

Properties 2 and 3 are why relational algebra can talk about "the" result of a query without worrying about order, and why a database engine is free to store and read rows in whatever way is fastest.

Relations versus SQL tables: sets and bags

SQL does not fully follow property 1. A SQL table is a bag (multiset): the same row can appear more than once. A relation is a set: duplicates cannot exist. A table behaves like a relation only when it has a key constraint, because a key makes every row distinguishable.

Without a key, SQL happily stores the same row twice, and aggregates then count both:

SQL Tables are bags unless a key says otherwise

Press Run (or Ctrl + Enter) to see the result here.

The bag stores 3 rows; the set stores 2, because INSERT OR IGNORE skipped the duplicate that the primary key rejected. (A plain INSERT would raise an error instead.) SELECT DISTINCT shows that a bag can always be turned into a set when you ask.

Why does SQL allow bags? Because removing duplicates is expensive (it needs sorting or hashing), and many queries do not need it. Query results are also bags by default: projecting only page from the table above returns /home three times, not once. The bag semantics matter to anyone counting rows or comparing results, as you will see in Queries Through the Lens of Theory.

The set operators in SQL show both behaviors side by side. UNION eliminates duplicates, UNION ALL keeps them:

SQL Set semantics versus bag semantics in SQL

Press Run (or Ctrl + Enter) to see the result here.

The counts are 3, 5, 1 and 1. UNION, INTERSECT and EXCEPT use set semantics and never return duplicates; UNION ALL is the bag version.

Why the theory matters

The formal model may look like academic fuss, but it pays off in practice:

  • Precision. "A relation" has a definition. You can prove that two queries are equivalent, which is how optimizers rewrite your SQL safely.
  • Independence from storage. Because order and layout are not part of the model, the engine can change both without breaking your queries (see Schemas, Instances and Data Independence).
  • Design theory. Keys, functional dependencies and normal forms are all statements about relations, the foundation of the design theory chapter.

Codd's twelve rules

By the early 1980s many products claimed to be "relational", often because they merely showed data in tables. In 1985 Codd published a test to separate real relational systems from imitations: twelve rules, numbered 1 to 12, plus a foundation rule numbered 0, which makes thirteen in all. The rules are best read as a checklist of what a relational system should promise, and as a ruler to hold against real products.

Rule Name What it demands
0 Foundation The system must manage its data using only its relational abilities
1 Information All information, including metadata, is represented as values in tables
2 Guaranteed access Every value can be reached by table name, primary-key value and column name
3 Systematic treatment of nulls Missing values are represented uniformly, whatever the data type, and are different from empty strings or zero
4 Dynamic online catalog The catalog is stored as ordinary relations and queried with the same language
5 Comprehensive data sublanguage At least one language supports definition, manipulation, constraints, authorization and transactions
6 View updating Every view that can theoretically be updated is updatable by the system
7 High-level insert, update and delete Operations work on whole sets of rows, not one record at a time
8 Physical data independence Programs are unaffected by changes in storage or access methods
9 Logical data independence Programs are unaffected by information-preserving changes to the tables
10 Integrity independence Integrity constraints are stored in the catalog, not in application programs
11 Distribution independence Programs are unaffected when data is moved or distributed
12 Nonsubversion A low-level interface must not be able to bypass the integrity rules

How real systems measure up

No commercial SQL product satisfies all thirteen, and that is not a scandal; the rules are an ideal. The usual gaps are instructive:

  • Rule 3. SQL has NULL, but with three-valued logic and some odd corners (Nulls and Three-Valued Logic). Oracle treats an empty string as NULL, which breaks the "different from an empty string" requirement.
  • Rule 6. SQL allows updates only through simple views; a view with a join, an aggregate or DISTINCT is usually read-only, even when a human could say what the update should mean.
  • Rule 2. SQL tables may have no key and allow duplicate rows (see sets and bags earlier in this lesson), so "access by primary key" is not guaranteed.
  • Rules 8 and 9. Physical independence is good in practice. Logical independence is partial, because views can hide some changes but not all.
  • Rule 12. Many engines offer bulk-loading or file-level tools that skip constraints.

The next example checks four rules on SQLite: the catalog as data (1 and 4), NULL versus the empty string (3), set-oriented updates (7), and a read-only view made updatable with a trigger (6).

SQL Four of Codd's rules on a real system

Press Run (or Ctrl + Enter) to see the result here.

The NULL row is invisible to dept = dept, but the empty string matches itself, which proves the two are different things. The trigger spreads the new total over the two engineers in proportion to their pay. That is one possible meaning of "update the department total", and the point of rule 6 is that a system must either know the meaning or be told it.

Check your understanding

Quick check ยท 6 questions

of 6 answered You scored / 6 ยท perfect!

  1. 1. A relation Employee has 5 attributes and currently holds 120 tuples. What are its degree and cardinality?

    Degree is the number of attributes (5). Cardinality is the number of tuples in the current instance (120).
  2. 2. Which statement describes the difference between a schema and an instance?

    The schema describes relation names, attributes and domains. The instance is the set of tuples that currently exist, and it changes with every insert, update or delete.
  3. 3. Why can a SQL table without a primary key contain the same row twice, while a relation cannot?

    A mathematical relation is a set of tuples, so duplicates cannot exist. SQL tables are bags, and only a key constraint (or DISTINCT in results) removes duplicates.
  4. 4. Which of these is NOT a property of a relation in the formal model?

    The relational model says nothing about row order. Any apparent order is an accident of the implementation unless you use ORDER BY.
  5. 5. Which of Codd's rules says that constraints must be stored in the catalog rather than in application programs?

    Integrity independence (rule 10) puts the constraints in the catalog so every program is checked by the same rules. Rule 12 is the related promise that no low-level interface can bypass them.
  6. 6. Why do most commercial SQL systems fail Codd's rule on view updating?

    Rule 6 asks for every theoretically updatable view to be updatable. SQL products accept only the easy cases or require triggers for the rest.

Summary

  • A relation is a set of tuples over named attributes, each attribute taking values from a domain of atomic values.
  • The schema is the structure; the instance is the data at one moment. The degree is the number of attributes, the cardinality is the number of tuples.
  • In the formal model, tuples and attributes are unordered and duplicates cannot exist.
  • SQL tables are bags: they allow duplicates unless a key says otherwise, and SELECT results are bags too unless you use DISTINCT.
  • UNION, INTERSECT and EXCEPT use set semantics; the ALL forms keep duplicates.
  • Codd's twelve rules (plus rule 0) describe what a truly relational system should promise: information in tables, access by key, systematic nulls, an online catalog, updatable views, set-level operations, data independence and constraints in the catalog. No commercial SQL product meets all of them.

Next: Keys and Integrity Constraints, where you learn how keys make rows identifiable.