The Relational Model Explained

The relational model is the mathematical foundation behind every SQL database — MySQL, PostgreSQL, SQL Server, Oracle, and more. Proposed by E. F. Codd in 1970, it represents all data as relations (tables) made of tuples (rows) and attributes (columns). Its big idea: store data in simple tables and link them by values, not by physical pointers.

If the ER model is the sketch, the relational model is the precise, rule-governed structure that sketch turns into.

The vocabulary you actually need

The textbook terms have everyday equivalents:

A relation schema is the table's structure — its name and the list of attributes with their domains, e.g. student(student_id INTEGER, name VARCHAR, city VARCHAR). The relation instance is the actual set of rows in it at a given moment.

The properties that make a relation a relation

A true relation is not just any grid. It obeys rules:

• Each value is atomic. One cell holds one indivisible value — no lists, no nested tables. This is the first normal form principle.
• Rows are unordered. There is no "first" row. You impose order with ORDER BY when querying, not in storage.
• Columns are unordered and each has a unique name.
• No duplicate rows. Every row is uniquely identifiable — which is exactly what a primary key guarantees.

These properties are why SQL behaves predictably: because the underlying model forbids ambiguity.

Keys and how tables link

A relation needs a way to identify each row uniquely. That is a key:

• Candidate key — a minimal set of columns that uniquely identifies a row.
• Primary key — the candidate key you choose as the official identifier.
• Foreign key — a column in one table whose values must match a key in another table. This is how the relational model links data — by matching values.

Linking by value (not by physical address) is the model's superpower: you can reorganise storage freely and the relationships still hold.

Integrity rules

The model defines two core integrity rules that databases enforce for you:

1. Entity integrity — a primary key column can never be NULL. If it could, you would not be able to identify the row.
2. Referential integrity — a foreign key must either match an existing primary key in the referenced table or be NULL. You cannot point to a row that does not exist.

Here is the model expressed in SQL, with the integrity rules visible:

-- PostgreSQL / MySQL compatible

-- A relation (table). Each column has a DOMAIN (type + constraints).
CREATE TABLE student (
    student_id  INTEGER     PRIMARY KEY,        -- entity integrity: never NULL, always unique
    name        VARCHAR(120) NOT NULL,
    city        VARCHAR(80)
);

CREATE TABLE enrolment (
    enrolment_id INTEGER     PRIMARY KEY,
    student_id   INTEGER     NOT NULL,
    course_name  VARCHAR(150) NOT NULL,
    -- Referential integrity: this value MUST exist in student.student_id
    FOREIGN KEY (student_id) REFERENCES student(student_id)
);

-- This INSERT succeeds: student 1 exists
INSERT INTO student (student_id, name, city) VALUES (1, 'Asha Patil', 'Jalgaon');
INSERT INTO enrolment (enrolment_id, student_id, course_name)
VALUES (101, 1, 'Database Systems');

-- This INSERT FAILS: student 99 does not exist (referential integrity violation)
-- INSERT INTO enrolment (enrolment_id, student_id, course_name)
-- VALUES (102, 99, 'Web Development');

The database rejects the bad insert automatically. That guarantee comes straight from the relational model.

A word on relational algebra

The relational model also comes with relational algebra — a set of operations (select, project, join, union, etc.) that describe how to retrieve data. SQL is a practical language built on top of these ideas. When you write a SELECT with a WHERE and a JOIN, you are expressing relational algebra in a friendlier syntax. You do not need to master the algebra to write SQL, but knowing it exists explains why SQL works the way it does.

Common mistakes

• Storing lists in a single cell. A phone_numbers column holding "123, 456" breaks atomicity. Split it into rows or a related table.
• Assuming row order is stable. Without ORDER BY, the database may return rows in any order — and that order can change between queries. Never rely on insertion order.
• Skipping foreign keys "for speed". Without referential integrity, orphaned rows accumulate silently. The performance you save is dwarfed by the data corruption you invite.
• Treating a table as a spreadsheet. Spreadsheets allow merged cells, mixed types, and free-form layouts. Relations do not. Respect the column domains.

FAQ

Is the relational model outdated now that NoSQL exists? No. NoSQL databases solve specific scaling and flexibility problems, but the relational model still dominates for structured, consistency-critical data. Understanding it makes you better at every database, relational or not.

What is the difference between a schema and an instance? The schema is the fixed structure (column names and types); the instance is the changing set of rows inside it. Schema = the form; instance = the data filled in.

Keep learning

Build on this with the keys in DBMS guide, learn to query relations with SQL SELECT basics, and tidy your relations with normalization.

Ready to design and query real relational databases? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.