/* Database Systems, Coronel/Morris */
/* Type of SQL : MySQL */


CREATE TABLE AIRCRAFT (
AC_NUMBER varchar(5),
MOD_CODE varchar(10),
AC_TTAF float(8),
AC_TTEL float(8),
AC_TTER float(8)
)
ENGINE = INNODB;

INSERT INTO AIRCRAFT VALUES('1484P','PA23-250','1833.1','1833.1','101.8');
INSERT INTO AIRCRAFT VALUES('2289L','C-90A','4243.8','768.9','1123.4');
INSERT INTO AIRCRAFT VALUES('2778V','PA31-350','7992.9','1513.1','789.5');
INSERT INTO AIRCRAFT VALUES('4278Y','PA31-350','2147.3','622.1','243.2');

/* -- */


CREATE TABLE CHARTER (
CHAR_TRIP int,
CHAR_DATE datetime,
CHAR_PILOT int,
CHAR_COPILOT int,
AC_NUMBER varchar(5),
CHAR_DESTINATION varchar(3),
CHAR_DISTANCE float(8),
CHAR_HOURS_FLOWN float(8),
CHAR_HOURS_WAIT float(8),
CHAR_FUEL_GALLONS float(8),
CHAR_OIL_QTS int,
CUS_CODE int
)
ENGINE = INNODB;

INSERT INTO CHARTER VALUES('10001','2016-2-5','104','','2289L','ATL','936','5.1','2.2','354.1','1','10011');
INSERT INTO CHARTER VALUES('10002','2016-2-5','101','','2778V','BNA','320','1.6','0','72.6','0','10016');
INSERT INTO CHARTER VALUES('10003','2016-2-5','105','109','4278Y','GNV','1574','7.8','0','339.8','2','10014');
INSERT INTO CHARTER VALUES('10004','2016-2-6','106','','1484P','STL','472','2.9','4.9','97.2','1','10019');
INSERT INTO CHARTER VALUES('10005','2016-2-6','101','','2289L','ATL','1023','5.7','3.5','397.7','2','10011');
INSERT INTO CHARTER VALUES('10006','2016-2-6','109','','4278Y','STL','472','2.6','5.2','117.1','0','10017');
INSERT INTO CHARTER VALUES('10007','2016-2-6','104','105','2778V','GNV','1574','7.9','0','348.4','2','10012');
INSERT INTO CHARTER VALUES('10008','2016-2-7','106','','1484P','TYS','644','4.1','0','140.6','1','10014');
INSERT INTO CHARTER VALUES('10009','2016-2-7','105','','2289L','GNV','1574','6.6','23.4','459.9','0','10017');
INSERT INTO CHARTER VALUES('10010','2016-2-7','109','','4278Y','ATL','998','6.2','3.2','279.7','0','10016');
INSERT INTO CHARTER VALUES('10011','2016-2-7','101','104','1484P','BNA','352','1.9','5.3','66.4','1','10012');
INSERT INTO CHARTER VALUES('10012','2016-2-8','101','','2778V','MOB','884','4.8','4.2','215.1','0','10010');
INSERT INTO CHARTER VALUES('10013','2016-2-8','105','','4278Y','TYS','644','3.9','4.5','174.3','1','10011');
INSERT INTO CHARTER VALUES('10014','2016-2-9','106','','4278Y','ATL','936','6.1','2.1','302.6','0','10017');
INSERT INTO CHARTER VALUES('10015','2016-2-9','104','101','2289L','GNV','1645','6.7','0','459.5','2','10016');
INSERT INTO CHARTER VALUES('10016','2016-2-9','109','105','2778V','MQY','312','1.5','0','67.2','0','10011');
INSERT INTO CHARTER VALUES('10017','2016-2-10','101','','1484P','STL','508','3.1','0','105.5','0','10014');
INSERT INTO CHARTER VALUES('10018','2016-2-10','105','104','4278Y','TYS','644','3.8','4.5','167.4','0','10017');

/* -- */

CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE numeric
)
ENGINE = INNODB;

INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','896.54');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','1285.19');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','673.21');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','1014.56');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','0');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','0');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','0');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','453.98');

/* -- */

CREATE TABLE EMPLOYEE (
EMP_NUM int,
EMP_TITLE varchar(4),
EMP_LNAME varchar(15),
EMP_FNAME varchar(15),
EMP_INITIAL varchar(1),
EMP_DOB datetime,
EMP_HIRE_DATE datetime
)
ENGINE = INNODB;

INSERT INTO EMPLOYEE VALUES('100','Mr.','Kolmycz','George','D','1942-6-15','1988-3-15');
INSERT INTO EMPLOYEE VALUES('101','Ms.','Lewis','Rhonda','G','1965-3-19','1986-4-25');
INSERT INTO EMPLOYEE VALUES('102','Mr.','VanDam','Rhett','','1958-11-14','1993-5-18');
INSERT INTO EMPLOYEE VALUES('103','Ms.','Jones','Anne','M','1974-5-11','1999-7-26');
INSERT INTO EMPLOYEE VALUES('104','Mr.','Lange','John','P','1971-7-12','1990-8-20');
INSERT INTO EMPLOYEE VALUES('105','Mr.','Williams','Robert','D','1975-3-14','2003-6-19');
INSERT INTO EMPLOYEE VALUES('106','Mrs.','Duzak','Jeanine','K','1968-2-12','1989-3-13');
INSERT INTO EMPLOYEE VALUES('107','Mr.','Diante','Jorge','D','1975-5-1','1997-7-2');
INSERT INTO EMPLOYEE VALUES('108','Mr.','Wiesenbach','Paul','R','1966-2-14','1993-6-3');
INSERT INTO EMPLOYEE VALUES('109','Ms.','Travis','Elizabeth','K','1961-6-18','2006-2-14');
INSERT INTO EMPLOYEE VALUES('110','Mrs.','Genkazi','Leighla','W','1970-5-19','1990-6-29');

/* -- */


CREATE TABLE MODEL (
MOD_CODE varchar(10),
MOD_MANUFACTURER varchar(15),
MOD_NAME varchar(20),
MOD_SEATS float(8),
MOD_CHG_MILE numeric
)
ENGINE = INNODB;

INSERT INTO MODEL VALUES('C-90A','Beechcraft','KingAir','8','2.67');
INSERT INTO MODEL VALUES('PA23-250','Piper','Aztec','6','1.93');
INSERT INTO MODEL VALUES('PA31-350','Piper','Navajo Chieftain','10','2.35');
INSERT INTO MODEL VALUES('B200','Beechcraft', 'Super KingAir', '10', '1.93');

/* -- */


CREATE TABLE PILOT (
EMP_NUM int,
PIL_LICENSE varchar(25),
PIL_RATINGS varchar(30),
PIL_MED_TYPE varchar(1),
PIL_MED_DATE datetime,
PIL_PT135_DATE datetime
)
ENGINE = INNODB;

INSERT INTO PILOT VALUES('101','ATP','ATP/SEL/MEL/Instr/CFII','1','2016-1-20','2016-1-11');
INSERT INTO PILOT VALUES('104','ATP','ATP/SEL/MEL/Instr','1','2015-12-18','2016-1-17');
INSERT INTO PILOT VALUES('105','COM','COMM/SEL/MEL/Instr/CFI','2','2016-1-5','2016-1-2');
INSERT INTO PILOT VALUES('106','COM','COMM/SEL/MEL/Instr','2','2015-12-10','2016-2-2');
INSERT INTO PILOT VALUES('109','COM','ATP/SEL/MEL/SES/Instr/CFII','1','2016-1-22','2016-1-15');

