CREATE TABLE Avia_Customer(
CUS_CODE NUMBER(10,0) NOT NULL, 
CUS_LNAME VARCHAR(15), 
CUS_FNAME VARCHAR(15), 
CUS_INITIAL CHAR(1),
CUS_AREACODE CHAR(3),
CUS_PHONE CHAR(8),
CUS_BALANCE NUMBER(10,2),
PRIMARY KEY(CUS_CODE));

CREATE TABLE Avia_Model(
MOD_CODE VARCHAR(10) NOT NULL, 
MOD_MANUFACTURER VARCHAR(15), 
MOD_NAME VARCHAR(20), 
MOD_SEATS INTEGER,
MOD_CHR_MILE NUMBER(10,2),
PRIMARY KEY(MOD_CODE));

CREATE TABLE Avia_Aircraft(
AC_NUMBER VARCHAR(5) NOT NULL, 
MOD_CODE VARCHAR(10),
AC_TTAF NUMBER(10,1),
AC_TTEL NUMBER(10,1),
AC_TTER NUMBER(10,1),
PRIMARY KEY(AC_NUMBER),
FOREIGN KEY(MOD_CODE) REFERENCES Avia_Model);

CREATE TABLE Avia_Rating(
RTG_CODE VARCHAR(5) NOT NULL, 
RTG_NAME VARCHAR(50),
PRIMARY KEY(RTG_CODE));

CREATE TABLE Avia_Charter(
CHAR_TRIP NUMBER(10,0) NOT NULL,
CHAR_DATE DATE,
AC_NUMBER VARCHAR(5),
CHAR_DESTINATION CHAR(3),
CHAR_DISTANCE NUMBER(10,1),
CHAR_HOURS_FLOWN NUMBER(10,1),
CHAR_HOURS_WAIT NUMBER(10,1),
CHAR_FUEL_GALLONS NUMBER(10,1),
CHAR_OIL_QTS INTEGER,
CUS_CODE NUMBER(10,0),
PRIMARY KEY(CHAR_TRIP),
FOREIGN KEY(CUS_CODE) REFERENCES Avia_Customer,
FOREIGN KEY(AC_NUMBER) REFERENCES Avia_Aircraft);

CREATE TABLE Avia_Employee(
EMP_NUM INTEGER NOT NULL, 
EMP_TITLE VARCHAR(4),
EMP_LNAME VARCHAR(15), 
EMP_FNAME VARCHAR(15), 
EMP_INITIAL CHAR(1),
EMP_DOB DATE,
EMP_HIRE_DATE DATE,
PRIMARY KEY(EMP_NUM));

CREATE TABLE Avia_Crew(
CHAR_TRIP INTEGER NOT NULL, 
EMP_NUM INTEGER NOT NULL,
CREW_JOB VARCHAR(20),
PRIMARY KEY(CHAR_TRIP, EMP_NUM),
FOREIGN KEY(CHAR_TRIP) REFERENCES Avia_Charter,
FOREIGN KEY(EMP_NUM) REFERENCES Avia_Employee);

CREATE TABLE Avia_Pilot(
EMP_NUM INTEGER NOT NULL,
PIL_LICENSE VARCHAR(25),
PIL_MED_TYPE CHAR(1),
PIL_MED_DATE DATE,
PIL_PT135_DATE DATE,
PRIMARY KEY(EMP_NUM),
FOREIGN KEY(EMP_NUM) REFERENCES Avia_Employee);

CREATE TABLE Avia_EarnedRating(
EMP_NUM INTEGER NOT NULL,
RTG_CODE VARCHAR(5) NOT NULL,
EARNRTG_DATE DATE,
PRIMARY KEY(EMP_NUM, RTG_CODE),
FOREIGN KEY(EMP_NUM) REFERENCES Avia_Pilot,
FOREIGN KEY(RTG_CODE) REFERENCES Avia_Rating);