In lesson 01-01 we used words like table, row, column, primary key and foreign key in a deliberately informal way: they were enough to diagnose BiblioRed's spreadsheet without having to define anything rigorously. Now it is time to do it properly. The relational model is not "a tidy way of organizing tables": it is a mathematical theory, published by Edgar F. Codd in 1970, and that formal foundation is exactly why relational systems have gone half a century without being displaced.

Understanding the model has a very practical consequence. When, two lessons from now, you write a JOIN and it does not return what you expected, or when a comparison against NULL filters out every row, the explanation will not be in the PostgreSQL manual: it will be in the theory we cover today. This is the last lesson without typing (almost: there is illustrative SQL, but you will not run it yet). By the end you will have the complete blueprint of the BiblioRed schema, which in the next lesson we will turn into real tables inside biblioredb.

1. Relation, tuple, attribute and domain
2. Degree and cardinality
3. Why a relation is a set
4. Relation versus spreadsheet
5. The key family: superkey, candidate, primary, alternate
6. Foreign keys and the link between relations
7. Natural key versus surrogate key: the ISBN case
8. The model's three integrity rules
9. The NULL value and three-valued logic
10. Introduction to relational algebra
11. The conceptual schema of BiblioRed
12. Common mistakes and tips
13. Exercises
14. Conclusion

The relational model is built on four concepts. We present each with its formal name and its everyday name, because in daily work you will hear both.

Beware of a very widespread confusion: "relation" does not mean "link between tables". In the relational model, a relation is a table. The link between tables is called a foreign key (or, in design terms, a relationship). The model is called "relational" not because tables relate to one another, but because each table is, mathematically, a relation in the set-theory sense: a subset of the Cartesian product of its domains.

Let's look at it with BiblioRed's members relation:

• The relation is members.
• Each of the three lines is a tuple.
• first_name, email, join_date… are attributes.
• The domain of join_date is "valid calendar dates"; that of member_id, "positive integers"; that of email, "strings of up to 120 characters". The domain is what stops "last Tuesday" from showing up in join_date.

In 01-01 we distinguished schema (the structure) from instance (the data at a given moment). Formally:

• The relation schema is written members(member_id, first_name, last_name, email, join_date, branch_id). It is stable: it changes only when the designer changes it.
• The instance or extension is the concrete set of tuples present right now. It changes with every loan, every sign-up and every cancellation.

When we talk about "the members table" we almost always mean the schema; when we say "there are 12,000 members", we mean the instance.

Two elementary measurements of a relation:

• Degree (or arity): the number of attributes. The members relation in the example has degree 6. Degree is a property of the schema.
• Cardinality: the number of tuples. In the example, 3; in the real BiblioRed, 12,000. Cardinality is a property of the instance.

A handy mnemonic: degree is counted across and hardly ever changes; cardinality is counted down and changes all the time.

Edge cases worth having clear:

• A relation with cardinality 0 (no tuples) is perfectly valid: it is exactly what we have right now in biblioredb, where the tables do not even exist yet, and what we will have after creating them in the next lesson.
• A relation with degree 0 is a mathematical curiosity with no practical use; SQL does not allow it.

Here is the idea with the most consequences. A relation is a set of tuples, and mathematical sets have two properties that spreadsheets do not:

In a set, {a, b, c} and {c, a, b} are the same set. Therefore, the rows of a table have no intrinsic order. Neither do the columns, even though SQL declares them in a sequence.

The practical consequence is blunt: if you run a query without ORDER BY, the manager can return the rows in any order, and that order can change between two identical runs without anyone having touched anything. It is not a bug: it is the model. If order matters, you have to ask for it explicitly (we will see this in 02-03).

In a set, an element either is or is not there; it cannot be there "twice". Therefore, a relation cannot contain two identical tuples. This property is what justifies the mandatory existence of a primary key: there must always be something that tells one tuple from another.

Intellectual honesty: SQL does not implement the pure relational model. An SQL table is technically a multiset (bag), not a set: if you declare neither a primary key nor a uniqueness constraint, SQL will let you insert two identical rows and both will coexist.

That is why one of the course's first practical rules is: every table gets a primary key. It is not bureaucracy; it is what restores the table's status as a relation.

The BiblioRed spreadsheet we diagnosed in 01-01 looked a lot like a table. These are the differences that made it fail:

The decisive difference is the second-to-last line. In a spreadsheet, a row is identified by where it is; in a relation, by what it is worth. That is why inserting a row in the middle of a sheet breaks formulas, and why in a database it breaks nothing: nobody depends on position.

To guarantee there are no duplicate tuples we need to identify each tuple. The model defines a precise hierarchy of concepts. We will work on BiblioRed's books relation:

A set of attributes that uniquely identifies each tuple: there cannot be two tuples with the same values in all of them.

In books, these are superkeys, among others:

• {book_id}
• {isbn}
• {book_id, title}
• {isbn, publisher, publication_year}
• The set of all attributes (it is always a superkey, because there are no duplicate tuples)

Notice that you can add irrelevant attributes to a superkey and it remains a superkey. That is why we need to sharpen the definition.

A minimal superkey: if you remove any attribute, it stops identifying uniquely. {book_id, title} is not a candidate, because {book_id} already suffices on its own. In books the candidate keys are:

• {book_id}
• {isbn}

A relation can have several candidate keys, and it always has at least one.

The candidate key that the designer chooses as the official identifier. It is the one that foreign keys in other tables will use and the one the manager relies on to organize storage. In books we will choose book_id (section 7 justifies why).

The candidate keys that were not chosen. In books, isbn is an alternate key. In SQL they are declared with UNIQUE, and they are just as mandatory to respect as the primary key: if you let two books share an ISBN, the BiblioRed catalog is corrupted all the same.

A key (candidate or primary) made up of more than one attribute. If BiblioRed had a books_authors table for books written by several hands, its natural primary key would be {book_id, author_id}: neither the book alone nor the author alone is enough, but the pair is.

A foreign key is an attribute, or set of attributes, of one relation whose values must match those of the primary key of another relation (or of the same one).

In BiblioRed:

• members.branch_id is a foreign key to branches.branch_id: every member belongs to an existing branch.
• copies.book_id is a foreign key to books.book_id: every physical copy is a copy of a book in the catalog.
• loans.member_id and loans.copy_id are two foreign keys in the same table: a loan connects one member with one copy.

Vocabulary: the table containing the foreign key is the child or referencing table; the one containing the primary key being pointed at is the parent or referenced table.

Two important observations:

1. A foreign key can accept NULL (unless it is declared NOT NULL), and that NULL means "this row points at nobody". If we allowed copies.branch_id to be null, we would be saying there are copies with no branch assigned. In BiblioRed we do not want that, so it will be NOT NULL.
2. A foreign key can repeat. branch_id being 2 in hundreds of members is exactly what we expect: it is a one-to-many relationship.

The practical handling of foreign keys —what happens when you delete the parent row, ON DELETE CASCADE, RESTRICT, deferrable constraints— is the entire content of lesson 02-06. Here we stay with the concept.

This is a real design debate, and BiblioRed has it right in front of it.

• A natural key is an identifier that already exists in the real world and that we have adopted as a key: a book's ISBN, a person's tax ID, an airport's IATA code.
• A surrogate key (or artificial key) is a number the database itself invents, with no meaning outside it: book_id = 331.

Should books have the isbn (natural) as its primary key, or a book_id (surrogate)? Let's compare:

Decision for BiblioRed: surrogate primary key book_id, and isbn as a UNIQUE alternate key. The three reasons that weigh most:

1. Not everything BiblioRed lends has an ISBN. Holdings from before 1970, municipal publications and uncataloged donations would be left with no key. And a primary key cannot accept NULL (we will see this in the next section).
2. ISBNs get corrected. When a librarian spots that an ISBN was mistyped and fixes it, with a natural key the change would have to be propagated to every child table; with a surrogate key, a single value is corrected and nobody else notices.
3. Foreign keys get cheaper. copies has 40,000 rows: storing a 4-byte integer in each one instead of a 13-character string reduces the size of the table and of its indexes.

What we do not do is give up on the ISBN: it stays declared UNIQUE, so it remains impossible to catalog the same book twice. Choosing a surrogate key is no excuse for losing real-world constraints; that is the classic mistake.

The same reasoning applies to copies: the physical label EJ-3081 stuck on the copy is an excellent natural key for the front desk, but we will store it in a code column with UNIQUE, and the primary key will be the integer copy_id.

The relational model defines three rules that every system must enforce. They are called integrity rules because their job is to stop the database from entering an impossible state.

Every value of an attribute must belong to its domain.

It is the most elementary rule and the one that saves the most work. If the domain of loan_date is dates, the manager rejects 'yesterday', '32/13/2026' and '-1'. If the domain of publication_year is integers, it rejects 'nineteen eighty-nine'.

In SQL, domain integrity is expressed with data types (we will see them in action in 02-02) and refined with CHECK and NOT NULL constraints (full catalog in 04-04).

BiblioRed's spreadsheet had no domain integrity: that is why 2026-06-02, 02/06/26 and pending coexisted in the same date column.

No attribute of the primary key can be NULL.

The reasoning is direct: the primary key exists to identify the tuple. If its value is unknown, the tuple is not identifiable, and then it cannot be part of a relation (remember: no duplicates, and two tuples with an unknown key cannot be told apart).

From this follows the practical consequence of the previous section: since not every BiblioRed item has an ISBN, the ISBN cannot be the primary key, because it would have to accept NULL.

In SQL this rule is automatic: declaring PRIMARY KEY implies NOT NULL even if you do not write it.

Every non-null value of a foreign key must correspond to an existing value of the referenced primary key.

Stated that simply, it forbids orphan rows: a loan whose member_id is 9999 when member 9999 does not exist; a copy of a book that is not in the catalog. BiblioRed's spreadsheet had them by the dozen, because nothing stopped anyone from typing a made-up member number.

How it is declared, what the manager checks on each operation and what to do when you delete the parent row is the entire content of lesson 02-06. Here it is enough to retain the statement.

NULL is not zero. NULL is not the empty string. NULL is not "false". NULL is the absence of a value, and it admits at least three different readings that the model does not distinguish:

• Unknown: member 19, Pau Miralles, has an email address but did not give it when signing up.
• Not applicable: the return_date of a loan that is still open — the book has not been returned, so there is no date to put.
• Pending: it has not been recorded yet.

In BiblioRed we will use return_date IS NULL as the operational definition of "open loan". It is a clean and very common use: the absence of a date means something.

Since NULL means "I don't know", any comparison with NULL produces… I don't know. SQL formalizes this with a three-valued logic: TRUE, FALSE and UNKNOWN.

Yes: NULL = NULL is not true. And it makes perfect sense: if I know neither Marta's age nor Iván's, I cannot claim they are equal.

Truth tables for the logical operators (U = unknown):

Two cells deserve attention: FALSE AND UNKNOWN is FALSE (if one part already fails, the rest does not matter) and TRUE OR UNKNOWN is TRUE (if one part is already satisfied, the rest does not matter).

A filter only lets through the rows for which the condition is TRUE. UNKNOWN does not pass. That is why:

The first query does not raise a syntax error —and that is what makes it dangerous—, it simply returns zero rows every time. The correct operators are IS NULL and IS NOT NULL. We will practice them in 02-03.

A subtler and very real trap: WHERE status <> 'on_loan' does not return the rows whose status is NULL, because NULL <> 'on_loan' is UNKNOWN. If you want to include them, you have to ask: WHERE status <> 'on_loan' OR status IS NULL.

Codd did not stop at defining what a relation is: he also defined an algebra for operating on them. The idea is elegant: each operation takes one or two relations and returns another relation. Being closed (relation in, relation out), operations can be chained indefinitely, and from that the possibility of querying is born.

Here we present it conceptually only, with no notation exercises. What matters is that you see how each algebra operation has its direct translation into SQL: SQL is the practical realization of relational algebra, and the optimizer we saw in 01-04 works precisely by reordering these operations so that they cost less.

Picks rows that satisfy a condition. The result has the same degree and equal or lower cardinality.

"The copies at branch 2" → σ branch_id = 2 (copies)

Picks columns. The result has lower degree. In pure algebra, projection removes duplicates (because the result must be a set); in SQL it does not, unless you write DISTINCT.

"Only the title and the ISBN of the books" → π title, isbn (books)

Combines each tuple of one relation with each tuple of the other. If members has 12,000 rows and books 8,000, the product has 96,000,000. It is rarely wanted for its own sake, but it is the theoretical foundation of every combination.

A Cartesian product followed by a selection that pairs up the related tuples. It is the operation that reassembles information spread across several tables.

"Each loan with its member's details" → loans ⋈ loans.member_id = members.member_id members

It is so central that we devote the whole of lesson 02-04 to it.

Puts together the tuples of two compatible relations (same number of attributes and compatible domains) and removes duplicates.

"All the member identifiers that appear in loans or in reservations"

The tuples that are in the first relation and not in the second. It is the operation that answers negative questions.

"Members who have made reservations but have never borrowed anything"

This table is, in practice, the index of the next three lessons.

With all the vocabulary in hand, this is the blueprint we will build in lesson 02-02. Seven relations:

The design decisions we can already justify with what we have learned:

• Seven surrogate primary keys, one per table, all integers and auto-generated. They satisfy entity integrity without depending on real-world data.
• books.isbn and copies.code as alternate keys (UNIQUE): we keep the natural constraints without turning them into the primary key.
• The distinction between books and copies is the heart of the model. books is the work (the title, the ISBN, the author); copies is the physical object that gets lent and that sits on a specific shelf. BiblioRed has about 8,000 distinct books and 40,000 copies. Lending a "book" means nothing: what gets lent is a copy.
• loans points at copies, not at books, precisely because of the above. reservations, by contrast, points at books: a member reserves the work, and whichever copy frees up first will be assigned to them. This asymmetry is not an oversight, it is the business model.
• return_date accepts NULL and that NULL means "open loan". It is the legitimate use of the null value we saw in section 9.
• surcharge is NUMERIC, not floating point, because it represents money. The full justification is in the next lesson.

This schema is not yet a finished design: a book can have several authors, and books.author_id only has room for one. It is a conscious simplification for module 2; the techniques for modeling that case properly (E-R diagrams, N:M relationships, junction tables) arrive in module 4, and the theory that explains why certain designs degenerate, in module 5.

• Confusing "relation" with "link between tables". A relation is a table. The link is a foreign key. It is misunderstanding number one of relational vocabulary.
• Assuming rows have an order. "The most recent loans are at the end of the table" is false. Without ORDER BY there is no guaranteed order, and trusting whatever comes out today is a deferred breakdown.
• Designing tables without a primary key. SQL allows it, which is why you have to impose it on yourself. Without a primary key you can end up with two identical rows that cannot be updated or deleted separately.
• Writing = NULL instead of IS NULL. It raises no error: it silently returns zero rows. It is the most expensive mistake in this lesson.
• Choosing a natural key out of convenience. The ISBN looks perfect until the first municipal pamphlet with no ISBN turns up, or the first typo that has to be corrected in cascade.
• Dropping the UNIQUE because there is already a surrogate key. Adding book_id does not authorize you to allow two books with the same ISBN. The surrogate key identifies; the alternate key protects reality.
• Using NULL as an all-purpose wildcard. If NULL in status sometimes means "I don't know" and sometimes "deregistered", nobody will ever query that column with confidence again. One NULL, one meaning.
• Tip: when a future query returns fewer rows than expected, check first whether NULLs are involved. Three-valued logic is behind most "inexplicable" results.

Given this instance of BiblioRed's copies relation:

Answer:

1. What is the degree and what is the cardinality?
2. Propose a reasonable domain for status and another for acquisition_date.
3. State two superkeys, all the candidate keys, the chosen primary key and the alternate key.
4. Is {book_id, branch_id} a candidate key? Justify your answer with the data.
5. List this relation's foreign keys and where they point.

The loans table contains these rows:

Say how many rows each query returns and why:

1. SELECT * FROM loans WHERE return_date = NULL;
2. SELECT * FROM loans WHERE return_date IS NULL;
3. SELECT * FROM loans WHERE surcharge > 0;
4. SELECT * FROM loans WHERE surcharge > 0 OR return_date IS NULL;
5. SELECT * FROM loans WHERE member_id = 14 AND surcharge > 0;
6. SELECT * FROM loans WHERE NOT (surcharge > 0);

Express each question using the algebra operations (σ, π, ⋈, ∪, −) and say which SQL clause will correspond to it. Correct SQL syntax is not required yet.

1. The codes of the copies that are under repair.
2. The titles of all the books, without repeats.
3. Each copy together with the title of its book.
4. The members who have loans or reservations.
5. The books that have never been reserved.

Solution 1

1. Degree 6 (six attributes: copy_id, code, book_id, branch_id, status, acquisition_date). Cardinality 4 (four tuples). Degree belongs to the schema; cardinality, to the instance.
2. For status, a closed domain of labels: {'available', 'on_loan', 'in_repair', 'withdrawn'}. For acquisition_date, valid calendar dates, not later than today (a library does not acquire in the future). The first will be implemented with a CHECK, the subject of 04-04.
3. Superkeys: {copy_id}, {code}, {copy_id, status}, {code, book_id, branch_id}, the set of all attributes… Candidate keys: {copy_id} and {code}, because both identify and neither can be reduced. Primary key: copy_id (surrogate, stable, cheap in the foreign keys of loans). Alternate key: code, declared UNIQUE, because the physical label must also be unique.
4. No. With this data no combination repeats, but that is an accident of the instance: nothing stops branch 2 from having two copies of book 331 (in fact that is normal in a library). Keys are determined by the business rules, never by inspecting a specific instance; an instance can only refute a candidate key, never confirm it.
5. book_id → books.book_id, and branch_id → branches.branch_id. Both should be NOT NULL: a copy with no book makes no sense and a copy with no branch cannot be located on the shelf.

Solution 2

Solution 3

Notice the pattern in point 5: every question that starts with "the ones that never…" is a difference. Remember it, because in 02-04 and 02-06 it will come back several times.

We have turned the informal vocabulary of the first lesson into a model with precise rules:

• A relation is a set of tuples with the same attributes, each with its domain. Its degree is the number of attributes and its cardinality, the number of tuples.
• Being a set, it has no order and no duplicates, and that separates it radically from a spreadsheet, where a row is identified by its position and not by its value.
• Keys form a hierarchy: superkey → candidate key (minimal superkey) → primary key (the chosen one) and alternate keys (the rest, UNIQUE). Foreign keys link relations to one another.
• In the natural versus surrogate key debate, BiblioRed chooses book_id as the primary key and keeps isbn as an alternate one: because not everything has an ISBN, because ISBNs get corrected and because an integer is cheaper to replicate.
• The three integrity rules: domain (values belong to their type), entity (the primary key is never null) and referential (every foreign key points at something that exists; its practical handling is lesson 02-06).
• NULL means "I don't know", and hence three-valued logic: NULL = NULL is UNKNOWN, IS NULL is the only valid operator for asking about its absence, and NOT UNKNOWN is still UNKNOWN.
• Relational algebra —selection, projection, Cartesian product, join, union, difference— is the machinery SQL implements: each operation has its clause, and the optimizer from 01-04 does nothing but reorder them.
• And we have the BiblioRed blueprint: branches, members, authors, books, copies, loans and reservations, with their keys and their links.

The blueprint is drawn; now it has to be built. In lesson 02-02, The SQL Language, you will meet the language used to talk to a relational system —why it is declarative, what sublanguages it has, how its data types are written— and you will finish by running the complete CREATE TABLE script for the seven tables inside biblioredb. When you finish it, this lesson's diagram will have stopped being a drawing.