-- ================================================================
-- Airport POC — Generování testovacích dat v2
-- Změny oproti v1:
-- • letiště pouze CZ · PT · FR · GB (32 reálných letišť)
-- • výčtové / stavové hodnoty malým písmem
-- • sekce "DDL úpravy" aktualizuje constrainty a defaulty
-- ================================================================
-- Distribuce řádků
-- ─────────────────────────────────────────────────────────────
-- airline : 50
-- airport_ref : 32 (reálná letiště CZ/PT/FR/GB)
-- aircraft_type : 20
-- gate : 80 (5 terminálů × 16 bran)
-- checkin_desk : 100 (5 terminálů × 20 přepážek)
-- passenger : 120 000
-- flight : 25 000
-- flight_status : 25 000 (1:1 s flight)
-- flight_checkin : 75 000 (3 přepážky / let)
-- booking : 400 000 (16 sedadel / let)
-- boarding : 360 000 (90 % bookingů)
-- ---------
-- CELKEM : 1 005 302
-- ================================================================
-- ================================================================
-- DDL ÚPRAVY — constrainty a defaulty na malá písmena
--
-- Zachováváme velká písmena u:
-- • IATA / ICAO kódů (letecký standard)
-- • ISO 3166-1 alpha-3 (nationality: CZE, FRA …)
-- • cabin_class F/C/W/Y (jednopísmenné průmyslové kódy)
-- • flight_number, booking_ref, document_number
-- ================================================================
-- gate.gate_type
alter table gate modify gate_type default 'all';
alter table gate drop constraint gate_type_chk;
alter table gate add constraint gate_type_chk
check (gate_type in ('dep','arr','all'));
-- flight_status.status_code
alter table flight_status modify status_code default 'scheduled';
alter table flight_status drop constraint fs_status_chk;
alter table flight_status add constraint fs_status_chk
check (status_code in (
'scheduled','boarding','departed','delayed',
'cancelled','arrived','diverted'));
-- booking.status (cabin_class f/c/w/y zůstává velkými — kód)
alter table booking modify status default 'confirmed';
alter table booking drop constraint booking_status_chk;
alter table booking add constraint booking_status_chk
check (status in ('confirmed','checked_in','boarded','no_show','cancelled'));
-- boarding.checkin_type
alter table boarding drop constraint boarding_checkin_type_chk;
alter table boarding add constraint boarding_checkin_type_chk
check (checkin_type in ('online','desk','kiosk'));
-- boarding.boarding_status
alter table boarding modify boarding_status default 'pending';
alter table boarding drop constraint boarding_status_chk;
alter table boarding add constraint boarding_status_chk
check (boarding_status in ('pending','checked_in','at_gate','boarded','no_show'));
-- passenger.document_type (žádný check constraint — jen default)
alter table passenger modify document_type default 'passport';
-- ================================================================
-- 1. airline (50 řádků)
-- ================================================================
insert /*+ append */ into airline (iata_code, icao_code, name, country)
select
chr(64 + ceil(level / 26.0))
|| chr(64 + mod(level - 1, 26) + 1) as iata_code,
chr(64 + ceil(level / 26.0))
|| chr(64 + mod(level - 1, 26) + 1)
|| chr(65 + mod(level * 3, 26))
|| chr(65 + mod(level * 7, 26)) as icao_code,
'Air '
|| chr(64 + ceil(level / 26.0))
|| chr(64 + mod(level - 1, 26) + 1)
|| case mod(level, 5)
when 0 then ' Express'
when 1 then ' International'
when 2 then ' Regional'
when 3 then ' Connect'
else ' Lines'
end as name,
case mod(level, 10)
when 0 then 'Czech Republic' when 1 then 'Germany'
when 2 then 'France' when 3 then 'United Kingdom'
when 4 then 'United States' when 5 then 'Netherlands'
when 6 then 'Spain' when 7 then 'Italy'
when 8 then 'Austria' else 'Switzerland'
end as country
from dual
connect by level <= 50;
commit;
-- ================================================================
-- 2. airport_ref (32 řádků — reálná letiště cz · pt · fr · gb)
-- ================================================================
insert /*+ append */ into airport_ref (iata_code, city, country, timezone)
select iata_code, city, country, timezone from (
-- ── czech republic (5) ──────────────────────────────────────
select 'PRG' iata_code, 'Prague' city, 'Czech Republic' country, 'Europe/Prague' timezone from dual union all
select 'BRQ', 'Brno', 'Czech Republic', 'Europe/Prague' from dual union all
select 'OSR', 'Ostrava', 'Czech Republic', 'Europe/Prague' from dual union all
select 'KLV', 'Karlovy Vary', 'Czech Republic', 'Europe/Prague' from dual union all
select 'PED', 'Pardubice', 'Czech Republic', 'Europe/Prague' from dual union all
-- ── portugal (5) ────────────────────────────────────────────
select 'LIS', 'Lisbon', 'Portugal', 'Europe/Lisbon' from dual union all
select 'OPO', 'Porto', 'Portugal', 'Europe/Lisbon' from dual union all
select 'FAO', 'Faro', 'Portugal', 'Europe/Lisbon' from dual union all
select 'FNC', 'Funchal', 'Portugal', 'Atlantic/Madeira' from dual union all
select 'PDL', 'Ponta Delgada', 'Portugal', 'Atlantic/Azores' from dual union all
-- ── france (12) ─────────────────────────────────────────────
select 'CDG', 'Paris', 'France', 'Europe/Paris' from dual union all
select 'ORY', 'Paris-Orly', 'France', 'Europe/Paris' from dual union all
select 'NCE', 'Nice', 'France', 'Europe/Paris' from dual union all
select 'LYS', 'Lyon', 'France', 'Europe/Paris' from dual union all
select 'MRS', 'Marseille', 'France', 'Europe/Paris' from dual union all
select 'TLS', 'Toulouse', 'France', 'Europe/Paris' from dual union all
select 'BOD', 'Bordeaux', 'France', 'Europe/Paris' from dual union all
select 'NTE', 'Nantes', 'France', 'Europe/Paris' from dual union all
select 'SXB', 'Strasbourg', 'France', 'Europe/Paris' from dual union all
select 'LIL', 'Lille', 'France', 'Europe/Paris' from dual union all
select 'MPL', 'Montpellier', 'France', 'Europe/Paris' from dual union all
select 'BIQ', 'Biarritz', 'France', 'Europe/Paris' from dual union all
-- ── england / united kingdom (10) ───────────────────────────
select 'LHR', 'London', 'United Kingdom', 'Europe/London' from dual union all
select 'LGW', 'London Gatwick', 'United Kingdom', 'Europe/London' from dual union all
select 'MAN', 'Manchester', 'United Kingdom', 'Europe/London' from dual union all
select 'STN', 'London Stansted', 'United Kingdom', 'Europe/London' from dual union all
select 'LTN', 'London Luton', 'United Kingdom', 'Europe/London' from dual union all
select 'LCY', 'London City', 'United Kingdom', 'Europe/London' from dual union all
select 'BHX', 'Birmingham', 'United Kingdom', 'Europe/London' from dual union all
select 'BRS', 'Bristol', 'United Kingdom', 'Europe/London' from dual union all
select 'LBA', 'Leeds Bradford', 'United Kingdom', 'Europe/London' from dual union all
select 'NCL', 'Newcastle', 'United Kingdom', 'Europe/London' from dual
);
commit;
-- ================================================================
-- 3. aircraft_type (20 řádků — realistické typy)
-- ================================================================
insert /*+ append */ into aircraft_type (icao_type, name, seat_capacity)
select icao_type, name, seat_capacity from (
select 'A20N' icao_type, 'Airbus A320neo' name, 180 seat_capacity from dual union all
select 'A21N', 'Airbus A321neo', 220 from dual union all
select 'A319', 'Airbus A319', 140 from dual union all
select 'A333', 'Airbus A330-300', 300 from dual union all
select 'A332', 'Airbus A330-200', 250 from dual union all
select 'A359', 'Airbus A350-900', 350 from dual union all
select 'A388', 'Airbus A380-800', 555 from dual union all
select 'B737', 'Boeing 737-800', 162 from dual union all
select 'B38M', 'Boeing 737 MAX 8', 178 from dual union all
select 'B752', 'Boeing 757-200', 200 from dual union all
select 'B763', 'Boeing 767-300ER', 218 from dual union all
select 'B772', 'Boeing 777-200ER', 314 from dual union all
select 'B77W', 'Boeing 777-300ER', 396 from dual union all
select 'B789', 'Boeing 787-9', 296 from dual union all
select 'B78X', 'Boeing 787-10', 336 from dual union all
select 'E190', 'Embraer E190', 100 from dual union all
select 'E195', 'Embraer E195', 120 from dual union all
select 'DH8D', 'Bombardier Q400', 78 from dual union all
select 'CRJ9', 'Bombardier CRJ-900', 90 from dual union all
select 'AT75', 'ATR 72-500', 70 from dual
);
commit;
-- ================================================================
-- 4. gate (80 řádků — 5 terminálů × 16 bran)
-- gate_type: 'dep' | 'arr' | 'all' (malá písmena)
-- ================================================================
insert /*+ append */ into gate (terminal, gate_number, gate_type)
select
'T' || ceil(level / 16.0) as terminal,
lpad(to_char(mod(level - 1, 16) + 1), 2, '0') as gate_number,
case mod(level, 3)
when 0 then 'dep'
when 1 then 'arr'
else 'all'
end as gate_type
from dual
connect by level <= 80;
commit;
-- ================================================================
-- 5. checkin_desk (100 řádků — 5 terminálů × 20 přepážek)
-- ================================================================
insert /*+ append */ into checkin_desk (terminal, desk_number, desk_label)
select
'T' || ceil(level / 20.0) as terminal,
lpad(to_char(mod(level - 1, 20) + 1), 2, '0') as desk_number,
'desk T' || ceil(level / 20.0)
|| '-' || lpad(to_char(mod(level - 1, 20) + 1), 2, '0') as desk_label
from dual
connect by level <= 100;
commit;
-- ================================================================
-- 6. passenger (120 000 řádků)
-- document_type: 'passport' (malá písmena)
-- nationality : iso 3166-1 alpha-3 — velká písmena (standard)
-- ================================================================
insert /*+ append */ into passenger (
first_name, last_name,
document_type, document_number,
nationality, contact_email, contact_phone
)
select
case mod(level - 1, 20)
when 0 then 'Jan' when 1 then 'Petr'
when 2 then 'Pavel' when 3 then 'Martin'
when 4 then 'Tomas' when 5 then 'Lukas'
when 6 then 'Ondrej' when 7 then 'Jakub'
when 8 then 'David' when 9 then 'Jiri'
when 10 then 'Anna' when 11 then 'Eva'
when 12 then 'Jana' when 13 then 'Marie'
when 14 then 'Petra' when 15 then 'Lucie'
when 16 then 'Katerina' when 17 then 'Veronika'
when 18 then 'Tereza' else 'Zuzana'
end as first_name,
case mod(level * 7, 30)
when 0 then 'Novak' when 1 then 'Svoboda'
when 2 then 'Novotny' when 3 then 'Dvorak'
when 4 then 'Cerny' when 5 then 'Prochazka'
when 6 then 'Kucera' when 7 then 'Vesely'
when 8 then 'Horak' when 9 then 'Blaha'
when 10 then 'Kovar' when 11 then 'Blazek'
when 12 then 'Fiala' when 13 then 'Sedlacek'
when 14 then 'Dostal' when 15 then 'Kratochvil'
when 16 then 'Navratil' when 17 then 'Maly'
when 18 then 'Nedved' when 19 then 'Nguyen'
when 20 then 'Schmidt' when 21 then 'Mueller'
when 22 then 'Smith' when 23 then 'Johnson'
when 24 then 'Williams' when 25 then 'Garcia'
when 26 then 'Martinez' when 27 then 'Chen'
when 28 then 'Kim' else 'Patel'
end as last_name,
'passport' as document_type,
'P' || lpad(to_char(level), 9, '0') as document_number,
case mod(level, 10)
when 0 then 'CZE' when 1 then 'DEU' when 2 then 'GBR'
when 3 then 'FRA' when 4 then 'USA' when 5 then 'SVK'
when 6 then 'POL' when 7 then 'AUT' when 8 then 'ITA'
else 'PRT'
end as nationality,
'pax' || lpad(to_char(level), 9, '0') || '@airport.test' as contact_email,
'+420' || lpad(to_char(600000000 + mod(level * 97, 399999999)), 9, '0')
as contact_phone
from dual
connect by level <= 120000;
commit;
-- ================================================================
-- 7. flight (25 000 řádků)
--
-- 32 letišť: origin mod 32, dest mod(lvl+12, 32)
-- offset v kruhu = 13; gcd(13,32)=1 → origin ≠ dest vždy ✓
--
-- flight_number = iata_code || lpad(ceil(lvl/50), 4)
-- v každém bloku 50 vystřídá každá aerolinie právě 1×
-- → flight_number globálně unikátní → uk(fn,date) ✓
-- ================================================================
insert /*+ append */ into flight (
flight_number, airline_id, origin_id, dest_id,
flight_date, sched_dep, sched_arr, type_id
)
with
al as (
select airline_id, iata_code,
row_number() over (order by airline_id) as rn
from airline
),
ap as (
select airport_id,
row_number() over (order by airport_id) as rn
from airport_ref
),
ty as (
select type_id,
row_number() over (order by type_id) as rn
from aircraft_type
)
select
al.iata_code
|| lpad(to_char(ceil(lvl / 50.0)), 4, '0') as flight_number,
al.airline_id,
ap_o.airport_id as origin_id,
ap_d.airport_id as dest_id,
trunc(sysdate) - trunc((lvl - 1) / 100) as flight_date,
trunc(sysdate) - trunc((lvl - 1) / 100)
+ mod(lvl * 7, 24) / 24 as sched_dep,
trunc(sysdate) - trunc((lvl - 1) / 100)
+ mod(lvl * 7, 24) / 24
+ (90 + mod(lvl * 3, 600)) / 1440 as sched_arr,
ty.type_id
from (select level as lvl from dual connect by level <= 25000) gen
join al on al.rn = mod(lvl - 1, 50) + 1
join ap ap_o on ap_o.rn = mod(lvl - 1, 32) + 1
join ap ap_d on ap_d.rn = mod(lvl + 12, 32) + 1 -- offset 13, gcd(13,32)=1 → ≠ origin ✓
join ty on ty.rn = mod(lvl - 1, 20) + 1;
commit;
-- ================================================================
-- 8. flight_status (25 000 řádků — 1:1 s flight)
-- status_code: malá písmena (constraint aktualizován výše)
-- baggage_belt: 'b01' … 'b12'
-- ================================================================
insert /*+ append */ into flight_status (
flight_id, status_code, delay_minutes,
actual_dep, actual_arr,
gate_id, gate_terminal, gate_number,
baggage_belt, boarding_opens, checkin_closes,
updated_at
)
with g as (
select gate_id, terminal, gate_number,
row_number() over (order by gate_id) as rn
from gate
)
select
f.flight_id,
case mod(f.flight_id, 7)
when 0 then 'scheduled' when 1 then 'boarding'
when 2 then 'departed' when 3 then 'delayed'
when 4 then 'cancelled' when 5 then 'arrived'
else 'diverted'
end as status_code,
case when mod(f.flight_id, 7) = 3
then mod(f.flight_id, 120) + 15
else 0
end as delay_minutes,
case when mod(f.flight_id, 7) in (2, 5, 6)
then cast(f.sched_dep as date)
else null
end as actual_dep,
case when mod(f.flight_id, 7) = 5
then cast(f.sched_arr as date)
else null
end as actual_arr,
g.gate_id,
g.terminal as gate_terminal,
g.gate_number,
'b' || lpad(to_char(mod(f.flight_id, 12) + 1), 2, '0') as baggage_belt,
f.sched_dep - 45 / 1440 as boarding_opens,
f.sched_dep - 60 / 1440 as checkin_closes,
systimestamp as updated_at
from flight f
join g on g.rn = mod(f.flight_id, 80) + 1;
commit;
-- ================================================================
-- 9. flight_checkin (75 000 řádků — 3 přepážky na let)
-- offset n×37 (coprime se 100) → 3 různé přepážky ✓
-- ================================================================
insert /*+ append */ into flight_checkin (
flight_id, desk_id, open_time, close_time
)
with desk_rn as (
select desk_id,
row_number() over (order by desk_id) as rn
from checkin_desk
)
select
f.flight_id,
d.desk_id,
f.sched_dep - (180 - n.n * 30) / 1440 as open_time,
f.sched_dep - 45 / 1440 as close_time
from flight f
cross join (select level - 1 as n from dual connect by level <= 3) n
join desk_rn d
on d.rn = mod(f.flight_id + n.n * 37, 100) + 1;
commit;
-- ================================================================
-- 10. booking (400 000 řádků — přesně 16 na let)
-- status : malá písmena
-- cabin_class f/c/w/y : velká písmena (letecký průmyslový kód)
-- booking_ref: to_char(lvl,'FM00000000') → globálně unikátní ✓
-- ================================================================
insert /*+ append */ into booking (
booking_ref, flight_id, passenger_id,
seat_number, cabin_class, status
)
with
fl as (
select flight_id,
row_number() over (order by flight_id) as rn
from flight
),
px as (
select passenger_id,
row_number() over (order by passenger_id) as rn
from passenger
)
select
to_char(lvl, 'FM00000000') as booking_ref,
fl.flight_id,
px.passenger_id,
lpad(to_char(trunc(mod(lvl - 1, 300) / 6) + 1), 2, '0')
|| chr(65 + mod(lvl - 1, 6)) as seat_number,
case mod(lvl, 100)
when 0 then 'F'
when 1 then 'C'
when 2 then 'W'
else 'Y'
end as cabin_class,
case mod(lvl, 5)
when 0 then 'confirmed' when 1 then 'checked_in'
when 2 then 'boarded' when 3 then 'no_show'
else 'cancelled'
end as status
from (select level as lvl from dual connect by level <= 400000) gen
join fl on fl.rn = trunc((lvl - 1) / 16) + 1
join px on px.rn = mod(lvl - 1, 120000) + 1;
commit;
-- ================================================================
-- 11. boarding (~360 000 řádků — 90 % bookingů)
-- checkin_type : 'online' | 'desk' | 'kiosk'
-- boarding_status : 'pending' | 'checked_in' | 'at_gate' | 'boarded' | 'no_show'
-- ================================================================
insert /*+ append */ into boarding (
booking_id, checkin_time, checkin_type,
boarding_time, boarding_status, notif_sent_at
)
select
bk.booking_id,
f.sched_dep
- numtodsinterval(90 + mod(bk.booking_id * 7, 180), 'MINUTE') as checkin_time,
case mod(bk.booking_id, 3)
when 0 then 'online'
when 1 then 'desk'
else 'kiosk'
end as checkin_type,
f.sched_dep
- numtodsinterval(20 + mod(bk.booking_id * 3, 40), 'MINUTE') as boarding_time,
case mod(bk.booking_id, 5)
when 0 then 'pending' when 1 then 'checked_in'
when 2 then 'at_gate' when 3 then 'boarded'
else 'no_show'
end as boarding_status,
case when mod(bk.booking_id, 4) != 0
then f.sched_dep - interval '30' minute
else null
end as notif_sent_at
from booking bk
join flight f on f.flight_id = bk.flight_id
where mod(bk.booking_id, 10) != 0;
commit;
-- ================================================================
-- souhrnná kontrola počtů řádků
-- ================================================================
select
table_name,
to_char(row_count, '999G999G999') as row_count
from (
select 'airline' as table_name, count(*) as row_count from airline union all
select 'airport_ref', count(*) from airport_ref union all
select 'aircraft_type', count(*) from aircraft_type union all
select 'gate', count(*) from gate union all
select 'checkin_desk', count(*) from checkin_desk union all
select 'passenger', count(*) from passenger union all
select 'flight', count(*) from flight union all
select 'flight_status', count(*) from flight_status union all
select 'flight_checkin', count(*) from flight_checkin union all
select 'booking', count(*) from booking union all
select 'boarding', count(*) from boarding
)
union all
select '── celkem ──', to_char(sum(row_count), '999G999G999')
from (
select count(*) as row_count from airline union all
select count(*) from airport_ref union all
select count(*) from aircraft_type union all
select count(*) from gate union all
select count(*) from checkin_desk union all
select count(*) from passenger union all
select count(*) from flight union all
select count(*) from flight_status union all
select count(*) from flight_checkin union all
select count(*) from booking union all
select count(*) from boarding
)
order by 1;