--создание таблиц
create table city (
    id int primary key,
    name nvarchar(30) not null
)
commit;

create table t_office (
    id int primary key,
    name nvarchar(30) not null,
    id_city int not null
)
commit;

alter table t_office add constraint fk_t_office_city
foreign key(id_city) references city(id);
commit;

create table bus (
    id int primary key,
    auto_mark nvarchar(30) not null,
    place_count int not null
)
commit;

create table driver (
    id int primary key,
    name nvarchar(30) not null,
    id_city int not null
)
commit;

alter table driver add constraint fk_driver_city
foreign key(id_city) references city(id);
commit;

create table time_table (
    id int primary key,
    city_from int not null,
    city_where int not null,
    start_time time not null,
    go_time time not null,
    cost int not null
)
commit;
alter table time_table add constraint fk_time_table_city
foreign key(city_from) references city(id);

alter table time_table add constraint fk2_time_table_city
foreign key(city_where) references city(id);
commit;


create table voyage (
    id int primary key,
    id_time_table int not null,
    id_bus int not null,
    dr_date date not null,
    id_driver int not null
)
commit;

alter table voyage add constraint fk_voyage_time_table
foreign key(id_time_table) references time_table(id);

alter table voyage add constraint fk_voyage_bus
foreign key(id_bus) references bus(id);

alter table voyage add constraint fk_voyage_driver
foreign key(id_driver) references driver(id);
commit;

create table ticket (
    id int primary key,
    sell_time datetime not null,
    id_t_office int not null,
    id_voyage int not null,
    passanger_name nvarchar(50) not null
)
commit;
alter table ticket add constraint fk_ticket_t_office
foreign key(id_t_office) references t_office(id);

alter table ticket add constraint fk_ticket_voyage
foreign key(id_voyage) references voyage(id);
commit;