Relational Database Concepts and the PostgreSQL Environment

What Is a Relational Database?

A relational database organizes data into tables—formally called relations—where every piece of information has a precise location defined by a row and a column. The model was described by E.F. Codd in 1970 and remains the dominant approach for storing structured data in web and software applications today.

Each table represents one category of data, such as `users`, `orders`, or `products`. A single row in a table is called a tuple; it represents one record. A single column is called an attribute; it describes one property of every record in the table. For example, a `users` table might have attributes like `user_id`, `email`, and `created_at`.

Keys and Relationships

Relational databases stay consistent through keys. A primary key is a column (or set of columns) whose value uniquely identifies every tuple in a table—no two rows may share the same primary key value, and it may never be NULL. A foreign key is a column in one table that references the primary key of another table, creating a relationship between the two. If a row in `orders` contains a `user_id` column that points to the `users` table, that `user_id` is a foreign key. This link is how relational databases avoid repeating the same customer name and address inside every order record.

Enforcing these keys is part of what a database management system does automatically, preventing orphan records and keeping data accurate across an entire application.

The Database Management System (DBMS)

A DBMS is the software layer that sits between raw data files on disk and the applications or people who need that data. It handles reading, writing, searching, securing, and recovering data. A relational DBMS (RDBMS) specifically implements the relational model and responds to queries written in SQL (Structured Query Language).

Every RDBMS has several core components:

• Query processor — parses and executes SQL statements.
• Storage engine — manages how data is physically arranged on disk.
• Transaction manager — enforces ACID properties (Atomicity, Consistency, Isolation, Durability) so that a crash or error never leaves data in a half-written state.
• Catalog (system catalog) — a set of internal tables that store metadata: what tables exist, what columns they have, who owns them, and what indexes are defined.
• Security layer — controls which users may read or change which objects.

Schemas and Catalogs

A schema is a named namespace inside a database that groups related tables, views, and other objects together. In PostgreSQL the default schema is called `public`. A large web application might place tables used by the billing module in a `billing` schema and tables used by the authentication module in an `auth` schema, keeping everything organized and reducing naming conflicts.

The catalog is where PostgreSQL itself stores its own data about the database—essentially a database about the database. In PostgreSQL this lives in the `pg_catalog` schema. You will query it indirectly every time you run `\d` in psql to describe a table.

PostgreSQL Architecture

PostgreSQL uses a multi-process architecture. When the server starts, a master process called postmaster listens for incoming connections. For each client connection it forks a separate backend process dedicated to that session. Shared memory holds buffers that all backend processes read from and write to, coordinating through a set of background utility processes responsible for tasks like writing data to disk (the background writer), managing transaction IDs (autovacuum), and archiving logs.

Data is stored in a directory called the data cluster, typically referenced by the environment variable `PGDATA`. Inside it you will find subdirectories for each database, configuration files, and the write-ahead log (WAL), which records every change before it is applied to the main data files—protecting against data loss if the system crashes.

Installing PostgreSQL and Connecting

On a Linux lab workstation you install PostgreSQL through the package manager for your distribution. On Windows you use the graphical installer from the PostgreSQL project website. During installation you set a password for the built-in superuser account named postgres.

After installation you will edit pg_hba.conf (the host-based authentication configuration file) to control how clients authenticate. For a local development environment, setting the authentication method for local connections to `trust` allows any local OS user to connect without a password—convenient for lab work, but never appropriate for a production or internet-facing server.

You can connect in two ways:

1. psql — the command-line client. Run `psql -U postgres` to open an interactive SQL session. Type `\l` to list databases and `\q` to quit.

2. pgAdmin 4 — a graphical tool that lets you browse objects, run queries, and view data through a web-based interface running in your browser.

Safety and Good Practices

Database work does not involve electrical or physical hazards typical of other trades, but it carries its own risks:

• Data integrity: Never test destructive SQL (`DROP`, `DELETE`, `TRUNCATE`) on a production database. Always work in a dedicated lab or development database.
• Credential hygiene: Do not leave `pg_hba.conf` set to `trust` outside a local lab. Use strong passwords and the `md5` or `scram-sha-256` authentication method in any shared or networked environment.
• Backups before changes: Before modifying configuration files, copy the original (`cp pg_hba.conf pg_hba.conf.bak`). A misconfigured file can lock everyone out of the database.
• Least privilege: Create application users with only the permissions they need. Do not connect web applications as the `postgres` superuser.