Chapter 13 of 41

Relational Model

In the previous chapter we converted an ER diagram into tables. But what exactly is a table, from the database's point of view? Why can't a cell hold a list? Why does the order of rows not matter? Why do databases insist that every row be identifiable?

The answers come from the relational model — the mathematical foundation on which MySQL, PostgreSQL, Oracle, SQL Server and SQLite are all built. It was proposed by E. F. Codd at IBM in his 1970 paper "A Relational Model of Data for Large Shared Data Banks". Its central idea is beautifully simple:

All data is represented as relations (tables), and all queries are operations on relations that produce new relations.

In Data Models you met the relational model as one of several data models. In this chapter we look at it closely: its vocabulary, its formal definition, the properties every relation must have, NULL values, and where real SQL bends the rules.


Basic Terminology

Let's use the departments table from the sample company_db database used in our SQL tutorial:

SELECT * FROM departments;
+---------+-------------+-----------+
| dept_id | dept_name   | location  |
+---------+-------------+-----------+
|      10 | Engineering | Bengaluru |
|      20 | Sales       | Mumbai    |
|      30 | HR          | Delhi     |
|      40 | Marketing   | Pune      |
|      50 | Research    | Hyderabad |
+---------+-------------+-----------+

Term

Meaning

In the example

Relation

A table: a set of tuples with the same attributes

departments

Tuple

A row: one record / one fact

(20, Sales, Mumbai)

Attribute

A named column

dept_name

Domain

The set of allowed (atomic) values for an attribute

location: names of Indian cities

Degree (arity)

Number of attributes

3

Cardinality

Number of tuples (at this moment)

5

Relation schema

The relation's name + its attributes (+ domains)

DEPARTMENTS(dept_id, dept_name, location)

Relation instance (state)

The set of tuples stored at a given moment

The 5 rows above

Notice the difference between degree and cardinality: degree is fixed by the schema (it changes only if you ALTER the table); cardinality changes every time you insert or delete a row. A unary relation has degree 1, a binary relation degree 2, and so on.

Watch out: "cardinality of a relation" (number of rows) is a different idea from "cardinality of a relationship" (1:1, 1:N, M:N) in the ER model. Same word, different meaning.

The same thing has three sets of names depending on who is talking:

Formal (relational model)

SQL / practical

Old file systems

Relation

Table

File

Tuple

Row

Record

Attribute

Column

Field

Degree

Number of columns

Number of fields

Cardinality

Number of rows

Number of records


Schema vs Instance

This idea from Three-Schema Architecture & Data Independence appears again at the level of a single relation:

  • The relation schema is the structure, written R(A1, A2, ..., An). For example DEPARTMENTS(dept_id, dept_name, location). It changes rarely.

  • The relation instance r(R) is the content at one moment. It changes constantly.

A relational database schema is the set of all relation schemas plus the integrity constraints. For company_db:

DEPARTMENTS (dept_id, dept_name, location)
EMPLOYEES   (emp_id, name, email, dept_id, manager_id, salary, hire_date, city)
CUSTOMERS   (customer_id, name, city, signup_date)
ORDERS      (order_id, customer_id, order_date, amount, status)

(By convention, the primary key is underlined in textbooks; in plain text it is usually listed first.) A relational database instance is the set of all relation instances at a moment.


The Formal Definition

Mathematically, a relation is a set, and that one word explains most of its properties.

Given domains D1, D2, ..., Dn, a relation r is a subset of the Cartesian product D1 × D2 × ... × Dn. Each element of r is an n-tuple (v1, v2, ..., vn) with each vi taken from Di.

A small example. Suppose

dom(course) = { DBMS, OS, CN }
dom(grade)  = { A, B }

dom(course) x dom(grade) = { (DBMS,A), (DBMS,B), (OS,A), (OS,B), (CN,A), (CN,B) }

The Cartesian product has 3 × 2 = 6 tuples. A relation RESULT(course, grade) is any subset of these, for example { (DBMS,A), (OS,B) }. So:

  • The maximum cardinality of the relation is |D1| × |D2| × ... × |Dn| = 6.

  • The number of different possible instances is the number of subsets: 26 = 64 (including the empty relation).

In practice we think of a tuple as a mapping from attribute names to values (dept_id → 20, dept_name → Sales, location → Mumbai) rather than a position-based list. That small change is why column order doesn't matter, as we'll see next.


Properties of a Relation

Because a relation is a set of tuples, and attributes are named, the following properties hold.

1. No two tuples are identical

A set can't contain the same element twice. So every relation has at least one combination of attributes (in the worst case, all of them) that uniquely identifies each tuple. This is the reason keys exist — the topic of the next chapter.

2. The order of tuples doesn't matter

Sets have no order. {(10, Engineering), (20, Sales)} and {(20, Sales), (10, Engineering)} are the same relation. This is why SQL gives no guarantee about row order unless you write ORDER BY.

3. The order of attributes doesn't matter

Since each value is tied to an attribute name, (dept_id=20, dept_name=Sales) and (dept_name=Sales, dept_id=20) are the same tuple.

4. Every value is atomic

Each cell holds a single, indivisible value from the attribute's domain — no lists, no sets, no nested tables. A student's three phone numbers cannot sit in one cell, which is why the previous chapter moved them to a separate table. This requirement is also the definition of First Normal Form, which you'll study in 1NF, 2NF & 3NF.

"Atomic" depends on how the database uses the value. A date like 2024-01-15 is treated as one value even though it has parts, because the DBMS treats it as one value of the DATE domain.

5. Attribute names are unique within a relation

A relation can't have two columns both called name. (Different relations can reuse names; employees.name and customers.name are distinct.)

6. Values come from the attribute's domain

Every value of salary must be a valid salary. This is the domain constraint, covered with the other rules in Integrity Constraints.


NULL Values

Sometimes a value is not available. The relational model uses a special marker, NULL, which can mean different things:

  • Unknown: the value exists but we don't know it. In company_db, Meera Nair (emp_id 8) has a NULL email — we just don't have it.

  • Not applicable / doesn't exist: Aarav Sharma (emp_id 1) has a NULL manager_id because he is at the top — there is no manager.

  • Not yet assigned: Ishita Gupta (emp_id 10) has a NULL dept_id — she hasn't been placed in a department yet.

Key facts about NULL:

  • NULL is not zero and not an empty string. It means "no value".

  • Any comparison with NULL (= NULL, <> NULL, > NULL) gives UNKNOWN, not TRUE or FALSE. That's why SQL uses IS NULL instead of = NULL. The SQL tutorial covers this three-valued logic in detail.

  • Some attributes must never be NULL — the primary key in particular (entity integrity), as you'll see in Integrity Constraints.


Where SQL Differs from the Pure Relational Model

SQL is based on the relational model but is not a perfect implementation of it. These differences regularly surprise students.

SQL tables are bags (multisets), not sets

If a table has no primary key or UNIQUE constraint, SQL happily stores duplicate rows:

CREATE TABLE visit_log (city VARCHAR(30), visit_date DATE);
INSERT INTO visit_log VALUES ('Pune','2026-10-01'), ('Pune','2026-10-01'), ('Delhi','2026-10-02');
SELECT * FROM visit_log;
+-------+------------+
| city  | visit_date |
+-------+------------+
| Pune  | 2026-10-01 |
| Pune  | 2026-10-01 |
| Delhi | 2026-10-02 |
+-------+------------+

Two identical rows — impossible in a true relation. Query results can also contain duplicates; you must ask for set behaviour explicitly with DISTINCT:

SELECT DISTINCT * FROM visit_log;
+-------+------------+
| city  | visit_date |
+-------+------------+
| Pune  | 2026-10-01 |
| Delhi | 2026-10-02 |
+-------+------------+

Why did SQL choose bags? Removing duplicates requires sorting or hashing, which costs time; and sometimes duplicates are meaningful (two identical visits really happened). Good practice: give every table a primary key, so base tables behave like true relations.

Columns have an order in SQL

SELECT * returns columns in the order they were defined, and INSERT INTO departments VALUES (60, 'Finance', 'Chennai') matches values by position. In the pure model, only names matter. (Safer SQL lists column names explicitly.)

Rows still have no guaranteed order

This part SQL does follow: without ORDER BY, the order of rows in a result is not guaranteed, even if it happens to look sorted today.


Why the Relational Model Won

  • Simplicity: everything is a table. Users don't need to know about pointers, record layouts or access paths (compare the hierarchical and network models in Data Models).

  • Data independence: queries say what data is wanted, not how to find it, so storage can change without rewriting programs.

  • Solid mathematical basis: set theory and predicate logic give precise meaning to queries. This is what Relational Algebra and Relational Calculus (later in this tutorial) formalise.

  • Closure: every operation on relations produces a relation, so operations can be nested freely — the reason SQL subqueries work.

  • Declarative language: SQL, built on the model, became the standard.

Codd later published a list of rules (usually called Codd's 12 rules, numbered 0 to 12, so actually 13) describing what a fully relational DBMS should provide — for example, all information represented as values in tables (the information rule), guaranteed access by table name + primary key + column name, systematic handling of NULLs, and a catalog that is itself stored as tables. No commercial DBMS satisfies all of them; they are a yardstick rather than a checklist.


Common Mistakes

  • Mixing up degree (columns) and cardinality (rows).

  • Thinking rows are stored or returned "in insertion order". There is no order without ORDER BY.

  • Treating NULL like 0 or '', or testing it with = NULL.

  • Saying "a relation can have duplicate rows". A relation can't; an SQL table without a key can.

  • Storing comma-separated lists in a column ("DBMS,OS,CN"). That breaks atomicity and makes searching and constraints painful.


Interview and Exam Questions

Define relation, tuple, attribute and domain.

A relation is a table — a set of tuples over the same attributes. A tuple is one row. An attribute is a named column. A domain is the set of atomic values an attribute may take.

What are the degree and cardinality of company_db's employees table?

Degree = 8 (emp_id, name, email, dept_id, manager_id, salary, hire_date, city). Cardinality = 10 (the number of rows currently stored).

Why can't a relation contain duplicate tuples?

Because a relation is defined as a set (a subset of the Cartesian product of the domains), and a set cannot contain the same element twice.

If A has 4 possible values and B has 5, how many tuples can R(A, B) contain at most? How many different instances of R are possible?

At most 4 × 5 = 20 tuples. The number of possible instances is 220 = 1,048,576, because each instance is a subset of those 20 tuples.

What is the difference between a relation schema and a relation instance?

The schema is the structure (name, attributes, domains) and changes rarely. The instance is the set of tuples present at one moment and changes with every insert, update or delete.

Does SQL follow the relational model exactly?

No. SQL tables are multisets (duplicates are allowed without a key), columns have a positional order, and NULL introduces three-valued logic. Rows, however, are still unordered without ORDER BY.


Summary

  • The relational model (Codd, 1970) represents all data as relations: sets of tuples over named attributes, with values from domains.

  • Degree = number of attributes; cardinality = number of tuples.

  • The schema is the structure; the instance is the data at a moment.

  • A relation has no duplicate tuples, no tuple order, no attribute order, atomic values, unique attribute names, and values from the domain.

  • NULL marks a missing/unknown/inapplicable value; comparisons with it are UNKNOWN.

  • SQL tables are bags, not sets — so always define a primary key.

Property 1 says every relation can identify its tuples uniquely. The next chapter, Keys in DBMS, explains exactly how: super keys, candidate keys, primary keys and foreign keys.