Relational Databases and SQL

Introduction

Most backend applications must store information beyond the lifetime of a single program execution. User accounts, products, orders, payments, messages, and audit records must be stored consistently, retrieved efficiently, and protected from unauthorized access. A relational database provides a structured way to manage this information using tables, relationships, constraints, and a formal query language: Structured Query Language (SQL).

Relational database skills are central to backend development because application logic frequently depends on reliable persistence. A web service may validate a request, insert a row, retrieve related records, update a stock quantity, and commit several changes as one transaction. Poor database design can produce duplicated data, inconsistent records, security vulnerabilities, and slow responses. Good design makes correctness explicit and gives the database responsibility for enforcing important rules.

This chapter develops the foundations needed to design and use relational databases in backend systems. Examples use PostgreSQL-style SQL, although the main concepts apply to MySQL, MariaDB, SQL Server, SQLite, and other relational database management systems (RDBMSs). Syntax varies slightly between systems, so production developers should consult the documentation for their selected platform.

Learning Objectives

By the end of this chapter, you should be able to:

• identify entities, attributes, relationships, and cardinalities;
• design tables using primary keys, foreign keys, and constraints;
• explain normalization up to Third Normal Form (3NF);
• write secure SQL for CRUD operations;
• retrieve related data using joins;
• group, aggregate, and filter results;
• explain transactions and the ACID properties;
• use indexes appropriately and interpret basic query plans;
• describe schema migrations and safe database change practices.

1. The Relational Model

A relational database represents data as relations, commonly displayed as tables. A table contains rows, called tuples, and columns, called attributes. Each column has a defined data type and represents one kind of value. For example, a customers table may contain customer_id, full_name, and email.

An entity is a distinguishable real-world object or concept, such as a customer, product, or order. An attribute describes an entity. A relationship describes how entities are associated. In an online store, a customer places orders, and an order contains products. These concepts are translated into tables and foreign-key references.

A relational schema is the structural definition of a database: its tables, columns, data types, constraints, indexes, views, and relationships. The word schema can also refer to a named namespace within a database, depending on the DBMS.

Keys and relationships

A primary key uniquely identifies each row. It must be unique and non-null. A key may be natural, such as a government-issued identifier, or surrogate, such as an automatically generated integer or UUID. Surrogate keys are often convenient, but business identifiers may still require a UNIQUE constraint.

A foreign key is a column or group of columns whose values refer to a candidate key in another table. It maintains referential integrity. If orders.customer_id references customers.customer_id, an order cannot normally refer to a customer that does not exist.

Common relationship cardinalities include:

A many-to-many relationship must be decomposed into an associative or junction table. For example, order_items(order_id, product_id, quantity, unit_price) records which products occur in each order. Its composite primary key may be (order_id, product_id) when a product can occur only once per order.

2. Designing a Schema

Schema design begins with requirements rather than SQL syntax. Identify the facts the system must store, the operations it must support, and the rules that must always hold. A useful design process is:

1. Identify entities and important events.
2. List attributes and their data types.
3. Identify candidate keys.
4. Define relationships and cardinalities.
5. Select constraints.
6. Normalize the design.
7. Consider workload, security, and indexing.

For a small store, a normalized design might include:

• customers(customer_id, full_name, email);
• products(product_id, name, price, stock_quantity);
• orders(order_id, customer_id, ordered_at, status);
• order_items(order_id, product_id, quantity, unit_price).

The unit_price in order_items is intentional. Although the current product price is stored in products, an order must preserve the price charged at the time of purchase. This is a historical fact, not unnecessary duplication.

Data types

Choose types according to meaning and required precision. Use integer types for counts and identifiers where appropriate. Use date for calendar dates and timestamp types for moments in time. Monetary values should generally use NUMERIC or an integer number of the smallest currency unit; binary floating-point types can introduce rounding problems. Text columns should have suitable length or use unrestricted text when the DBMS supports it efficiently.

Null is not the same as zero, an empty string, or false. NULL means that a value is unknown, missing, or not applicable. Comparisons with null use three-valued logic: true, false, or unknown. Therefore, use IS NULL and IS NOT NULL, not = NULL.

Constraints

Constraints express rules in the database:

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    ordered_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))
);

CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

NOT NULL requires a value. UNIQUE prevents duplicate values. CHECK enforces a predicate for each row. DEFAULT supplies a value when one is omitted. Foreign-key actions such as ON DELETE CASCADE must be chosen carefully: cascading deletion may be appropriate for dependent line items but dangerous for legally required records.

3. Normalization

Normalization is a method for reducing undesirable redundancy and update anomalies. It does not mean that every table must be maximally decomposed. A practical design balances integrity, clarity, and performance.

A table is in First Normal Form (1NF) when each field contains an atomic value rather than a repeating list or nested group. A comma-separated list of product IDs in an order column violates this principle because individual products cannot be reliably constrained or queried.

Second Normal Form (2NF) requires 1NF and that every non-key attribute depend on the whole candidate key, not merely part of a composite key. This matters mainly when a table has a composite key. In order_items(order_id, product_id, quantity), quantity depends on both order and product, so it is appropriate there. A customer name depending only on customer_id would not belong in this table.

Third Normal Form (3NF) requires 2NF and that non-key attributes do not depend on other non-key attributes. For example, storing department_id and department_name in an employee table can create a transitive dependency: employee → department_id → department_name. Separating departments prevents inconsistent department names.

Normalization reduces insertion, update, and deletion anomalies. However, controlled denormalization may be justified for reporting or performance when the duplicated value is governed by a clear synchronization strategy. The reason for denormalization should be documented and tested.

4. SQL Categories and CRUD

SQL is commonly discussed in categories:

• DDL (Data Definition Language): CREATE, ALTER, and DROP;
• DML (Data Manipulation Language): INSERT, UPDATE, and DELETE;
• DQL (Data Query Language): principally SELECT;
• transaction control: BEGIN, COMMIT, and ROLLBACK;
• permission control: commands such as GRANT and REVOKE.

Create and read

INSERT INTO customers (full_name, email)
VALUES ('Amina Patel', '[email protected]')
RETURNING customer_id;

SELECT customer_id, full_name, email
FROM customers
WHERE email = '[email protected]';

Always specify columns in an INSERT; relying on physical column order makes code fragile. Use RETURNING where supported to obtain generated identifiers without issuing a second query.

Update and delete

UPDATE products
SET price = price * 1.05
WHERE product_id = 17;

DELETE FROM customers
WHERE customer_id = 42;

An UPDATE or DELETE without an appropriate WHERE clause can affect every row. Professional code checks the affected-row count and uses a transaction when the operation is consequential.

Secure query construction

Never construct SQL by concatenating untrusted input:

"SELECT ... WHERE email = '" + userInput + "'"

This permits SQL injection. Use parameterized queries or prepared statements:

SELECT customer_id, full_name
FROM customers
WHERE email = $1;

The application supplies the email as a parameter separately from the SQL text. Parameterization protects values, but identifiers such as table names cannot usually be bound in the same way; dynamic identifiers must be selected from a trusted allow-list. Input validation, least-privilege database accounts, secret management, and careful error handling provide additional protection.

5. Joins and Query Composition

A join combines rows from tables using a related condition.

SELECT o.order_id, c.full_name, o.ordered_at, o.status
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'paid';

An inner JOIN returns only matching rows. A LEFT JOIN returns every row from the left table and nulls for missing matches:

SELECT c.customer_id, c.full_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;

This is useful for finding customers who have no orders, but conditions on the right table must be placed carefully. A condition in WHERE can remove null-extended rows and effectively turn a left join into an inner join.

A many-to-many query requires two joins:

SELECT o.order_id, p.name, oi.quantity, oi.unit_price
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.order_id = $1;

Joins can multiply rows. If one customer has three orders and each order has four items, joining all three tables may produce twelve rows for that customer. This is correct relational behavior, but aggregation must account for the resulting grain of the query.

6. Aggregation and Reporting

Aggregate functions summarize rows. Common functions include COUNT, SUM, AVG, MIN, and MAX.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

WHERE filters rows before grouping. HAVING filters groups after aggregation:

SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

A total order value can be calculated from line items:

SELECT oi.order_id,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM order_items AS oi
GROUP BY oi.order_id;

The expression is valid because quantity is a count of units and unit price is currency per unit, producing currency. In real systems, taxes, discounts, shipping, refunds, and currency conversion require explicit business rules. Do not assume that a simple sum represents the legally or financially correct total.

COUNT(*) counts rows, while COUNT(column) excludes rows where that column is null. SUM and AVG generally ignore null values. These behaviors must be considered when interpreting reports.

7. Transactions and ACID

A transaction is a logical unit of work. For example, placing an order may insert an order, insert its items, and reduce stock. These actions should not leave the database half-completed.

ACID describes four important properties:

• Atomicity: all operations in the transaction succeed or none do.
• Consistency: constraints and application rules remain satisfied.
• Isolation: concurrent transactions do not produce prohibited interference.
• Durability: committed changes survive failures, subject to the DBMS's durability guarantees.

A typical transaction is:

BEGIN;

INSERT INTO orders (customer_id, status)
VALUES ($1, 'pending')
RETURNING order_id;

UPDATE products
SET stock_quantity = stock_quantity - $2
WHERE product_id = $3
  AND stock_quantity >= $2;

-- Application verifies that one row was updated.
COMMIT;

If the stock update affects zero rows, the application should roll back rather than create an order that cannot be fulfilled. In a concurrent environment, the update condition and transaction isolation help prevent overselling. Row locks, such as SELECT ... FOR UPDATE, may be appropriate when several related decisions depend on the same row.

Isolation levels vary by DBMS. Read Committed commonly prevents dirty reads but allows some changes between statements. Repeatable Read provides a stronger consistent view, while Serializable aims to produce results equivalent to some serial execution, usually at higher cost and with possible transaction retries. Applications using serializable transactions must be prepared to retry serialization failures.

Transactions should be short enough to limit lock contention. External network calls should generally not be held inside a database transaction unless a carefully designed coordination mechanism is required.

8. Indexes and Query Optimization

An index is an auxiliary data structure that helps the DBMS locate rows without scanning an entire table. Indexes are especially useful for columns frequently used in selective WHERE conditions, join predicates, or ordering. A common B-tree index is suitable for equality and range searches:

CREATE INDEX idx_orders_customer_ordered_at
ON orders (customer_id, ordered_at);

A composite index is ordered by its leading columns. The index above is useful for searches beginning with customer_id, and often for that condition combined with an ordered_at range. It is not generally equivalent to separate indexes for every possible query.

Indexes consume storage and slow inserts, updates, and deletes because index entries must also be maintained. Avoid indexing every column. A primary key and many unique constraints create indexes automatically, but exact behavior depends on the DBMS.

Use the query planner to investigate performance:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, ordered_at
FROM orders
WHERE customer_id = $1
ORDER BY ordered_at DESC;

EXPLAIN describes the planned operations; ANALYZE executes the query and reports observed timing and row counts. It should be used carefully on production systems because it executes the statement. Compare estimated and actual rows, identify expensive scans or joins, and test with realistic data. Query performance depends on data distribution, statistics, hardware, concurrency, and the query itself.

Avoid wrapping indexed columns in functions when a range predicate can express the same condition. For example, instead of extracting a date from every timestamp, use a half-open interval such as ordered_at >= $1 AND ordered_at < $2, with appropriately calculated boundary values. Also avoid selecting unnecessary columns and retrieve only the rows needed.

9. Migrations and Operational Practice

A migration is a versioned, repeatable change to database structure or controlled data. A migration tool records which changes have been applied. Typical changes include creating a table, adding a nullable column, adding an index, and later enforcing a constraint.

Safe deployment often follows an expand-and-contract pattern:

1. Expand: add the new structure without breaking the old application.
2. Migrate: backfill or dual-write data as needed.
3. Switch: deploy application code that uses the new structure.
4. Contract: remove obsolete columns or behavior after verification.

Adding a non-null column to a large populated table may require a staged approach. Index creation may need a concurrent or online option, depending on the DBMS. Every migration should be tested against representative data, monitored, and accompanied by a rollback or recovery plan. A rollback is not always a simple inverse operation, particularly after destructive data changes.

Backups are not a substitute for tested restoration. Operational readiness includes backup frequency, retention, encryption, access control, recovery objectives, and periodic restore exercises.

10. Applied Case: A Course Registration Service

Background

Consider an illustrative university course-registration service. Students enroll in course sections. A section has a capacity, and a student may not enroll in the same section twice. Registration must be reliable when many students attempt to enroll simultaneously.

Problem

A naive design stores a student's courses as a comma-separated string and checks capacity in application code before inserting a registration. This design makes searching difficult, cannot enforce uniqueness reliably, and may allow two concurrent requests to exceed capacity.

Analysis

The entities are students, courses, sections, and enrollments. A course can have many sections, and a student can enroll in many sections. The many-to-many relationship is represented by enrollments. The business rules are:

• a student and section pair must be unique;
• a section cannot exceed its capacity;
• a student cannot enroll after a registration deadline;
• deletion of a student should not silently destroy legally required enrollment history.

Approach

A suitable core design is:

CREATE TABLE sections (
    section_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_code TEXT NOT NULL,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    enrolled_count INTEGER NOT NULL DEFAULT 0 CHECK (enrolled_count >= 0),
    registration_deadline TIMESTAMPTZ NOT NULL
);

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL REFERENCES students(student_id),
    section_id BIGINT NOT NULL REFERENCES sections(section_id),
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, section_id)
);

Registration can be protected by a transaction. One approach locks the section, checks the deadline and count, inserts the enrollment, and increments the count. The unique primary key prevents duplicate registration even if the application receives repeated requests. The transaction rolls back if any rule fails.

A more compact capacity update can be written as:

BEGIN;

UPDATE sections
SET enrolled_count = enrolled_count + 1
WHERE section_id = $1
  AND CURRENT_TIMESTAMP < registration_deadline
  AND enrolled_count < capacity;

-- Verify exactly one row changed.
INSERT INTO enrollments (student_id, section_id)
VALUES ($2, $1);

COMMIT;

The application must handle a duplicate-key error and roll back when the student is already enrolled. In production, the precise transaction behavior, error handling, and isolation level should be tested under concurrent load.

Outcome and learning points

The normalized design makes relationships explicit, constraints enforce important rules, and transactions coordinate capacity changes. The case also illustrates that database correctness is a collaboration between schema design, SQL statements, transaction control, and application error handling. The database should enforce invariants that must remain true regardless of which service or script writes the data.

11. Governance, Ethics, and Contemporary Practice

Databases often contain personal, financial, educational, or health information. Developers should collect only necessary data, define retention rules, restrict access by role, encrypt sensitive data in transit and at rest where appropriate, and maintain audit trails for significant actions. Privacy laws may impose requirements concerning lawful processing, access, correction, deletion, breach notification, and data residency. Legal obligations vary by jurisdiction; technical teams should involve qualified governance and legal personnel.

Use separate accounts for applications, migrations, reporting, and administration. Grant the minimum permissions required. Avoid placing secrets in source code or logs. Do not expose raw database errors to users because they may reveal schema details or sensitive values.

Modern systems increasingly combine relational databases with JSON columns, full-text search, extensions, and analytical replicas. JSON can be useful for genuinely variable attributes, but it should not replace relational structure when fields require constraints, joins, or frequent querying. Change-data capture, managed cloud databases, automated failover, and observability tools have also become common. AI-assisted SQL generation can accelerate development, but generated queries require human review for correctness, authorization, privacy, performance, and injection risk. An AI tool should never be given confidential production data merely to obtain query help.

Best Practices Checklist

• Model entities and relationships before writing queries.
• Give every important table a stable primary key.
• Use foreign keys and constraints to enforce invariants.
• Treat null semantics explicitly.
• Normalize first; denormalize only for a documented reason.
• Parameterize all user-supplied values.
• Use transactions for related changes.
• Check affected-row counts and handle constraint errors.
• Add indexes based on measured access patterns.
• Inspect query plans with realistic data.
• Version schema changes through tested migrations.
• Apply least privilege and protect sensitive information.
• Test concurrency, backup restoration, and failure recovery.

Chapter Summary

Relational databases organize structured information into tables connected by keys and relationships. Effective schema design begins with entities, attributes, cardinalities, and business rules. Primary keys identify rows; foreign keys preserve referential integrity; NOT NULL, UNIQUE, CHECK, and default constraints make valid states explicit. Normalization reduces redundancy and update anomalies, while carefully justified denormalization can support performance or historical requirements.

SQL supports schema definition, CRUD operations, joins, aggregation, and reporting. Parameterized queries are essential for preventing SQL injection. Transactions provide atomic, consistent, isolated, and durable units of work, especially when concurrent requests modify related records. Indexes can improve retrieval but impose maintenance costs, so their value should be evaluated through query plans and realistic measurements. Migrations, backups, least privilege, privacy controls, and tested recovery procedures are essential parts of professional database operation.

Key Terms

Entity: A distinguishable object or concept represented in a data model.

Attribute: A property describing an entity.

Relation: The formal relational-model term corresponding broadly to a table.

Primary key: A unique, non-null identifier for a row.

Foreign key: A reference to a key in another table.

Candidate key: A minimal set of attributes capable of uniquely identifying a row.

Cardinality: The numerical nature of a relationship, such as one-to-many.

Constraint: A rule enforced by the database.

Null: A marker representing missing, unknown, or inapplicable information.

Normalization: The structured decomposition of data to reduce redundancy and anomalies.

CRUD: Create, read, update, and delete operations.

Join: An operation that combines rows from related tables.

Aggregation: Summarizing multiple rows with functions such as SUM or COUNT.

Transaction: A logical unit of database work.

ACID: Atomicity, consistency, isolation, and durability properties.

Index: An auxiliary structure that accelerates selected data access patterns.

Query plan: The execution strategy selected by a database optimizer.

Migration: A versioned change to database structure or controlled data.

SQL injection: An attack caused by treating untrusted input as SQL code.

Referential integrity: The requirement that foreign-key references remain valid.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why is a junction table required for a many-to-many relationship?
3. What problem does Third Normal Form address?
4. Why should application code use parameterized SQL?
5. Name and briefly define the four ACID properties.

Long Answer Questions

1. Explain how you would transform an unnormalized order table into a normalized design involving customers, orders, products, and order items.
2. Compare inner joins and left joins, including a situation in which each is appropriate.
3. Explain how constraints and transactions work together to protect database correctness.
4. Discuss the benefits and costs of indexes and describe how EXPLAIN can assist optimization.
5. Describe an expand-and-contract strategy for deploying a breaking schema change.

Analytical / Application Questions

1. Design tables and constraints for a library in which members borrow books, each physical copy is distinct, and a copy cannot be loaned to two members at the same time.
2. A product has a stock quantity of 1, and two requests arrive concurrently. Explain how a transaction and conditional update can prevent both requests from successfully selling the product.
3. A report joining customers, orders, and order items gives totals twice as large as expected. Analyze the likely cause and propose a corrected aggregation strategy.

Multiple Choice Questions with Answers

Question 1

Which constraint ensures that a foreign-key value refers to an existing row in another table?

A. CHECK

B. FOREIGN KEY

C. DEFAULT

D. INDEX

Correct Answer: B. FOREIGN KEY

Question 2

Which table design correctly represents a many-to-many relationship between orders and products?

A. A comma-separated product list in orders

B. A product_id column only in orders

C. A junction table containing order_id and product_id

D. A second products table for every order

Correct Answer: C. A junction table containing order_id and product_id

Question 3

Why should user-supplied values be passed through parameterized queries?

A. To prevent SQL injection

B. To create indexes automatically

C. To eliminate the need for constraints

D. To guarantee every query uses a transaction

Correct Answer: A. To prevent SQL injection

Question 4

Which clause filters groups after aggregation?

A. WHERE

B. ORDER BY

C. HAVING

D. FROM

Correct Answer: C. HAVING

Question 5

What does atomicity mean in a transaction?

A. Committed data is stored permanently

B. All operations succeed or none do

C. Concurrent queries never wait

D. Every table has a primary key

Correct Answer: B. All operations succeed or none do

Question 6

A table has columns student_id, section_id, and enrolled_at, with a composite primary key of student_id and section_id. Which normal-form principle requires attributes to depend on the whole key?

A. First Normal Form

B. Second Normal Form

C. Third Normal Form

D. Fourth Normal Form

Correct Answer: B. Second Normal Form

Question 7

What is a principal cost of adding an index?

A. It prevents all table scans

B. It makes foreign keys unnecessary

C. It requires maintenance during writes

D. It removes the need for query planning

Correct Answer: C. It requires maintenance during writes

Question 8

Which query correctly tests whether email is missing?

A. WHERE email = NULL

B. WHERE email == NULL

C. WHERE email IS NULL

D. WHERE email LIKE NULL

Correct Answer: C. WHERE email IS NULL

Question 9

What is the main purpose of a migration?

A. To version and apply database schema or data changes

B. To replace all foreign keys with indexes

C. To convert every relational table into JSON

D. To execute only read-only queries

Correct Answer: A. To version and apply database schema or data changes

Question 10

In an order-items table, why might unit_price be stored even when products already have a price column?

A. To preserve the historical price charged for that order

B. To remove the need for a product identifier

C. To guarantee prices are always current

D. To make a foreign key unnecessary

Correct Answer: A. To preserve the historical price charged for that order