Relational Database Concepts

Learning Focus

Use this lesson to understand Relational Database Concepts with practical syntax and examples before diving into PostgreSQL-specific features.

What is a Relational Database?

Definition:

• A database that organizes data into tables (relations) with rows and columns
• Based on the relational model proposed by Edgar F. Codd (1970)
• Data relationships are maintained through keys

Key Characteristics:

• Data is stored in a structured, tabular format
• Relationships between data elements are explicitly defined
• Uses SQL (Structured Query Language) for data manipulation
• Maintains data integrity through constraints and rules

Core Components

a) Tables (Relations)

• Structure:
  • Columns: Represent attributes (e.g., customer_name, order_date)
  • Rows: Represent individual records (tuples)

Example: Customers Table

| customer_id | name     | email               |
|-------------|----------|---------------------|
| 1           | Alice    | alice@example.com   |
| 2           | Bob      | bob@company.org     |

b) Keys

• Primary Key:
  • Unique identifier for each record (e.g., customer_id)
  • Cannot contain NULL values
  • PostgreSQL supports GENERATED ALWAYS AS IDENTITY for auto-incrementing primary keys
• Foreign Key:
  • Field that links to another table's primary key
  • Maintains referential integrity
  • PostgreSQL enforces foreign keys strictly by default

c) Relationships

1. One-to-Many (Most common)
   • e.g., One customer can have many orders
2. Many-to-Many
   • e.g., Students and Courses (requires junction table)
3. One-to-One
   • e.g., User and UserProfile tables

Database Normalization

Process of structuring data to:

• Eliminate data redundancy
• Ensure data dependencies make sense
• Improve data integrity

Common Normal Forms:

1. 1NF: Atomic values, no repeating groups
2. 2NF: No partial dependencies (every non-key column depends on the whole primary key)
3. 3NF: No transitive dependencies (non-key columns depend only on the primary key)

Example of Normalization:

Before 1NF (Denormalized):
| order_id | items                     |
|----------|---------------------------|
| 1001     | Shirt, Jeans, Hat         |

After 1NF:
| order_id | item    |
|----------|---------|
| 1001     | Shirt   |
| 1001     | Jeans   |
| 1001     | Hat     |

Example — Applying 3NF in PostgreSQL:

-- Separate customer data from order data
CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  email       text NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  order_date  date   NOT NULL DEFAULT CURRENT_DATE,
  total       numeric(10,2) NOT NULL
);

ACID Properties

Guarantees database transactions are processed reliably:

• Atomicity: All-or-nothing transactions — if any part fails, everything rolls back
• Consistency: The database moves from one valid state to another
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Committed transactions persist even after a crash

PostgreSQL achieves ACID compliance through its Write-Ahead Log (WAL) and Multi-Version Concurrency Control (MVCC).

SQL Overview

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): BEGIN, COMMIT, ROLLBACK

Advantages of Relational Databases

• Data integrity through constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY)
• Flexible and powerful query capabilities via SQL
• Mature technology with strong ANSI/ISO standards
• ACID compliance for reliable transactions
• Fine-grained access control with roles and privileges

Common RDBMS Systems

System	License	Notable Strength
PostgreSQL	Open Source (PostgreSQL License)	Standards compliance, extensibility
MySQL	Open Source (GPL) + Commercial	Web application popularity
Oracle Database	Commercial	Enterprise features
Microsoft SQL Server	Commercial	Windows ecosystem integration
SQLite	Public Domain	Embedded, serverless

Relational vs. NoSQL Databases

Feature	Relational	NoSQL
Data Model	Table-based (rows + columns)	Document / Graph / Key-Value / Column-family
Schema	Fixed, predefined	Flexible, schema-on-read
Scalability	Primarily vertical	Built for horizontal
Transactions	ACID compliant	Varies (often BASE model)
Best For	Complex queries, data integrity	High-velocity, flexible schemas
Query Language	SQL (standardized)	Varies per system

Visual Learning Aids

Table Relationships Diagram

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    CUSTOMERS {
        bigint customer_id PK
        text name
        text email
    }
    ORDERS {
        bigint order_id PK
        bigint customer_id FK
        date order_date
        numeric total
    }

Normalization Process Flow

flowchart LR
    A[Raw Data] --> B[1NF: Atomic values]
    B --> C[2NF: Remove partial deps]
    C --> D[3NF: Remove transitive deps]

Learning Check

Questions:

1. Why is a primary key important in a relational database?
2. What problem does normalization solve?
3. How does a foreign key differ from a primary key?
4. When would you choose a relational database over NoSQL?

Exercises:

1. Identify primary and foreign keys in a sample database schema
2. Normalize a denormalized dataset to 3NF
3. Draw an ER diagram for a simple library system (books, authors, members, loans)

Real-World Examples

1. Banking systems — account transactions with strict ACID guarantees
2. E-commerce platforms — orders, inventory, and customer relationships
3. Hospital management — patient records, appointments, prescriptions
4. SaaS applications — multi-tenant data isolation with schemas

Key Takeaways

• Relational databases organize data in tables with defined relationships
• Keys (primary and foreign) maintain data integrity and relationships
• Normalization reduces redundancy and improves data structure
• ACID properties ensure reliable transactions
• SQL is the standard language for interaction
• PostgreSQL is one of the most standards-compliant RDBMS implementations

This foundation will help you understand why PostgreSQL works the way it does before learning how to use it.

Concept Map

flowchart LR
    A[Relational Model] --> B[Tables & Keys]
    B --> C[Normalization]
    C --> D[ACID Properties]
    D --> E[SQL Querying]
    E --> F[PostgreSQL Practice]

Common Pitfalls

Pitfall	Consequence	Prevention
Skipping normalization	Duplicated data, update anomalies	Design tables in at least 3NF before writing queries
Ignoring NULL behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Forgetting foreign key constraints	Orphaned records break data integrity	Always define REFERENCES and test with cascading deletes
Using advanced syntax before checking schema	Queries fail due to missing columns or wrong types	Verify structure with \d+ table_name in psql

Quick Reference

-- PostgreSQL: Inspect your environment
\l                     -- list databases
\c your_database       -- connect to a database
\dt                    -- list tables
\d+ your_table         -- describe table structure
SELECT * FROM your_table LIMIT 10;

What's Next

• Next: PostgreSQL Core Concepts — Continue to the next concept with incremental complexity.
• Section Overview — Return to this section index and choose another related lesson.