Designing a Constrained Ticket System for Flights: Oracle SQL Implementation

Designing a Constrained Ticket System for Flights

=====================================================

In this article, we will explore how to design a constrained ticket system for flights using Oracle SQL. The goal is to ensure that the number of tickets sold does not exceed the available seats on a flight plane.

Background and Requirements

Our ticket system consists of three main tables: flight, seat, and ticket. The flight table references the aircraft table, which contains information about the aircraft’s tail number, manufacturer, model, and airline. The seat table references both the aircraft and the flight tables.

The ticket table has foreign keys to both the flight and seat tables. We want to ensure that a ticket can only be sold for a seat on the same flight aircraft.

Current Implementation

The provided trigger trg_ticket_BRIU attempts to enforce this constraint using a count function on the flight table:

CREATE OR REPLACE TRIGGER trg_ticket_BRIU 
    BEFORE INSERT OR UPDATE ON Ticket 
    FOR EACH ROW 
DECLARE
    l_numberofseats flight.numberofseats%type;
BEGIN
    select numberofseats into l_numberofseats
    from flight
    where flightnumber=:new.flightnumber;

    IF :new.count(flightnumber) > l_numberofseats
    THEN
        raise_application_error(-2000, 'Not enough seats');
    END IF;
END;
/

However, this trigger has a syntax error due to the use of the COUNT function in a bind variable. We need to correct this and implement an alternative approach that ensures referential consistency between the flight, seat, and ticket tables.

Designing the Constrained System

We will redesign our system using the following steps:

Create separate tables for aircraft and seat.
Add a foreign key constraint to the flight table referencing both aircraft_id and seat_id.
Create a trigger on the ticket table that ensures referential consistency between flight_id and seat_id.

Step 1: Creating Separate Tables for Aircraft and Seat

CREATE TABLE aircraft (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT aircraft__id__pk PRIMARY KEY,
  tail_number  VARCHAR2(6)
               NOT NULL
               CONSTRAINT aircraft__tn__u UNIQUE
               CONSTRAINT aircraft__tn_chk CHECK(
                 REGEXP_LIKE(
                   tail_number,
                   '[A-Z]\d{1,5}|[A-Z]\d{1,4}[A-Z]|[A-Z]\d{1,3}[A-Z]{2}'
                 )
               ),
  manufacturer VARCHAR2(20)
               NOT NULL,
  model        VARCHAR2(20)
               NOT NULL,
  airline_id   CONSTRAINT aircraft__aid__fk REFERENCES airline(airline_id)
               NOT NULL
);

CREATE TABLE seat (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT seat__id__pk PRIMARY KEY,
  aircraft_id  CONSTRAINT seat__aid__fk REFERENCES aircraft(id)
               NOT NULL,
  seat_row     VARCHAR2(3)
               NOT NULL,
  seat_column  NUMBER
               NOT NULL,
  CONSTRAINT seat__aid_r_c__u UNIQUE (aircraft_id, seat_row, seat_column)
);

Step 2: Updating the Flight Table to Reference Aircraft and Seat

CREATE TABLE flight (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT flight__id__pk PRIMARY KEY,
  aircraft_id  CONSTRAINT flight__aid__fk REFERENCES aircraft(id)
               NOT NULL,
  -- ...
);

Step 3: Creating the Ticket Table with Foreign Keys to Flight and Seat

CREATE TABLE ticket (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT ticket__id__pk PRIMARY KEY,
  flight_id    CONSTRAINT ticket__fid__fk REFERENCES flight(id)
               NOT NULL,
  seat_id      CONSTRAINT ticket__sid__fk REFERENCES seat(id)
               NOT NULL,
  -- ...
  CONSTRAINT ticket__fid_sid__u UNIQUE (flight_id, seat_id)
);

Step 4: Creating the Constrained Trigger on the Ticket Table

CREATE TRIGGER ticket_check_seat_on_flight
  BEFORE INSERT OR UPDATE ON ticket
  FOR EACH ROW
DECLARE
  is_valid NUMBER(1);
BEGIN
  SELECT 1
  INTO   is_valid
  FROM   flight f
         INNER JOIN seat s
         ON (f.aircraft_id = s.aircraft_id)
  WHERE  f.id = :NEW.flight_id
  AND    s.id = :NEW.seat_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(
      -20000,
      'Flight and seat are on different aircraft.'
    );
END;
/

Example Use Cases

To test our constrained ticket system, let’s consider the following scenarios:

Creating a new flight with an existing aircraft:

INSERT INTO flight (aircraft_id, id)
VALUES (1, 100);

Creating a new seat on the same aircraft:

INSERT INTO seat (aircraft_id, seat_row, seat_column)
VALUES (1, 'A', 10);

Creating a new ticket referencing the flight and seat:

INSERT INTO ticket (flight_id, seat_id)
VALUES (100, 100);

Attempting to create a new ticket with an invalid flight or seat reference:

-- Invalid: Flight not exists on aircraft
INSERT INTO ticket (flight_id, seat_id)
VALUES (2, 100);

-- Invalid: Seat does not exist on the same aircraft as flight
INSERT INTO ticket (flight_id, seat_id)
VALUES (100, 200);

These examples demonstrate how our constrained system ensures referential consistency between the flight, seat, and ticket tables.

Conclusion

In this article, we have redesigned a constrained ticket system for flights using Oracle SQL. We created separate tables for aircraft and seat, added foreign key constraints to the flight table, and implemented a trigger on the ticket table to ensure referential consistency between flight_id and seat_id. This design ensures that tickets can only be sold for seats on the same flight aircraft, preventing errors due to mismatched references.

Last modified on 2025-02-02