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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

-- -----------------------------------------------------
-- Table `mydb`.`k_staff`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_staff` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_staff` (
  `staff_num` INT auto_increment,
  `staff_name` VARCHAR(45) NOT NULL,
  `staff_post` VARCHAR(45) NULL,
  `staff_hiredate` DATE NULL,
  `staff_termdate` DATE NULL,
  `K_dept_dept_num` INT NOT NULL,
  PRIMARY KEY (`staff_num`),
  INDEX `fk_k_staff_K_dept1_idx` (`K_dept_dept_num` ASC),
  CONSTRAINT `fk_k_staff_K_dept1`
    FOREIGN KEY (`K_dept_dept_num`)
    REFERENCES `mydb`.`K_dept` (`dept_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`K_dept`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`K_dept` ;

CREATE TABLE IF NOT EXISTS `mydb`.`K_dept` (
  `dept_num` INT auto_increment,
  `dept_full_name` VARCHAR(45) NULL,
  `dept_short_name` VARCHAR(10) NULL,
  `k_staff_staff_num` INT NOT NULL,
  PRIMARY KEY (`dept_num`),
  INDEX `fk_K_dept_k_staff_idx` (`k_staff_staff_num` ASC),
  CONSTRAINT `fk_K_dept_k_staff`
    FOREIGN KEY (`k_staff_staff_num`)
    REFERENCES `mydb`.`k_staff` (`staff_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`k_firm`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_firm` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_firm` (
  `firm_num` INT auto_increment,
  `firm_name` VARCHAR(45) NOT NULL,
  `firm_addr` VARCHAR(45) NULL,
  `firm_phone` VARCHAR(20) NULL,
  PRIMARY KEY (`firm_num`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`k_contract`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_contract` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_contract` (
  `contract_num` INT auto_increment,
  `contract_date` DATE NULL,
  `contract_type` ENUM('A', 'B', 'C') NULL,
  `k_firm_firm_num` INT NOT NULL,
  `k_staff_staff_num` INT NOT NULL,
  PRIMARY KEY (`contract_num`),
  INDEX `fk_k_contract_k_firm1_idx` (`k_firm_firm_num` ASC),
  INDEX `fk_k_contract_k_staff1_idx` (`k_staff_staff_num` ASC),
  CONSTRAINT `fk_k_contract_k_firm1`
    FOREIGN KEY (`k_firm_firm_num`)
    REFERENCES `mydb`.`k_firm` (`firm_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_k_contract_k_staff1`
    FOREIGN KEY (`k_staff_staff_num`)
    REFERENCES `mydb`.`k_staff` (`staff_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`k_bill`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_bill` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_bill` (
  `bill_num` INT auto_increment,
  `bill_date` DATE NULL,
  `bill_sum` DECIMAL(9,2) NULL,
  `bill_term` DATE NULL,
  `bill_peni` DECIMAL(6,2) NULL,
  `k_contract_contract_num` INT NOT NULL,
  PRIMARY KEY (`bill_num`),
  INDEX `fk_k_bill_k_contract1_idx` (`k_contract_contract_num` ASC),
  CONSTRAINT `fk_k_bill_k_contract1`
    FOREIGN KEY (`k_contract_contract_num`)
    REFERENCES `mydb`.`k_contract` (`contract_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`k_payment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_payment` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_payment` (
  `payment_num` INT,
  `payment_date` DATE NULL,
  `payment_sum` DECIMAL(9,2) NULL,
  `k_bill_bill_num` INT NOT NULL,
  PRIMARY KEY (`payment_num`, `k_bill_bill_num`),
  INDEX `fk_k_payment_k_bill1_idx` (`k_bill_bill_num` ASC),
  CONSTRAINT `fk_k_payment_k_bill1`
    FOREIGN KEY (`k_bill_bill_num`)
    REFERENCES `mydb`.`k_bill` (`bill_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`k_price`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_price` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_price` (
  `price_num` INT auto_increment,
  `price_name` VARCHAR(45) NOT NULL,
  `price_sum` VARCHAR(45) NULL,
  `price_type` VARCHAR(1) NULL,
  PRIMARY KEY (`price_num`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`k_protokol`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`k_protokol` ;

CREATE TABLE IF NOT EXISTS `mydb`.`k_protokol` (
  `kolvo` INT NULL,
  `price_sum` DECIMAL(9,2) NOT NULL,
  `k_bill_bill_num` INT NOT NULL,
  `k_price_price_num` INT NOT NULL,
  PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`),
  INDEX `fk_k_protokol_k_price1_idx` (`k_price_price_num` ASC),
  CONSTRAINT `fk_k_protokol_k_bill1`
    FOREIGN KEY (`k_bill_bill_num`)
    REFERENCES `mydb`.`k_bill` (`bill_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_k_protokol_k_price1`
    FOREIGN KEY (`k_price_price_num`)
    REFERENCES `mydb`.`k_price` (`price_num`)
    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;