TABLE `airline` (
`code` varchar(5) NOT NULL,
`full_name` varchar(30) NOT NULL,
PRIMARY KEY (`code`)
);
TABLE `aircraft` (
`model` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`model`)
);
TABLE `airline_has_aircraft` (
`airline_code` varchar(5) NOT NULL DEFAULT '',
`aircraft_model` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`airline_code`,`aircraft_model`),
KEY `airline_code` (`airline_code`) USING BTREE,
CONSTRAINT `airline_has_aircraft_ibfk_1` FOREIGN KEY (`airline_code`) REFERENCES `airline` (`code`)
);
TABLE `airline_has_flight` (
`airline_code` varchar(5) NOT NULL,
`flight_number` smallint(5) unsigned NOT NULL DEFAULT '0',
`from_airport` varchar(5) NOT NULL,
`to_airport` varchar(5) NOT NULL,
`duration` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`flight_number`),
KEY `from_airport` (`from_airport`),
CONSTRAINT `airline_has_flight_ibfk_4` FOREIGN KEY (`airline_code`) REFERENCES `airline` (`code`)
);
-- Необходимо достигнуть уникальность и запретить добавление записи если, airline_code разные
-- Для airline_has_flight, airline_has_aircarft
TABLE `journey` (
`airline_code` varchar(5) NOT NULL DEFAULT '',
`aircraft_model` varchar(15) NOT NULL DEFAULT '',
`flight_number` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`airline_code`,`aircraft_model`,`flight_number`)
)