Создание таблиц - проект БД

gistfile1.txt

-- MySQL Script generated by MySQL Workbench
-- Thu Jun 12 17:21:40 2024
-- 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 project_bd
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `project_bd`.`position`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`position` (
  `id_position` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `group` ENUM('Младший начальствующий состав', 'Средний начальствующий состав', 'Старший начальствующий состав', 'Высший начальствующий состав', "Работники") NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE INDEX `id_position_UNIQUE` (`id_position` ASC) VISIBLE,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`rank`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`rank` (
  `initial_prepar_period` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`employee` (
  `id_employee` INT NOT NULL AUTO_INCREMENT,
  `last_name` VARCHAR(50) NOT NULL,
  `first_name` VARCHAR(50) NOT NULL,
  `middle_name` VARCHAR(50) NULL,
  `birthday` DATE NOT NULL,
  `identification` INT NOT NULL,
  `position` VARCHAR(100) NOT NULL,
  `rank` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_employee`, `rank`, `position`),
  UNIQUE INDEX `id_employee_UNIQUE` (`id_employee` ASC) VISIBLE,
  UNIQUE INDEX `identification_UNIQUE` (`identification` ASC) VISIBLE,
  INDEX `position_key_idx` (`position` ASC) VISIBLE,
  INDEX `rank_key_idx` (`rank` ASC) VISIBLE,
  CONSTRAINT `position_key`
    FOREIGN KEY (`position`)
    REFERENCES `project_bd`.`position` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `rank_key`
    FOREIGN KEY (`rank`)
    REFERENCES `project_bd`.`rank` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`attestation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`attestation` (
  `id_attestation` INT NOT NULL AUTO_INCREMENT,
  `status_to_work_SIZOD` ENUM('готов', 'не готов') NOT NULL,
  `no_SIZOD_reason` TEXT NULL,
  `date_of_test` DATE NOT NULL,
  `date_of_admis_order` DATE NULL,
  `num_of_admis_order` INT NULL,
  PRIMARY KEY (`id_attestation`),
  UNIQUE INDEX `id_attestation_UNIQUE` (`id_attestation` ASC) VISIBLE,
  UNIQUE INDEX `num_of_admis_order_UNIQUE` (`num_of_admis_order` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`type_of_exercise`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`type_of_exercise` (
  `name` VARCHAR(100) NOT NULL,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE,
  PRIMARY KEY (`name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`exercise`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`exercise` (
  `id_exercise` INT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(100) NOT NULL,
  `date` DATE NOT NULL,
  `address` VARCHAR(100) NOT NULL,
  `type_of_device` ENUM('ДАСВ', 'ДАСК') NOT NULL,
  PRIMARY KEY (`id_exercise`, `type`),
  UNIQUE INDEX `id_exercise_UNIQUE` (`id_exercise` ASC) VISIBLE,
  INDEX `type_key_idx` (`type` ASC) VISIBLE,
  CONSTRAINT `type_key`
    FOREIGN KEY (`type`)
    REFERENCES `project_bd`.`type_of_exercise` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`EmployeesAttes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`EmployeesAttes` (
  `employee_id` INT NOT NULL,
  `attestation_id` INT NOT NULL,
  INDEX `attes_key_idx` (`attestation_id` ASC) VISIBLE,
  CONSTRAINT `emp_key`
    FOREIGN KEY (`employee_id`)
    REFERENCES `project_bd`.`employee` (`id_employee`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `attes_key`
    FOREIGN KEY (`attestation_id`)
    REFERENCES `project_bd`.`attestation` (`id_attestation`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project_bd`.`EmployeesExercise`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_bd`.`EmployeesExercise` (
  `employee_ id` INT NOT NULL,
  `exercise_id` INT NOT NULL,
  INDEX `emp_id_idx` (`employee_ id` ASC) VISIBLE,
  INDEX `ex_key_idx` (`exercise_id` ASC) VISIBLE,
  CONSTRAINT `employee_key`
    FOREIGN KEY (`employee_ id`)
    REFERENCES `project_bd`.`employee` (`id_employee`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `ex_key`
    FOREIGN KEY (`exercise_id`)
    REFERENCES `project_bd`.`exercise` (`id_exercise`)
    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;