CREATE TABLE "info_missiles"(
"name" char( 6 ) NOT NULL,
"type" text DEFAULT 'unknown' CHECK(type in ('air-air', 'air-ground', 'unknown')),
"pointing" text DEFAULT 'unknown' CHECK(pointing in('mechanical', 'radio-locating', 'laser', 'thermal', 'unknown')),
"power" int CHECK(power> 0 ),
"distance" int CHECK(distance> 0 ),
PRIMARY KEY(name)
);


CREATE TABLE "info_guns"(
"name" char( 6 ),
"type" text DEFAULT 'unknown' CHECK(type in('revolver', 'belt', 'disk', 'rotate_block', 'unknown')),
"distance" int CHECK(distance> 0 ),
"calible" int CHECK(calible> 0 ),
PRIMARY KEY(name)
);


CREATE TABLE "types_aircraft"(
"id" char( 5 ),
"type" text CHECK(type in('fighter', 'bomber', 'transport', 'recon', 'flyingboat')),
"first_model" text DEFAULT 'unknown',
"year_first_model" int CHECK(year_first_model> 1928  AND year_first_model< 2010 ),
"first_designer" text DEFAULT 'unknown',
"max_speed" int CHECK(max_speed> 0 ),
"max_distance" int CHECK(max_distance> 0 ),
PRIMARY KEY(id), 
UNIQUE(type), UNIQUE (first_model)
);



CREATE TABLE "organizations_creator"(
"id" char( 5 ),
"name" text NOT NULL,
"year_foundation" int CHECK(year_foundation> 1928 ),
"founder" text,
"years_of_life" text DEFAULT 'unknown',
"rank" text DEFAULT 'unknown',
PRIMARY KEY(id), 
UNIQUE(name)
);

CREATE TABLE "aircrafts"(
"name" char( 6 ),
"creator" char( 5 ) NOT NULL,
"crew" int CHECK(crew> 0  AND crew< 40 ),
"speed" int CHECK(speed> 0 ),
"distance" int CHECK(distance> 0 ),
"ceiling" int CHECK(ceiling> 0 ),
"year" int CHECK(year> 1928  AND year< 2010 ),
"type" char( 5 ),
"engine" text DEFAULT 'unknown',
"engine_power" int CHECK(engine_power> 0 ),
"engine_num" int CHECK(engine_num> 0  AND engine_num< 10 ),
"all_up_weight" int CHECK(all_up_weight> 0 ),
"bomb_load" int CHECK(bomb_load> 0 ),
"span" numeric( 5 , 2 ) CHECK(span> 0 ), 
"length" numeric( 5 , 2 ) CHECK(length> 0 ),
"height" numeric( 5 , 2 ) CHECK(height> 0 ),

PRIMARY KEY(name), 
FOREIGN KEY(type) REFERENCES types_aircraft(id), 
FOREIGN KEY(creator) REFERENCES organizations_creator(id) 
);


CREATE TABLE "missiles"(
"aircraft" char( 6 ) NOT NULL,
"codename" char( 6 ) NOT NULL,
PRIMARY KEY(codename, aircraft) , 
FOREIGN KEY(codename) REFERENCES info_missiles(name), 
FOREIGN KEY(aircraft) REFERENCES aircrafts(name) 
);


CREATE TABLE "guns"(
"aircraft" char( 6 ) NOT NULL,
"codename" char( 6 ) NOT NULL,
PRIMARY KEY(codename, aircraft) , 
FOREIGN KEY(codename) REFERENCES info_guns(name), 
FOREIGN KEY(aircraft) REFERENCES aircrafts(name)
);

INSERT INTO guns VALUES ('SU-34','NR-30');
INSERT INTO missiles VALUES ('SU-34','K-7');