TaxiTest.sql

-- MySQL Script generated by MySQL Workbench
-- Sat Sep 15 17:02:30 2018
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema TaxiTest
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema TaxiTest
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `TaxiTest` DEFAULT CHARACTER SET utf8 ;
USE `TaxiTest` ;

-- -----------------------------------------------------
-- Table `TaxiTest`.`Client`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Client` (
  `idClient` INT NOT NULL AUTO_INCREMENT,
  `Phone` VARCHAR(45) NOT NULL,
  `Password` VARCHAR(45) NOT NULL,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`idClient`),
  UNIQUE INDEX `Phone_UNIQUE` (`Phone` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Driver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Driver` (
  `idDriver` INT NOT NULL,
  `Name` VARCHAR(45) NULL,
  `DriverLisence` VARCHAR(45) NOT NULL,
  `Phone` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idDriver`),
  UNIQUE INDEX `DriverLisence_UNIQUE` (`DriverLisence` ASC) VISIBLE,
  UNIQUE INDEX `Phone1_UNIQUE` (`Phone` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Operator`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Operator` (
  `idOperator` INT NOT NULL,
  `Name` VARCHAR(45) NOT NULL,
  `Phone` VARCHAR(45) NOT NULL,
  `Mail` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idOperator`),
  UNIQUE INDEX `Phone_UNIQUE` (`Phone` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`InsuranceCompany`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`InsuranceCompany` (
  `idInsuranceCompany` INT NOT NULL,
  `Address` VARCHAR(45) NOT NULL,
  `Phone` VARCHAR(45) NOT NULL,
  `Mail` VARCHAR(45) NOT NULL,
  `Name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idInsuranceCompany`),
  UNIQUE INDEX `Address_UNIQUE` (`Address` ASC) VISIBLE,
  UNIQUE INDEX `Phone_UNIQUE` (`Phone` ASC) VISIBLE,
  UNIQUE INDEX `Mail_UNIQUE` (`Mail` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Insurance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Insurance` (
  `idInsurance` INT NOT NULL,
  `InsCompany_idInsCompany` INT NOT NULL,
  `InsuranceEndDate` DATETIME NOT NULL,
  `InsuranceStartDate` DATETIME NOT NULL,
  PRIMARY KEY (`idInsurance`),
  INDEX `fk_Insurance_InsuranceCompany1_idx` (`InsCompany_idInsCompany` ASC) VISIBLE,
  CONSTRAINT `fk_Insurance_InsuranceCompany1`
    FOREIGN KEY (`InsCompany_idInsCompany`)
    REFERENCES `TaxiTest`.`InsuranceCompany` (`idInsuranceCompany`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Car`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Car` (
  `idCar` INT NOT NULL,
  `Lisense` VARCHAR(45) NOT NULL,
  `Mark` VARCHAR(45) NOT NULL,
  `Model` VARCHAR(45) NOT NULL,
  `Insurance_idInsurance` INT NOT NULL,
  `ChildSeat` TINYINT(1) NOT NULL,
  PRIMARY KEY (`idCar`),
  INDEX `fk_Car_Insurance1_idx` (`Insurance_idInsurance` ASC) VISIBLE,
  CONSTRAINT `fk_Car_Insurance1`
    FOREIGN KEY (`Insurance_idInsurance`)
    REFERENCES `TaxiTest`.`Insurance` (`idInsurance`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Call`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Call` (
  `idCall` INT NOT NULL AUTO_INCREMENT,
  `Operator_idOperator` INT NOT NULL,
  `Client_idClient` INT NOT NULL,
  `Datetime` DATETIME NULL,
  `Duration` INT NULL,
  PRIMARY KEY (`Operator_idOperator`, `Client_idClient`),
  INDEX `fk_Operator_has_Client_Client1_idx` (`Client_idClient` ASC) VISIBLE,
  INDEX `fk_Operator_has_Client_Operator1_idx` (`Operator_idOperator` ASC) VISIBLE,
  UNIQUE INDEX `idCall_UNIQUE` (`idCall` ASC) VISIBLE,
  CONSTRAINT `fk_Operator_has_Client_Operator1`
    FOREIGN KEY (`Operator_idOperator`)
    REFERENCES `TaxiTest`.`Operator` (`idOperator`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Operator_has_Client_Client1`
    FOREIGN KEY (`Client_idClient`)
    REFERENCES `TaxiTest`.`Client` (`idClient`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`PaymentService`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`PaymentService` (
  `idPaymentService` INT NOT NULL,
  `Name` VARCHAR(45) NOT NULL,
  `Address` VARCHAR(45) NOT NULL,
  `Site` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idPaymentService`),
  UNIQUE INDEX `Name_UNIQUE` (`Name` ASC) VISIBLE,
  UNIQUE INDEX `Site_UNIQUE` (`Site` ASC) VISIBLE,
  UNIQUE INDEX `Address_UNIQUE` (`Address` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Payment` (
  `idPayment` INT NOT NULL AUTO_INCREMENT,
  `PaymentService_idPaymentService` INT NOT NULL,
  `Cost` DOUBLE NOT NULL,
  `PayTime` DATETIME NOT NULL,
  `Type` TINYINT(1) NOT NULL,
  `Paycheck` VARCHAR(45) NULL,
  PRIMARY KEY (`idPayment`),
  INDEX `fk_Payment_PaymentService1_idx` (`PaymentService_idPaymentService` ASC) VISIBLE,
  CONSTRAINT `fk_Payment_PaymentService1`
    FOREIGN KEY (`PaymentService_idPaymentService`)
    REFERENCES `TaxiTest`.`PaymentService` (`idPaymentService`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Address`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Address` (
  `idAddress` INT NOT NULL,
  `City` VARCHAR(45) NOT NULL,
  `Street` VARCHAR(45) NOT NULL,
  `HouseNumber` INT NOT NULL,
  `GeoPoint` POINT NOT NULL,
  PRIMARY KEY (`idAddress`),
  UNIQUE INDEX `City_UNIQUE` (`City` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Taxi`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Taxi` (
  `idTaxi` INT NOT NULL,
  `Driver_idDriver` INT NOT NULL,
  `Car_idCar` INT NOT NULL,
  `Current_idAddress` INT NOT NULL,
  `Available` TINYINT(1) NOT NULL,
  `LatUpdate` DATETIME NOT NULL,
  PRIMARY KEY (`Driver_idDriver`, `Car_idCar`),
  INDEX `fk_Taxi_Car1_idx` (`Car_idCar` ASC) INVISIBLE,
  INDEX `fk_Taxi_Address1_idx` (`Current_idAddress` ASC) INVISIBLE,
  INDEX `fk_Taxi_idTaxi` (`idTaxi` ASC) INVISIBLE,
  CONSTRAINT `fk_Taxi_Driver1`
    FOREIGN KEY (`Driver_idDriver`)
    REFERENCES `TaxiTest`.`Driver` (`idDriver`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Taxi_Car1`
    FOREIGN KEY (`Car_idCar`)
    REFERENCES `TaxiTest`.`Car` (`idCar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Taxi_Address1`
    FOREIGN KEY (`Current_idAddress`)
    REFERENCES `TaxiTest`.`Address` (`idAddress`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaxiTest`.`Order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaxiTest`.`Order` (
  `idOrder` INT NOT NULL,
  `Call_idCall` INT NOT NULL,
  `Departure_idAddress` INT NOT NULL,
  `Destination_idAddress` INT NOT NULL,
  `Taxi_idTaxi` INT NOT NULL,
  `Payment_idPayment` INT NOT NULL,
  `StartDatetime` DATETIME NOT NULL,
  `FinishDatetime` DATETIME NULL,
  `Cost` INT NOT NULL,
  `Rate` INT NULL,
  PRIMARY KEY (`idOrder`),
  INDEX `fk_Order_Call_idx` (`Call_idCall` ASC) VISIBLE,
  INDEX `fk_Order_Payment1_idx` (`Payment_idPayment` ASC) VISIBLE,
  INDEX `fk_Order_Address1_idx` (`Departure_idAddress` ASC) VISIBLE,
  INDEX `fk_Order_Address2_idx` (`Destination_idAddress` ASC) VISIBLE,
  INDEX `fk_Order_Taxi_idx` (`Taxi_idTaxi` ASC) VISIBLE,
  CONSTRAINT `fk_Order_Call`
    FOREIGN KEY (`Call_idCall`)
    REFERENCES `TaxiTest`.`Call` (`idCall`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Order_Payment1`
    FOREIGN KEY (`Payment_idPayment`)
    REFERENCES `TaxiTest`.`Payment` (`idPayment`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Order_Address1`
    FOREIGN KEY (`Departure_idAddress`)
    REFERENCES `TaxiTest`.`Address` (`idAddress`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Order_Address2`
    FOREIGN KEY (`Destination_idAddress`)
    REFERENCES `TaxiTest`.`Address` (`idAddress`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Order_Taxi`
    FOREIGN KEY (`Taxi_idTaxi`)
    REFERENCES `TaxiTest`.`Taxi` (`idTaxi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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