Chapter 2 · Core Concepts of the Relational Model

Overview

The relational model is the foundation of modern data management and the engine behind SQL. Its beauty lies in its simplicity: all data is represented in a two-dimensional structure that is intuitive to humans—the table.

In this chapter, we will dissect this model's core components. You'll learn the essential vocabulary, understand how to guarantee data is unique and reliable using keys, and discover how to build logical connections between tables using relationships and constraints. Mastering these concepts is non-negotiable for anyone serious about working with data.

2.1 The Anatomy of a Table

Everything in the relational model begins with the table. Think of it as a simple, powerful spreadsheet, but with strict rules. To speak professionally, we must learn both the common and the formal terminology.

Common vs. Formal Terminology

While you'll use the common terms daily, knowing the formal terms is crucial for technical interviews and academic discussions.

Common Term	Formal Term	Simple Explanation
Table	Relation	A collection of related data (e.g., all employees).
Row	Tuple	A single record within the table (e.g., one employee).
Column	Attribute	A characteristic of each record (e.g., their first name).
Data Type	Domain	The set of all allowed values for a column (e.g., `INTEGER`, `TEXT`).

Example: The `Employees` Table

Here is a simple relation (Employees table) with three tuples (rows) and four attributes (columns).

EmployeeID	FirstName	LastName	DepartmentID
101	Alice	Williams	1
102	Bob	Johnson	1
103	Charlie	Brown	2

2.2 The Power of Keys: Enforcing Uniqueness

How do we guarantee that we can find exactly one employee without any ambiguity? We use keys. Keys are special columns that enforce uniqueness, acting as the primary mechanism for data integrity.

Primary Key (PK): The most important key. It's the column (or columns) chosen to be the official, unique identifier for every row in a table. A primary key must be unique and cannot be empty (NULL). In our Employees table, EmployeeID is the perfect primary key.
Candidate Key: Any column that could have been the primary key. For example, if our Employees table also had a unique NationalID column, both EmployeeID and NationalID would be candidate keys. We choose one to be primary.
Composite Key: A primary key that consists of more than one column. For example, in a table linking students to courses, the primary key might be the combination of (StudentID, CourseID).

For Your Interview: Surrogate vs. Natural Keys

This is a classic database design question.

A natural key is an attribute that is already unique in the real world (e.g., a Social Security Number, an email address).
A surrogate key is an artificial key with no business meaning, usually an auto-incrementing number (e.g., EmployeeID).

Which is better? Almost always use a surrogate key as your primary key. Why? A natural key might change (a person changes their email), it might not be unique forever, or privacy laws might require it to be hidden. A surrogate key is stable, guaranteed to be unique, and has no external dependencies. Mentioning this trade-off shows design maturity.

2.3 Forging Connections with Foreign Keys

If primary keys give tables their identity, foreign keys are what allow tables to form relationships.

A Foreign Key (FK) is a column in one table that refers to the primary key of another table. It acts as a pointer or a link, creating a logical connection.

Example: Linking `Employees` to `Departments`

We can't just type "Sales" in the Employees table; that's inefficient and prone to typos. Instead, we create a separate Departments table and link to it.

Employees Table

EmployeeID (PK)	FirstName	DepartmentID (FK)
101	Alice	1
102	Bob	1
103	Charlie	2

Departments Table

DepartmentID (PK)	DepartmentName
1	Sales
2	Engineering

Here, Employees.DepartmentID is a foreign key that references Departments.DepartmentID. This structure, known as referential integrity, guarantees that an employee can only be assigned to a department that actually exists.

2.4 Understanding Relationship Types

Foreign keys allow us to model three fundamental types of relationships between data.

One-to-Many (1:N): This is the most common relationship. One row in Table A can be linked to many rows in Table B, but one row in Table B can only link to one row in Table A.
- Example: One Department can have many Employees.
Many-to-Many (M:N): One row in Table A can link to many in Table B, and one row in Table B can also link to many in Table A. We model this using a third table, called a junction table (or linking table).
- Example: One Student can enroll in many Courses, and one Course can have many Students. We would create a StudentCourses junction table with StudentID and CourseID foreign keys.
One-to-One (1:1): One row in Table A can link to exactly one row in Table B. This is less common but useful for splitting a large table or isolating sensitive data.
- Example: One Employee has one set of optional EmployeeContactDetails.

2.5 Constraints: The Automated Rulebook

Constraints are rules that you define for your columns to guarantee data quality and integrity automatically. They are the database's guardrails.

NOT NULL: Ensures a column cannot have an empty (NULL) value. Every employee must have a name, so FirstName should be NOT NULL.
UNIQUE: Ensures that every value in a column is unique (e.g., no two users can have the same email address). A primary key is implicitly UNIQUE and NOT NULL.
CHECK: Enforces a custom rule on a column's values. For example, CHECK (Salary > 0) would prevent invalid salary entries.
DEFAULT: Provides a default value for a column when no value is specified during an insert.
FOREIGN KEY: As we've seen, this ensures referential integrity between tables.

-- An example of constraints in action
CREATE TABLE TeamMembers (
    MemberID SERIAL PRIMARY KEY, -- Enforces uniqueness and NOT NULL
    FullName TEXT NOT NULL, -- Must have a value
    Email TEXT NOT NULL UNIQUE, -- Must exist and be unique
    Role TEXT CHECK (Role IN ('Data Engineer', 'Data Analyst', 'Manager')), -- Must be one of these values
    StartDate DATE DEFAULT CURRENT_DATE -- Defaults to today's date if not provided
);

-- This insert would FAIL because the 'Role' is invalid.
INSERT INTO TeamMembers (FullName, Email, Role)
VALUES ('Kai Lopez', 'kai@example.com', 'Intern');
-- ERROR: new row violates check constraint "teammembers_role_check"

Chapter Summary & Next Steps

In this chapter, you learned the fundamental building blocks of the relational model: tables (relations), rows (tuples), and columns (attributes). You now understand that primary keys give rows a unique identity, while foreign keys create powerful relationships between tables. Finally, you saw how constraints act as an automated rulebook to enforce data integrity.

These concepts are the alphabet of database design. In the next chapter, we'll learn how to combine them using the principles of Normalization to design clean, efficient, and robust database schemas.

Check Your Understanding

These are common interview questions. Practice answering them concisely.

What is the difference between a primary key and a foreign key?
An interviewer asks you to design a schema for a blog (with Posts and Tags). What kind of relationship exists between them, and how would you model it in the database?
Why is it generally a bad idea to use a user's email address as a primary key, even though it's unique?
How would you use a CHECK constraint to ensure that a product's DiscountPrice is always lower than its ListPrice?

Overview​

2.1 The Anatomy of a Table​

Example: The Employees Table​

2.2 The Power of Keys: Enforcing Uniqueness​

2.3 Forging Connections with Foreign Keys​

Example: Linking Employees to Departments​

2.4 Understanding Relationship Types​

2.5 Constraints: The Automated Rulebook​

Chapter Summary & Next Steps​