Travel Agency Database System

A comprehensive SQL Server database system for managing a travel agency's operations, including tour management, reservations, payments, and customer relations.

Features

• Tour Management

  • Tour creation and scheduling
  • Guide assignment and availability tracking
  • Amenity management
  • Capacity control and participant tracking

• Reservation System

  • Client booking management
  • Payment processing
  • Insurance plan integration
  • Status tracking (Pending, Confirmed, Cancelled, Completed)

• Client Management

  • Client categorization with discount levels
  • Contact information management
  • Booking history tracking
  • Custom client categories

• Geographic Data

  • Multi-level geography hierarchy (Continents, Countries, Cities)
  • Support for international tours
  • Polish character support (Polish_CS_AS collation)

• Analytics and Reporting

  • Revenue analysis
  • Tour popularity metrics
  • Guide performance tracking
  • Destination analytics

Database Structure

Core Tables

• Clients
• Tours
• TourSchedules
• Reservations
• Payments
• Guides

Supporting Tables

• Continents
• Countries
• Cities
• TourTypes
• ClientCategories
• AmenityTypes
• InsurancePlans
• TourAmenities
• ReservationInsurance

Security Features

• Role-based access control
• Three user roles:
  • TravelManagerRole
  • TravelAgentRole
  • TravelReaderRole
• Granular permissions for different operations

Views

• vwOrderedToursAnalytics
• vwTourDetails
• vwUpcomingTours
• vwReservationSummary
• vwRevenueAnalysis
• vwPaymentAnalytics
• vwGuidePerformance
• vwDestinationPopularity

Stored Procedures

Client Management

• CreateClient
• UpdateClient
• GetClientTourHistory

Reservation Management

• CreateReservation
• UpdateReservationStatus
• AddInsuranceToReservation

Tour Management

• CreateTour
• CreateTourSchedule
• UpdateTourScheduleStatus
• SearchAvailableTours

Financial Management

• CreatePayment
• GetTourStatistics

Testing

The system includes comprehensive testing procedures:

• Business rules validation
• Data integrity checks
• User permission verification
• Trigger functionality testing
• Use case scenarios

Test Cases Include

• Polish character support
• Tour capacity validation
• Payment validation
• Reservation status flow
• Date validation
• Email uniqueness
• Insurance plan integration

Installation

1. Execute scripts in the following order:

   DatabaseCreation.sql
   TableStructure.sql
   StoredProcedures.sql
   TriggerCreation.sql
   Views.sql
   LoginsUsersRoles.sql
   DataPopulation.sql

2. Run test scripts:

   Tests.sql
   TestUseCases.sql