01 / Overview
Context and objective
A ticketing system depends on relationships that remain valid as schedules, passengers, routes, seats, and transactions change. This project focused on modeling that domain before building queries around it.
Create a normalized Oracle database that represents a booking lifecycle clearly, prevents invalid relationships, and supports useful operational queries.
Responsibilities
- Translated the ticketing domain into an entity-relationship model.
- Defined primary keys, foreign keys, constraints, and user roles.
- Wrote joins, aggregation queries, views, and PL/SQL routines.
- Tested expected operations and deliberate integrity violations.
Constraints
- Database-focused academic deliverable rather than a public application.
- Schema needed to support different users without duplicating core records.
- Correctness and traceability took priority over visual presentation.
02 / Process
A structured route through the problem
Model the lifecycle
I mapped the journey from route and schedule creation through passenger booking and payment, looking for entities with independent lifecycles and stable identifiers.
Encode the rules
Relationships and constraints were moved into the schema wherever possible so invalid states would be rejected consistently, regardless of the calling interface.
Query real questions
Views, joins, and aggregation were designed around questions a ticketing operation would need to answer, rather than existing only as isolated SQL exercises.
03 / Product
Key features
- 01Normalized route, schedule, passenger, and booking entities
- 02Primary and foreign key relationships
- 03Validation constraints and integrity tests
- 04Operational joins and aggregate reporting
- 05Views and role-aware database access
Interface studies
Visualizing the core flows
04 / Engineering
Decisions with consequences
Integrity belongs in the schema
Required relationships and valid values use database constraints. Application checks can improve feedback, but the database remains the final guard against inconsistent records.
Readable query surfaces
Views collect common joins into stable, named query surfaces, reducing repetition and clarifying which fields are intended for reporting use.
Challenge / lesson
Balancing normalization with query readability was the central tradeoff. The exercise showed that a clean model still needs deliberate views and naming to be practical for the people querying it.
05 / Outcome
What exists today
The resulting schema supports the planned booking relationships, common queries, and integrity checks. The outcome is an academic database implementation, not a deployed consumer ticketing service.