The Relational Model

Before writing SQL, it is crucial to understand the Relational Model, which is the foundational theory behind an RDBMS. Proposed by Edgar F. Codd in 1970, it organizes data into one or more tables (or "relations") of columns and rows.

Core Concepts

1. Relations (Tables)

A relation is simply a table. It is a set of tuples (rows). In SQL, we use the term TABLE. For example, a users table holds user data.

2. Tuples (Rows / Records)

A tuple represents a single, complete item in the relation. In SQL, this is a ROW. For example, a single row in the users table representing "Alice".

3. Attributes (Columns / Fields)

An attribute is a named column of a relation. In SQL, this is a COLUMN. Every column has a specific data type (e.g., text, integer, date). For example, first_name, email, and date_of_birth are columns in the users table.

Why the Relational Model?

The relational model separates logical data representation from physical data storage. You don't need to know where the data is on the hard drive; you only interact with the logical tables using SQL.

TIP

The biggest advantage of the relational model is its rigorous mathematical foundation, which guarantees data consistency (ACID properties) and allows for highly optimized query execution.

Relationships Between Tables

Data in a relational database is often distributed across multiple tables to avoid redundancy (a process called Normalization). We then define Relationships between these tables:

• One-to-One (1:1): One row in Table A relates to exactly one row in Table B. (e.g., user and user_profile).
• One-to-Many (1:N): One row in Table A relates to many rows in Table B. (e.g., user and blog_posts). This is the most common relationship.
• Many-to-Many (M:N): Many rows in Table A relate to many rows in Table B. (e.g., students and classes). This requires a "join table" to resolve.

To enforce these relationships and uniquely identify rows, we use Keys, which we will explore next.