SQL 1 🛢️ What Relational Databases Are

A relational database is a system for storing and managing data as a collection of tables that are linked to each other through shared values. This model was introduced by Edgar F. Codd at IBM in the 1970s, and it replaced the earlier “navigation” approach to data management with a more intuitive, data-centric one . The core idea is simple: instead of organizing data in application-specific structures, you store it in normalized tables and define relationships between them . The relational model has been the dominant approach to data management for decades because it is both powerful and flexible enough to serve almost any application .

Key point: The word “relational” does not mean “relationships between tables,” although those relationships exist. It refers to the mathematical concept of a relation—a set of tuples—which in database terms is a table. A table is a relation in and of itself because it contains rows of data . The relationships between tables are constraints that connect these relations, but the tables themselves are the primary relational construct.

---

Why relational databases exist

Before relational databases, data was stored in file-based systems or in navigational databases that required developers to know the physical structure of the data to retrieve it. These approaches were inefficient, difficult to maintain, and coupled the application tightly to the storage format .

The structure problem. In early computing systems, every application stored data in its own proprietary structure. Developers building applications that used that data had to understand the specific structure just to find what they needed. The relational model solves this by providing a standard way to represent data and query it that works for any application .

The redundancy problem. Storing data in a single flat file leads to redundancy: the same information is repeated across multiple records. A customer’s address appears in every order they place. The relational model addresses this through normalization—dividing large tables into smaller, less redundant tables and defining relationships between them . Each fact is stored in one place, and the relationships connect the facts.

The consistency problem. When data is redundant, updates become error-prone. Changing a customer’s address requires updating it in every order. A relational database with proper constraints can validate data at the point of entry. A user table can reject an age that is not a non-negative integer between 1 and 130, for example, preventing invalid data from being stored in the first place .

The query problem. Ad hoc questions—”which customers in New York placed orders over $500 last month?”—required custom code in file-based systems. SQL, the standard query language for relational databases, was developed by IBM alongside the relational model to provide a reasonably intuitive language for creating and managing databases . A single SQL statement can express a complex query that would require pages of procedural code otherwise.

The trade-off. Relational databases have limitations. Traditional RDBMSs were designed to run on a single computer, and scaling them horizontally across multiple nodes while maintaining strict consistency is difficult . NoSQL databases have emerged to address workloads with different priorities—unstructured data, massive scale, and fast access at the expense of some consistency guarantees . The choice between relational and non-relational depends on the system’s needs and the characteristics of the data it manages.

---

a. Tables, Rows, and Columns

In a relational database, all data is stored in tables. A table is a collection of related data organized into rows and columns . The columns define the structure—the types of data the table can hold. The rows contain the actual data—one row per instance of the entity the table represents .

Each column has a name and a data type. The name identifies the field—customer_id, first_name, order_date. The data type constrains what values can be stored: integers, strings, dates, decimals. Every row in a table has the same set of columns, although some columns may allow null values to represent unknown or inapplicable data .

The rows are sometimes called tuples or records. A row represents a single instance of the entity. In a customers table, each row is one customer. In an orders table, each row is one order .

The terminology is worth knowing because it appears in database documentation and academic material. The columns are also called fields or attributes. The rows are also called tuples or records. The number of columns in a table is its degree. The number of rows is its cardinality .

A simple example illustrates the structure. Consider a table for employees:

employee_id | first_name | last_name  | department_id
------------|------------|------------|--------------
101         | Alice      | Johnson    | 10
102         | Bob        | Smith      | 20
103         | Carol      | Williams   | 10

The columns define the structure. The rows contain the data. The department_id column is a link to another table that defines the departments.

---

b. Primary Keys and Foreign Keys

The relationships between tables are defined through keys. A primary key is a column or set of columns that uniquely identifies each row in a table . No two rows can have the same primary key value, and the primary key cannot be null. In the employees table, employee_id is the primary key—each employee has a unique ID .

A table can have multiple candidate keys—columns that could serve as the primary key. One is chosen as the primary key; the others are declared with a UNIQUE constraint . For example, if each employee has a unique email address, the email column is a candidate key. The database administrator chooses which candidate becomes the primary key.

A foreign key is a column or set of columns in one table that refers to the primary key of another table . It is a logical pointer from one table to another. In the employees table, department_id is a foreign key that refers to the department_id column in the departments table. This ensures that every employee is assigned to a department that actually exists .

Foreign keys enforce referential integrity. If all foreign key constraints are enforced, there can be no dangling references—no employee assigned to a department that does not exist . The database system rejects any insert or update that would violate this rule.

The behavior when a referenced row is deleted or updated can be configured. The options are NO ACTION (reject the delete or update), CASCADE (also delete or update the referencing rows), SET NULL (set the foreign key to null), and SET DEFAULT (set the foreign key to a default value) . The default is NO ACTION, which means the database refuses to delete a department while employees are still assigned to it.

---

c. Relationships Between Tables

The relationships between tables are the “relational” part of the model, even though the term originally referred to the tables themselves. A relationship is defined by the foreign key constraint that connects one table’s column to another table’s primary key .

The most common relationship is one-to-many. One customer can place many orders. One department can have many employees. The “one” side is the table with the primary key. The “many” side is the table with the foreign key. In the employees example, one department has many employees. The departments table has the primary key, and the employees table has the foreign key .

A many-to-many relationship requires a third table, often called a junction table or association table. Students can enroll in many courses, and a course can have many students. The enrollment table has foreign keys to both the students table and the courses table. The primary key of the enrollment table is typically the combination of the two foreign keys .

A one-to-one relationship is less common but exists when one entity is an extension of another. A user might have one profile, and the profile belongs to exactly one user. The profile table has a foreign key to the user table, and the foreign key is also unique.

The relationships are what allow data to be spread across multiple tables without redundancy. The customer’s address is stored once in the customers table. The orders table stores only the customer ID. When you query for an order and the customer’s address, the database joins the two tables on the customer ID .

---

Complete Example Session

This session builds a small relational database schema for an e-commerce application, demonstrating tables, primary keys, foreign keys, and a query that joins them.

-- ============================================
-- PART 1: THE CUSTOMERS TABLE
-- ============================================

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- customer_id is the primary key.
-- email is a candidate key with a UNIQUE constraint.
-- NOT NULL prevents missing values.

-- ============================================
-- PART 2: THE PRODUCTS TABLE
-- ============================================

CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    price         DECIMAL(10, 2) NOT NULL,
    stock         INTEGER DEFAULT 0
);

-- product_id is the primary key.

-- ============================================
-- PART 3: THE ORDERS TABLE WITH A FOREIGN KEY
-- ============================================

CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    order_date    DATE NOT NULL,
    total         DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- customer_id is a foreign key referring to customers.
-- ON DELETE CASCADE means if a customer is deleted,
-- their orders are also deleted.

-- ============================================
-- PART 4: THE ORDER_ITEMS TABLE (MANY-TO-MANY)
-- ============================================

CREATE TABLE order_items (
    order_id      INTEGER NOT NULL,
    product_id    INTEGER NOT NULL,
    quantity      INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

-- The primary key is the combination of order_id and product_id.
-- This prevents the same product from appearing twice in one order.
-- ON DELETE RESTRICT prevents deleting a product that is in an order.

-- ============================================
-- PART 5: INSERTING DATA
-- ============================================

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'Alice', 'Johnson', 'alice@example.com');

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (2, 'Bob', 'Smith', 'bob@example.com');

INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, 'Laptop', 999.99, 10);

INSERT INTO products (product_id, product_name, price, stock)
VALUES (102, 'Mouse', 29.99, 50);

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1001, 1, '2026-09-30');

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 101, 1);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 102, 2);

-- ============================================
-- PART 6: A QUERY THAT JOINS TABLES
-- ============================================

SELECT
    o.order_id,
    o.order_date,
    c.first_name || ' ' || c.last_name AS customer_name,
    p.product_name,
    oi.quantity,
    p.price,
    (oi.quantity * p.price) AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id = 1001;

-- Output:
-- order_id | order_date | customer_name | product_name | quantity | price | line_total
-- 1001     | 2026-09-30 | Alice Johnson | Laptop       | 1        | 999.99| 999.99
-- 1001     | 2026-09-30 | Alice Johnson | Mouse        | 2        | 29.99 | 59.98

-- The query joins four tables to produce a complete view of the order.

-- ============================================
-- PART 7: THE SCHEMA DIAGRAM
-- ============================================

-- customers (1) ───< orders (many)
--   customer_id          customer_id (FK)
--
-- orders (1) ───< order_items (many)
--   order_id              order_id (FK)
--
-- products (1) ───< order_items (many)
--   product_id            product_id (FK)

-- ============================================
-- PART 8: TESTING REFERENTIAL INTEGRITY
-- ============================================

-- Try to insert an order for a non-existent customer
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1002, 999, '2026-09-30');

-- Error: FOREIGN KEY constraint failed
-- The database rejects the insert.

-- ============================================
-- PART 9: TESTING CASCADE DELETE
-- ============================================

-- Delete a customer
DELETE FROM customers WHERE customer_id = 1;

-- The customer's orders and order_items are also deleted
-- because of ON DELETE CASCADE.

SELECT COUNT(*) FROM orders WHERE customer_id = 1;
-- 0

-- ============================================
-- PART 10: THE KEY CONCEPTS IN ONE VIEW
-- ============================================

-- Table: customers, products, orders, order_items
-- Primary key: customer_id, product_id, order_id
-- Foreign key: orders.customer_id → customers.customer_id
-- Foreign key: order_items.order_id → orders.order_id
-- Foreign key: order_items.product_id → products.product_id
-- Relationship: one-to-many (customers → orders)
-- Relationship: many-to-many (orders ↔ products via order_items)
-- Referential integrity: enforced by foreign key constraints
-- Cascade: deleting a customer deletes their orders

The ten parts cover creating the customers table, the products table, the orders table with a foreign key, the order_items table with a composite primary key, inserting data, a query that joins four tables, the schema diagram, testing referential integrity, testing cascade delete, and a summary of the key concepts.

---

Quick Reference

The Core Concepts

Concept	Definition
Table (Relation)	A collection of related data organized into rows and columns
Row (Tuple/Record)	A single instance of an entity
Column (Field/Attribute)	A property of the entity
Primary Key	Column(s) that uniquely identify each row
Foreign Key	Column(s) that refer to the primary key of another table
Referential Integrity	The guarantee that foreign keys always reference valid rows
Degree	The number of columns in a table
Cardinality	The number of rows in a table

The Relationship Types

Relationship	Example	Implementation
One-to-Many	Customer → Orders	Foreign key on the “many” side
Many-to-Many	Students ↔ Courses	Junction table with two foreign keys
One-to-One	User → Profile	Foreign key with a unique constraint

The Referential Integrity Actions

Action	Behavior
NO ACTION	Reject the delete or update (default)
CASCADE	Also delete or update the referencing rows
SET NULL	Set the foreign key to null
SET DEFAULT	Set the foreign key to a default value

The SQL Command Categories

Category	Purpose	Examples
DDL	Define and modify schema	CREATE, ALTER, DROP
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE
DQL	Query data	SELECT

---

Best Practices

✅ Do This:

-- Define a primary key for every table
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, ...);  -- ✅

-- Use foreign keys to enforce referential integrity
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)     -- ✅

-- Use CASCADE carefully and intentionally
ON DELETE CASCADE                                               -- ✅

-- Normalize data to reduce redundancy
-- Store a customer's address once, not in every order.         -- ✅

❌ Don’t Do This:

-- Don't store redundant data
-- Don't repeat the customer's address in every order row.      -- ❌

-- Don't use a foreign key without a primary key on the target
FOREIGN KEY (customer_id) REFERENCES customers(some_column)      -- ❌ must be PK or UNIQUE

-- Don't allow NULL in a primary key
PRIMARY KEY (customer_id)  -- cannot be NULL                     -- ❌

-- Don't delete a referenced row without considering cascade
DELETE FROM customers WHERE customer_id = 1;  -- may fail if FK exists -- ⚠️

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Foreign key violation	Inserting a row with a non-existent referenced ID	Insert the referenced row first
Cannot delete referenced row	Foreign key constraint with NO ACTION	Use CASCADE or delete child rows first
Duplicate primary key	Inserting a row with an existing ID	Use a sequence or auto-increment
NULL in primary key	Primary key columns must be unique and not null	Ensure all PK columns have values
Orphaned rows	No foreign key constraint defined	Add the foreign key constraint

---

Real-World Examples

1. Customers Table

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name VARCHAR(100));

2. Orders Table with Foreign Key

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

3. Many-to-Many Junction Table

CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, PRIMARY KEY (order_id, product_id));

4. Join Query

SELECT c.name, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

5. Cascade Delete

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE

6. Restrict Delete

FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT

7. Unique Constraint

email VARCHAR(100) UNIQUE NOT NULL

8. Check Constraint

age INTEGER CHECK (age >= 0 AND age <= 130)

9. Primary Key Definition

PRIMARY KEY (order_id, product_id)

10. Schema Inspection

SELECT * FROM information_schema.tables WHERE table_schema = 'public';

---

Visual

The Relational Model

┌──────────────────────────────────────────────┐
│  RELATIONAL MODEL                            │
│                                              │
│  Data is stored in tables (relations).       │
│  Each table has rows (tuples) and columns    │
│  (attributes).                               │
│                                              │
│  Tables are linked by foreign keys.          │
│  A foreign key in one table refers to the    │
│  primary key of another table.               │
│                                              │
│  The relationships are the "relational"      │
│  part of the model.                          │
│                                              │
└──────────────────────────────────────────────┘

The Primary Key and Foreign Key

┌──────────────────────────────────────────────┐
│  CUSTOMERS                                   │
│    customer_id (PK)                          │
│    first_name                                │
│    last_name                                 │
│    email                                     │
│                                              │
│  ORDERS                                      │
│    order_id (PK)                             │
│    customer_id (FK → customers.customer_id)  │
│    order_date                                │
│                                              │
│  The foreign key creates the relationship.   │
│  It ensures every order belongs to a valid   │
│  customer.                                   │
│                                              │
└──────────────────────────────────────────────┘

The One-to-Many Relationship

┌──────────────────────────────────────────────┐
│  ONE-TO-MANY                                 │
│                                              │
│  Customer 1 ──────< Order 1                  │
│             │                                │
│             ├──────< Order 2                  │
│             │                                │
│             └──────< Order 3                  │
│                                              │
│  One customer can have many orders.          │
│  Each order belongs to one customer.         │
│                                              │
│  The "one" side has the primary key.         │
│  The "many" side has the foreign key.        │
│                                              │
└──────────────────────────────────────────────┘

The Many-to-Many Relationship

┌──────────────────────────────────────────────┐
│  MANY-TO-MANY                                │
│                                              │
│  Students ───< Enrollments >─── Courses      │
│                                              │
│  Enrollments table:                          │
│    student_id (FK)                           │
│    course_id (FK)                            │
│    grade                                     │
│    PRIMARY KEY (student_id, course_id)       │
│                                              │
│  The junction table connects the two         │
│  entities and can store relationship data.   │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Relational model	Data stored in tables linked by keys
Introduced by	Edgar F. Codd, 1970s
Table	Collection of rows and columns
Row	Single instance of an entity
Column	Property of the entity
Primary key	Unique identifier for each row
Foreign key	Reference to another table’s primary key
Referential integrity	Guarantee that foreign keys are valid
One-to-many	Foreign key on the “many” side
Many-to-many	Junction table with two foreign keys
SQL	Standard query language for relational databases

Key takeaways:

• A relational database stores data in tables that are linked by keys. The word “relational” refers to the mathematical concept of a relation—a table of values. The relationships between tables are constraints that connect these relations .
• A table has rows and columns. The columns define the structure—the fields and their types. The rows contain the data—one row per instance of the entity. Every row in a table has the same set of columns .
• A primary key uniquely identifies each row. No two rows can have the same primary key, and the primary key cannot be null. Every table should have a primary key .
• A foreign key refers to the primary key of another table. It creates a relationship between the two tables and enforces referential integrity. The database rejects any insert or update that would create a dangling reference .
• One-to-many relationships use a foreign key on the “many” side. One customer has many orders. The orders table has a customer_id foreign key that refers to the customers table .
• Many-to-many relationships use a junction table. The junction table has foreign keys to both entities, and its primary key is often the combination of the two foreign keys .
• SQL is the standard query language for relational databases. It was developed by IBM alongside the relational model. It is a declarative language: you specify what you want, not how to get it .

Remember: A relational database is a collection of tables linked by keys. The tables store the data. The primary keys identify the rows. The foreign keys connect the tables. The relationships enforce consistency. SQL queries the data. This model has dominated data management for five decades because it is intuitive, flexible, and powerful. Understanding the table, the row, the column, the primary key, and the foreign key is the foundation for everything that follows.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!