create table aircraft ( 
    airc_id varchar2(5) not null constraint pk_airc primary key, 
    airc_type varchar(5),  
    capacity int, 
    manf_year int
)

create table route ( 
    route_id varchar2(5) constraint pk_route primary key not null, 
    dept_airport varchar2(5), 
    dest_airport varchar2(5) ,
    departure varchar2(15),
    destination varchar2(15)
)

create table contact_details( 
    cid varchar2(5) primary key not null, 
    name varchar2(15), 
    age int, 
    nationality varchar2(10), 
    email varchar2(40), 
    mobile varchar2(15) 
)

create Table flight_schedule ( 
    flight_id varchar2(5) not null constraint pk_flight primary key, 
    airc_id varchar2(5) not null, 
    route_id varchar2(5) not null, 
    flight_date timestamp, 
    constraint fk_airc foreign key (airc_id) references aircraft(airc_id), 
    constraint fk_route foreign key (route_id) references route(route_id) 
)

create table passenger ( 
    ps_id varchar2(10) not null, 
    cid varchar2(5) not null, 
    constraint pk_ps primary key (ps_id) 
)

create table transaction ( 
    trans_id varchar2(5) constraint pk_trans primary key not null, 
    flight_id varchar2(5) not null, 
    ps_id varchar2(5) not null, 
    booking_date timestamp, 
    constraint fk_flight foreign key (flight_id) references flight_schedule(flight_id), 
    constraint fk_ps foreign key (ps_id) references passenger(ps_id) 
)