Relational Database Fundamentals

Overview

• What you’ll learn: The relational data model, core SQL operations (DDL/DML/DCL), ACID transaction properties, normalization forms, indexing fundamentals, and criteria for selecting an RDBMS on Ubuntu Server.
• Prerequisites: Module 1 (Linux Fundamentals), basic command-line proficiency
• Estimated reading time: 20 minutes

Introduction

Relational databases have been the backbone of enterprise data management for over four decades. From financial transactions and customer records to application session state and content management systems, relational database management systems (RDBMS) provide the structured, consistent, and durable storage that production workloads demand.

Before diving into the installation and administration of specific database servers such as MySQL and PostgreSQL, it is essential to understand the theoretical foundation upon which they are built. The relational model, first proposed by Edgar F. Codd in 1970, organizes data into tables (relations), enforces integrity through constraints, and provides a powerful query language — SQL — for manipulating that data.

In this lesson you will learn the core concepts of relational databases, understand how ACID properties guarantee data reliability, explore normalization to eliminate redundancy, and survey the RDBMS options available on Ubuntu Server so that you can make an informed choice for your workloads.

The Relational Model

At its core, the relational model represents data as a collection of relations (tables). Each relation consists of tuples (rows) and attributes (columns). Every attribute has a defined data type and an associated domain — the set of permissible values.

Tables, Rows, and Columns

A table is the fundamental storage unit. Each row represents a single record, and each column represents a specific piece of information about that record. For example, a users table might contain columns for id, username, email, and created_at.

-- Example: creating a simple users table
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    username   VARCHAR(64) NOT NULL UNIQUE,
    email      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Primary Keys and Foreign Keys

A primary key uniquely identifies each row in a table. It must be unique and not null. A foreign key is a column (or set of columns) in one table that references the primary key of another table, establishing a relationship between the two.

-- Foreign key example: orders references users
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(id),
    total    DECIMAL(10,2) NOT NULL,
    status   VARCHAR(20) DEFAULT 'pending'
);

Foreign keys enforce referential integrity — you cannot insert an order for a user that does not exist, and you cannot delete a user who has outstanding orders (unless cascading rules are defined).

SQL Foundations

Structured Query Language (SQL) is the standard interface for interacting with relational databases. SQL statements are grouped into three categories:

• DDL (Data Definition Language): CREATE, ALTER, DROP — define and modify schema objects.
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE — read and write data.
• DCL (Data Control Language): GRANT, REVOKE — manage access permissions.

Essential DML Operations

-- Insert a row
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- Query with a filter
SELECT id, username, email FROM users WHERE username = 'alice';

-- Update a row
UPDATE users SET email = 'alice@newdomain.com' WHERE username = 'alice';

-- Delete a row
DELETE FROM users WHERE username = 'alice';

Joins

Joins combine rows from two or more tables based on a related column. The most common types are:

• INNER JOIN: Returns only rows that have matching values in both tables.
• LEFT JOIN: Returns all rows from the left table plus matched rows from the right table (nulls where no match).
• RIGHT JOIN: The reverse of LEFT JOIN.
• FULL OUTER JOIN: Returns all rows from both tables, with nulls where there is no match.

-- Inner join: users with their orders
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

ACID Properties

Reliable databases guarantee four properties for every transaction, collectively known as ACID:

• Atomicity: A transaction is all-or-nothing. If any statement within the transaction fails, the entire transaction is rolled back.
• Consistency: A transaction moves the database from one valid state to another. All constraints, triggers, and rules are satisfied before and after the transaction.
• Isolation: Concurrent transactions do not interfere with each other. The database provides isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) to balance consistency with performance.
• Durability: Once a transaction is committed, the changes survive system crashes. This is typically achieved through write-ahead logging (WAL).

-- Transaction example
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- If anything fails, roll back
ROLLBACK;

Normalization

Normalization is the process of organizing tables to reduce data redundancy and improve integrity. The most commonly referenced forms are:

• First Normal Form (1NF): Every column contains atomic (indivisible) values; no repeating groups.
• Second Normal Form (2NF): 1NF plus every non-key column is fully dependent on the entire primary key (eliminates partial dependencies).
• Third Normal Form (3NF): 2NF plus no non-key column depends on another non-key column (eliminates transitive dependencies).

In practice, most production schemas aim for 3NF. Denormalization — intentionally introducing redundancy — is sometimes used for read-heavy workloads where join performance becomes a bottleneck.

Indexing Fundamentals

An index is a data structure (typically a B-tree or hash) that allows the database to locate rows without scanning the entire table. Indexes dramatically speed up SELECT queries but add overhead to INSERT, UPDATE, and DELETE operations because the index must be maintained.

-- Create an index on the email column
CREATE INDEX idx_users_email ON users (email);

-- Composite index on two columns
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Examine query execution plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

As a general rule, index columns that appear frequently in WHERE clauses, JOIN conditions, and ORDER BY clauses. Avoid over-indexing — each additional index consumes disk space and slows down write operations.

Choosing an RDBMS on Ubuntu Server

Ubuntu Server provides official packages for several relational databases. The two most popular open-source options are:

• MySQL / MariaDB: Widely used in web applications (LAMP/LEMP stacks). MySQL is developed by Oracle; MariaDB is a community-maintained fork. Both use the apt packages mysql-server or mariadb-server.
• PostgreSQL: Known for standards compliance, advanced features (JSONB, CTEs, window functions, full-text search), and extensibility. Available via the postgresql package.

# Check available MySQL packages
$ apt-cache search mysql-server

# Check available PostgreSQL packages
$ apt-cache search postgresql | head -20

When selecting a database, consider factors such as: the application framework’s default support, required SQL feature set, replication and clustering needs, community and commercial support, and your team’s existing expertise.

Key Takeaways

• The relational model organizes data into tables with rows and columns, enforcing integrity through primary keys, foreign keys, and constraints.
• SQL is divided into DDL (schema), DML (data), and DCL (permissions) categories.
• ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee reliable transactions.
• Normalization (1NF through 3NF) reduces redundancy; denormalization is a deliberate trade-off for read performance.
• Indexes accelerate reads at the cost of additional write overhead — index strategically.
• Ubuntu Server offers MySQL/MariaDB and PostgreSQL as the primary open-source RDBMS options; choose based on feature needs, ecosystem, and team expertise.

What’s Next

In Lesson 41, you will install and configure MySQL Server on Ubuntu, set up secure authentication, create databases and users, and learn essential administration commands for day-to-day operations.

-- 範例：建立簡單的使用者表
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    username   VARCHAR(64) NOT NULL UNIQUE,
    email      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 外鍵範例：orders 引用 users
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(id),
    total    DECIMAL(10,2) NOT NULL,
    status   VARCHAR(20) DEFAULT 'pending'
);

-- 插入一列
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- 帶篩選條件的查詢
SELECT id, username, email FROM users WHERE username = 'alice';

-- 更新一列
UPDATE users SET email = 'alice@newdomain.com' WHERE username = 'alice';

-- 刪除一列
DELETE FROM users WHERE username = 'alice';

-- 在 email 欄位上建立索引
CREATE INDEX idx_users_email ON users (email);

-- 檢查查詢執行計畫
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

# 檢查可用的 MySQL 套件
$ apt-cache search mysql-server

# 檢查可用的 PostgreSQL 套件
$ apt-cache search postgresql | head -20

-- 例：シンプルなユーザーテーブルの作成
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    username   VARCHAR(64) NOT NULL UNIQUE,
    email      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 外部キーの例：orders が users を参照
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(id),
    total    DECIMAL(10,2) NOT NULL,
    status   VARCHAR(20) DEFAULT 'pending'
);

-- 行の挿入
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- フィルタ付きクエリ
SELECT id, username, email FROM users WHERE username = 'alice';

-- 行の更新
UPDATE users SET email = 'alice@newdomain.com' WHERE username = 'alice';

-- 行の削除
DELETE FROM users WHERE username = 'alice';

-- email 列にインデックスを作成
CREATE INDEX idx_users_email ON users (email);

-- クエリ実行計画の確認
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

# 利用可能な MySQL パッケージを確認
$ apt-cache search mysql-server

# 利用可能な PostgreSQL パッケージを確認
$ apt-cache search postgresql | head -20