Relational Database Design Practical

A hands-on exercise for turning operational requirements into a normalized, validated, documented, and optimized SQLite database

Author

Complex Data Insights

Published

Aug 2026

COMPLEX DATA INSIGHTS
CDI Practicals

Hands-on exercises and step-by-step tools for doing, applying, and building evidence.

Relational Database Design Practical

A hands-on exercise for turning operational requirements into a normalized, validated, documented, and optimized SQLite database

AuthorComplex Data Insights
PublishedAug 2026
ID CDI-DATASCIENCE-PRACTICAL-006
Type Practical Product
Audience Students, researchers, analysts, professionals, mentors, and learners developing practical data science skills
Theme Reliable relational design turns operational requirements into normalized structures, explicit relationships, enforced integrity, and maintainable database systems

Practical Overview

A useful database does more than store rows. It represents business rules, prevents invalid data, supports dependable analysis, and remains understandable as requirements change. In this practical, you will turn operational requirements and a flat service-record concept into a normalized SQLite database design.

You will define entities and relationships, document table grain, normalize the model, implement keys and constraints, populate synthetic records, preserve status history, validate integrity, and assess indexes and query plans. SQLite is used because it is portable and requires no database server; the design principles apply to PostgreSQL, MySQL, SQL Server, and other relational systems.

The dataset is synthetic and intended only for teaching and workflow testing. Do not treat any record as real customer, employee, or organizational data.

Learning Outcomes

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

  • translate operational requirements into entities, attributes, and relationships;
  • distinguish conceptual, logical, and physical database design;
  • normalize a flat structure to reduce duplication and update anomalies;
  • define primary keys, foreign keys, uniqueness rules, checks, and nullability;
  • implement and populate a relational database with SQLite;
  • create a reporting view that demonstrates how the schema supports downstream use;
  • choose indexes for documented access patterns;
  • use transactions to protect multi-step changes;
  • inspect a query plan and validate database integrity; and
  • document conclusions without overstating what the database proves.

Project Structure

After extracting the project files, the main structure is:

relational-database-design-practical/
├── README.md
├── requirements.txt
├── data/
├── database/
├── results/
│   ├── quality-control/
│   └── tables/
└── scripts/
    ├── bash/
    │   └── run-workflow.sh
    └── python/
        ├── build-database.py
        └── validate-database.py

Tools and Requirements

You need Python 3.10 or later, VS Code with the Python and Jupyter extensions, and the packages listed in requirements.txt. SQLite support is included with Python.

Create and activate a dedicated environment from the project root.

python3 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

On Windows PowerShell, activate with:

.venv\Scripts\Activate.ps1

Run the complete workflow:

bash scripts/bash/run-workflow.sh

Alternatively, run each stage separately:

python scripts/python/build-database.py
python scripts/python/validate-database.py

Scenario and Requirements

A regional service organization needs a database to track customers and their requests. Each customer belongs to one region. Each request has one service category, is submitted by one customer, and may be assigned to one staff member. A request changes status over time, so the current request record and its status history must both be retained.

The database must support the following rules:

  • customer email addresses must be unique;
  • region and category names must be unique;
  • request priority must be Low, Medium, or High;
  • request status must be Submitted, In Progress, Resolved, or Closed;
  • satisfaction scores, when available, must be integers from 1 to 5;
  • resolved timestamps cannot be earlier than submitted timestamps;
  • every request must reference a valid customer and category;
  • staff assignment may be missing while a request waits in a queue; and
  • deleting referenced master data must not silently orphan requests.

Before examining the proposed schema, write down the entities you expect, one identifier for each entity, and the relationships between them.

Step 1: Examine the Flat-Table Risk

An initial spreadsheet might contain one row per request with columns such as:

request_id customer_name customer_email region_name category_name staff_name status submitted_at

This is convenient for a single report but weak as the primary storage design. Customer, region, category, and staff details repeat across many requests. The structure creates three common anomalies:

  • Update anomaly: changing a region or staff name requires multiple row updates;
  • Insertion anomaly: a new service category cannot be recorded until a request exists; and
  • Deletion anomaly: deleting the only request for a category may also remove the only stored description of that category.

Normalization separates facts according to what they describe. The goal is not to create the largest possible number of tables; it is to store each fact in an appropriate place and connect those facts explicitly.

Step 2: Define the Relational Model

The practical uses six tables:

Table Purpose Primary key
regions valid operating regions region_id
customers customer details and region membership customer_id
service_categories controlled service types category_id
staff staff details staff_id
service_requests one row per request request_id
request_status_history timestamped request-status events history_id

The relationships are:

  • one region has many customers;
  • one customer submits many service requests;
  • one category classifies many service requests;
  • one staff member may handle many service requests; and
  • one service request has many status-history events.

The design is broadly in third normal form: repeating groups are removed, non-key attributes describe their table’s key, and descriptive values such as region and category names are not repeated in the request table.

Step 3: Review Keys and Integrity Rules

Surrogate integer primary keys provide stable internal identifiers. Natural candidate keys—such as email addresses and category names—are retained as UNIQUE constraints rather than being used as foreign keys throughout the design.

The core request table is implemented as follows:

CREATE TABLE service_requests (
    request_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    assigned_staff_id INTEGER,
    priority TEXT NOT NULL
        CHECK (priority IN ('Low', 'Medium', 'High')),
    current_status TEXT NOT NULL
        CHECK (current_status IN
            ('Submitted', 'In Progress', 'Resolved', 'Closed')),
    submitted_at TEXT NOT NULL,
    resolved_at TEXT,
    satisfaction_score INTEGER
        CHECK (satisfaction_score BETWEEN 1 AND 5),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (category_id) REFERENCES service_categories(category_id),
    FOREIGN KEY (assigned_staff_id) REFERENCES staff(staff_id),
    CHECK (resolved_at IS NULL OR resolved_at >= submitted_at)
);

NOT NULL is used when a value is required. Assignment, resolution time, and satisfaction may legitimately be unknown, so those fields allow NULL. A missing value is not the same as an empty string or zero.

Step 4: Build the Database

Run:

python scripts/python/build-database.py

The script performs the following work:

  1. creates database/service_operations.sqlite;
  2. enables foreign-key enforcement;
  3. creates tables and constraints;
  4. generates reproducible synthetic records;
  5. inserts records inside a transaction;
  6. creates indexes and a reporting view; and
  7. records build and integrity information.

The fixed random seed makes the example reproducible. Re-running the script replaces only the generated SQLite file.

Step 5: Inspect Tables and Row Counts

Connect through Python:

import sqlite3

connection = sqlite3.connect("database/service_operations.sqlite")
connection.execute("PRAGMA foreign_keys = ON")

tables = connection.execute("""
    SELECT name
    FROM sqlite_master
    WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
    ORDER BY name
""").fetchall()

print(tables)
connection.close()

Row counts are a simple but important validation. They help detect missing loads, accidental duplication, and unexpected filtering. The workflow writes them to results/quality-control/table-row-counts.csv.

Step 6: Verify That the Schema Supports a Priority Queue

Use WHERE to select records and ORDER BY to make results deterministic.

SELECT
    request_id,
    priority,
    current_status,
    submitted_at
FROM service_requests
WHERE priority = 'High'
  AND current_status IN ('Submitted', 'In Progress')
ORDER BY submitted_at, request_id;

Without ORDER BY, a database does not guarantee presentation order. Include a stable tie-breaker such as the primary key when equal values are possible.

Step 7: Verify Relationships Through a Joined View

Foreign keys store identifiers. Joins recover the related descriptive information.

SELECT
    r.request_id,
    c.customer_name,
    g.region_name,
    sc.category_name,
    COALESCE(s.staff_name, 'Unassigned') AS staff_name,
    r.priority,
    r.current_status
FROM service_requests AS r
JOIN customers AS c
  ON r.customer_id = c.customer_id
JOIN regions AS g
  ON c.region_id = g.region_id
JOIN service_categories AS sc
  ON r.category_id = sc.category_id
LEFT JOIN staff AS s
  ON r.assigned_staff_id = s.staff_id
ORDER BY r.request_id;

The staff join is a LEFT JOIN because unassigned requests must remain in the result. An inner join would silently remove them.

Step 8: Test the Grain of a Regional Summary

The query below summarizes volume and satisfaction by region.

SELECT
    g.region_name,
    COUNT(*) AS request_count,
    ROUND(AVG(r.satisfaction_score), 2) AS mean_satisfaction,
    SUM(CASE WHEN r.current_status IN ('Resolved', 'Closed')
             THEN 1 ELSE 0 END) AS completed_count
FROM service_requests AS r
JOIN customers AS c
  ON r.customer_id = c.customer_id
JOIN regions AS g
  ON c.region_id = g.region_id
GROUP BY g.region_id, g.region_name
ORDER BY request_count DESC, g.region_name;

COUNT(*) counts rows, while AVG(column) ignores NULL. Always state the denominator and missing-value behavior when interpreting an aggregate.

Step 9: Test Derived Measures Without Storing Redundant Values

A common table expression makes a multi-stage query easier to read. This example compares each category’s mean resolution time with the overall mean.

WITH resolved_requests AS (
    SELECT
        category_id,
        (julianday(resolved_at) - julianday(submitted_at)) * 24
            AS resolution_hours
    FROM service_requests
    WHERE resolved_at IS NOT NULL
),
category_summary AS (
    SELECT
        category_id,
        AVG(resolution_hours) AS mean_resolution_hours
    FROM resolved_requests
    GROUP BY category_id
)
SELECT
    sc.category_name,
    ROUND(cs.mean_resolution_hours, 2) AS mean_resolution_hours
FROM category_summary AS cs
JOIN service_categories AS sc
  ON cs.category_id = sc.category_id
WHERE cs.mean_resolution_hours >
      (SELECT AVG(resolution_hours) FROM resolved_requests)
ORDER BY mean_resolution_hours DESC;

The subquery returns one overall benchmark. The CTEs name intermediate result sets without storing them permanently.

Step 10: Validate the Status-History Design

The current status supports fast operational reporting, while the history table preserves event-level detail.

SELECT
    h.request_id,
    h.status,
    h.changed_at,
    LAG(h.status) OVER (
        PARTITION BY h.request_id
        ORDER BY h.changed_at, h.history_id
    ) AS previous_status
FROM request_status_history AS h
ORDER BY h.request_id, h.changed_at, h.history_id;

LAG() is a window function. It adds context from a related row without collapsing the event records as GROUP BY would.

Step 11: Encapsulate Reusable Relationships in a View

The workflow creates request_details, a view that centralizes common joins and a derived resolution-time field.

SELECT
    region_name,
    category_name,
    COUNT(*) AS request_count,
    ROUND(AVG(resolution_hours), 2) AS mean_resolution_hours
FROM request_details
GROUP BY region_name, category_name
ORDER BY region_name, request_count DESC;

A view improves reuse and consistency, but it does not replace documentation. Users still need to know the grain, join logic, definitions, and treatment of missing values.

Step 12: Protect Changes with Transactions

A transaction makes a related set of changes succeed or fail as one unit.

import sqlite3

with sqlite3.connect("database/service_operations.sqlite") as connection:
    connection.execute("PRAGMA foreign_keys = ON")
    connection.execute(
        """
        UPDATE service_requests
        SET current_status = ?, resolved_at = ?
        WHERE request_id = ?
        """,
        ("Resolved", "2026-07-31 10:00:00", 1),
    )
    connection.execute(
        """
        INSERT INTO request_status_history
            (request_id, status, changed_at)
        VALUES (?, ?, ?)
        """,
        (1, "Resolved", "2026-07-31 10:00:00"),
    )

The context manager commits if both statements succeed and rolls back if an exception occurs. Parameter placeholders also keep data separate from SQL syntax and are the correct default for application-supplied values.

Step 13: Add and Evaluate Indexes

Indexes can accelerate filtering and joins but consume storage and add work to inserts and updates. Create them for demonstrated access patterns rather than for every column.

CREATE INDEX idx_requests_status_priority
ON service_requests(current_status, priority);

CREATE INDEX idx_requests_customer
ON service_requests(customer_id);

CREATE INDEX idx_history_request_changed
ON request_status_history(request_id, changed_at);

Inspect a plan with:

EXPLAIN QUERY PLAN
SELECT request_id, submitted_at
FROM service_requests
WHERE current_status = 'In Progress'
  AND priority = 'High';

On a small teaching database, a full scan may still be fast. The purpose is to learn how the optimizer intends to access data, not to claim a meaningful benchmark from a tiny dataset.

Step 14: Validate Database Quality

Run the complete design-validation workflow:

python scripts/python/validate-database.py

The script checks:

  • SQLite’s PRAGMA integrity_check result;
  • foreign-key violations;
  • table row counts;
  • duplicate values for declared business keys;
  • invalid request-state combinations;
  • current-status agreement with the latest history event; and
  • the query plan for an indexed filter.

Expected outputs include:

Output Purpose
results/tables/request-details.csv joined request-level extract
results/tables/region-summary.csv operational summary by region
results/tables/category-resolution-summary.csv completed-request timing by category
results/tables/staff-workload-summary.csv assigned workload by staff member
results/tables/monthly-request-summary.csv request volume by month
results/quality-control/table-row-counts.csv row-count checks
results/quality-control/validation-summary.csv integrity and business-rule checks
results/quality-control/query-plan.txt SQLite query-plan output
results/quality-control/run-manifest.json reproducibility metadata

Step 15: Interpret Results Carefully

Use the generated summaries to answer:

  1. Which regions receive the most requests?
  2. Which service categories have above-average resolution time?
  3. How many active high-priority requests are unassigned?
  4. Which staff members have the largest active workload?
  5. Does monthly request volume appear stable over the generated period?

These are descriptive operational results from synthetic data. Differences do not establish causes, staff quality, customer behavior, or future performance.

Step 16: Complete the Design Review

Assess the database using the checklist below.

Review area Question
Grain Is one row in each table clearly defined?
Keys Does every table have a stable primary key?
Relationships Are foreign keys placed on the correct side?
Cardinality Are one-to-many relationships represented correctly?
Integrity Do constraints enforce important business rules?
Missingness Are nullable fields genuinely optional?
Normalization Are repeated descriptive facts stored once?
History Are time-varying facts preserved when required?
Queries Do joins retain the intended population?
Performance Are indexes linked to actual access patterns?
Security Are application values parameterized?
Documentation Are grain, definitions, limitations, and assumptions recorded?

Write a short design decision note explaining one normalization choice, one integrity constraint, one index, and one limitation of the current database.

Practical Challenge

Extend the database with a service_channels table for channels such as web, phone, and in-person submission.

Your extension should:

  1. create a table with a primary key and unique channel name;
  2. add a non-null channel foreign key to new request records;
  3. update the synthetic generator;
  4. produce a channel-level volume and resolution-time summary;
  5. add an index only if a documented query benefits from it; and
  6. update validation checks and the schema documentation.

For an advanced extension, add request tags through a many-to-many relationship using tags and request_tags rather than storing comma-separated tags in one field.

Common Problems and Corrections

Problem Why it matters Correction
Repeating names in every request creates inconsistency and anomalies move stable descriptive facts to related tables
Storing lists in one column prevents reliable joins and constraints use a junction table
Using inner joins automatically can discard unmatched records choose join type from the analytical population
Comparing with = NULL never evaluates as expected use IS NULL or IS NOT NULL
Building SQL with string concatenation introduces quoting errors and injection risk use parameters
Indexing every field slows writes and adds maintenance index demonstrated filter/join patterns
Updating current status only loses the audit trail update current state and append history transactionally
Reporting averages without denominators hides sample size and missingness report counts and missing-value rules

Completion Checklist

Final Deliverable

Prepare a short database review containing:

  • a description of the operational problem and table grain;
  • the entity relationships and key design decisions;
  • evidence that integrity and foreign-key checks passed;
  • two useful SQL findings with their denominators;
  • one query-plan observation;
  • one limitation; and
  • one recommended extension.

Key Takeaway

Reliable relational systems begin with explicit design. Normalized tables, documented grain, enforced relationships, reproducible loading, targeted indexes, and systematic validation turn a collection of records into a dependable database.

© Complex Data Insights | CDI Practicals

Hands-on exercises and step-by-step tools