Test01

Tables (40)

ADDITIONAL_SERVICE

  • ServiceID — int
  • TicketID — int — Foreign key → TICKET.TicketID — NOT NULL
  • PAXID — int — NOT NULL
  • SEGID — int — NOT NULL
  • ProductID — int
  • Description — varchar(255)
  • Price — DECIMAL(10, 2)

AIRLINE

  • AirLineID — int
  • AirLineName — varchar(10) — NOT NULL
  • AirLineBase — varchar(10) — NOT NULL

ATCAdvisory

  • AdvisoryID — int
  • AdvisoryDateTime — DATETIME — NOT NULL
  • AdvisoryType — ENUM — NOT NULL
  • Severity — ENUM
  • Status — ENUM
  • Description — text — NOT NULL

Accessory

  • ProductID — int — Foreign key → Product.ProductID
  • AccessoryType — varchar(100) — NOT NULL
  • Price — int — NOT NULL
  • Description — text

AddOnService

  • ProductID — int — Foreign key → Product.ProductID
  • ServiceType — varchar(100) — NOT NULL
  • Price — int — NOT NULL
  • Description — text

AddOnServiceReservation

  • ProductID — int — Primary key — NOT NULL
  • PAXID — int — Foreign key → PAX.PAXID — NOT NULL
  • SEGID — int — Foreign key → SEG.SEGID — NOT NULL
  • PNR — varchar(20) — NOT NULL
  • ServiceDetail — varchar(255)
  • Quantity — int

AffectedFlights

  • AffectedID — int
  • AdvisoryID — int — Foreign key → ATCAdvisory.AdvisoryID — NOT NULL
  • FlightID — int — Foreign key → FlightPlan.FlightID — NOT NULL

Airport

  • AirportCode — varchar(10)
  • Name — varchar(100) — NOT NULL
  • City — varchar(100) — NOT NULL
  • Country — varchar(100) — NOT NULL
  • Timezone — varchar(50)
  • Latitude — DECIMAL(9, 6)
  • Longitude — DECIMAL(9, 6)
  • IsInternational — boolean
  • CreatedAt — DATETIME

BundleProduct

  • ProductID — int — Foreign key → Product.ProductID
  • BundleName — varchar(255) — NOT NULL
  • BundleType — varchar(100)
  • Price — int
  • Description — text

BundleProductDetail

  • BundleDetailID — int
  • BundleProductID — int — Foreign key → BundleProduct.ProductID — NOT NULL
  • ComponentProductID — int — Foreign key → Product.ProductID — NOT NULL
  • Quantity — int
  • Price — int

COUPON

  • CouponID — int
  • TicketID — int — Foreign key → TICKET.TicketID — NOT NULL
  • SEGID — int — NOT NULL
  • CouponNumber — varchar(50)
  • CouponStatus — ENUM

CorporateBenefit

  • BenefitID — int
  • CorporateCustomerID — int — Foreign key → CorporateCustomer.CorporateCustomerID — NOT NULL
  • BenefitName — varchar(100)
  • BenefitDetails — text
  • ContractDate — date

CorporateCustomer

  • CorporateCustomerID — int — Foreign key → Customer.CustomerID
  • CustomerID — int
  • EmailVerified — boolean
  • PhoneVerified — boolean

Crew

  • CrewID — int
  • Name — varchar(100) — NOT NULL
  • ROLE — ENUM — NOT NULL
  • ContactInfo — varchar(100)
  • EmploymentDate — date — NOT NULL
  • created_at — DATETIME

CrewAssignment

  • AssignmentID — int
  • CrewID — int — Foreign key → Crew.CrewID — NOT NULL
  • ScheduleID — int — Foreign key → FlightSchedule.ScheduleID — NOT NULL
  • ROLE — ENUM — NOT NULL

Customer

  • CustomerID — int — Foreign key → CustomerID.CustomerID
  • CustomerType — ENUM — NOT NULL

CustomerID

  • CustomerID — int
  • Name — varchar(20) — NOT NULL
  • RegistraionNumber — varchar(14) — NOT NULL
  • PassportNumber — varchar(9)
  • Age — int
  • Phone — varchar(20)
  • Email — varchar(30)

Employee

  • EmployeeID — int — Foreign key → IndividualCustomer.IndividualCustomerID
  • CorporateCustomerID — int — Foreign key → CorporateCustomer.CorporateCustomerID — NOT NULL

FARE

  • FareID — int
  • TicketID — int — Foreign key → TICKET.TicketID — NOT NULL
  • Amount — DECIMAL(10, 2)
  • Currency — varchar(10)
  • PassengerType — ENUM

FarePlan

  • FarePlanId — int
  • AirLineID — int — Foreign key → AIRLINE.AirLineID — NOT NULL
  • AircraftType — varchar(50) — NOT NULL
  • PlannedDepartureTime — time — NOT NULL
  • PlannedArrivalTime — time — NOT NULL
  • StartDate — date — NOT NULL
  • EndDate — date — NOT NULL
  • Status — ENUM

FlightPlan

  • FlightID — int
  • AirlineCode — varchar(10) — NOT NULL
  • AirlineID — int — NOT NULL
  • AircraftType — varchar(50) — NOT NULL
  • DepartureCity — varchar(100) — NOT NULL
  • DepartureAirportCode — varchar(10) — Foreign key → Airport.AirportCode — NOT NULL
  • ArrivalCity — varchar(100) — NOT NULL
  • ArrivalAirportCode — varchar(10) — Foreign key → Airport.AirportCode — NOT NULL
  • PlannedDepartureTime — time — NOT NULL
  • PlannedArrivalTime — time — NOT NULL

FlightSchedule

  • ScheduleID — int
  • FlightID — int — Foreign key → FlightPlan.FlightID — NOT NULL
  • ScheduledDate — date — NOT NULL
  • ActualDepartureTime — DATETIME
  • ActualArrivalTime — DATETIME
  • Status — ENUM

FlightSegmentDetail

  • SegmentID — int
  • FlightID — int — NOT NULL
  • DepartureAirportCode — varchar(10) — NOT NULL
  • ArrivalAirportCode — varchar(10) — NOT NULL
  • DepartureTime — DATETIME — NOT NULL
  • ArrivalTime — DATETIME — NOT NULL
  • MileageEarnable — int
  • SegmentOrder — int

FlightTicket

  • ProductID — int — Foreign key → Product.ProductID
  • FlightNumber — varchar(10) — NOT NULL
  • AirlineCode — varchar(10)
  • DepartureCity — varchar(100) — NOT NULL
  • ArrivalCity — varchar(100) — NOT NULL
  • DepartureDate — date — NOT NULL
  • ArrivalDate — date — NOT NULL
  • OutboundDepartureTime — time
  • OutboundArrivalTime — time
  • InboundDepartureTime — time
  • InboundArrivalTime — time
  • TravelGrade — ENUM
  • FareType — varchar(100)
  • BaggageAllowance — varchar(100)
  • FlightDuration — time
  • PriceAdult — int — NOT NULL
  • PriceChild — int
  • PriceInfant — int

IndividualCustomer

  • IndividualCustomerID — int — Foreign key → Customer.CustomerID
  • MembershipType — ENUM — NOT NULL
  • Verification_method — varchar(50)

PAX

  • PAXID — int
  • ReservationID — int — Foreign key → PNR.ReservationID — NOT NULL

PAYMENT_METHOD

  • PaymentID — int
  • TicketID — int — Foreign key → TICKET.TicketID — NOT NULL
  • MethodType — varchar(50)
  • Amount — DECIMAL(10, 2)

PNR

  • ReservationID — int
  • CustomerID — int — NOT NULL
  • FlightID — int — NOT NULL
  • ReservationDatetime — DATETIME — NOT NULL
  • Price — DECIMAL(10, 2) — NOT NULL

PREFERENCE

  • PreferenceID — int
  • CustomerID — int — Foreign key → Customer.CustomerID — NOT NULL
  • SeatPreference — ENUM
  • MealPreference — varchar(50)

PartnerProduct

  • ProductID — int — Foreign key → Product.ProductID
  • PartnerType — varchar(100) — NOT NULL
  • PartnerName — varchar(255) — NOT NULL
  • Price — int — NOT NULL
  • Description — text

Product

  • ProductID — int
  • ProductName — varchar(255) — NOT NULL
  • ProductType — ENUM — NOT NULL
  • Description — text
  • CreatedAt — timestamp
  • UpdatedAt — timestamp

REFUND

  • RefundID — int
  • TicketID — int — Foreign key → TICKET.TicketID — NOT NULL
  • RefundDate — date
  • Reason — text

REFUND_FARE

  • RefundFareID — int
  • RefundID — int — Foreign key → REFUND.RefundID — NOT NULL
  • Amount — DECIMAL(10, 2)

REFUND_PAYMENT

  • RefundPaymentID — int
  • RefundID — int — Foreign key → REFUND.RefundID — NOT NULL
  • MethodType — varchar(50)
  • Amount — DECIMAL(10, 2)

REFUND_TAX

  • RefundTaxID — int
  • RefundID — int — Foreign key → REFUND.RefundID — NOT NULL
  • TaxType — varchar(50)
  • Amount — DECIMAL(10, 2)

Route

  • RouteID — int
  • FlightID — int — Foreign key → FlightPlan.FlightID — NOT NULL
  • StopoverCity — varchar(100)
  • Distance — int

SEG

  • SEGID — int
  • ReservationID — int — Foreign key → PNR.ReservationID — NOT NULL
  • FlightID — int — NOT NULL
  • SegmentOrder — int — NOT NULL

SSR

  • SSRID — int
  • PAXID — int — Foreign key → PAX.PAXID
  • SEGID — int — Foreign key → SEG.SEGID
  • SSRCode — varchar(10) — NOT NULL
  • Description — Text
  • Status — ENUM
  • RequestedAt — timestamp

TAX

  • TAXID — int
  • TicketID — int — Foreign key → TICKET.TicketID — NOT NULL
  • TaxType — varchar(50)
  • Amount — DECIMAL(10, 2)

TICKET

  • TicketID — int
  • PAXID — int — NOT NULL
  • IssueDate — date
  • Status — varchar(50)