# airline.sql
#
# This script sets up the database tables for the lab sheet for lecture 8
#
DROP TABLE IF EXISTS Route;
DROP TABLE IF EXISTS Aircraft;

CREATE TABLE Aircraft (
    aircraft_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	aircraft_type varchar(4),
	registration varchar(10),
	PRIMARY KEY (aircraft_id)
) ENGINE=InnoDB;

INSERT INTO Aircraft (aircraft_type, registration) VALUES ('B737', 'ZK-BLOB');
INSERT INTO Aircraft (aircraft_type, registration) VALUES ('B737', 'ZK-DUCK');
INSERT INTO Aircraft (aircraft_type, registration) VALUES ('B747', 'ZK-BIGG');
INSERT INTO Aircraft (aircraft_type, registration) VALUES ('A320', 'ZK-WHIZ');
INSERT INTO Aircraft (aircraft_type, registration) VALUES ('A380', 'ZK-HUGE');
INSERT INTO Aircraft (aircraft_type, registration) VALUES ('A380', 'ZK-GIGA');

CREATE TABLE Route (
    route_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	route_description varchar(100),
    aircraft_id INT UNSIGNED,
	distance INT UNSIGNED,
    PRIMARY KEY (route_id),
	INDEX ix_route_aircraft_id (aircraft_id)
) ENGINE=InnoDB;

INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Christchurch',1,760);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Wellington',1,490);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Sydney',4,2200);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Melbourne',3,2600);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Perth',8,5400);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Los Angeles',5,10500);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Singapore',6,8400);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Tokyo',7,8800);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Honolulu',7,7100);
INSERT INTO Route (route_description, aircraft_id, distance) VALUES ('Auckland-Dubai',6,14200);