SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `eLogBook` ;
USE `eLogBook`;

-- -----------------------------------------------------
-- Table `eLogBook`.`Planes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eLogBook`.`Planes` ;

CREATE  TABLE IF NOT EXISTS `eLogBook`.`Planes` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Number` VARCHAR(45) NULL ,
  `Manufacturer` VARCHAR(45) NULL ,
  `Model` VARCHAR(45) NULL ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eLogBook`.`Pilots`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eLogBook`.`Pilots` ;

CREATE  TABLE IF NOT EXISTS `eLogBook`.`Pilots` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `First Name` VARCHAR(45) NULL ,
  `Last Name` VARCHAR(45) NULL ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eLogBook`.`Activities`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eLogBook`.`Activities` ;

CREATE  TABLE IF NOT EXISTS `eLogBook`.`Activities` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `PlaneID` INT NULL ,
  `PilotID` INT NULL ,
  `From` VARCHAR(45) NULL ,
  `To` VARCHAR(45) NULL ,
  `Stops` VARCHAR(45) NULL ,
  `Remarks and Endorsements` TEXT NULL ,
  `Instrument Approaches` INT UNSIGNED NULL ,
  `Takeoffs` INT UNSIGNED NULL ,
  `Landings` INT UNSIGNED NULL ,
  `SEL` FLOAT UNSIGNED NULL ,
  `MEL` FLOAT UNSIGNED NULL ,
  `Night` FLOAT UNSIGNED NULL ,
  `Actual Instrument` FLOAT UNSIGNED NULL ,
  `Simulated Instrument` FLOAT UNSIGNED NULL ,
  `Filght Simulator` FLOAT UNSIGNED NULL ,
  `Cross Country` FLOAT UNSIGNED NULL ,
  `Solo` FLOAT UNSIGNED NULL ,
  `Dual` FLOAT UNSIGNED NULL ,
  `PIC` FLOAT UNSIGNED NULL ,
  `Total` FLOAT UNSIGNED NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX fk_Activities_Pilots (`PilotID` ASC) ,
  INDEX fk_Activities_Planes (`PlaneID` ASC) ,
  CONSTRAINT `fk_Activities_Pilots`
    FOREIGN KEY (`PilotID` )
    REFERENCES `eLogBook`.`Pilots` (`ID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Activities_Planes`
    FOREIGN KEY (`PlaneID` )
    REFERENCES `eLogBook`.`Planes` (`ID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;