Airport Ticketing System in SQL Server
T-SQLSQL ServerDatabase designStored proceduresTriggersSecurity
A complete SQL Server database for an airport ticketing desk covering passengers, flights, reservations, tickets and baggage. Business rules are enforced inside the database itself, revenue and occupancy reporting is wrapped in views, and access is split across staff, supervisor and admin roles.
6Tables
5Stored procedures
2Functions
2Views
1Trigger
3Security roles
The problem
A ticketing desk needs fast, correct answers: who is booked on which flight, which seat, what was paid, which bags are checked in. The database has to protect that data from bad input and from users who should not touch it.
The data
- Six related tables: Employees, Passengers, Flights, Reservations, Tickets and Baggage.
- CHECK constraints restrict employee role, meal preference, reservation status, ticket class and baggage status; UNIQUE constraints protect PNRs, e-mail addresses, flight numbers and e-boarding numbers.
Approach
- Model the schemaPrimary and foreign keys connect passengers to reservations, reservations to flights and tickets, tickets to issuing employees and baggage.
- Enforce business rulesReservation dates cannot be in the past, enforced by a CHECK constraint and by a guarded InsertReservation procedure that raises a clear error.
- Wrap logic in procedures and functionsSearch tickets by surname, update passenger details with an existence check, hash passwords on employee creation, and generate a complete boarding pass.
- Report with viewsPer-ticket revenue combining fare, baggage fees and seat fare; per-flight tickets issued and occupancy percentage.
- Automate with a triggerIssuing a ticket automatically marks the seat as reserved.
- Lock it downThree database roles with least-privilege permissions, mapped to SQL logins and users.
Design and database objects
| Type | Name | What it does |
|---|---|---|
| Procedure | InsertReservation | Inserts a reservation and rejects dates in the past |
| Procedure | SearchTicketsByLastName | Finds a passenger’s tickets by surname, most recent first |
| Procedure | InsertEmployee | Adds an employee and stores a SHA-256 password hash |
| Procedure | UpdatePassengerDetails | Updates passenger records after checking the PNR exists |
| Procedure | GenerateBoardingPass | Joins passenger, flight, seat, baggage and issuing agent into one boarding pass |
| Function | GetBusinessClassPassengersWithMeals | Table-valued: passengers of a chosen ticket class (for example Business) with their meal preferences, for today’s reservations |
| Function | GetCheckedInBaggageCount | Scalar: checked-in bags for a flight on a given date |
| View | EmployeeRevenueView | Revenue per ticket: fare plus baggage fees plus seat fare, by employee |
| View | FlightOccupancyView | Tickets issued and occupancy percentage per confirmed flight |
| Trigger | UpdateSeatStatusOnTicketIssue | Marks the seat as reserved when a ticket is issued |
Key findings
- Pushing validation into constraints, procedures and a trigger keeps data consistent no matter which application writes to it.
- Views capture the revenue and occupancy logic once, so reports become one-line queries.
- Least-privilege roles keep duties apart: ticketing staff cannot delete passengers or edit employees, supervisors manage reservations and tickets, and the admin role holds database control.
Next steps
- Add indexes on the PNR and FlightID foreign keys and verify the gain with execution plans.
- Rewrite the trigger to be set-based so it handles multi-row inserts.
- Model aircraft capacity so occupancy is measured against real seat counts.