Skip to content

README.md

Latest commit

 

History

History
1991 lines (1563 loc) · 66 KB

README.md

File metadata and controls

1991 lines (1563 loc) · 66 KB

📊 XaprAir Database

Comprehensive documentation for all database objects including Stored Procedures, Functions, Views, Triggers, and SQL Agent Jobs.

SQL Server

⚠️ Important Note

All SQL scripts in this directory contain USE DB_EXAMPLE_NAME statements. Before running the scripts:

  1. Option 1: Use the default database name DB_EXAMPLE_NAME

    • Create the database with this exact name
    • Run scripts as-is

  2. Option 2: Use your own database name

    • Find and replace all occurrences of DB_EXAMPLE_NAME in all .sql files
    • Update backend/.env with your database name: DB_NAME=YourDatabaseName

Quick Find & Replace (Linux/macOS):

# Replace database name in all SQL files
find . -name "*.sql" -type f -exec sed -i 's/DB_EXAMPLE_NAME/YourDatabaseName/g' {} +

Quick Find & Replace (Windows PowerShell):

# Replace database name in all SQL files
Get-ChildItem -Recurse -Filter *.sql | ForEach-Object { (Get-Content $_.FullName) -replace 'DB_EXAMPLE_NAME', 'YourDatabaseName' | Set-Content $_.FullName }

📋 Table of Contents

  1. Database Overview
  2. Stored Procedures
  3. Functions
  4. Views
  5. Triggers
  6. SQL Agent Jobs
  7. Installation & Deployment

🎯 Database Overview

The XaprAir database is a fully normalized, transactional RDBMS design for a flight reservation system.

Entity-Relationship Diagram

Database ER Diagram

Complete database schema showing all tables, relationships, and constraints

Database Objects

  • Tables: 14 (fully normalized)
  • Stored Procedures: 38 (13 Customer + 7 Manager + 13 Admin + 5 Maintenance)
  • Functions: 4 (Price calculation, validation, and string aggregation)
  • Triggers: 7 (Automatic updates and auditing)
  • Views: 8 (Available flights and reporting)
  • SQL Agent Jobs: 5 (Automated maintenance tasks)
  • Constraints: 40+ Foreign Key + Check constraints

Key Features

✅ ACID Compliant - Guaranteed transactions ✅ Referential Integrity - FK constraints always enforced ✅ 3NF Normalization - Third Normal Form ✅ Audit Trail - All changes are logged ✅ Row-level Locking - No race conditions in concurrent bookings ✅ Dynamic Pricing - Seat class × passenger type × segments ✅ Online Check-in - Opens 24 hours before, closes 1 hour before departure ✅ Automated Check-in - Auto check-in 1 hour before departure

🏗️ Architecture & Design Philosophy

Database Design Principles

┌─────────────────────────────────────────┐
│        Normalization (3NF)              │
├─────────────────────────────────────────┤
│ • Avoid data redundancy                 │
│ • Enforce referential integrity         │
│ • Only atomic values                    │
└─────────────────────────────────────────┘
                    ↓
┌─────────────────────────────────────────┐
│     Business Logic in DB Layer          │
├─────────────────────────────────────────┤
│ • Stored Procedures (operations)        │
│ • Functions (calculations)              │
│ • Triggers (automatic updates)          │
│ • Constraints (data validation)         │
└─────────────────────────────────────────┘
                    ↓
┌─────────────────────────────────────────┐
│    Audit & Compliance (AuditLog)        │
├─────────────────────────────────────────┤
│ • All INSERT/UPDATE/DELETE logged       │
│ • User and timestamp tracking           │
│ • Old/new values stored                 │
└─────────────────────────────────────────┘

Design Decisions

1. Dynamic Pricing Model

Price = basePrice × seatClassMultiplier × passengerTypeMultiplier

Seat Classes:
  - Economy: 1.0x
  - Business: 2.5x
  - First: 4.0x

Passenger Types:
  - Adult: 1.0x
  - Young (12-17): 0.9x (10% discount)
  - Child (2-11): 0.75x (25% discount)
  - Infant (0-2): 0.1x (90% discount)

Connections (Connection Discount):
  - Direct (1 segment): +0%
  - 1 stop (2 segments): +8%
  - 2 stops (3 segments): +16%
  - 3+ stops: +32% (maximum)

2. Reservation Architecture

  • Reservations → 1 reservation per customer
  • ReservationFlights → N flights per reservation (multi-leg support)
  • Passengers → N passengers per reservation
  • Tickets → N tickets per passenger for each segment

3. Seat Allocation & Check-in Strategy

  • Per-aircraft seat inventory (Seats table)
  • Real-time availability check via fn_GetAvailableSeats
  • Row-level locking (UPDLOCK HOLDLOCK) prevents double booking
  • Supports flexible seat class configurations
  • Deferred Seat Assignment: Seat selection occurs after payment (assigned at check-in)
  • Check-in Window: Opens 24 hours before departure, closes 1 hour before
  • Automated Check-in: Auto check-in 1 hour before departure (sp_AutoCheckIn job)
  • Manual Check-in: Customers can manually check-in during the window

4. User Preferences & Localization

  • UserPreferences table → language, theme, currency per user
  • Supported languages: en, tr, de, fr, es, ar, zh, ja, ru
  • Supported currencies: TRY, USD, EUR, GBP, JPY, CNY, AED
  • Backend applies currency conversion server-side

5. Price Calculation Method

  • fn_CalculateTicketPrice function

    • Input: flightId, seatClass, passengerType
    • Output: calculated price (in TRY)
    • Dynamically uses Flights.basePrice and multipliers

  • Flights.basePrice calculation (sp_CreateFlight procedure):

    • Flight distance (km) calculated from latitude/longitude
    • Minimum 250km threshold enforced
    • Flight Duration = distance / 800.0 (avg speed km/h) * 60 (minutes)
      • Minimum 30 minutes if calculated duration is less
    • Base Price = 350.0 TRY + (distance * 5.0 / 2.0) TRY

📊 Table Schema

Independent Tables (No FK)

1. Users - System Users

userId INT IDENTITY(1,1) PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
passwordHash VARCHAR(255) NOT NULL,
firstName VARCHAR(100) NOT NULL,
lastName VARCHAR(100) NOT NULL,
phoneNumber VARCHAR(20) NOT NULL,
dateOfBirth DATE,
nationality VARCHAR(100) NOT NULL,
nationalIdNumber VARCHAR(20) NOT NULL, -- Turkish ID (11 digits) or passport number
userRole VARCHAR(20) NOT NULL DEFAULT 'customer', -- 'customer', 'manager', or 'admin'
isActive BIT NOT NULL DEFAULT 1,
tokenVersion INT NOT NULL DEFAULT 0, -- For JWT token invalidation
lastLogin DATETIME2 NULL,
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),
updatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Kısıtlamalar:

CONSTRAINT CHK_Users_Role CHECK (userRole IN ('customer', 'manager', 'admin')),
CONSTRAINT CHK_Users_Email CHECK (email LIKE '%@%')

2. UserPreferences - User Preferences

preferenceId INT IDENTITY(1,1) PRIMARY KEY,
userId INT NOT NULL,
language VARCHAR(10) NOT NULL DEFAULT 'en', -- 'en', 'tr', 'de', 'fr', etc.
theme VARCHAR(20) NOT NULL DEFAULT 'light', -- 'light'
currency VARCHAR(3) NOT NULL DEFAULT 'USD', -- 'USD', 'TRY', 'EUR', etc.
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),
updatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_UserPreferences_Language CHECK (language IN ('en', 'tr', 'de', 'fr', 'es', 'ar', 'zh', 'ja', 'ru')),
CONSTRAINT CHK_UserPreferences_Currency CHECK (currency IN ('TRY', 'USD', 'EUR', 'GBP', 'JPY', 'CNY', 'AED')),
CONSTRAINT FK_UserPreferences_Users FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE,
CONSTRAINT UQ_UserPreferences_UserId UNIQUE (userId)

3. Airlines - Airlines

airlineId INT IDENTITY(1,1) PRIMARY KEY,
airlineCode VARCHAR(10) NOT NULL UNIQUE, -- IATA 2-letter code (e.g., 'TK')
airlineName VARCHAR(100) NOT NULL,
logo IMAGE NULL,
country VARCHAR(100) NOT NULL,
isActive BIT NOT NULL DEFAULT 1,
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_Airlines_Code CHECK (LEN(airlineCode) >= 2)

4. Airports - Airports

airportId INT IDENTITY(1,1) PRIMARY KEY,
airportCode VARCHAR(10) NOT NULL UNIQUE, -- IATA 3-letter code (e.g., 'IST')
airportName VARCHAR(200) NOT NULL,
city VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
isInternational BIT NOT NULL DEFAULT 0,
isActive BIT NOT NULL DEFAULT 1,
latitude DECIMAL(9,6) NULL,  -- Decimal degrees, e.g., 40.977000
longitude DECIMAL(9,6) NULL, -- Decimal degrees, e.g., 28.821000
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_Airports_Code CHECK (LEN(airportCode) = 3)

5. Aircraft - Aircraft Models

aircraftId INT IDENTITY(1,1) PRIMARY KEY,
airlineId INT NOT NULL, -- FK to Airlines
aircraftModel VARCHAR(100) NOT NULL, -- e.g., 'Boeing 737-800'
registrationNumber VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'TC-JHC'
isActive BIT NOT NULL DEFAULT 1,
createdAt DATETIME2 NOT NULL DEFAULT GETDATE()

6. Seats - Seat Inventory (per aircraft)

seatId INT IDENTITY(1,1) PRIMARY KEY,
aircraftId INT NOT NULL, -- FK to Aircraft
seatNumber VARCHAR(10) NOT NULL, -- e.g., '12A', '5F'
seatClass VARCHAR(20) NOT NULL, -- 'economy', 'business', 'first'
isActive BIT NOT NULL DEFAULT 1,

Constraints:

CONSTRAINT CHK_Seats_Class CHECK (seatClass IN ('economy', 'business', 'first')),
CONSTRAINT UQ_Seats_AircraftSeat UNIQUE (aircraftId, seatNumber)

7. Flights - Flights

flightId INT IDENTITY(1,1) PRIMARY KEY,
airlineId INT NOT NULL, -- FK to Airline
flightNumber VARCHAR(20) NOT NULL UNIQUE, -- e.g., 'TK2025'
basePrice DECIMAL(10,2) NOT NULL, -- In TRY
status VARCHAR(20) NOT NULL DEFAULT 'scheduled', -- scheduled/boarding/departed/arrived/cancelled
availableSeats INT NOT NULL,
totalDuration INT NULL, -- In minutes (flight duration)
isDirectFlight BIT NOT NULL DEFAULT 1, -- legacy
originAirportId INT NOT NULL,
destinationAirportId INT NOT NULL,
departureTime DATETIME2 NOT NULL,
arrivalTime DATETIME2 NOT NULL,
aircraftId INT NOT NULL,
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),
updatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_Flights_Status CHECK (status IN ('scheduled', 'boarding', 'departed', 'arrived', 'cancelled')),
CONSTRAINT CHK_Flights_Price CHECK (basePrice > 0),
CONSTRAINT CHK_Flights_Seats CHECK (availableSeats >= 0)

8. Reservations - Reservations

reservationId INT IDENTITY(1,1) PRIMARY KEY,
userId INT NOT NULL, -- FK to Users
reservationCode VARCHAR(10) NOT NULL UNIQUE, -- e.g., 'PNR123456'
totalAmount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending/confirmed/checked_in/auto_checked_in/cancelled/completed
numberOfPassengers INT NOT NULL,
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),
updatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_Reservations_Status CHECK (status IN ('pending', 'confirmed', 'checked_in', 'auto_checked_in', 'cancelled', 'completed', 'expired')),
CONSTRAINT CHK_Reservations_Amount CHECK (totalAmount >= 0),
CONSTRAINT CHK_Reservations_Passengers CHECK (numberOfPassengers > 0 AND numberOfPassengers <= 9)

9. ReservationFlights - Reservation Flights

reservationFlightId INT IDENTITY(1,1) PRIMARY KEY,
reservationId INT NOT NULL CONSTRAINT FK_ReservationFlights_Reservations REFERENCES Reservations(reservationId),
flightId INT NOT NULL CONSTRAINT FK_ReservationFlights_Flights REFERENCES Flights(flightId),
segmentOrder INT NOT NULL,
direction VARCHAR(20) NOT NULL DEFAULT 'outbound', -- 'outbound' / 'inbound'
price DECIMAL(10,2) NULL, -- Transfer leg fee (calculated per passenger)
departureTime DATETIME2 NULL,
arrivalTime DATETIME2 NULL,
createdAt DATETIME2 DEFAULT GETDATE(),
updatedAt DATETIME2 NULL,

Constraints:

CONSTRAINT UQ_Reservation_SegmentOrder UNIQUE(reservationId, segmentOrder)

10. Passengers - Passengers

passengerId INT IDENTITY(1,1) PRIMARY KEY,
reservationFlightId INT NULL, -- FK to ReservationFlights
flightId INT NOT NULL, -- FK to Flights
seatId INT NULL, -- FK to Seats (can be null until check-in)
firstName VARCHAR(100) NOT NULL,
lastName VARCHAR(100) NOT NULL,
dateOfBirth DATE NOT NULL,
nationality VARCHAR(100) NOT NULL,
passportNumber VARCHAR(50),
passengerType VARCHAR(20) NOT NULL, -- 'adult', 'young', 'child', 'infant'
seatClass VARCHAR(20) NOT NULL DEFAULT 'economy', -- 'economy', 'business', 'first' (set during reservation)

Constraints:

CONSTRAINT CHK_Passengers_Type CHECK (passengerType IN ('adult', 'young', 'child', 'infant')),
CONSTRAINT CHK_Passengers_SeatClass CHECK (seatClass IN ('economy', 'business', 'first')),
CONSTRAINT CHK_Passengers_DOB CHECK (dateOfBirth < CAST(GETDATE() AS DATE))

11. Payments - Payments

paymentId INT IDENTITY(1,1) PRIMARY KEY,
reservationId INT NOT NULL, -- FK to Reservations
amount DECIMAL(10,2) NOT NULL,
paymentMethod VARCHAR(50) NOT NULL, -- credit_card/debit_card/paypal
paymentStatus VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending/completed/failed/refunded
transactionId VARCHAR(100) UNIQUE,
paymentDate DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_Payments_Status CHECK (paymentStatus IN ('pending', 'completed', 'failed', 'refunded')),
CONSTRAINT CHK_Payments_Amount CHECK (amount > 0),
CONSTRAINT CHK_Payments_Method CHECK (paymentMethod IN ('credit_card', 'debit_card', 'paypal'))

12. Tickets - Tickets

ticketId INT IDENTITY(1,1) PRIMARY KEY,
reservationFlightId INT NOT NULL, -- FK to ReservationFlights
passengerId INT NOT NULL, -- FK to Passengers
ticketNumber VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'PNR1234567890'
seatNumber VARCHAR(10) NOT NULL,
ticketPrice DECIMAL(10,2) NOT NULL,
issuedDate DATETIME2 NOT NULL DEFAULT GETDATE(),
status VARCHAR(20) NOT NULL DEFAULT 'active', -- active/used/cancelled

Constraints:

CONSTRAINT CHK_Tickets_Status CHECK (status IN ('active', 'used', 'cancelled')),
CONSTRAINT CHK_Tickets_Price CHECK (ticketPrice > 0)

13. CheckIn - Check-in Table

checkInId INT IDENTITY(1,1) PRIMARY KEY,
passengerId INT NOT NULL, -- FK to Passenger
flightId INT NOT NULL, -- FK to Flights
seatId INT NOT NULL, -- FK to Seats
checkInTime DATETIME2 NOT NULL DEFAULT GETDATE(), -- Passenger check-in time / auto check-in time
checkInStatus VARCHAR(20) NOT NULL DEFAULT 'checked_in', -- checked_in/auto_checked_in/cancelled
createdAt DATETIME2 NOT NULL DEFAULT GETDATE(),
updatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_CheckIn_Status CHECK (checkInStatus IN ('auto_checked_in', 'checked_in', 'cancelled')),
CONSTRAINT UQ_CheckIn_PassengerFlight UNIQUE (passengerId, flightId) -- One check-in per passenger per flight

14. AuditLog - Audit Log

auditId INT IDENTITY(1,1) PRIMARY KEY,
tableName VARCHAR(100) NOT NULL,
recordId INT NOT NULL,
action VARCHAR(50) NOT NULL,
oldValues NVARCHAR(MAX), -- JSON format
newValues NVARCHAR(MAX), -- JSON format
changedBy INT, -- userId
changedAt DATETIME2 NOT NULL DEFAULT GETDATE(),

Constraints:

CONSTRAINT CHK_AuditLog_Action CHECK (action IN ('INSERT', 'UPDATE', 'DELETE', 'AUTO_EXPIRE_PENDING', 'AUTO_MARK_COMPLETED', 'CHECK_IN', 'AUTO_CHECK_IN'))

🔧 Stored Procedures

The system has 38 stored procedures organized into 4 categories:

  • Customer (13): Customer operations (reservation, search, payment, preferences, profile, check-in)
  • Manager (7): Manager operations (flight management, reservation management)
  • Admin (13): System administrator operations (user, airline, airport, audit log management - each with CRUD)
  • Maintenance (5): Automated maintenance jobs (auto check-in, expiration, status updates, token expiry)

Customer Procedures

1. sp_CreateReservation

Purpose: Create reservation for multiple passengers and flights (multi-leg support)

EXEC sp_CreateReservation
    @userId = 1,
    @segmentsJson = N'[
        {"flightId": 101, "seatClass": "economy", "direction": "outbound"},
        {"flightId": 102, "seatClass": "economy", "direction": "return"}
    ]',
    @passengerFirstNames = 'John,Jane',
    @passengerLastNames = 'Doe,Smith',
    @passengerDOBs = '1990-01-15,1992-05-20',
    @passengerTypes = 'adult,adult',
    @passportNumbers = 'A123456,B789012',
    @nationalities = 'USA,USA',
    @reservationId OUTPUT,
    @reservationCode OUTPUT;

Business Logic:

  • All operations occur within a transaction
  • Seat availability is checked (fn_GetAvailableSeats)
  • Price is calculated (fn_CalculateTicketPrice - based on age group, seat class, distance)
  • Passengers, Tickets and ReservationFlights rows are created
  • Flights.availableSeats is updated
  • Unique reservationCode is generated (XAPR + 6 digit)
  • Failure: full ROLLBACK

Seat Assignment Timeline:

  1. Before payment: seatId = NULL (seat not yet assigned)
  2. After payment: Check-in window opens (24 hours before departure)
  3. During check-in: seatId is assigned (manual check-in)
  4. 1 hour before: Auto check-in occurs (sp_AutoCheckIn - seatId automatically assigned)

2. sp_CancelReservation

Purpose: Cancels a reservation (releases seats back)

EXEC sp_CancelReservation @reservationId = 100;

Business Logic:

  • Cancellation fee is calculated (fn_CalculateCancellationFee - based on time until departure)
  • Reservation status → 'cancelled'
  • Associated Tickets → 'cancelled'
  • Flights.availableSeats increases (seats returned to inventory)
  • AuditLog entry is created
  • If refund applies, payment status is updated

3. sp_SearchFlights

Purpose: Searches for flights between two airports (direct + connecting - max 3 segments) Algorithm: Minimum 45 minutes connection time check, per-segment seat availability validation

EXEC sp_SearchFlights
    @originAirportId = 1,
    @destinationAirportId = 2,
    @departureDate = '2025-12-25',
    @passengers = 2,
    @maxStops = 2,
    @airlines = 'TK,BA', -- filter (optional)
    @directOnly = 0,
    @page = 1,
    @limit = 10;

Features:

  • Direct flights + connecting flights (max 3 segments - 2 connections)
  • Connection time check: Minimum 45 minutes required between connecting flights
  • Pagination (default 10 per page, max 10)
  • Availability check per-segment (vw_FlightAvailability indexed view is used - O(1) lookup)
  • Price calculation: calculated separately for each segment (fn_CalculateTicketPrice), shown in total
  • Returns JSON segments array with full details

Outputs: Resultset with:

  • basePrice, totalMinutes, duration, departureTime, arrivalTime
  • economyPricePerPerson, economyTotalPrice
  • businessPricePerPerson, businessTotalPrice
  • firstClassPricePerPerson, firstClassTotalPrice
  • segments (JSON array: flightId, flightNumber, airline, aircraft, times, availableSeats)

4. sp_ProcessPayment

Purpose: Processes payment (confirms the reservation)

EXEC sp_ProcessPayment
    @userId = 1,
    @reservationId = 100,
    @amount = 2500.00,
    @paymentMethod = 'credit_card',
    @transactionId = 'iyz_123abc';

Business Logic:

  • Reservation status: pending → confirmed
  • Payment record is created (Payments table)
  • Check-in window becomes active (24 hours before departure - until 1 hour before)
  • AuditLog entry is created

5. sp_UserPreferences

Purpose: Get/update user preferences (language, currency, theme)

EXEC sp_UserPreferences
    @userId = 1,
    @language = 'tr',
    @currency = 'TRY',
    @theme = 'dark',
    @mode = 'GET'; -- GET / SET

Supported Preferences:

  • Language: EN, TR, DE, FR, ES, AR, ZH, JA, RU (9 languages)
  • Currency: TRY, USD, EUR, GBP, JPY, CNY, AED (7 currencies)
  • Theme: Light/Dark × 5 colors = 10 theme variants

5. sp_GetOrigins

Purpose: Lists departure airports of active flights (for flight search)

EXEC sp_GetOrigins;

Returned Fields: airportId, airportCode, airportName, city, country (distinct origin airports)

6. sp_GetAvailableDates

Purpose: Lists available flight dates for a specific origin-destination route

EXEC sp_GetAvailableDates
    @originAirportId = 1,
    @destinationAirportId = 2;

Returned Fields: departureDate (distinct dates with scheduled flights)

7. sp_GetReachableAirports

Purpose: Lists destination airports reachable from a specific origin

EXEC sp_GetReachableAirports @originAirportId = 1;

Returned Fields: airportId, airportCode, airportName, city, country (reachable destinations)

8. sp_GetUserPreferences

Purpose: Retrieves user preferences (language, currency, theme)

EXEC sp_GetUserPreferences @userId = 1;

Returned Fields: preferenceId, userId, language, theme, currency, createdAt, updatedAt

9. sp_SetUserPreferences

Purpose: Updates/creates user preferences (upsert)

EXEC sp_SetUserPreferences
    @userId = 1,
    @language = 'tr',
    @currency = 'TRY',
    @theme = 'dark';

Business Logic: INSERT if UserPreferences record doesn't exist, UPDATE if it does

10. sp_DeleteUserPreferences

Purpose: Deletes user preferences (returns to default values)

EXEC sp_DeleteUserPreferences @userId = 1;

11. sp_GetUserProfile

Purpose: Retrieves user profile information (read-only)

EXEC sp_GetUserProfile @userId = 1;

Returned Fields: userId, firstName, lastName, email, phoneNumber, dateOfBirth, userRole, createdAt

12. sp_UpdateUserProfile

Purpose: Updates user profile information

EXEC sp_UpdateUserProfile
    @userId = 1,
    @firstName = 'John',
    @lastName = 'Doe',
    @phoneNumber = '+905551234567',
    @dateOfBirth = '1990-01-15';

Business Logic: NULL parameters preserve existing values (COALESCE is used)

13. sp_CheckIn

Purpose: Performs manual check-in for passenger (with seat selection) Time Constraint: Check-in window opens 24 hours before departure, valid until 1 hour before

DECLARE @checkInId INT, @msg VARCHAR(MAX);
EXEC sp_CheckIn
    @passengerId = 5,
    @flightId = 100,
    @seatId = 42,
    @checkInId = @checkInId OUTPUT,
    @message = @msg OUTPUT;

Business Logic:

  • Passenger must have a reservation (confirmed status)
  • Flight departure time check: 24 hours before - 1 hour before (window)
  • Seat must be available and compatible with aircraft
  • CheckIn record is created, Passengers.seatId is updated
  • Flights.availableSeats is not updated (already decremented during payment)
  • AuditLog entry is created

Manager Procedures (Manager Operations)

Note: Manager category contains a total of 7 stored procedures (3 flight management + 4 reservation management)

14. sp_CreateFlight

File: manager/01_sp_FlightManagement.sql (contains 3 SPs) Purpose: Creates a new flight (distance and price are calculated automatically) Algorithm: Distance between airports is calculated using Haversine formula, basePrice and totalDuration are determined accordingly Validation Rules:

  1. Minimum 250 km distance required
  2. Same-city flights prohibited (originCity ≠ destinationCity)
  3. International route: Both airports must have isInternational=1
EXEC sp_CreateFlight
    @airlineId = 1,
    @flightNumber = 'TK123',
    @originAirportId = 1,
    @destinationAirportId = 2,
    @departureTime = '2025-12-25 10:00:00',
    @aircraftId = NULL, -- NULL ise sistem otomatik seçer
    @basePrice = NULL, -- NULL ise mesafe bazlı hesaplanır
    @createdBy = 1;

Distance Calculation (Haversine Formula):

distance_km = geography::Point(lat1, lon1, 4326).STDistance(geography::Point(lat2, lon2, 4326)) / 1000.0

Duration Calculation:

  • Average cruise speed: 800 km/h
  • totalDuration (minutes) = ROUND((distance_km / 800.0) × 60, 0)
  • Minimum duration: 30 minutes

Price Calculation (if basePrice is NULL):

basePrice = ROUND(350.0 + (distance_km × 5.0) / 2.0, 2)

Automatic Aircraft Selection:

  • Selects from active aircraft belonging to the airline
  • Finds one with no overlapping flights in the departure-arrival window
  • Seat capacity is calculated automatically (from Seats table)

15. sp_UpdateFlight

File: manager/01_sp_FlightManagement.sql

EXEC sp_UpdateFlight
    @flightId = 100,
    @basePrice = 500.00,
    @status = 'scheduled',
    @updatedBy = 1;

Validation: Departed/arrived flights cannot be updated

16. sp_DeleteFlight

File: manager/01_sp_FlightManagement.sql

EXEC sp_DeleteFlight
    @flightId = 100,
    @deletedBy = 1;

Cascade: Reservations, ReservationFlights, Passengers, Tickets are deleted

17. sp_GetAllReservations

File: manager/02_sp_ReservationManagement.sql (contains 4 SPs)

EXEC sp_GetAllReservations
    @status = 'confirmed',
    @fromDate = '2025-11-01',
    @toDate = '2025-11-30',
    @userId = NULL,
    @email = NULL,
    @flightNumber = NULL,
    @airlineCode = NULL,
    @minAmount = NULL,
    @maxAmount = NULL,
    @page = 1,
    @limit = 20;

Advanced Filtering: user, email, flight, airline, status, date range, price range Pagination: Configurable, max 100 per page

18. sp_UpdateReservationStatus

File: manager/02_sp_ReservationManagement.sql

EXEC sp_UpdateReservationStatus
    @reservationId = 100,
    @newStatus = 'cancelled',
    @updatedBy = 1,
    @notes = 'Manager cancelled due to flight cancellation';

Status Transitions (Validation):

  • pending → confirmed (payment completed)
  • pending → cancelled (payment not made/cancelled)
  • confirmed → cancelled (cancelled - seats returned)
  • confirmed → completed (flight occurred - automatic)

19. sp_GetReservationDetails

File: manager/02_sp_ReservationManagement.sql

EXEC sp_GetReservationDetails @reservationId = 100;

3 Resultsets:

  1. Reservation Header: code, totalAmount, status, createdAt, updatedAt
  2. Associated Flights: flightNumber, segments, departureTime, arrivalTime, prices
  3. Passengers + Tickets: firstName, lastName, type, seatClass, seatNumber, ticketPrice

20. sp_DeleteReservation

File: manager/02_sp_ReservationManagement.sql

EXEC sp_DeleteReservation
    @reservationId = 100,
    @deletedBy = 1;

Cascade: Passengers, Tickets, ReservationFlights are deleted Seat Release: Flights.availableSeats increases

Admin Procedures (System Administrator)

Note: Admin category contains 4 files, each with 4 SPs (Get, Create, Update, Delete) = total of 13 SPs (AuditLog has only 1 Get SP)

21. sp_GetUsers

File: admin/01_sp_UserManagement.sql (contains 4 SPs)

EXEC sp_GetUsers
    @firstName = 'John',
    @lastName = 'Doe',
    @email = 'user@example.com',
    @userRole = 'customer',
    @isActive = 1,
    @page = 1,
    @limit = 20;

Advanced Filtering: firstName, lastName, email, userRole, isActive, nationalIdNumber, nationality Pagination: Configurable page/limit

22. sp_CreateUser

File: admin/01_sp_UserManagement.sql

EXEC sp_CreateUser
    @email = 'newuser@example.com',
    @passwordHash = 'hashed_password',
    @firstName = 'John',
    @lastName = 'Doe',
    @phoneNumber = '+905551234567',
    @dateOfBirth = '1990-01-15',
    @nationality = 'Turkey',
    @nationalIdNumber = '12345678901',
    @userRole = 'customer';

Validation: Email uniqueness, userRole check (customer/manager/admin)

23. sp_UpdateUser

File: admin/01_sp_UserManagement.sql

EXEC sp_UpdateUser
    @userId = 1,
    @email = 'updated@example.com',
    @firstName = 'Updated',
    @lastName = 'Name',
    @userRole = 'manager';

Business Logic: NULL parameters preserve existing values

24. sp_DeleteUser

File: admin/01_sp_UserManagement.sql

EXEC sp_DeleteUser @userId = 1;

Business Logic: Hard delete (user and related records are deleted)

25. sp_GetAirlines

File: admin/02_sp_AirlineManagement.sql (contains 4 SPs)

EXEC sp_GetAirlines
    @airlineCode = 'TK',
    @airlineName = 'Turkish',
    @country = 'Turkey',
    @isActive = 1,
    @page = 1,
    @limit = 50;

26. sp_CreateAirline

File: admin/02_sp_AirlineManagement.sql

EXEC sp_CreateAirline
    @airlineCode = 'TK',
    @airlineName = 'Turkish Airlines',
    @country = 'Turkey',
    @isActive = 1;

Validation: airlineCode uniqueness (IATA 2-letter code)

27. sp_UpdateAirline

File: admin/02_sp_AirlineManagement.sql

EXEC sp_UpdateAirline
    @airlineId = 1,
    @airlineName = 'Updated Name',
    @country = 'Turkey',
    @isActive = 1;

28. sp_DeleteAirline

File: admin/02_sp_AirlineManagement.sql

EXEC sp_DeleteAirline @airlineId = 1;

Business Logic: Hard delete

29. sp_GetAirports

File: admin/03_sp_AirportManagement.sql (contains 4 SPs)

EXEC sp_GetAirports
    @airportCode = 'IST',
    @airportName = 'Istanbul',
    @city = 'Istanbul',
    @country = 'Turkey',
    @isInternational = 1,
    @isActive = 1,
    @page = 1,
    @limit = 50;

30. sp_CreateAirport

File: admin/03_sp_AirportManagement.sql

EXEC sp_CreateAirport
    @airportCode = 'IST',
    @airportName = 'Istanbul Airport',
    @city = 'Istanbul',
    @country = 'Turkey',
    @latitude = 41.275278,
    @longitude = 28.751944,
    @isInternational = 1,
    @isActive = 1;

Validation: airportCode uniqueness (IATA 3-letter code), latitude/longitude for distance calculation

31. sp_UpdateAirport

File: admin/03_sp_AirportManagement.sql

EXEC sp_UpdateAirport
    @airportId = 1,
    @airportName = 'Updated Name',
    @city = 'Istanbul',
    @isInternational = 1;

32. sp_DeleteAirport

File: admin/03_sp_AirportManagement.sql

EXEC sp_DeleteAirport @airportId = 1;

33. sp_GetAuditLogs

File: admin/04_sp_AuditLog.sql (contains 1 SP - read-only)

EXEC sp_GetAuditLogs
    @tableName = 'Reservations',
    @recordId = 100,
    @action = 'UPDATE',
    @changedBy = 1,
    @actorEmail = 'admin@example.com',
    @fromDate = '2025-11-01',
    @toDate = '2025-11-30',
    @page = 1,
    @limit = 50;

Filtering: Table, recordId, action, user, email, date range Returned Fields: auditId, tableName, recordId, action, oldValues (JSON), newValues (JSON), changedBy, changedAt

Maintenance Procedures (Automated Maintenance Tasks)

Note: These SPs are automatically executed by SQL Agent jobs (at 5-60 minute intervals).

34. sp_ExpirePendingReservations

File: maintenance/01_sp_ExpirePendingReservations.sql Purpose: Automatically cancels pending reservations (30 minute timeout) Algorithm: Pending reservations where createdAt + 30 minutes > NOW → status = 'cancelled'

EXEC sp_ExpirePendingReservations @PendingMinutes = 30;

Business Logic:

  • Unpaid reservations are automatically cancelled
  • Seats are returned to inventory (availableSeats not incremented - already decremented during payment)
  • AuditLog entry is created (action: AUTO_EXPIRE_PENDING)

Job Schedule: Every 5 minutes

35. sp_UpdateFlightStatuses

File: maintenance/02_sp_UpdateFlightStatuses.sql Purpose: Automatically updates flight statuses (departureTime check) Algorithm: Scheduled flights where departureTime <= NOW → status = 'departed'

EXEC sp_UpdateFlightStatuses;

Business Logic:

  • Flights past departure time are marked as 'departed'
  • Cancelled/arrived flights are skipped
  • AuditLog entry is created

Job Schedule: Every 10 minutes

36. sp_UpdateReservationStatuses

File: maintenance/03_sp_UpdateReservationStatuses.sql Purpose: Updates reservation statuses based on flight departure Algorithm:

  • Confirmed + departed flight → status = 'completed'
  • Pending + departed flight → status = 'expired'
EXEC sp_UpdateReservationStatuses;

Business Logic:

  • Confirmed reservations: Marked as 'completed' if flight has departed
  • Pending reservations: Marked as 'expired' if flight has departed (unpaid, now invalid)
  • AuditLog entries are created

Job Schedule: Every 15 minutes

37. sp_ExpireTokens

File: maintenance/04_sp_ExpireToken.sql Purpose: Automatically expires JWT tokens (inactivity timeout) Algorithm: Users where lastLogin + @hoursExpire > NOW → tokenVersion++

EXEC sp_ExpireTokens @hoursExpire = 24; -- 24 hour inactivity

Business Logic:

  • tokenVersion is incremented → all existing JWTs become invalid
  • Backend checks token.version <= user.tokenVersion on every request
  • Re-login required

Job Schedule: Every 1 hour

38. sp_AutoCheckIn

File: maintenance/05_sp_AutoCheckIn.sql Purpose: Performs automatic check-in (1 hour before departure, if manual check-in not done) Algorithm: Unchecked-in passengers where departureTime - 1 hour <= NOW → random seat assignment + CheckIn record

EXEC sp_AutoCheckIn;

Business Logic:

  • Finds unchecked-in passengers in confirmed reservations
  • Runs for flights 1 hour before departure
  • Random seat assignment: Uses NEWID() to select seat matching passenger's seatClass
  • CheckIn record is created (status: 'auto_checked_in')
  • Passengers.seatId is updated
  • Flights.availableSeats is not updated (already decremented during payment)
  • If all passengers are checked in → Reservation.status = 'auto_checked_in'
  • AuditLog entry is created

Job Schedule: Every 5 minutes

Check-in Window:

  • Opens: 24 hours before departure (after payment)
  • Manual Check-in: 24 hours before - 1 hour before
  • Auto Check-in: 1 hour before departure (if not manually done)

📊 Stored Procedures Summary Table

# SP Name Category File Purpose
1 sp_CreateReservation Customer customer/01 Create reservation
2 sp_CancelReservation Customer customer/02 Cancel reservation
3 sp_SearchFlights Customer customer/03 Search flights (direct+connecting)
4 sp_GetOrigins Customer customer/03 List origin airports
5 sp_GetAvailableDates Customer customer/03 Available flight dates
6 sp_GetReachableAirports Customer customer/03 Reachable destinations
7 sp_ProcessPayment Customer customer/04 Process payment
8 sp_GetUserPreferences Customer customer/05 Get preferences
9 sp_SetUserPreferences Customer customer/05 Update preferences
10 sp_DeleteUserPreferences Customer customer/05 Delete preferences
11 sp_GetUserProfile Customer customer/06 Get profile
12 sp_UpdateUserProfile Customer customer/06 Update profile
13 sp_CheckIn Customer customer/07 Manual check-in
14 sp_CreateFlight Manager manager/01 Create flight (Haversine)
15 sp_UpdateFlight Manager manager/01 Update flight
16 sp_DeleteFlight Manager manager/01 Delete flight
17 sp_GetAllReservations Manager manager/02 List reservations
18 sp_UpdateReservationStatus Manager manager/02 Update reservation status
19 sp_GetReservationDetails Manager manager/02 Reservation details
20 sp_DeleteReservation Manager manager/02 Delete reservation
21 sp_GetUsers Admin admin/01 List users
22 sp_CreateUser Admin admin/01 Create user
23 sp_UpdateUser Admin admin/01 Update user
24 sp_DeleteUser Admin admin/01 Delete user
25 sp_GetAirlines Admin admin/02 List airlines
26 sp_CreateAirline Admin admin/02 Create airline
27 sp_UpdateAirline Admin admin/02 Update airline
28 sp_DeleteAirline Admin admin/02 Delete airline
29 sp_GetAirports Admin admin/03 List airports
30 sp_CreateAirport Admin admin/03 Create airport
31 sp_UpdateAirport Admin admin/03 Update airport
32 sp_DeleteAirport Admin admin/03 Delete airport
33 sp_GetAuditLogs Admin admin/04 Query audit log
34 sp_ExpirePendingReservations Maintenance maintenance/01 Expire pending reservations
35 sp_UpdateFlightStatuses Maintenance maintenance/02 Update flight statuses
36 sp_UpdateReservationStatuses Maintenance maintenance/03 Update reservation statuses
37 sp_ExpireTokens Maintenance maintenance/04 Expire tokens
38 sp_AutoCheckIn Maintenance maintenance/05 Automatic check-in

Categories:

  • Customer: 13 SPs (reservation, search, payment, preferences, profile, check-in)
  • Manager: 7 SPs (flight management 3 + reservation management 4)
  • Admin: 13 SPs (user 4 + airline 4 + airport 4 + audit 1)
  • Maintenance: 5 SPs (automated maintenance tasks)

🔢 Functions

The system contains 4 functions:

  • 2 Scalar Functions: Price calculation (ticket price, cancellation fee)
  • 2 Helper Functions: String concatenation (airport codes, flight numbers)

1. fn_CalculateTicketPrice

File: functions/02_fn_CalculateTicketPrice.sql Type: Scalar Function Returns: DECIMAL(10,2) - Calculated ticket price (TRY) Purpose: Calculates final ticket price based on flight, seat class, and passenger type

SELECT dbo.fn_CalculateTicketPrice(
    @flightId = 101,
    @seatClass = 'business',
    @passengerType = 'adult'
);
-- Example: basePrice 1000 TL → 1000 × 2.5 × 1.0 = 2500 TL

Pricing Formula:

finalPrice = basePrice × seatClassMultiplier × passengerTypeMultiplier

Seat Class Multipliers:

  • economy: 1.0x (base price)
  • business: 2.5x (premium seat, extra services)
  • first: 4.0x (luxury seat, premium services)
  • default: 1.0x (invalid value → economy)

Passenger Type Multipliers:

  • adult: 1.0x (full price)
  • young (12-17 years): 0.9x (10% discount)
  • child (2-11 years): 0.75x (25% discount)
  • infant (0-2 years): 0.1x (90% discount)
  • default: 1.0x (invalid value → adult)

Example Calculations (basePrice = 1000 TL):

  • Economy Adult: 1000 × 1.0 × 1.0 = 1000 TL
  • Business Child: 1000 × 2.5 × 0.75 = 1875 TL
  • First Infant: 1000 × 4.0 × 0.1 = 400 TL
  • Economy Young: 1000 × 1.0 × 0.9 = 900 TL

Important Notes:

  • Segment multipliers are applied at reservation level (not here)
  • Returns NULL for NULL flightId
  • Result is always rounded to 2 decimal places
  • Invalid seatClass/passengerType values default to base values

Used In:

  1. sp_SearchFlights - Price calculation for each segment (economy, business, first)
    • Called 6 times for direct flights (3 classes × 2 adult/total)
    • Called 12 times for 2-segment connecting flights
    • Called 18 times for 3-segment connecting flights
  2. sp_ProcessPayment - Ticket price validation during payment
  3. Frontend - Price preview modal
  4. Revenue Management - Revenue analysis and reporting

2. fn_CalculateCancellationFee

File: functions/01_fn_CalculateCancellationFee.sql Type: Scalar Function Returns: DECIMAL(10,2) - Cancellation fee (TRY) Purpose: Calculates cancellation fee based on time until departure Algorithm: Time until departure (hours) → Fee percentage → totalAmount × percentage

SELECT dbo.fn_CalculateCancellationFee(@reservationId = 100);
-- Example: 2000 TL reservation, 5 days before → 2000 × 0.25 = 500 TL fee

Cancellation Fee Structure (Industry Standard):

Time Until Departure      Fee %   Example (2000 TL reservation)
─────────────────────────────────────────────────────────────────
> 7 days (168+ hours)      10%     200 TL fee, 1800 TL refund
3-7 days (72-167 hours)    25%     500 TL fee, 1500 TL refund
1-3 days (24-71 hours)     50%     1000 TL fee, 1000 TL refund
< 24 hours (6-23 hours)    75%     1500 TL fee, 500 TL refund
< 6 hours                  100%    2000 TL fee, 0 TL refund (no refund)

Calculation Logic:

  1. Get reservation's totalAmount value
  2. Get FIRST segment's departure time (ReservationFlights.departureTime or Flights.departureTime fallback)
  3. Calculate hours until departure: DATEDIFF(HOUR, GETDATE(), firstDepartureTime)
  4. Determine fee percentage based on hours remaining (tiered structure)
  5. Calculate cancellation fee: totalAmount × feePercentage
  6. Round to 2 decimal places

Special Cases:

  • NULL reservation: Returns NULL
  • Past departure: 100% fee (no refund)
  • Negative hours: 100% fee (flight departed)
  • Snapshot usage: ReservationFlights.departureTime is prioritized (for flight changes)

Example Scenarios:

  • Cancel 10 days before: 2000 × 0.10 = 200 TL fee (1800 TL refund)
  • Cancel 5 days before: 2000 × 0.25 = 500 TL fee (1500 TL refund)
  • Cancel 2 days before: 2000 × 0.50 = 1000 TL fee (1000 TL refund)
  • Cancel 12 hours before: 2000 × 0.75 = 1500 TL fee (500 TL refund)
  • Cancel 3 hours before: 2000 × 1.00 = 2000 TL fee (no refund)

Used In:

  1. sp_CancelReservation - Fee calculation and refund amount determination
    • @cancellationFee = fn_CalculateCancellationFee(@reservationId)
    • @refundAmount = totalAmount - @cancellationFee
  2. Payment Processing - Refund amount determination
  3. Customer Notifications - Cancellation fee transparency to customers
  4. Frontend - Estimated fee display in cancellation preview screen

3. fn_ConcatAirportCodes

File: functions/03_fn_HelperConcats.sql Type: Helper Function (String Aggregation) Returns: NVARCHAR(MAX) - Chain of airport codes Purpose: Shows reservation's multi-segment route in readable format (destination codes) Technical: String concatenation with FOR XML PATH, sorted by segmentOrder

-- Outbound route: "IST → FRA → LHR"
SELECT dbo.fn_ConcatAirportCodes(
    @reservationId = 100,
    @direction = 'outbound'
);

-- Inbound route: "LHR → FRA → IST"
SELECT dbo.fn_ConcatAirportCodes(
    @reservationId = 100,
    @direction = 'inbound'
);

Business Logic:

  1. Filter from ReservationFlights table by reservation + direction
  2. Join each segment's destinationAirportId with Airports table
  3. Sort by segmentOrder (ASC - logical order)
  4. Concatenate airport codes with ' → ' separator (FOR XML PATH)
  5. Returns empty string if no results

Format Examples:

  • Direct flight: "AYT" (single destination)
  • 1 connection: "FRA → LHR" (2 segments)
  • 2 connections: "IST → FRA → LHR" (3 segments)
  • Round-trip outbound: "FRA → LHR"
  • Round-trip inbound: "LHR → FRA"

Used In:

  1. sp_GetAllReservations - Route chain display in reservation list
    • Outbound route chain: originCode + ' → ' + fn_ConcatAirportCodes(reservationId, 'outbound')
    • Inbound route chain: originCode + ' → ' + fn_ConcatAirportCodes(reservationId, 'inbound')
  2. sp_GetReservationDetails - Full route in detailed reservation view
  3. Reporting Queries - Manager dashboards and revenue reports
  4. Frontend - Route display on reservation detail page

4. fn_ConcatFlightNumbers

File: functions/03_fn_HelperConcats.sql Type: Helper Function (String Aggregation) Returns: NVARCHAR(MAX) - Chain of flight numbers Purpose: Shows flight numbers used by reservation in sequential order Technical: String concatenation with FOR XML PATH, sorted by segmentOrder

-- Outbound flights: "TK123 → LH456 → BA789"
SELECT dbo.fn_ConcatFlightNumbers(
    @reservationId = 100,
    @direction = 'outbound'
);

-- Inbound flights: "BA321 → LH654 → TK987"
SELECT dbo.fn_ConcatFlightNumbers(
    @reservationId = 100,
    @direction = 'inbound'
);

Business Logic:

  1. Filter from ReservationFlights table by reservation + direction
  2. Join each segment's flightId with Flights table
  3. Sort by segmentOrder (ASC - logical order)
  4. Concatenate flight numbers with ' → ' separator (FOR XML PATH)
  5. Returns empty string if no results

Format Examples:

  • Direct flight: "TK123" (single flight)
  • 1 connection: "TK123 → LH456" (2 flights)
  • 2 connections: "TK123 → LH456 → BA789" (3 flights)
  • Round-trip outbound: "TK123 → LH456"
  • Round-trip inbound: "BA321 → LH654"

Used In:

  1. sp_GetAllReservations - Flight chain display in reservation list
    • Outbound flight chain: fn_ConcatFlightNumbers(reservationId, 'outbound')
    • Inbound flight chain: fn_ConcatFlightNumbers(reservationId, 'inbound')
  2. sp_GetReservationDetails - Flights used in detailed reservation view
  3. Customer Service - Reservation lookup
  4. Frontend - Flight information on reservation detail page

📊 Functions Summary Table

# Function Name File Type Return Value Purpose
1 fn_CalculateTicketPrice functions/02 Scalar DECIMAL(10,2) Ticket price calculation (class × type)
2 fn_CalculateCancellationFee functions/01 Scalar DECIMAL(10,2) Cancellation fee calculation (time-based)
3 fn_ConcatAirportCodes functions/03 Helper NVARCHAR(MAX) Airport codes chain (IST→FRA→LHR)
4 fn_ConcatFlightNumbers functions/03 Helper NVARCHAR(MAX) Flight numbers chain (TK123→LH456)

Usage Statistics:

  • fn_CalculateTicketPrice: Used ~36 times (sp_SearchFlights direct+2seg+3seg + sp_ProcessPayment)
  • fn_CalculateCancellationFee: Used 4 times (sp_CancelReservation + frontend)
  • fn_ConcatAirportCodes: Used 2 times (sp_GetAllReservations outbound/inbound)
  • fn_ConcatFlightNumbers: Used 2 times (sp_GetAllReservations outbound/inbound)

🔔 Triggers

The system contains 7 triggers:

  • 5 Audit Triggers: Record table changes to AuditLog
  • 1 Auto-Update Trigger: Automatic flight status updates
  • 1 Validation Trigger: Reservation validation (passthrough)

Audit Triggers (5 total)

1. tr_AuditAirlineChanges

File: triggers/01_tr_AuditAirlineChanges.sql | Table: Airlines | Event: INSERT, UPDATE, DELETE Purpose: Records airline master data changes to AuditLog (JSON format, delta tracking) Used In: sp_CreateAirline, sp_UpdateAirline, sp_DeleteAirline

2. tr_AuditFlightChanges

File: triggers/02_tr_AuditFlightChanges.sql | Table: Flights | Event: INSERT, UPDATE, DELETE Purpose: Records flight changes (price, status, route) to AuditLog Used In: sp_CreateFlight, sp_UpdateFlight, sp_DeleteFlight, sp_UpdateFlightStatuses

3. tr_AuditPaymentChanges

File: triggers/03_tr_AuditPaymentChanges.sql | Table: Payments | Event: INSERT, UPDATE Purpose: Records financial transactions to AuditLog (critical for compliance) Feature: DELETE not supported (financial records never deleted) Used In: sp_ProcessPayment, sp_CancelReservation

4. tr_AuditReservationChanges

File: triggers/04_tr_AuditReservationChanges.sql | Table: Reservations | Event: INSERT, UPDATE Purpose: Records reservation lifecycle (pending → confirmed → cancelled → completed) to AuditLog Used In: sp_CreateReservation, sp_CancelReservation, sp_UpdateReservationStatus, sp_ExpirePendingReservations, sp_UpdateReservationStatuses

5. tr_AuditUserChanges

File: triggers/05_tr_AuditUserChanges.sql | Table: Users | Event: INSERT, UPDATE, DELETE Purpose: Records user account management to AuditLog (security compliance, GDPR) Feature: Password hash changes recorded as '[password_changed]' (hash not stored) Used In: sp_Register, sp_UpdateUserProfile, sp_UpdateUser, sp_DeleteUser

Audit Triggers Common Features:

  • JSON format oldValues/newValues
  • Delta tracking (only changed fields)
  • changedBy tracking (userId or NULL)
  • Set-based operations (performance optimized)

6. tr_UpdateFlightStatus

File: triggers/06_tr_UpdateFlightStatus.sql Table: Flights Event: AFTER INSERT, UPDATE Purpose: Automatically updates flight statuses based on departure/arrival times

Status Transitions:

NOW < departureTime - 1h    → scheduled
departureTime - 1h ≤ NOW    → boarding
NOW ≥ departureTime         → departed
NOW ≥ arrivalTime           → arrived
cancelled (manual)          → cancelled (skip)

Business Logic:

  • Affected flights are iterated with cursor
  • Cancelled flights are skipped (manual status preserved)
  • Each status change is recorded to AuditLog

Used In: sp_UpdateFlightStatuses (maintenance job), sp_CreateFlight, sp_UpdateFlight

7. tr_ValidateReservation

File: triggers/07_tr_ValidateReservation.sql Table: Reservations Event: INSTEAD OF INSERT Purpose: Reservation validation (passthrough design)

Business Logic:

  • Trigger only inserts data (passthrough)
  • Actual validation done in sp_CreateReservation stored procedure
  • Required for multi-leg reservations (trigger doesn't contain complex logic)

Why Passthrough?

  • INSTEAD OF trigger can only be one (SQL Server limitation)
  • SP provides better error handling
  • Transaction management easier at SP level

Used In: sp_CreateReservation (primary validation)

📊 Triggers Summary Table

# Trigger Name File Table Event Purpose
1 tr_AuditAirlineChanges triggers/01 Airlines INSERT, UPDATE, DELETE Airline audit trail
2 tr_AuditFlightChanges triggers/02 Flights INSERT, UPDATE, DELETE Flight audit trail
3 tr_AuditPaymentChanges triggers/03 Payments INSERT, UPDATE Payment audit trail
4 tr_AuditReservationChanges triggers/04 Reservations INSERT, UPDATE Reservation audit trail
5 tr_AuditUserChanges triggers/05 Users INSERT, UPDATE, DELETE User audit trail
6 tr_UpdateFlightStatus triggers/06 Flights INSERT, UPDATE Automatic flight status update
7 tr_ValidateReservation triggers/07 Reservations INSTEAD OF INSERT Reservation validation

Categories:

  • Audit Triggers (5): Airlines, Flights, Payments, Reservations, Users
  • Auto-Update Trigger (1): Flight status management
  • Validation Trigger (1): Reservation integrity

📊 Views

The system contains 8 views:

  • 1 Real-time View: Flight availability check (vw_FlightAvailability)
  • 4 Analytics Views: Dashboard statistics, flight performance, occupancy rates, popular routes
  • 2 Reporting Views: Reservation summary, periodic revenue
  • 1 Search View: Available flights search (vw_AvailableFlights)

1. vw_FlightAvailability

File: views/01_vw_FlightAvailability.sql Type: Real-time View (Direct read from Flights.availableSeats) Purpose: O(1) performance for flight availability check

Returned Fields: flightId, flightNumber, availableSeats, status, departureTime, arrivalTime, airportIds, basePrice

Used In:

  • flight.controller.js: getFlightStatus (line 413, 546, 924)
  • vw_AvailableFlights: Used for JOIN
  • sp_SearchFlights: Availability check (indirect)

2. vw_DashboardStats

File: views/02_vw_DashboardStats.sql Type: Analytics View (CTE-based aggregations) Purpose: System-wide metrics for admin dashboard (returns single row)

Returned Fields:

  • Revenue: totalRevenue, todayRevenue, avgReservationValue
  • Reservations: totalReservations, confirmedReservations, pendingReservations, cancelledReservations, completedReservations
  • Flights: activeFlights, departedToday
  • Passengers: totalPassengers
  • Occupancy: avgOccupancyRate

Used In:

  • admin.controller.js: getDashboardStats (line 1083, 1099)

3. vw_FlightOccupancy

File: views/03_vw_FlightOccupancy.sql Type: Analytics View (Occupancy metrics) Purpose: Flight occupancy rate analysis

Returned Fields: flightId, flightNumber, airlineName, originCode, destinationCode, departureTime, totalCapacity, bookedSeats, availableSeats, occupancyRate (%), reservationCount

Used In:

  • admin.controller.js: getAnalytics - flightOccupancy (line 1296)

4. vw_FlightPerformance

File: views/04_vw_FlightPerformance.sql Type: Analytics View (Comprehensive flight metrics) Purpose: Flight performance analysis (occupancy, cancellation rates, revenue)

Returned Fields:

  • Flight Info: flightId, flightNumber, airlineId, airlineName, airlineCode, route (origin→destination), departureTime, aircraftModel
  • Capacity: totalCapacity, bookedSeats, availableSeats, occupancyRate (%)
  • Reservations: reservationCount, confirmedReservations, cancelledReservations, cancellationRate (%)
  • Revenue: totalRevenue, avgTicketPrice
  • Status: flightStatus

Used In:

  • admin.controller.js: getAnalytics - flightPerformance, topPerformingFlights (line 1327, 1347)

5. vw_PopularRoutes

File: views/05_vw_PopularRoutes.sql Type: Analytics View (Route-level metrics) Purpose: Route-based analysis (demand planning, revenue optimization)

Returned Fields: originCode, originCity, destinationCode, destinationCity, flightCount, reservationCount, totalPassengers, totalRevenue, avgReservationValue, avgOccupancy (%), confirmedCount, cancelledCount

Used In:

  • admin.controller.js: getAnalytics - popularRoutes (line 1313)

6. vw_ReservationSummary

File: views/06_vw_ReservationSummary.sql Type: Reporting View (Complete reservation data) Purpose: Reservation details (user, flights, route, chain information)

Returned Fields:

  • Reservation: reservationId, reservationCode, totalAmount, status, numberOfPassengers, createdAt, updatedAt
  • User: userId, userEmail, userFirstName, userLastName
  • Journey: firstFlightId, lastFlightId, originCode, destinationCode, departureTime, arrivalTime, totalJourneyMinutes
  • Airline: airlineId, airlineCode, airlineName, airlineLogo
  • Chains: flightNumbersChain (TK001→TK002→TK003), routeChain (IST→ESB→AYT)
  • Segments: segmentCount

Used In:

  • reservation.controller.js: getMyReservations (line 625, 664)

7. vw_RevenueByPeriod

File: views/07_vw_RevenueByPeriod.sql Type: Reporting View (Payment-based revenue data) Purpose: Periodic revenue analysis (aggregation by date, airline, route, month)

Returned Fields:

  • Payment: paymentId, reservationId, totalAmount, paymentStatus
  • Period: reportDate, reportYear, reportMonth, monthName
  • Airline: airlineId, airlineName, airlineCode
  • Route: originCode, originCity, destinationCode, destinationCity
  • Passengers: numberOfPassengers

Used In:

  • admin.controller.js: getRevenueReport - by date/airline/route/month (line 1168, 1181, 1195, 1208)

8. vw_AvailableFlights

File: views/08_vw_AvailableFlights.sql Type: Search View (Flight search results) Purpose: Flight search results (pre-formatted data for frontend)

Returned Fields:

  • Flight: flightId, flightNumber, basePrice, flightStatus, isDirectFlight, totalDuration
  • Airline: airlineId, airlineName, airlineCode, airlineLogo
  • Route: originAirport (code, name, city, country), destinationAirport, routeDisplay
  • Times: departureTime, arrivalTime, totalMinutes, duration (formatted)
  • Availability: availableSeats
  • Pricing: economyPricePerPerson, businessPricePerPerson, firstClassPricePerPerson
  • Segments: JSON array (segment details)
  • Type: flightType (Direct/Connecting)

Filtering: status IN ('scheduled','boarding'), availableSeats > 0, departureTime > NOW+1h, active airlines only

Used In:

  • flight.controller.js: getAllFlights (line 597, 613)

📊 Views Summary Table

# View Name File Type Purpose Used In Controller
1 vw_FlightAvailability views/01 Real-time Flight availability check (O(1)) flight.controller.js
2 vw_DashboardStats views/02 Analytics Admin dashboard metrics admin.controller.js
3 vw_FlightOccupancy views/03 Analytics Flight occupancy rates admin.controller.js
4 vw_FlightPerformance views/04 Analytics Flight performance analysis admin.controller.js
5 vw_PopularRoutes views/05 Analytics Popular routes analysis admin.controller.js
6 vw_ReservationSummary views/06 Reporting Reservation details reservation.controller.js
7 vw_RevenueByPeriod views/07 Reporting Periodic revenue report admin.controller.js
8 vw_AvailableFlights views/08 Search Flight search results flight.controller.js

Categories:

  • Real-time (1): vw_FlightAvailability
  • Analytics (4): Dashboard stats, flight occupancy/performance, popular routes
  • Reporting (2): Reservation summary, revenue by period
  • Search (1): Available flights

Performance Features:

  • CTE-based aggregations (nested aggregate errors prevented)
  • Pre-calculated joins (complex JOINs avoided in controllers)
  • Indexed lookups (vw_FlightAvailability)
  • Read-only (data integrity preserved)

⏰ SQL Agent Jobs

The system contains 5 jobs:

  • 2 Maintenance Jobs: Token expiration, pending reservation cleanup
  • 2 Status Update Jobs: Flight status sync, reservation status sync
  • 1 Automation Job: Auto check-in before departure

1. ExpireTokensJob

File: jobs/01_create_expire_tokens_job.sql Frequency: Hourly (Every 1 hour, 24/7) Stored Procedure: sp_ExpireTokens(@hoursExpire = 24) Purpose: Invalidates authentication tokens older than 24 hours to ensure security

Business Logic: Find tokens (lastActivity + 24h < NOW) → tokenVersion++ → AuditLog record Benefits: Stale token cleanup, security compliance, forced re-authentication

Used In: Runs automatically (no manual call from backend)

2. ExpirePendingReservations_Job

File: jobs/02_create_expire_pending_reservations_job.sql Frequency: Every 5 minutes Stored Procedure: sp_ExpirePendingReservations(@PendingMinutes = 30) Purpose: Cancels reservations without payment for 30+ minutes, frees seats

Business Logic: Find pending reservations (createdAt + 30min < NOW) → status = 'expired' → availableSeats++ Benefits: Seat blocking prevented, inventory optimization, customer experience (fair access)

Used In: Runs automatically (no manual call from backend)

3. UpdateFlightStatuses_Job

File: jobs/03_create_update_flight_status_job.sql Frequency: Every 1 minute (near-real-time) Stored Procedure: sp_UpdateFlightStatuses Purpose: Automatically updates flight statuses based on departure/arrival times

Status Transitions: scheduled → boarding (departure - 1h) → departed (after departure) → arrived (at arrival) Benefits: Real-time status accuracy, booking eligibility control, customer notification support

Used In: Runs automatically (no manual call from backend)

4. UpdateReservationStatuses_Job

File: jobs/04_create_update_reservation_status_job.sql Frequency: Every 5 minutes Stored Procedure: sp_UpdateReservationStatuses Purpose: Synchronizes reservation statuses with flight statuses

Status Transitions: confirmed → completed (flight arrived), pending → expired (flight departed unpaid) Benefits: Flight-reservation consistency, post-flight analytics, accurate customer support data

Used In: Runs automatically (no manual call from backend)

5. AutoCheckIn_Job

File: jobs/05_job_AutoCheckIn.sql Frequency: Every 5 minutes Stored Procedure: sp_AutoCheckIn Purpose: Performs automatic check-in 1 hour before departure for passengers who haven't checked in, assigns random seats

Business Logic: Find confirmed reservations (departure - 1h) → Unchecked-in passengers → Assign random seat (by seatClass) → CheckIn record + AuditLog Benefits: No-show prevention, customer convenience, airport check-in counter load reduction

Used In: Runs automatically (no manual call from backend)

📊 SQL Agent Jobs Summary Table

# Job Name File Frequency Stored Procedure Purpose
1 ExpireTokensJob jobs/01 Every 1 hour sp_ExpireTokens Token expiration (24h)
2 ExpirePendingReservations_Job jobs/02 Every 5 minutes sp_ExpirePendingReservations Pending reservation cleanup (30min)
3 UpdateFlightStatuses_Job jobs/03 Every 1 minute sp_UpdateFlightStatuses Flight status sync (time-based)
4 UpdateReservationStatuses_Job jobs/04 Every 5 minutes sp_UpdateReservationStatuses Reservation status sync
5 AutoCheckIn_Job jobs/05 Every 5 minutes sp_AutoCheckIn Auto check-in (1h before departure)

Categories:

  • Maintenance (2): Token expiration, pending cleanup
  • Status Sync (2): Flight status, reservation status
  • Automation (1): Auto check-in

Important Notes:

  • All jobs run in msdb context (SQL Agent database)
  • Idempotent design (deletes existing job before creating if exists)
  • Background execution (no manual call required from backend)
  • AuditLog integration (all operations are logged)

🚀 Setup & Management

Database Creation

Step 1: Create Database

cd database
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i reset_database.sql

Step 2: Create Schema

sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> << EOF
:r schema/00_cleanup.sql
:r schema/01_create_tables.sql
:r schema/02_create_indexes.sql
:r schema/03_create_constraints.sql
GO
EOF

Step 3: Create Stored Procedures

# Customer procedures
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i procedures/customer/01_sp_CreateReservation.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i procedures/customer/02_sp_CancelReservation.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i procedures/customer/03_sp_SearchFlights.sql
# ... etc for all procedures

Step 4: Create Functions

sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i functions/fn_CalculateTicketPrice.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i functions/fn_GetAvailableSeats.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i functions/fn_CalculateCancellationFee.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i functions/fn_HelperConcats.sql

Step 5: Create Triggers

sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i triggers/tr_ValidateReservation.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i triggers/tr_UpdateFlightStatus.sql
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i triggers/tr_AuditReservationChanges.sql

Step 6: Create SQL Agent Jobs (Optional)

# If SQL Agent is running and has permissions:

# 1. Token expiration job
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i jobs/create_expire_tokens_job.sql

# 2. Pending reservation expiration job
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i jobs/create_expire_pending_reservations_job.sql

# 3. Flight status update job
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i jobs/create_update_flight_status_job.sql

# 4. Reservation status update job
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i jobs/create_update_reservation_status_job.sql

# 5. Automatic check-in job (1 hour before)
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i jobs/create_auto_checkin_job.sql

Complete Reset (Dangerous!)

# Clear all objects and data, then reset
sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> -i reset_database.sql

# Then re-run steps 1-7

⚡ Performance & Indexes

Created Indexes

Clustered Indexes (Primary Keys):

  • On all 13 tables
  • B-tree structure

Non-Clustered Indexes (Performance):

-- Speed up flight search
CREATE INDEX idx_Flights_AirportDeparture 
ON Flights(originAirportId, departureTime);

CREATE INDEX idx_Flights_AirportDestination 
ON Flights(destAirportId);

-- Speed up user-preference relationship
CREATE INDEX idx_UserPreferences_UserId 
ON UserPreferences(userId);

-- Speed up reservation queries
CREATE INDEX idx_Reservations_UserStatus 
ON Reservations(userId, status);

CREATE INDEX idx_Reservations_Code 
ON Reservations(reservationCode);

-- Speed up seat availability check
CREATE INDEX idx_Seats_AircraftClass 
ON Seats(aircraftId, seatClass);

🌱 Data Seeding & Testing

Generate Test Data

sqlcmd -S <SERVER> -U <USER> -P <PASSWORD> << EOF
:r seed/00_cleanup_data.sql
:r seed/01_seed_users.sql
:r seed/02_seed_airlines.sql
:r seed/03_seed_airports.sql
:r seed/04_seed_aircraft.sql
:r seed/05_seed_flights.sql
GO
EOF
# Run Node.js seed scripts
cd seed
node generate-flights.js     
node generate-reservations.js

👨‍💻 Author

Seymen 'xaprier' Kalkan

Part of the XaprAir Flight Management System

Main Documentation | Frontend | Backend

⬆ Back to Top