Дипломная работа: Автоматизация процесса внутрикорпоративного взаимодействия сотрудников компании ООО "Ситилинк"
Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам
68
Рис. 2.5 Физическая структура базы данных в MySQL
69
Переведем данную схему в формат базы данных MySQL.
-- -----------------------------------------------------
-- Schema citilink
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `citilink` ;
-- -----------------------------------------------------
-- Schema citilink
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `citilink` DEFAULT CHARACTER SET
cp1251 ;
USE `citilink` ;
-- -----------------------------------------------------
-- Table `citilink`.`dostup`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `citilink`.`dostup` ;
CREATE TABLE IF NOT EXISTS `citilink`.`dostup` (
`id_dostup` INT NOT NULL AUTO_INCREMENT,
`name_dostup` VARCHAR(45) NULL,
PRIMARY KEY (`id_dostup`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `citilink`.`login_password`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `citilink`.`login_password` ;
CREATE TABLE IF NOT EXISTS `citilink`.`login_password` (
`login` VARCHAR(45) NOT NULL,
`password_user` VARCHAR(45) NULL,
PRIMARY KEY (`login`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `citilink`.`dolgnost`
-- -----------------------------------------------------
70
DROP TABLE IF EXISTS `citilink`.`dolgnost` ;
CREATE TABLE IF NOT EXISTS `citilink`.`dolgnost` (
`id_dolgnost` INT NOT NULL AUTO_INCREMENT,
`name_dolgnost` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_dolgnost`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `citilink`.`sotrudnik`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `citilink`.`sotrudnik` ;
CREATE TABLE IF NOT EXISTS `citilink`.`sotrudnik` (
`tabel_nomer` VARCHAR(8) NOT NULL,
`family` VARCHAR(45) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`second_name` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NOT NULL,
`login` VARCHAR(45) NULL,
`dostup` INT NULL,
`id_dolgnost` INT NULL,
PRIMARY KEY (`tabel_nomer`),
INDEX `1_idx` (`dostup` ASC) VISIBLE,
INDEX `2_idx` (`login` ASC) VISIBLE,
INDEX `15_idx` (`id_dolgnost` ASC) VISIBLE,
CONSTRAINT `1`
FOREIGN KEY (`dostup`)
REFERENCES `citilink`.`dostup` (`id_dostup`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `2`
FOREIGN KEY (`login`)
REFERENCES `citilink`.`login_password` (`login`)
ON DELETE CASCADE
71
ON UPDATE CASCADE,
CONSTRAINT `15`
FOREIGN KEY (`id_dolgnost`)
REFERENCES `citilink`.`dolgnost` (`id_dolgnost`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `citilink`.`proekt`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `citilink`.`proekt` ;
CREATE TABLE IF NOT EXISTS `citilink`.`proekt` (
`id_proekt` INT NOT NULL AUTO_INCREMENT,
`name_proekt` VARCHAR(250) NOT NULL,
`sotrudnik_creat` VARCHAR(8) NULL,
`date_creat` DATETIME NOT NULL,
`status` TINYINT NULL COMMENT '0 - в работе False\n1 - выполнен True',
PRIMARY KEY (`id_proekt`),
UNIQUE INDEX `name_proekt_UNIQUE` (`name_proekt` ASC) VISIBLE,
INDEX `13_idx` (`sotrudnik_creat` ASC) VISIBLE,
CONSTRAINT `13`
FOREIGN KEY (`sotrudnik_creat`)
REFERENCES `citilink`.`sotrudnik` (`tabel_nomer`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `citilink`.`pod_proekt`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `citilink`.`pod_proekt` ;
CREATE TABLE IF NOT EXISTS `citilink`.`pod_proekt` (
`id_proekt` INT NOT NULL,
72
`id_pod_proekt` INT NOT NULL AUTO_INCREMENT,
`name_pod_proekt` VARCHAR(250) NOT NULL,
`date_begin` DATETIME NULL,
`date_end` DATETIME NULL,
`status` TINYINT NULL COMMENT '0 - в работе False\n1 - выполнен True',
PRIMARY KEY (`id_pod_proekt`),
INDEX `1_idx` (`id_proekt` ASC) VISIBLE,
CONSTRAINT `14`
FOREIGN KEY (`id_proekt`)
REFERENCES `citilink`.`proekt` (`id_proekt`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `citilink`.`document`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `citilink`.`document` ;
CREATE TABLE IF NOT EXISTS `citilink`.`document` (
`name_file` VARCHAR(60) NOT NULL,
`id_pod_proekt` INT NOT NULL,
`avtor` VARCHAR(8) NULL,
`date_add` DATETIME NULL,
PRIMARY KEY (`name_file`, `id_pod_proekt`),
INDEX `3_idx` (`avtor` ASC) VISIBLE,
INDEX `47_idx` (`id_pod_proekt` ASC) VISIBLE,
CONSTRAINT `3`
FOREIGN KEY (`avtor`)
REFERENCES `citilink`.`sotrudnik` (`tabel_nomer`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `47`
FOREIGN KEY (`id_pod_proekt`)
Источник: https://baza.diplomsite.ru/previewfile/2079