Session 3 · The relational model#

The big idea#

In the relational model all data lives in tables. A table has columns (what you know about each thing) and rows (one thing each). Tables are linked by keys: a primary key identifies a row, and a foreign key is a column that stores another table’s primary key. Rules called integrity constraints make sure the data stays correct.

Everyday example: a library again. The books shelf is a table; each book is a row; the ISBN is its primary key. A borrowing slip says “ISBN 978-…, member card 42”. The slip does not copy the book’s title or the member’s address; it points to them. If the ISBN or card did not exist, the librarian would refuse the slip. That refusal is referential integrity.

directors and movies are two tables. movies.director_id is a foreign key that points at directors.director_id.

Tables, rows, columns, keys#

Idea	Library	In our database
Table	A bookshelf	`movies`, `directors`
Row	One book	`Nayakan, 1987, Crime`
Column	A field on the card	`title`, `year`
Primary key	ISBN	`movie_id`
Foreign key	A reference to another book	`movies.director_id`

A good primary key never repeats and is never empty. SERIAL in PostgreSQL gives a counter that fills it for you.

The four kinds of integrity, tried for real#

code/03-integrity.sql tries to break each rule on purpose. PostgreSQL refuses every bad row.

\echo '-- entity integrity: a duplicate primary key is refused'
INSERT INTO movies (movie_id, title, year) VALUES (1, 'Duplicate', 2000);
\echo '-- referential integrity: director 99 does not exist'
INSERT INTO movies (title, year, director_id) VALUES ('Ghost Film', 2000, 99);
\echo '-- domain integrity: year must be a number above 1900'
INSERT INTO movies (title, year) VALUES ('Too Old', 1800);
\echo '-- NOT NULL: a title is required'
INSERT INTO movies (year) VALUES (2000);
\echo '-- deleting a director who still has movies is refused'
DELETE FROM directors WHERE name = 'Mani Ratnam';
\echo '-- a valid row works'
INSERT INTO movies (title, year, genre, director_id) VALUES ('Test Film', 2024, 'Drama', 1) RETURNING movie_id, title;
DELETE FROM movies WHERE title = 'Test Film';

-- entity integrity: a duplicate primary key is refused
ERROR:  duplicate key value violates unique constraint "movies_pkey"
DETAIL:  Key (movie_id)=(1) already exists.
-- referential integrity: director 99 does not exist
ERROR:  insert or update on table "movies" violates foreign key constraint "movies_director_id_fkey"
DETAIL:  Key (director_id)=(99) is not present in table "directors".
-- domain integrity: year must be a number above 1900
ERROR:  new row for relation "movies" violates check constraint "movies_year_check"
DETAIL:  Failing row contains (13, Too Old, 1800, null, null, null).
-- NOT NULL: a title is required
ERROR:  null value in column "title" of relation "movies" violates not-null constraint
DETAIL:  Failing row contains (14, null, 2000, null, null, null).
-- deleting a director who still has movies is refused
ERROR:  update or delete on table "directors" violates foreign key constraint "movies_director_id_fkey" on table "movies"
DETAIL:  Key (director_id)=(1) is still referenced from table "movies".
-- a valid row works
 movie_id |   title   
----------+-----------
       15 | Test Film
(1 row)

INSERT 0 1
DELETE 1

Match each error to its kind:

Entity integrity: duplicate primary key 1 was refused. Every row must be unique and findable.
Referential integrity: director 99 does not exist, so a movie cannot point at it. The last delete is the reverse: Mani Ratnam is still referenced by two movies, so he cannot be removed.
Domain integrity: year must pass CHECK (year > 1900), and title is NOT NULL.
User-defined rules are just constraints you write yourself, like that CHECK.

One surprise in the output: the valid insert got movie_id 15, not 12. Each failed attempt used up a number from the SERIAL counter. Gaps in ids are normal and harmless; never rely on them being continuous.

Normalisation in one paragraph#

Notice we did not put the director’s name in movies. We stored it once in directors and pointed to it. Splitting data so each fact is stored once is called normalisation. It stops the “rename in two thousand places” problem from Session 1.

Common mistakes#

Using a person’s name as the primary key. Names repeat and change. Use a plain id.
Skipping the foreign key. Without REFERENCES, nothing stops director_id = 99. The column would be a number the database does not understand.
Storing the same fact twice (director name inside movies as well). One of the copies will eventually be out of date.
Expecting ids with no gaps. Rolled-back or failed inserts consume numbers.
Treating NULL as zero or empty text. NULL means “unknown”; it behaves differently (Session 5).

Hands-on exercises#

Exercise 1 · Pick keys. For a students table with roll_no, name, email, which column should be the primary key and why?

Solution

roll_no, if the school guarantees it is unique and never reused. name repeats; email can change. If you cannot guarantee a natural key, add a student_id SERIAL instead.

Exercise 2 · Break a rule. Try to insert an actor with no name into the practice database. Which error do you get?

Solution

INSERT INTO actors (name) VALUES (NULL);

PostgreSQL answers with a “null value in column “name” … violates not-null constraint” error, because actors.name was declared NOT NULL.

Exercise 3 · Design. Write the CREATE TABLE for a reviews table: each review belongs to one movie, has a score from 1 to 10 and optional text.

Solution

CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    movie_id  INT NOT NULL REFERENCES movies(movie_id),
    score     INT NOT NULL CHECK (score BETWEEN 1 AND 10),
    comment   TEXT
);

The foreign key ties each review to a real movie; the CHECK keeps scores in range.

Practice#

Take the Session 3 quiz: keys and the four kinds of integrity.