--------------------------------------------------------------- 
-----------------------  MEMBERS   ----------------------------
---------------------------------------------------------------  
 CREATE  TABLE `members` (
 `Member_Id` 			    VARCHAR(11)   NOT NULL ,
  `Name` 				    VARCHAR(100)  DEFAULT NULL ,
  `Surname` 			    VARCHAR(100)  DEFAULT NULL ,
  `Middle_Name` 		    VARCHAR(100)  DEFAULT NULL ,
  `Birth_Date` 			    DATETIME      DEFAULT NULL ,
  `Language` 			    VARCHAR(2)    DEFAULT NULL ,
  `Gender` 				    VARCHAR(1)    DEFAULT NULL ,
  `Password` 			    VARCHAR(10)   DEFAULT NULL ,
  `Card_Tier`	 		    VARCHAR(10)   DEFAULT NULL ,
  `Member_Type` 		    VARCHAR(2)    DEFAULT NULL ,
  `Member_Status` 		    VARCHAR(2)    DEFAULT NULL ,
  `Member_Prefix` 		    VARCHAR(45)   DEFAULT NULL ,
  `Member_Titles` 		    VARCHAR(45)   DEFAULT NULL ,
  `Enrollment_Date` 		DATETIME 	  DEFAULT SYSDATE ,
  `Enrollment_Source_Code` 	VARCHAR(10)   DEFAULT NULL ,
  `Enrolled_By` 			VARCHAR(45)   DEFAULT NULL ,
 PRIMARY KEY (`Member_Id`) ,
 UNIQUE INDEX (`Member_Id`) ,
 INDEX (`Language`) ,
 INDEX (`Member_Type`) ,
 INDEX (`Member_Titles`) ,
 INDEX (`Member_Status`) ,
 INDEX (`Enrollment_Source_Code`) ,
 INDEX (`Card_Tier`) ,
  CONSTRAINT `Card_Tier`
    FOREIGN KEY (`Card_Tier`)
    REFERENCES  tier_types` (`CODE`)
  CONSTRAINT `Enrollment_Source_Code`
    FOREIGN KEY (`Enrollment_Source_Code`)
    REFERENCES enrollment_sources` (`CODE`)
  CONSTRAINT `Language`
    FOREIGN KEY (`Language`)
    REFERENCES  languages` (`CODE`)
  CONSTRAINT `Member_Status`
    FOREIGN KEY (`Member_Status`)
    REFERENCES  status_types` (`CODE`)
  CONSTRAINT `Member_Titles`
    FOREIGN KEY (`Member_Titles`)
    REFERENCES  titles` (`TITLE`)
  CONSTRAINT `Member_Type`   
    FOREIGN KEY (`Member_Type`)
    REFERENCES  member_types` (`CODE`)
---------------------------
CREATE  TABLE `languages` (
  `CODE` 		VARCHAR(2)  	NOT NULL ,
  `DEFINITION` 	VARCHAR(45) 	DEFAULT NULL ,
  `DEFINED_BY` 	VARCHAR(45)   	DEFAULT NULL ,
  `DEFINE_DATE` DATE 		 	DEFAULT SYSDATE ,
PRIMARY KEY (CODE) ,
UNIQUE INDEX (CODE) )
---------------------------
 CREATE  TABLE `status_types` (
  `CODE` 		  VARCHAR(2)	NOT NULL ,
  `DEFINITION` 	  VARCHAR(45)   DEFAULT NULL ,
  `DEFINED_BY` 	  VARCHAR(45)   DEFAULT NULL ,
  `DEFINED_DATE`  VARCHAR(45)   DEFAULT SYSDATE ,
PRIMARY KEY (`CODE`) ,
UNIQUE INDEX  (`CODE`)
---------------------------
CREATE  TABLE `member_types` (
  `CODE` 		VARCHAR(2)   NOT NULL ,
  `Explanation` VARCHAR(100) DEFAULT NULL ,
  `DEFINED_BY` 	VARCHAR(45)  DEFAULT NULL ,
  `DEFINE_DATE` VARCHAR(45)  DEFAULT SYSDATE ,
PRIMARY KEY (`CODE`) ,
UNIQUE INDEX (`CODE`) )
---------------------------
CREATE  TABLE `titles` (
  `TITLE` 		 VARCHAR(45   NOT NULL DEFAULT '' ,
  `EXPLANANTION` VARCHAR(45)  DEFAULT NULL ,
  `GENDER_TYPE`  VARCHAR(45)  DEFAULT NULL ,
  `LANGUAGE` 	 VARCHAR(2)   DEFAULT NULL ,
PRIMARY KEY (`TITLE`) )
---------------------------
CREATE  TABLE `billing` (
  `Seq` 			    INT(11) 	 NOT NULL ,
  `Member_No` 			VARCHAR(11)  DEFAULT NULL ,
  `Cost` 				VARCHAR(15)  DEFAULT NULL ,
  `Issuing_Office_Id` 	VARCHAR(45)  DEFAULT NULL ,
  `Issue_Date` 			DATETIME 	 DEFAULT NULL ,
  PRIMARY KEY (`Seq`) ,
  INDEX (`Member_No` ,
  
  CONSTRAINT `Member_No`
    FOREIGN KEY (`Member_No` )
    REFERENCES  `members` (`Member_Id` ) ) 
---------------------------
CREATE  TABLE `enrollment_sources` (
  `CODE` 		VARCHAR(10)  NOT NULL ,
  `DEFINITION` 	VARCHAR(45)  DEFAULT NULL ,
  `DEFINED_BY` 	VARCHAR(45)  DEFAULT NULL ,
  `DEFINE_DATE` VARCHAR(45)  DEFAULT SYSDATE ,
  `MEMBER_TYPE` VARCHAR(2)   DEFAULT NULL ,
  PRIMARY KEY (`CODE`) ,
  UNIQUE INDEX (`CODE`) ) 
---------------------------
CREATE  TABLE `tier_types` (
  `CODE` 	   VARCHAR(10)  NOT NULL ,
  `Definition` VARCHAR(45)  DEFAULT NULL ,
  PRIMARY KEY (`CODE`) ) 

--------------------------------------------------------------- 
------------------  FULL PASSENGER LIST  ----------------------
--------------------------------------------------------------- 
CREATE  TABLE  `full_passenger_list` (
  `FPL_SEQ` 		INT(11) 	  NOT NULL AUTO_INCREMENT ,
  `FLOWN_ID` 		VARCHAR(11)   DEFAULT NULL ,
  `FLIGHT_NUMBER` 	VARCHAR(45)   DEFAULT NULL ,
  `FLIGHT_DATE` 	DATETIME 	  DEFAULT NULL ,
  `FLIGHT_CLASS` 	VARCHAR(1)    DEFAULT NULL ,
  `FLYER_COMPANY` 	VARCHAR(10)   DEFAULT NULL ,
  `ORIGIN` 			VARCHAR(3)    DEFAULT NULL ,
  `DESTINATION` 	VARCHAR(3)    DEFAULT NULL ,
  `PNR` 			VARCHAR(45)   DEFAULT NULL ,
  `NAME` 			VARCHAR(200)  DEFAULT NULL ,
  `SURNAME` 		VARCHAR(100)  DEFAULT NULL ,
  `STATUS` 			VARCHAR(45)   DEFAULT NULL ,
  `Free_Ticket` 	VARCHAR(1)    DEFAULT NULL ,
  `Redemption`	 	VARCHAR(1)    DEFAULT NULL ,
  `By_Money` 		VARCHAR(1)    DEFAULT NULL ,
  
  PRIMARY KEY (`FPL_SEQ`) ,
  UNIQUE INDEX  (`FLOWN_ID`) ,
  INDEX  (`FLIGHT_CLASS`) ,
  INDEX  (`ORIGIN` ),
  INDEX  (`DESTINATION`) ,
  INDEX  (`FLOWN_ID`) ,
  CONSTRAINT `DESTINATION`
    FOREIGN KEY (`DESTINATION` )
    REFERENCES `airports` (`Code`)
  CONSTRAINT `FLIGHT_CLASS`
    FOREIGN KEY (`FLIGHT_CLASS`)
    REFERENCES `flight_classes`(`CODE` )
  CONSTRAINT `FLOWN_ID`
    FOREIGN KEY (`FLOWN_ID` )
    REFERENCES `members` (`Member_Id` )
  CONSTRAINT `ORIGIN`
    FOREIGN KEY (`ORIGIN` )
    REFERENCES  `airports` (`Code` )
)
---------------------------
CREATE  TABLE `airports` (
  `Code` VARCHAR(3)  NOT NULL ,
  `Name` VARCHAR(45) DEFAULT NULL ,
  UNIQUE INDEX (`Code`) )
  --------------------------------------------------------------- 
-----------------  EMAIL - ADDRESS - PHONE ---------------------
--------------------------------------------------------------- 
CREATE  TABLE `flight_classes` (
  `CODE` 			VARCHAR(1)    NOT NULL ,
  `EXPLANANTION` 	VARCHAR(45)   DEFAULT NULL ,
  `DEFINED_BY` 		VARCHAR(45)   DEFAULT NULL ,
  `DEFINED_DATE` 	VARCHAR(45)   DEFAULT SYSDATE ,
  PRIMARY KEY (`CODE`) ,
  UNIQUE INDEX (`CODE`) )
---------------------------
CREATE  TABLE `member_email_table` (
  `Email_Seq` 		INT(11) 	NOT NULL ,
  `Own_Id` 			VARCHAR(11) DEFAULT NULL ,
  `Email_Address` 	VARCHAR(45) DEFAULT NULL ,
  `Type` 			VARCHAR(1)  DEFAULT 'P' ,
  `Defined_By` 		VARCHAR(45) DEFAULT NULL ,
  `Defined_Date` 	DATETIME 	DEFAULT SYSDATE ,
  `Preferred` 		VARCHAR(1)  DEFAULT 'T' ,
  `Status` 			VARCHAR(1)  DEFAULT 'T' ,
  PRIMARY KEY (`Email_Seq`) ,
  INDEX (`Own_Id`) ,
  CONSTRAINT `Own_Id`
    FOREIGN KEY (`Own_Id` )
    REFERENCES `members` (`Member_Id` )
 )
---------------------------
CREATE  TABLE `member_addres_table` (
  `Address_Seq` 	INT(11) 	  NOT NULL ,
  `Flyer_Id` 		VARCHAR(11)   DEFAULT NULL ,
  `Type` 			VARCHAR(1)    DEFAULT 'H' ,
  `Company` 		VARCHAR(10)   DEFAULT NULL ,
  `Address` 		VARCHAR(45)   DEFAULT NULL ,
  `Preferred` 		VARCHAR(1)    DEFAULT 'T' ,
  `Status` 			VARCHAR(1)    DEFAULT 'T' ,
  `P_Box` 			VARCHAR(45)   DEFAULT NULL ,
  `Country` 		VARCHAR(100)  DEFAULT NULL ,
  `City` 			VARCHAR(45)   DEFAULT NULL ,
  `State` 			VARCHAR(45)   DEFAULT NULL ,
  PRIMARY KEY (`Address_Seq`) ,
  INDEX  (`Flyer_Id`) ,
  INDEX  (`Company`) ,
  INDEX  (`Company`) ,
  CONSTRAINT `Company`
    FOREIGN KEY (`Company`)
    REFERENCES `companies` (`CODE`) ,
  CONSTRAINT `Flyer_Id`
    FOREIGN KEY (`Flyer_Id`)
    REFERENCES `members` (`Member_Id`) )
---------------------------
CREATE  TABLE `member_phone_table` (
  `Phone_Seq`	 	INT(11) 	  NOT NULL ,
  `Member_Id` 		VARCHAR(11)   DEFAULT NULL ,
  `Type` 			VARCHAR(1)    DEFAULT 'H' ,
  `Company_Name` 	VARCHAR(10)   DEFAULT NULL ,
  `Country_Code` 	VARCHAR(45)   DEFAULT NULL ,
  `City_Code` 		VARCHAR(45)   DEFAULT NULL ,
  `Area_Code` 		VARCHAR(45)   DEFAULT NULL ,
  `Telephone_Number`VARCHAR(45)   DEFAULT NULL ,
  `Preferred` 		VARCHAR(1)    DEFAULT 'T' ,
  `Status` 			VARCHAR(1)    DEFAULT 'T' ,
  PRIMARY KEY (`Phone_Seq`) ,
  INDEX (`Member_Id`) ,
  INDEX (`Company_Name`) ,
  CONSTRAINT `Company_Name`
    FOREIGN KEY (`Company_Name`)
    REFERENCES `companies` (`CODE`) ,
  CONSTRAINT `Member_Id`
    FOREIGN KEY (`Member_Id`)
    REFERENCES `members` (`Member_Id`) )
---------------------------
CREATE  TABLE `companies` (
  `CODE` 		  VARCHAR(10)   NOT NULL ,
  `NAME` 		  VARCHAR(45)   DEFAULT NULL ,
  `TYPE` 		  VARCHAR(2)    DEFAULT NULL ,
  `DEFINED_BY` 	  VARCHAR(25)   DEFAULT NULL ,
  `DEFINED_DATED` DATETIME 		DEFAULT SYSDATE ,
  `START_DATE` 	  DATETIME      DEFAULT NULL ,
  `END_DATE`      DATETIME 	    DEFAULT NULL ,
  PRIMARY KEY (`CODE`) ,
  UNIQUE INDEX(`CODE`) ,
  INDEX (`TYPE`) ,
  CONSTRAINT `TYPE`
    FOREIGN KEY (`TYPE` )
    REFERENCES `company_types` (`CODE`) ) 
---------------------------
CREATE  TABLE `company_types` (
  `CODE` 		 VARCHAR(2)    NOT NULL ,
  `DEFINITION` 	 VARCHAR(25)   DEFAULT NULL ,
  `DEFINED_BY` 	 VARCHAR(25)   DEFAULT NULL ,
  `DEFINED_DATE` DATETIME      DEFAULT SYSDATE ,
  PRIMARY KEY (`CODE`) , 
  UNIQUE INDEX `CODE` ) ) 
  
  --------------------------------------------------------------- 
----------------------------  ACTIVITY  ----------------------
--------------------------------------------------------------- 
CREATE  TABLE `activity` (
  `Seq` 			INT(11) 	  NOT NULL ,
  `Passenger_Id` 	VARCHAR(11)   DEFAULT NULL ,
  `Activity_Date` 	VARCHAR(45)   DEFAULT NULL ,
  `Points` 			VARCHAR(45)   DEFAULT NULL ,
  `Company_Code` 	VARCHAR(10)   DEFAULT NULL ,
  `Agent_Id` 		VARCHAR(45)   DEFAULT NULL ,
  `Cart_Tier` 		VARCHAR(10)   DEFAULT NULL ,
  `Isue_Date` 		DATE 		  DEFAULT NULL ,
  `Expire_Date` 	DATE 		  DEFAULT NULL ,
  PRIMARY KEY (`Seq`) ,
  INDEX (`Cart_Tier`) ,
  INDEX (`Passenger_Id`) ,
  INDEX (`Company_Code`) ,
  CONSTRAINT `Cart_Tier`
    FOREIGN KEY (`Cart_Tier`)
    REFERENCES  `tier_types` (`CODE` ) ,
  CONSTRAINT `Company_Code`
    FOREIGN KEY (`Company_Code`)
    REFERENCES `companies` (`CODE`) ,
  CONSTRAINT `Passenger_Id`
    FOREIGN KEY (`Passenger_Id` )
    REFERENCES  `members` (`Member_Id`) ) 
---------------------------
CREATE  TABLE `tier_types` (
  `CODE` 		VARCHAR(10)  NOT NULL ,
  `Definition` 	VARCHAR(45)  DEFAULT NULL ,
  PRIMARY KEY (`CODE`) ) 
 ---------------------------