The Relational Model: Relations, Tuples, Attributes, and Domains

Learning outcomes

The relational model is the conceptual foundation beneath SQL databases. SQL products add implementation details, but the model gives precise language for data structure, identity, integrity, and transformation.

From predicates to relations

A relation can be understood as a set of tuples that satisfy a shared meaning. Consider the predicate:

The relation contains the tuples for which that statement is true in the current database state. The schema defines the attributes and their domains; the instance is the set of tuples currently stored.

Relation schema and relation instance

A relation schema is commonly written as:

R is the relation name. Each attribute A_i draws values from domain D_i. For example:

The schema changes relatively rarely. The instance changes whenever tuples are inserted, updated, or deleted.

This instance has degree 4 because it has four attributes and cardinality 3 because it contains three tuples.

Domains carry meaning, not only storage size

A domain is more than a machine type. customer_id and product_id may both be integers, but they are not interchangeable. A timestamp in UTC and a local calendar date may both be represented as text, but they express different semantics.

A robust domain specifies:

• the conceptual meaning and unit;
• valid values or range;
• whether missing information is allowed;
• canonical encoding and normalization;
• comparison and ordering rules;
• ownership and change policy.

SQL data types approximate domains. CHECK, NOT NULL, UNIQUE, foreign keys, reference tables, and application rules refine them. Later chapters treat these mechanisms in detail.

Keys identify tuples

A superkey is any set of attributes whose values uniquely identify a tuple. A candidate key is a minimal superkey: remove any attribute and uniqueness is lost. One candidate key is selected as the primary key; other candidate keys remain alternate keys.

Properties of the classical relational model

1. Tuples are unique. A relation is a set, so duplicate tuples do not exist.
2. Tuple order is irrelevant. Storage or display order has no logical meaning.
3. Attribute order is not part of the meaning. Attributes are addressed by name.
4. Each tuple follows the same heading. Every tuple supplies one value for each attribute.
5. Values come from domains. A value must be valid for the corresponding attribute.

SQL differs in important ways. SQL query results commonly use bag or multiset semantics and may contain duplicate rows unless DISTINCT or a uniqueness rule removes them. SQL tables can contain null markers. Displayed columns have an order. Vendor types and nested values extend the classical model.

These differences do not make SQL “non-relational.” They mean SQL is a practical language inspired by and extending relational theory.

Relational operations

Relational operators are closed: applying an operator to relations produces another relation. This composability is why complex queries can be built from smaller expressions and why a DBMS can transform an expression into an equivalent but cheaper execution plan.

Selection and projection are different

Selection filters rows; projection chooses columns. For employee relation E:

keeps high-salary tuples, while:

keeps only two attributes. SQL places both operations in one statement:

SELECT employee_id, full_nameFROM employeesWHERE salary >= 70000; 

SQL’s written clause order is not the same as its logical processing order or physical execution plan. The DBMS is free to use an equivalent plan as long as it preserves the statement’s semantics.

Joins reconstruct relationships

Decomposing facts into multiple relations avoids repeating department details in every employee tuple. A join reconstructs the desired view. The join result’s cardinality depends on key constraints and the data: one-to-one, many-to-one, one-to-many, or accidental many-to-many matches.

Lab: create and query two relations

CREATE TABLE departments (  department_id INTEGER PRIMARY KEY,  department_name TEXT NOT NULL UNIQUE); CREATE TABLE employees (  employee_id INTEGER PRIMARY KEY,  full_name TEXT NOT NULL,  department_id INTEGER NOT NULL,  salary NUMERIC NOT NULL CHECK (salary >= 0),  FOREIGN KEY (department_id)    REFERENCES departments (department_id)); -- Selection: choose tuples satisfying a predicate.SELECT *FROM employeesWHERE salary >= 70000; -- Projection: choose attributes.SELECT employee_id, full_nameFROM employees; -- Join: combine related tuples.SELECT e.full_name, d.department_nameFROM employees AS eJOIN departments AS d  ON d.department_id = e.department_id; 

The foreign key states that every employee’s department identifier must correspond to a department tuple. Enable foreign-key enforcement in SQLite with PRAGMA foreign_keys = ON; for each connection used in the lab.

NULL and the model

The classical relational model assumes values come from domains. SQL introduces NULL as a marker for missing or inapplicable information and uses three-valued logic: predicates can be true, false, or unknown. This affects comparisons, constraints, joins, and aggregates.

Do not treat null as zero, an empty string, or a value that equals another null. Chapter 2 develops null semantics carefully. For now, recognize it as one of the largest practical differences between clean relational theory and SQL behavior.

Common mistakes

“A relation is simply any spreadsheet.”

A relation has a defined heading, domains, tuple semantics, and integrity expectations. A spreadsheet grid may mix formulas, headings, units, and unrelated regions.

“Rows have a natural order.”

They do not. Without ORDER BY, SQL does not promise a stable presentation order.

“Primary key means the only unique field.”

A relation may have several candidate keys. Selecting one primary key does not remove the need to enforce other business keys.

“Joining tables creates duplicate data in storage.”

A query result combines attributes logically. It does not necessarily persist the result unless you explicitly create a table or materialized object.

Checkpoint and practice

Summary and next lesson

A relation is a set of tuples defined by attributes and domains. A schema describes the structure; an instance records the current facts. Keys identify tuples, foreign keys connect relations, and closed relational operations transform relations into new relations. The final lesson of Chapter 1 builds a safe, reproducible SQL practice environment for all later labs.

References

• E. F. Codd, “A Relational Model of Data for Large Shared Data Banks,” Communications of the ACM, 1970.
• C. J. Date, An Introduction to Database Systems.
• PostgreSQL Tutorial: Relational Database Concepts.
• PostgreSQL Data Definition: Constraints.
• SQLite Foreign Key Support.