The Relational Model
Learning Objectives
- You can explain at a high level what the relational model means and describe how tables, rows, and columns represent data.
- You can explain why the relational model helps organize related data and reduce unnecessary repetition.
- You can explain the roles of primary keys and foreign keys.
For most of this course, we work with relational databases. The relational model is the main idea behind how they organize related data.
What the Relational Model Means
At a high level, the relational model says that data should be organized into relations.
In practice, these relations are usually shown as tables:
- each table represents one kind of thing, event, or relationship,
- each row represents one stored fact or record,
- each column describes one property of that fact,
- and tables can be connected in systematic ways.
This is the main idea to keep in mind throughout the course: organize related data so that the structure stays clear and the relationships can later be reconstructed reliably.
The word relational comes from the word relation, which is the formal term for this table-like structure. It does not just mean that the data happens to be related in an everyday sense.
A Short History of the Relational Model
The relational model is most closely associated with Edgar F. Codd, who described it in 1970 while working at IBM. His goal was to give databases a clearer logical foundation than the earlier hierarchical and network-style systems that were often harder to query and reason about.
One important idea in Codd’s work was that data structure and data access should be separated more cleanly. Instead of navigating records through system-specific pointer paths, users should be able to describe the data they want at a higher level.
Over time, relational ideas strongly shaped modern database systems and later practical query languages. Real database products are practical engineering systems rather than pure mathematical models, but the relational model still provides the core conceptual foundation.
Tables, Rows, and Columns
A relational database stores data in relations, usually presented as tables.
Each table has:
- Columns, which describe what kinds of values are stored
- Rows, which represent stored records
For example, a courses table might look like this.
| id | code | title |
|---|---|---|
| 1 | CS-A1150 | Introduction to Databases |
| 2 | CS-C3170 | Web Software Development |
| 3 | CS-A1113 | Introduction to Programming |
In the above table, there are three courses, each represented by one row. The columns id, code, and title describe the properties of each course.
The id column stores identifiers. The code column stores a course code. The title column stores the course title.
A related exercises table might look like this.
| id | course_id | title | deadline_at | max_points |
|---|---|---|---|---|
| 1 | 1 | Query Basics | 2026-03-10 23:59 | 10 |
| 2 | 1 | Filtering Queries | 2026-03-17 23:59 | 10 |
Even this small two-table example already supports several useful questions, such as how many exercises are there in the course with code CS-A1150 or what is the deadline for the exercise with title Filtering Queries.
Keys
Tables alone are not enough. Once a database stores many rows and several related tables, it needs reliable ways to identify one row and connect it to another.
Two important kinds of keys here are primary keys and foreign keys.
Primary Keys
A primary key identifies each row uniquely.
In the examples above, we use id as the primary key. It would be possible to also use the code column as a primary key, since course codes could be unique, but that can lead to problems if course codes ever need to change.
Foreign Keys
A foreign key points from one table to another.
If an exercises table contains a course_id column, that column can refer to the id in the courses table. This is how tables are connected.
For example, if a row in
exerciseshascourse_idequal to1, that means the exercise belongs to the course row whoseidis1.
When used together, primary keys and foreign keys make cross-table questions possible. Without identifiers, queries in the database would be ambiguous. For example, if the exercises table did not have a course_id, it would be much harder to ask which exercises belong to which course.
In the examples above, the id columns are called surrogate keys. They are created mainly to identify rows reliably, even if meaningful values such as titles or codes might later change.
They are typically auto-incrementing integers that have no meaning outside of their role as identifiers. Database systems can automatically generate new id values when new rows are added, which helps ensure that each row gets a unique identifier without needing to manage it manually.
We’ll look into key-choice trade-offs in more detail later in the course; for now, the main point is that primary keys and foreign keys are essential for organizing related data in a relational database.
One Fact in One Natural Place
One key benefit of the relational model is that it helps us avoid repeating the same fact over and over.
Suppose every submission row stored:
- the student’s full name,
- the student’s email,
- the course code,
- the course title,
- the exercise title,
- and the score.
That would repeat person and course information many times. If an email address changed, the system would need to update many rows consistently.
A better relational design stores:
- stable user information in
users, - stable course information in
courses, - and submission events in
submissions.
Then a submission row can point to a user and an exercise instead of copying those facts repeatedly.
A Slightly More Formal View
After seeing the practical idea, it helps to add one clarification: the relational model is more precise than having just tables or a spreadsheet.
In relational terms:
- a relation is the table-like structure,
- a tuple is a row,
- and an attribute is a column.
In the abstract model, each row represents a fact, row order does not matter, duplicate rows cannot exist, and columns are identified by name rather than by position.
The relational model is a mathematical abstraction that describes how data can be organized and related. It provides a clear framework for thinking about data structure and relationships, but it does not specify all the details of how to implement a database system.
Real database systems are practical implementations, so they add details beyond the pure model, such as data types, system-specific behavior, and other implementation features.
A useful way to think about this is that the relational model provides the conceptual foundation, and real database systems build on top of it.
Check Your Understanding
- What does the term relational model mean at a high level?
- Why is a primary key useful even in a small table?
- What does a foreign key mean in ordinary language?
- Why is it useful to keep one fact in one natural place instead of repeating it across many rows?
AI Study Aid
Create a chapter diagram
We're looking into to what extent AI-generated diagrams could help with studying.
Use this study aid to generate an AI-generated visual summary of the material you just studied.
Each diagram style emphasizes the content in a different way, so you can choose the focus
that feels most useful.
Using the diagram generator is voluntary and does not affect your course progress. We encourage
you to try it out and see if it helps, but it's totally up to you! Your answers help us understand
how to make better study aids in the future.
Selected style: choose a diagram style