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

CREATE SCHEMA IF NOT EXISTS `test_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `test_db` ;


CREATE  TABLE IF NOT EXISTS `test_db`.`Departments` (
  `Primary_key` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NOT NULL ,
  `Sort` INT NOT NULL ,
  PRIMARY KEY (`Primary_key`) )
ENGINE = InnoDB;



CREATE  TABLE IF NOT EXISTS `test_db`.`Employee` (
  `Primary_key` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NOT NULL DEFAULT 'Default_Name' ,
  `Family` VARCHAR(45) NOT NULL DEFAULT 'Default_Family' ,
  `Age` INT NOT NULL DEFAULT 32 ,
  `Salary` VARCHAR(45) NOT NULL DEFAULT '50000' ,
  `Department_key` INT NOT NULL ,
  PRIMARY KEY (`Primary_key`) ,
  INDEX `FKDapartment_idx` (`Department_key` ASC) ,
  CONSTRAINT `FKDapartment`
    FOREIGN KEY (`Department_key` )
    REFERENCES `test_db`.`Departments` (`Primary_key` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE  TABLE IF NOT EXISTS `test_db`.`MyTable` (  `MyField` VARCHAR(45) NOT NULL )
ENGINE = InnoDB;

USE `test_db` ;


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


START TRANSACTION;
USE `test_db`;
INSERT INTO `test_db`.`Departments` (`Primary_key`, `Name`, `Sort`) VALUES (1, 'Accounting', 1);
INSERT INTO `test_db`.`Departments` (`Primary_key`, `Name`, `Sort`) VALUES (2, 'Marketing', 2);
INSERT INTO `test_db`.`Departments` (`Primary_key`, `Name`, `Sort`) VALUES (3, 'Production', 3);
INSERT INTO `test_db`.`Departments` (`Primary_key`, `Name`, `Sort`) VALUES (4, 'Sales', 4);
INSERT INTO `test_db`.`Departments` (`Primary_key`, `Name`, `Sort`) VALUES (5, 'Quality_control', 5);

COMMIT;


START TRANSACTION;
USE `test_db`;
INSERT INTO `test_db`.`Employee` (`Primary_key`, `Name`, `Family`, `Age`, `Salary`, `Department_key`) VALUES (1, 'Simon', 'Kelly', 32, '50000', 1);
INSERT INTO `test_db`.`Employee` (`Primary_key`, `Name`, `Family`, `Age`, `Salary`, `Department_key`) VALUES (2, 'Sally', 'Ocran', 35, '43000', 3);
INSERT INTO `test_db`.`Employee` (`Primary_key`, `Name`, `Family`, `Age`, `Salary`, `Department_key`) VALUES (3, 'Ofelia', 'Oreiro', 37, '42000', 5);
INSERT INTO `test_db`.`Employee` (`Primary_key`, `Name`, `Family`, `Age`, `Salary`, `Department_key`) VALUES (4, 'Ontario', 'Anapa', 40, '41000', 1);
INSERT INTO `test_db`.`Employee` (`Primary_key`, `Name`, `Family`, `Age`, `Salary`, `Department_key`) VALUES (5, 'Maxim', 'Petrunin', 41, '40000', 2);

COMMIT;


START TRANSACTION;
USE `test_db`;
INSERT INTO `test_db`.`MyTable` (`MyField`) VALUES ('Record1');
INSERT INTO `test_db`.`MyTable` (`MyField`) VALUES ('Record2');
INSERT INTO `test_db`.`MyTable` (`MyField`) VALUES ('Record3');
INSERT INTO `test_db`.`MyTable` (`MyField`) VALUES ('Record4');
INSERT INTO `test_db`.`MyTable` (`MyField`) VALUES ('Record5');

COMMIT;
