-- ================================================================
-- airport poc — relational ddl
-- oracle 23ai / oracle ai database
-- ================================================================
-- ── master data (static, rarely changes) ──────────────────────
create table airline (
airline_id number generated by default on null as identity primary key,
iata_code varchar2(3) not null constraint airline_iata_uk unique,
icao_code varchar2(4),
name varchar2(100) not null,
country varchar2(60) not null
);
create table airport_ref (
airport_id number generated by default on null as identity primary key,
iata_code varchar2(3) not null constraint airport_iata_uk unique,
city varchar2(100) not null,
country varchar2(60) not null,
timezone varchar2(50)
);
create table aircraft_type (
type_id number generated by default on null as identity primary key,
icao_type varchar2(4) not null constraint aircraft_type_uk unique,
name varchar2(100) not null,
seat_capacity number(4) not null
);
create table gate (
gate_id number generated by default on null as identity primary key,
terminal varchar2(10) not null,
gate_number varchar2(10) not null,
gate_type varchar2(3) default 'ALL'
constraint gate_type_chk check (gate_type in ('DEP','ARR','ALL')),
constraint gate_uk unique (terminal, gate_number)
);
create table checkin_desk (
desk_id number generated by default on null as identity primary key,
terminal varchar2(10) not null,
desk_number varchar2(10) not null,
desk_label varchar2(30),
constraint desk_uk unique (terminal, desk_number)
);
-- ── flight operations ──────────────────────────────────────────
create table flight (
flight_id number generated by default on null as identity primary key,
flight_number varchar2(8) not null,
airline_id number not null references airline,
origin_id number not null references airport_ref,
dest_id number not null references airport_ref,
flight_date date not null,
sched_dep timestamp not null,
sched_arr timestamp not null,
type_id number references aircraft_type,
constraint flight_uk unique (flight_number, flight_date)
);
create index flight_sched_dep_ix on flight (sched_dep);
create index flight_date_dep_ix on flight (flight_date, sched_dep);
-- 1:1 with flight; always created together via trigger or app logic.
-- gate_terminal / gate_number are denormalised from the gate master
-- so that board duality views stay at 2-level depth (no scalar-in-
-- scalar nesting to reach gate data). gate_id fk is kept for full
-- gate-object access in flight_ops_dv.
create table flight_status (
flight_id number primary key references flight on delete cascade,
status_code varchar2(12) default 'SCHEDULED'
constraint fs_status_chk check (status_code in (
'SCHEDULED','BOARDING','DEPARTED','DELAYED',
'CANCELLED','ARRIVED','DIVERTED')),
delay_minutes number(4) default 0,
actual_dep timestamp,
actual_arr timestamp,
gate_id number references gate,
gate_terminal varchar2(10),
gate_number varchar2(10),
baggage_belt varchar2(10),
boarding_opens timestamp,
checkin_closes timestamp,
updated_at timestamp default systimestamp
);
create index fs_gate_status_ix on flight_status (gate_id, status_code);
create index fs_updated_ix on flight_status (updated_at desc);
-- surrogate pk avoids composite-key complexity in duality view arrays
create table flight_checkin (
flight_checkin_id number generated by default on null as identity primary key,
flight_id number not null references flight,
desk_id number not null references checkin_desk,
open_time timestamp,
close_time timestamp,
constraint flight_checkin_uk unique (flight_id, desk_id)
);
create index fc_flight_ix on flight_checkin (flight_id);
-- ── passengers ─────────────────────────────────────────────────
create table passenger (
passenger_id number generated by default on null as identity primary key,
first_name varchar2(60) not null,
last_name varchar2(60) not null,
document_type varchar2(10) default 'PASSPORT',
document_number varchar2(30) not null,
nationality varchar2(3),
contact_email varchar2(200),
contact_phone varchar2(30),
constraint pax_doc_uk unique (document_type, document_number)
);
create table booking (
booking_id number generated by default on null as identity primary key,
booking_ref varchar2(8) not null constraint booking_ref_uk unique,
flight_id number not null references flight,
passenger_id number not null references passenger,
seat_number varchar2(4),
cabin_class varchar2(1) default 'Y'
constraint booking_class_chk check (cabin_class in ('F','C','W','Y')),
status varchar2(12) default 'CONFIRMED'
constraint booking_status_chk check (status in (
'CONFIRMED','CHECKED_IN','BOARDED','NO_SHOW','CANCELLED')),
constraint booking_flt_pax_uk unique (flight_id, passenger_id)
);
create index booking_flight_ix on booking (flight_id, status);
create index booking_pax_ix on booking (passenger_id);
-- 1:1 with booking
create table boarding (
boarding_id number generated by default on null as identity primary key,
booking_id number not null constraint boarding_bk_uk unique
references booking,
checkin_time timestamp,
checkin_type varchar2(10)
constraint boarding_checkin_type_chk check (checkin_type in
('ONLINE','DESK','KIOSK')),
boarding_time timestamp,
boarding_status varchar2(12) default 'PENDING'
constraint boarding_status_chk check (boarding_status in (
'PENDING','CHECKED_IN','AT_GATE','BOARDED','NO_SHOW')),
notif_sent_at timestamp
);
create index boarding_status_bk_ix on boarding (boarding_status, booking_id);
create index flight_sched_arr_ix
on flight (sched_arr);
create index fs_status_ix
on flight_status (status_code);
create index boarding_notif_null_ix
on boarding (case when notif_sent_at is null then booking_id end);