Horse Race Database — Advanced Database Administration Project

An individual database administration project covering the full DBA toolkit — from server startup through performance tuning — on a PostgreSQL horse-racing schema.

Individual project for ICS424: Advanced Database Systems, KFUPM.

Overview

The assignment: take a horse-racing database (8 tables covering horses, owners, stables, trainers, races, race results, and tracks) and demonstrate every core skill in a database administrator's toolkit on PostgreSQL 16.2 via pgAdmin 4. This is a documentation-heavy DBA project rather than an application — the PDF report below is the deliverable, walking through each task with the actual queries run and their output.

Schema

Horse(horseId, horseName, age, gender, registration, stableId)
Owner(ownerId, lname, fname)
Owns(horseId, ownerId)
Stable(stableId, stableName, location, colors)
Trainer(trainerId, lname, fname, stableId)
Race(raceId, raceName, trackName, raceDate, raceTime)
RaceResults(raceId, horseId, results, prize)
Track(trackName, location, length)

Owners can own multiple horses across multiple stables, a stable can employ multiple trainers, and a horse can race in multiple races — modeled with the appropriate many-to-many junction tables and foreign keys.

What's demonstrated

• Server administration — startup/shutdown procedures and reconnecting with credentials
• Logical & physical structure — table structure via SQL, on-disk data files, and tablespaces
• Materialized views — a view joining race and track on a complex condition, with manual refresh
• Stored procedures & triggers — a function that derives a horse's birth year from its age, wired up as both a callable procedure and a BEFORE INSERT/UPDATE trigger
• Concurrency control — table-level locking between two concurrent transactions, plus timestamp-based conflict detection and rollback
• Locking conflict diagnosis — tracing a blocked transaction and resolving it via commit
• Backup strategy — evaluating pre-data/data/post-data sections, schema-only vs. data-only backups, and the "do not save" options
• Recovery — dropping a table and restoring it from a full backup
• Indexing — creating, monitoring (pg_stat_user_indexes), and dropping standard, unique, and partial indexes
• Performance analysis — reading pg_stat_user_tables to compare sequential vs. index scans across tables and flag optimization opportunities

Tech

PostgreSQL 16.2 · pgAdmin 4

Files

• ICS424 -- Project1.pdf — full report: every task above with queries, screenshots, and analysis
• Project 1B DDL & DML.pdf — schema definition and seed data (DDL/DML) for the racing database
• 424 Project 1.pdf — original assignment brief

---

By Osama Al-Bahnasi.