Capstone: designing and implementing a database, plus NoSQL and next steps

This final lesson puts the whole course together on one project: designing and building the database for an invented veterinary clinic, Prairie Paws, from requirements to a working, indexed, secured schema answering real questions. Each step points back to the lesson that taught it. The second part of the lesson surveys the NoSQL systems that complement relational databases, document stores, key-value stores, wide-column stores and graph databases, explains when each fits, and ends with where to go after this course.

Step 1: requirements

Interviews with the clinic's manager produce these requirements. The clinic records owners, with a name, a phone number that is required and an optional email. Each pet belongs to one owner, who may have several pets; a pet has a name, a species, and an optional birth date. The clinic employs veterinarians, each with a name and a provincial registration number. Owners book appointments for a pet with a particular vet at a start time; appointments are 30 minutes long, and a vet can never be booked twice at the same time. Each appointment can be booked, done or cancelled. During a visit the vet performs services from a price list, such as exams, vaccinations and radiographs, possibly several of one service. Prices change over time, but an invoice must always show the price actually charged. The manager wants reports of revenue by vet and month, pets overdue for a wellness exam, and each owner's total spending.

Several requirements carry hidden design decisions. 'A vet can never be booked twice at the same time' is a constraint the database should enforce, not just the booking screen. 'Prices change, but invoices show the price charged' means the charged price must be stored with each treatment, a deliberate copy rather than redundancy, because it records a historical fact the price list cannot. 'Reports by month' means times must be stored in a format that can be grouped by month.

Step 2: the ER model

Applying lesson 6: Owner, Pet, Vet and Service are entity sets. An appointment is an event with its own attributes, start time and status, and the same pet can see the same vet many times, so Appointment is an entity with many-to-one relationships to Pet and Vet, not a plain many-to-many relationship. The services performed at a visit are line items: Treatment is a weak entity identified by its appointment and a line number, with a many-to-one relationship to Service and attributes quantity and charged price. Owner to Pet is one-to-many with total participation on the pet side, since every pet has an owner.

Step 3: tables

Lesson 7's rules turn the diagram into tables. Each one-to-many relationship becomes a NOT NULL foreign key on the many side; the weak entity gets a composite key; CHECK constraints capture the allowed species and statuses and non-negative prices; and a UNIQUE constraint on (vet_id, starts_at) makes double-booking impossible. Money is stored as integer cents, as lesson 3 recommended.

CREATE TABLE owner (
    owner_id  INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    email     TEXT UNIQUE,
    phone     TEXT NOT NULL
);
CREATE TABLE pet (
    pet_id    INTEGER PRIMARY KEY,
    owner_id  INTEGER NOT NULL REFERENCES owner(owner_id),
    name      TEXT NOT NULL,
    species   TEXT NOT NULL CHECK (species IN ('dog', 'cat', 'rabbit', 'bird', 'other')),
    birth_date TEXT
);
CREATE TABLE vet (
    vet_id    INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    reg_no     TEXT NOT NULL UNIQUE
);
CREATE TABLE appointment (
    appt_id   INTEGER PRIMARY KEY,
    pet_id    INTEGER NOT NULL REFERENCES pet(pet_id),
    vet_id    INTEGER NOT NULL REFERENCES vet(vet_id),
    starts_at TEXT NOT NULL,                       -- 'YYYY-MM-DD HH:MM', 30-minute slots
    status    TEXT NOT NULL DEFAULT 'booked' CHECK (status IN ('booked', 'done', 'cancelled')),
    UNIQUE (vet_id, starts_at)                     -- a vet cannot be double-booked
);
CREATE TABLE service (
    service_code TEXT PRIMARY KEY,
    description  TEXT NOT NULL,
    price_cents  INTEGER NOT NULL CHECK (price_cents >= 0)
);
CREATE TABLE treatment (                           -- line items of a visit
    appt_id      INTEGER REFERENCES appointment(appt_id) ON DELETE CASCADE,
    line_no      INTEGER,
    service_code TEXT NOT NULL REFERENCES service(service_code),
    qty          INTEGER NOT NULL DEFAULT 1 CHECK (qty > 0),
    price_cents  INTEGER NOT NULL CHECK (price_cents >= 0),   -- price charged at the time
    PRIMARY KEY (appt_id, line_no)
);

SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;

name
-----------
appointment
owner
pet
service
treatment
vet

Step 4: checking the design with functional dependencies

Lesson 8's tools confirm that each table is in BCNF. In the treatment table the FDs are {appt_id, line_no} \(\to\) service_code, qty, price_cents. One might worry that service_code \(\to\) price_cents, which would violate BCNF, but that FD deliberately does not hold: the same service can be charged at different prices at different times, which is the reason price_cents is stored here. Checking FDs is precisely about the real-world rules, and here the rule is that price depends on the visit.

from itertools import combinations

def closure(attrs, fds):
    """Attribute closure: every attribute determined by attrs under fds."""
    result = set(attrs)
    changed = True
    while changed:
        changed = False
        for lhs, rhs in fds:
            if lhs <= result and not rhs <= result:
                result |= rhs
                changed = True
    return result

def candidate_keys(R, fds):
    keys = []
    for k in range(1, len(R) + 1):
        for combo in combinations(sorted(R), k):
            s = set(combo)
            if closure(s, fds) == R and not any(key <= s for key in keys):
                keys.append(s)
    return keys

def fd(s):  # "A B -> C" to (frozenset, frozenset)
    l, r = s.split("->")
    return frozenset(l.split()), frozenset(r.split())

def is_bcnf(R, fds):
    bad = [(sorted(l), sorted(r)) for l, r in fds if not r <= l and closure(l, fds) != R]
    return bad or "BCNF"

tables = {
    "pet":         (set("pet_id owner_id name species birth_date".split()),
                    [fd("pet_id -> owner_id name species birth_date")]),
    "appointment": (set("appt_id pet_id vet_id starts_at status".split()),
                    [fd("appt_id -> pet_id vet_id starts_at status"), fd("vet_id starts_at -> appt_id")]),
    "treatment":   (set("appt_id line_no service_code qty price_cents".split()),
                    [fd("appt_id line_no -> service_code qty price_cents")]),
    # a tempting but wrong design that adds owner_phone to pet:
    "pet_bad":     (set("pet_id owner_id owner_phone name".split()),
                    [fd("pet_id -> owner_id name"), fd("owner_id -> owner_phone")]),
}
for t, (R, F) in tables.items():
    print(f"{t:12s} keys {[sorted(k) for k in candidate_keys(R, F)]}  {is_bcnf(R, F)}")

pet          keys [['pet_id']]  BCNF
appointment  keys [['appt_id'], ['starts_at', 'vet_id']]  BCNF
treatment    keys [['appt_id', 'line_no']]  BCNF
pet_bad      keys [['pet_id']]  [(['owner_id'], ['owner_phone'])]

The appointment table has two candidate keys, appt_id and {vet_id, starts_at}, both enforced, and is in BCNF. The tempting design that copies the owner's phone into pet violates BCNF through owner_id \(\to\) owner_phone, and would produce exactly the update anomaly of lesson 8 the first time an owner changed numbers.

Step 5: data and reports

-- Revenue by vet and month, in dollars, from completed visits
SELECT v.name AS vet, substr(a.starts_at, 1, 7) AS month,
       printf('%.2f', SUM(t.qty * t.price_cents) / 100.0) AS revenue
FROM treatment t
JOIN appointment a ON a.appt_id = t.appt_id AND a.status = 'done'
JOIN vet v ON v.vet_id = a.vet_id
GROUP BY v.vet_id, month
ORDER BY month, vet;

-- Each owner's spending, including owners with none, ranked
SELECT o.name,
       COALESCE(SUM(t.qty * t.price_cents), 0) / 100.0 AS spent,
       RANK() OVER (ORDER BY COALESCE(SUM(t.qty * t.price_cents), 0) DESC) AS rnk
FROM owner o
LEFT JOIN pet p         ON p.owner_id = o.owner_id
LEFT JOIN appointment a ON a.pet_id = p.pet_id AND a.status = 'done'
LEFT JOIN treatment t   ON t.appt_id = a.appt_id
GROUP BY o.owner_id
ORDER BY rnk;

-- Dogs and cats with no completed wellness exam since 2026-01-01 (overdue reminders)
SELECT p.name, p.species, o.phone
FROM pet p JOIN owner o ON o.owner_id = p.owner_id
WHERE p.species IN ('dog', 'cat')
  AND NOT EXISTS (SELECT 1 FROM appointment a JOIN treatment t ON t.appt_id = a.appt_id
                  WHERE a.pet_id = p.pet_id AND a.status = 'done'
                    AND t.service_code = 'EXAM' AND a.starts_at >= '2026-01-01')
ORDER BY p.name;

vet           month    revenue
------------  -------  -------
Dr. Fournier  2026-10  815.00
Dr. Osei      2026-10  214.00

name       spent  rnk
---------  -----  ---
C. Mbeki   815    1
A. Singh   214    2
B. Larsen  0      3

name  species  phone
----  -------  -----
(no rows)

The reports use most of lessons 4 and 5: joins with a status condition placed in ON so that the left joins keep owners without visits, aggregation with expressions, a window function for ranking, and NOT EXISTS for an anti-join. In this sample every dog and cat has had a recent exam, so the reminder query correctly returns no rows; on a real clinic database it would drive the reminder phone calls.

Step 6: integrity under concurrency

-- Two receptionists try to book Dr. Osei at the same slot
INSERT INTO appointment (appt_id, pet_id, vet_id, starts_at) VALUES (201, 12, 1, '2026-11-03 11:00');
INSERT INTO appointment (appt_id, pet_id, vet_id, starts_at) VALUES (202, 14, 1, '2026-11-03 11:00');

-- A treatment for a service that does not exist
INSERT INTO treatment VALUES (106, 1, 'GROOM', 1, 3000);

SELECT appt_id, pet_id, starts_at FROM appointment WHERE vet_id = 1 AND starts_at LIKE '2026-11-03%';

(1 row affected)

Error: UNIQUE constraint failed: appointment.vet_id, appointment.starts_at

Error: FOREIGN KEY constraint failed

appt_id  pet_id  starts_at
-------  ------  ----------------
201      12      2026-11-03 11:00

Because the rule against double-booking is a UNIQUE constraint, it holds no matter how the two bookings interleave: whichever transaction commits second fails, as lesson 10 explained, and the application tells that receptionist the slot was just taken. Had the rule lived only in the booking screen, as a SELECT to check followed by an INSERT, two simultaneous bookings could both pass the check. Constraints in the database are the last line of defence for every program that will ever use it.

Step 7: indexes, security and privacy

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("""CREATE TABLE appointment (
    appt_id INTEGER PRIMARY KEY, pet_id INTEGER NOT NULL, vet_id INTEGER NOT NULL,
    starts_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'booked',
    UNIQUE (vet_id, starts_at))""")
q = "SELECT * FROM appointment WHERE pet_id = 14 ORDER BY starts_at"
show = lambda sql: " | ".join(r[3] for r in con.execute("EXPLAIN QUERY PLAN " + sql))
print("before:", show(q))
con.execute("CREATE INDEX appt_pet_time ON appointment(pet_id, starts_at)")
print("after :", show(q))
print("vet schedule:", show("SELECT * FROM appointment WHERE vet_id = 1 AND starts_at >= '2026-11-01'"))

before: SCAN appointment | USE TEMP B-TREE FOR ORDER BY
after : SEARCH appointment USING INDEX appt_pet_time (pet_id=?)
vet schedule: SEARCH appointment USING INDEX sqlite_autoindex_appointment_1 (vet_id=? AND starts_at>?)

Following lesson 9, the frequent queries decide the indexes. A pet's history, filtered by pet and sorted by time, gets a composite index on (pet_id, starts_at). The vet's daily schedule needs no new index, because the UNIQUE constraint on (vet_id, starts_at) is implemented with one. Foreign keys such as pet.owner_id and treatment.service_code deserve indexes if owners or services are often looked up or deleted. Following lesson 11, the booking web site connects with an account that can read the schedule and insert appointments but cannot read other owners' contact details, all SQL uses parameters, and the clinic's privacy notice and retention schedule govern how long owner contact information is kept after a client leaves. Veterinary records are not human health information, but owners' names, emails and phone numbers are personal information under privacy law.

NoSQL: beyond the relational model

Relational databases remain the default for structured, shared data with integrity rules, which describes most business data. Since the mid-2000s, though, other data models, loosely called NoSQL ('not only SQL'), have become common for particular needs: very high write volumes spread across many machines, flexible or nested records, simple key lookups at huge scale, or highly connected data. They usually trade away some of what this course has taught, such as joins, declared constraints, or full ACID transactions across items, in exchange for scale, availability or flexibility.

A document store would represent an owner with their pets embedded in one document. Reading an owner with all their pets is then one fetch with no join, which is fast, but data needed in several places, such as a service's description, is either duplicated in every document, reintroducing the update anomalies of lesson 8, or referenced by identifier and joined in application code. Distributed NoSQL systems also face the CAP theorem, Eric Brewer's observation that when the network partitions, a system must choose between consistency (every read sees the latest write) and availability (every request gets an answer); many choose availability and offer eventual consistency, in which replicas converge over time.

The boundaries have blurred. Relational systems store and index JSON: PostgreSQL's jsonb type, MySQL's JSON type, and SQLite's JSON functions let one table mix fixed columns with flexible attributes. Many NoSQL systems have added query languages, secondary indexes and transactions. Graph query support was added to the SQL standard itself as SQL/PGQ in 2023, alongside the separate GQL standard. The practical rule is to start with a relational database and add a specialized system only for a demonstrated need that the relational one handles badly.

import json, sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE pet (pet_id INTEGER PRIMARY KEY, name TEXT, extra TEXT CHECK (json_valid(extra)))")
con.executemany("INSERT INTO pet VALUES (?, ?, ?)", [
    (11, "Biscuit", json.dumps({"breed": "beagle", "microchip": "985-1121", "allergies": ["chicken"]})),
    (13, "Juniper", json.dumps({"breed": "lop", "indoor": True})),
    (14, "Atlas",   json.dumps({"breed": "husky", "allergies": []})),
])
rows = con.execute("""SELECT name, json_extract(extra, '$.breed') AS breed,
                             json_array_length(extra, '$.allergies') AS n_allergies
                      FROM pet ORDER BY pet_id""").fetchall()
for r in rows:
    print(r)
print(con.execute("SELECT name FROM pet WHERE json_extract(extra, '$.indoor') = 1").fetchall())

('Biscuit', 'beagle', 1)
('Juniper', 'lop', None)
('Atlas', 'husky', 0)
[('Juniper',)]

Where to go next

You can now design a relational database from requirements, express questions in SQL from simple selections to recursive queries and window functions, reason about keys, dependencies and normal forms, read query plans and choose indexes, and explain how transactions keep data correct under concurrency and failure. Building on this, CMPUT-301 applies databases inside larger software projects, CMPUT-331 deepens security, CMPUT-404 builds web applications on databases, and CMPUT-450 covers distributed systems, including replicated and partitioned databases and consensus. CMPUT-366, CMPUT-367 and CMPUT-466 use data at scale for artificial intelligence and machine learning. Many organizations also offer vendor certifications in specific systems, and the best practice of all is to design, build and query databases for real problems, starting with ones you care about.