---

-- Создание 10 таблиц и связей 
CREATE TABLE RouteTypes14 (
    ID_route_type INT PRIMARY KEY,
    type_name NVARCHAR(80) NOT NULL
);

CREATE TABLE BusModels14 (
    ID_model INT PRIMARY KEY,
    model_name NVARCHAR(100) NOT NULL
);

CREATE TABLE Carriers14 (
    ID_carrier INT PRIMARY KEY,
    carrier_name NVARCHAR(100) NOT NULL,
    contact_info NVARCHAR(150) NOT NULL
);

CREATE TABLE Drivers14 (
    ID_driver INT PRIMARY KEY,
    full_name NVARCHAR(100) NOT NULL,
    phone NVARCHAR(25) NOT NULL
);

CREATE TABLE Stops14 (
    ID_stop INT PRIMARY KEY,
    stop_name NVARCHAR(100) NOT NULL
);

CREATE TABLE Routes14 (
    ID_route INT PRIMARY KEY,
    route_number NVARCHAR(10) NOT NULL UNIQUE,
    ID_route_type INT NOT NULL,
    FOREIGN KEY (ID_route_type) REFERENCES RouteTypes14(ID_route_type)
);

CREATE TABLE Buses14 (
    ID_bus INT PRIMARY KEY,
    reg_number NVARCHAR(20) NOT NULL UNIQUE,
    ID_model INT NOT NULL,
    ID_carrier INT NOT NULL,
    FOREIGN KEY (ID_model) REFERENCES BusModels14(ID_model),
    FOREIGN KEY (ID_carrier) REFERENCES Carriers14(ID_carrier)
);

CREATE TABLE Schedules14 (
    ID_schedule INT PRIMARY KEY,
    departure_time TIME NOT NULL,
    arrival_time TIME NOT NULL,
    ID_route INT NOT NULL,
    ID_stop INT NOT NULL,
    FOREIGN KEY (ID_route) REFERENCES Routes14(ID_route),
    FOREIGN KEY (ID_stop) REFERENCES Stops14(ID_stop)
);

CREATE TABLE Trips14 (
    ID_trip INT PRIMARY KEY,
    trip_date DATE NOT NULL,
    ID_route INT NOT NULL,
    ID_bus INT NOT NULL,
    ID_driver INT NOT NULL,
    FOREIGN KEY (ID_route) REFERENCES Routes14(ID_route),
    FOREIGN KEY (ID_bus) REFERENCES Buses14(ID_bus),
    FOREIGN KEY (ID_driver) REFERENCES Drivers14(ID_driver)
);

CREATE TABLE Waybills14 (
    ID_waybill INT PRIMARY KEY,
    issue_date DATE NOT NULL,
    work_shift NVARCHAR(20) NOT NULL,
    ID_trip INT NOT NULL UNIQUE,
    FOREIGN KEY (ID_trip) REFERENCES Trips14(ID_trip)
);
GO

-- Заполнение таблиц
INSERT INTO RouteTypes14 (ID_route_type, type_name)
VALUES
    (1, N'Городской'),
    (2, N'Пригородный'),
    (3, N'Межмуниципальный'),
    (4, N'Кольцевой'),
    (5, N'Экспресс'),
    (6, N'Магистральный'),
    (7, N'Ночной'),
    (8, N'Подвозящий'),
    (9, N'Социальный'),
    (10, N'Школьный'),
    (11, N'Сезонный'),
    (12, N'Туристический'),
    (13, N'Радиальный'),
    (14, N'Дополнительный'),
    (15, N'Заказной');

INSERT INTO BusModels14 (ID_model, model_name)
VALUES
    (1, N'ЛиАЗ-5292'),
    (2, N'ПАЗ Вектор NEXT'),
    (3, N'НефАЗ-5299'),
    (4, N'МАЗ-203'),
    (5, N'ЛиАЗ-6213'),
    (6, N'МАЗ-206'),
    (7, N'КАвЗ-4238'),
    (8, N'Волгабас-5270'),
    (9, N'Волгабас-6271'),
    (10, N'ПАЗ-3204'),
    (11, N'МАЗ-107'),
    (12, N'ЛиАЗ-5256'),
    (13, 'Yutong ZK6118HGA'),
    (14, 'Hyundai County'),
    (15, 'Mercedes-Benz Conecto');

INSERT INTO Carriers14 (ID_carrier, carrier_name, contact_info)
VALUES
    (1, N'ЯрГорТранс', '+7 4852 31-20-40'),
    (2, N'Городская автобусная линия', '+7 4852 32-21-41'),
    (3, N'Транспортный парк Северный', '+7 4852 33-22-42'),
    (4, N'Пассажирские перевозки Центр', '+7 4852 34-23-43'),
    (5, N'Автолинии Заволжья', '+7 4852 35-24-44'),
    (6, N'Моторный парк Юг', '+7 4852 36-25-45'),
    (7, N'Автобусный перевозчик Волга', '+7 4852 37-26-46'),
    (8, N'Ярославские маршруты', '+7 4852 38-27-47'),
    (9, N'Автосервис Транспорт', '+7 4852 39-28-48'),
    (10, N'Городской экспресс', '+7 4852 40-29-49'),
    (11, N'ПассажирТранс', '+7 4852 41-30-50'),
    (12, N'Транспортная компания Регион', '+7 4852 42-31-51'),
    (13, N'СеверАвто', '+7 4852 43-32-52'),
    (14, N'Линия-Автобус', '+7 4852 44-33-53'),
    (15, N'Пригородный транспорт', '+7 4852 45-34-54');

INSERT INTO Drivers14 (ID_driver, full_name, phone)
VALUES
    (1, N'Иванов Иван Иванович', '+7 910 211-11-31'),
    (2, N'Петров Петр Петрович', '+7 910 212-12-32'),
    (3, N'Сидоров Алексей Андреевич', '+7 910 213-13-33'),
    (4, N'Кузнецов Михаил Викторович', '+7 910 214-14-34'),
    (5, N'Смирнов Дмитрий Олегович', '+7 910 215-15-35'),
    (6, N'Попов Андрей Сергеевич', '+7 910 216-16-36'),
    (7, N'Васильев Игорь Павлович', '+7 910 217-17-37'),
    (8, N'Соколов Павел Николаевич', '+7 910 218-18-38'),
    (9, N'Михайлов Артем Ильич', '+7 910 219-19-39'),
    (10, N'Новиков Сергей Евгеньевич', '+7 910 220-20-40'),
    (11, N'Федоров Николай Андреевич', '+7 910 221-21-41'),
    (12, N'Морозов Максим Игоревич', '+7 910 222-22-42'),
    (13, N'Волков Роман Олегович', '+7 910 223-23-43'),
    (14, N'Алексеев Денис Петрович', '+7 910 224-24-44'),
    (15, N'Лебедев Кирилл Александрович', '+7 910 225-25-45');

INSERT INTO Stops14 (ID_stop, stop_name)
VALUES
    (1, N'Ярославль-Главный'),
    (2, N'Красная площадь'),
    (3, N'Брагино'),
    (4, N'Московский вокзал'),
    (5, N'Центр'),
    (6, N'Автовокзал'),
    (7, N'Октябрьская площадь'),
    (8, N'Площадь Юности'),
    (9, N'Улица Свободы'),
    (10, N'Шинный завод'),
    (11, N'Нефтестрой'),
    (12, N'Областная больница'),
    (13, N'Улица Панина'),
    (14, N'Волковская площадь'),
    (15, N'Проспект Фрунзе');

INSERT INTO Routes14 (ID_route, route_number, ID_route_type)
VALUES
    (1, '1', 1),
    (2, '8', 1),
    (3, '21', 4),
    (4, '44', 14),
    (5, '2', 1),
    (6, '4', 1),
    (7, '6', 2),
    (8, '9', 2),
    (9, '13', 5),
    (10, '17', 6),
    (11, '22', 7),
    (12, '30', 8),
    (13, '41', 9),
    (14, '55', 10),
    (15, '90', 11);

INSERT INTO Buses14 (ID_bus, reg_number, ID_model, ID_carrier)
VALUES
    (1, N'А123ВС 76', 1, 1),
    (2, N'М415ОР 76', 2, 1),
    (3, N'Т810КУ 76', 1, 2),
    (4, N'К350МН 76', 3, 3),
    (5, N'С214ТР 76', 4, 4),
    (6, N'Е707АА 76', 5, 5),
    (7, N'Н516РР 76', 2, 1),
    (8, N'У923СМ 76', 6, 2),
    (9, N'О101ЕК 76', 7, 6),
    (10, N'Р645ВА 76', 8, 7),
    (11, N'Х204РН 76', 9, 8),
    (12, N'В356КС 76', 10, 9),
    (13, N'А806МА 76', 11, 10),
    (14, N'Т742НН 76', 12, 11),
    (15, N'М908ВЕ 76', 13, 12);

INSERT INTO Schedules14 (ID_schedule, departure_time, arrival_time, ID_route, ID_stop)
VALUES
    (1, '06:10:00', '06:25:00', 1, 1),
    (2, '06:23:00', '06:38:00', 1, 2),
    (3, '06:36:00', '06:51:00', 2, 3),
    (4, '06:49:00', '07:04:00', 2, 4),
    (5, '07:02:00', '07:17:00', 3, 5),
    (6, '07:15:00', '07:30:00', 4, 6),
    (7, '07:28:00', '07:43:00', 5, 7),
    (8, '07:41:00', '07:56:00', 6, 8),
    (9, '07:54:00', '08:09:00', 7, 9),
    (10, '08:07:00', '08:22:00', 8, 10),
    (11, '08:20:00', '08:35:00', 9, 11),
    (12, '08:33:00', '08:48:00', 10, 12),
    (13, '08:46:00', '09:01:00', 11, 13),
    (14, '08:59:00', '09:14:00', 12, 14),
    (15, '09:12:00', '09:27:00', 13, 15);

INSERT INTO Trips14 (ID_trip, trip_date, ID_route, ID_bus, ID_driver)
VALUES
    (1, '20261001', 1, 1, 1),
    (2, '20261002', 2, 2, 2),
    (3, '20261003', 3, 3, 3),
    (4, '20261004', 4, 4, 4),
    (5, '20261005', 5, 5, 5),
    (6, '20261006', 6, 6, 6),
    (7, '20261007', 7, 7, 7),
    (8, '20261008', 8, 8, 8),
    (9, '20261009', 9, 9, 9),
    (10, '20261010', 10, 10, 10),
    (11, '20261011', 11, 11, 11),
    (12, '20261012', 12, 12, 12),
    (13, '20261013', 13, 13, 13),
    (14, '20261014', 1, 14, 14),
    (15, '20261015', 2, 15, 15);

INSERT INTO Waybills14 (ID_waybill, issue_date, work_shift, ID_trip)
VALUES
    (1, '20261001', N'Первая', 1),
    (2, '20261002', N'Вторая', 2),
    (3, '20261003', N'Первая', 3),
    (4, '20261004', N'Вторая', 4),
    (5, '20261005', N'Первая', 5),
    (6, '20261006', N'Вторая', 6),
    (7, '20261007', N'Первая', 7),
    (8, '20261008', N'Вторая', 8),
    (9, '20261009', N'Первая', 9),
    (10, '20261010', N'Вторая', 10),
    (11, '20261011', N'Первая', 11),
    (12, '20261012', N'Вторая', 12),
    (13, '20261013', N'Первая', 13),
    (14, '20261014', N'Вторая', 14),
    (15, '20261015', N'Первая', 15);

GO