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


CREATE TABLE CUSTOMER (
  CUS_CODE      INT(6),
  CUS_LNAME     VARCHAR(15),
  CUS_FNAME     VARCHAR(15),
  CUS_INITIAL   VARCHAR(1),
  CUS_AREACODE  VARCHAR(3),
  CUS_PHONE     VARCHAR(8),
  CUS_BALANCE   FLOAT(8),
  PRIMARY KEY (CUS_CODE)
);
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',NULL,'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',NULL,'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 MODEL (
  MOD_CODE          varchar(10),
  MOD_MANUFACTURER  varchar(15),
  MOD_NAME          varchar(20),
  MOD_SEATS         float(8),
  MOD_CHG_MILE      float(8),
  PRIMARY KEY(MOD_CODE)
);
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');

/* -- */
CREATE TABLE AIRCRAFT (
  AC_NUMBER VARCHAR(5),
  MOD_CODE  VARCHAR(10),
  AC_TTAF   FLOAT(8),
  AC_TTEL   FLOAT(8),
  AC_TTER   FLOAT(8),
  PRIMARY KEY (AC_NUMBER),
  FOREIGN KEY (MOD_CODE) REFERENCES MODEL(MOD_CODE)
);


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

/* -- */
CREATE TABLE EMPLOYEE (
  EMP_NUM       INT(3),
  EMP_TITLE     VARCHAR(4),
  EMP_LNAME     VARCHAR(15),
  EMP_FNAME     VARCHAR(15),
  EMP_INITIAL   VARCHAR(1),
  EMP_DOB       DATETIME,
  EMP_HIRE_DATE DATETIME,
  PRIMARY KEY (EMP_NUM)
);
INSERT INTO EMPLOYEE VALUES('100','Mr.','Kolmycz','George','D','1952-6-15','2007-3-15');
INSERT INTO EMPLOYEE VALUES('101','Ms.','Lewis','Rhonda','G','1975-3-19','2008-4-25');
INSERT INTO EMPLOYEE VALUES('102','Mr.','VanDam','Rhett',NULL,'1968-11-14','2012-12-20');
INSERT INTO EMPLOYEE VALUES('103','Ms.','Jones','Anne','M','1984-10-16','2015-8-28');
INSERT INTO EMPLOYEE VALUES('104','Mr.','Lange','John','P','1981-11-8','2006-10-20');
INSERT INTO EMPLOYEE VALUES('105','Mr.','Williams','Robert','D','1985-3-14','2015-1-8');
INSERT INTO EMPLOYEE VALUES('106','Mrs.','Duzak','Jeanine','K','1978-2-12','2011-1-5');
INSERT INTO EMPLOYEE VALUES('107','Mr.','Diante','Jorge','D','1984-8-21','2006-2-1');
INSERT INTO EMPLOYEE VALUES('108','Mr.','Wiesenbach','Paul','R','1976-2-14','2014-11-18');
INSERT INTO EMPLOYEE VALUES('109','Ms.','Travis','Elizabeth','K','1971-6-18','2011-4-14');
INSERT INTO EMPLOYEE VALUES('110','Mrs.','Genkazi','Leighla','W','1980-5-19','2012-12-1');


/* -- */
CREATE TABLE RATING (
  RTG_CODE VARCHAR(5),
  RTG_NAME VARCHAR(50),
  PRIMARY KEY(RTG_CODE)
);
INSERT INTO RATING VALUES('CFI','Certified Flight Instructor');
INSERT INTO RATING VALUES('CFII','Certified Flight Instructor, Instrument');
INSERT INTO RATING VALUES('INSTR','Instrument');
INSERT INTO RATING VALUES('MEL','Multiengine Land');
INSERT INTO RATING VALUES('SEL','Single Engine, Land');
INSERT INTO RATING VALUES('SES','Single Engine, Sea');

/* -- */
CREATE TABLE CREW (
  CHAR_TRIP INT(6),
  EMP_NUM   INT(4),
  CREW_JOB  VARCHAR(20),
  PRIMARY KEY (CHAR_TRIP, EMP_NUM),
  FOREIGN KEY (CHAR_TRIP) REFERENCES CHARTER(CHAR_TRIP),
  FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO CREW VALUES('10001','104','Pilot');
INSERT INTO CREW VALUES('10002','101','Pilot');
INSERT INTO CREW VALUES('10003','105','Pilot');
INSERT INTO CREW VALUES('10003','109','Copilot');
INSERT INTO CREW VALUES('10004','106','Pilot');
INSERT INTO CREW VALUES('10005','101','Pilot');
INSERT INTO CREW VALUES('10006','109','Pilot');
INSERT INTO CREW VALUES('10007','104','Pilot');
INSERT INTO CREW VALUES('10007','105','Copilot');
INSERT INTO CREW VALUES('10008','106','Pilot');
INSERT INTO CREW VALUES('10009','105','Pilot');
INSERT INTO CREW VALUES('10010','108','Pilot');
INSERT INTO CREW VALUES('10011','101','Pilot');
INSERT INTO CREW VALUES('10011','104','Copilot');
INSERT INTO CREW VALUES('10012','101','Pilot');
INSERT INTO CREW VALUES('10013','105','Pilot');
INSERT INTO CREW VALUES('10014','106','Pilot');
INSERT INTO CREW VALUES('10015','101','Copilot');
INSERT INTO CREW VALUES('10015','104','Pilot');
INSERT INTO CREW VALUES('10016','105','Copilot');
INSERT INTO CREW VALUES('10016','109','Pilot');
INSERT INTO CREW VALUES('10017','101','Pilot');
INSERT INTO CREW VALUES('10018','104','Copilot');
INSERT INTO CREW VALUES('10018','105','Pilot');


/* -- */
CREATE TABLE EARNEDRATING (
  EMP_NUM      INT(4),
  RTG_CODE     VARCHAR(5),
  EARNRTG_DATE DATETIME,
  PRIMARY KEY (EMP_NUM, RTG_CODE),
  FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
  FOREIGN KEY (RTG_CODE) REFERENCES RATING(RTG_CODE)
);
INSERT INTO EARNEDRATING VALUES('101','CFI','2004-2-18');
INSERT INTO EARNEDRATING VALUES('101','CFII','2011-12-15');
INSERT INTO EARNEDRATING VALUES('101','INSTR','1999-11-8');
INSERT INTO EARNEDRATING VALUES('101','MEL','2002-6-23');
INSERT INTO EARNEDRATING VALUES('101','SEL','1999-4-21');
INSERT INTO EARNEDRATING VALUES('104','INSTR','2002-7-15');
INSERT INTO EARNEDRATING VALUES('104','MEL','2003-1-29');
INSERT INTO EARNEDRATING VALUES('104','SEL','2001-3-12');
INSERT INTO EARNEDRATING VALUES('105','CFI','2003-11-18');
INSERT INTO EARNEDRATING VALUES('105','INSTR','2001-4-17');
INSERT INTO EARNEDRATING VALUES('105','MEL','2001-8-12');
INSERT INTO EARNEDRATING VALUES('105','SEL','2000-9-23');
INSERT INTO EARNEDRATING VALUES('106','INSTR','2001-12-20');
INSERT INTO EARNEDRATING VALUES('106','MEL','2002-4-2');
INSERT INTO EARNEDRATING VALUES('106','SEL','2000-3-10');
INSERT INTO EARNEDRATING VALUES('109','CFI','2004-11-5');
INSERT INTO EARNEDRATING VALUES('109','CFII','2009-6-21');
INSERT INTO EARNEDRATING VALUES('109','INSTR','2002-7-23');
INSERT INTO EARNEDRATING VALUES('109','MEL','2003-3-15');
INSERT INTO EARNEDRATING VALUES('109','SEL','2002-2-5');
INSERT INTO EARNEDRATING VALUES('109','SES','2002-5-12');


/* -- */
CREATE TABLE PILOT (
  EMP_NUM         INT(4),
  PIL_LICENSE     VARCHAR(25),
  PIL_RATINGS     VARCHAR(25),
  PIL_MED_TYPE    VARCHAR(1),
  PIL_MED_DATE    DATETIME,
  PIL_PT135_DATE  DATETIME,
  PRIMARY KEY(EMP_NUM),
  FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO PILOT VALUES('101','ATP','SEL/MEL/Instr/CFII','1','2015-4-12','2016-6-15');
INSERT INTO PILOT VALUES('104','ATP','SEL/MEL/Instr','1','2015-6-10','2016-3-23');
INSERT INTO PILOT VALUES('105','COM','SEL/MEL/Instr/CFI','2','2015-2-25','2016-2-12');
INSERT INTO PILOT VALUES('106','COM','SEL/MEL/Instr','2','2015-4-2','2016-12-24');
INSERT INTO PILOT VALUES('109','COM','SEL/MEL/SES/Instr/CFII','1','2015-4-14','2016-4-21');




