Дипломная работа: Автоматизация учета предоставленных услуг в комплексном центре социального обслуживания населения и анализа данных на примере СПБ ГБУСОН "КЦСОН Адмиралтейского района Санкт-Петербурга"

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам
106
ПРИЛОЖЕНИЕ А ФРАГМЕНТ ТЕКСТА SQL (DDL) КОДА
СОЗДАНИЯ БАЗЫ ДАННЫХ MYSQL
-- 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_B
Y_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema kcson
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `kcson` ;
-- -----------------------------------------------------
-- Schema kcson
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `kcson` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
SHOW WARNINGS;
USE `kcson` ;
-- -----------------------------------------------------
-- Table `kcson`.`dep`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`dep` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dep` VARCHAR(45) NOT NULL,
`depFullName` VARCHAR(255) NULL,
`note` VARCHAR(255) NULL DEFAULT NULL,
`archive` TINYINT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE INDEX `depName_UNIQUE` (`dep` ASC, `id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Departments';
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`serv`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`serv` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`serv` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`tnum` VARCHAR(10) NOT NULL COMMENT '  ',
`serv` VARCHAR(1000) NULL DEFAULT NULL,
`year` YEAR(4) NOT NULL,
`sub_serv` INT NULL DEFAULT NULL,
`sub_serv_str` VARCHAR(10) NULL DEFAULT NULL,
`price` DECIMAL(10,2) NULL,
`price2` DECIMAL(10,2) NULL,
`price3` DECIMAL(10,2) NULL,
`archive` TINYINT NULL DEFAULT 0,
`total` TINYINT NULL DEFAULT 0,
`acronym` VARCHAR(45) NULL,
`workload` INT NULL,
`content` VARCHAR(1000) NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `serv_num_UNIQUE` (`tnum` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
107
COLLATE = utf8mb4_0900_ai_ci;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`ufio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`ufio` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`ufio` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ufio` VARCHAR(255) NOT NULL,
`ufio_short` VARCHAR(150) NULL,
`ufioDeath` DATE NULL DEFAULT NULL,
`ufiobirth` DATE NULL,
`ESRN` BIGINT NULL,
`prim` VARCHAR(255) NULL,
`phone` VARCHAR(45) NULL,
`snils` VARCHAR(14) NULL,
`curator` VARCHAR(45) NULL,
`create` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`cr_by` INT NULL,
`upd_by` INT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ufio_UNIQUE` (`ufio` ASC) VISIBLE)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`servform`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`servform` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`servform` (
`id` INT NOT NULL AUTO_INCREMENT,
`servform` VARCHAR(255) NULL,
`prim` VARCHAR(255) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`ripso`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`ripso` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`ripso` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ripso` VARCHAR(20) NOT NULL,
`year` YEAR(4) NOT NULL,
`archive` TINYINT NULL DEFAULT 0,
`servform_id` INT NOT NULL,
`months` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_ripso_servform1_idx` (`servform_id` ASC) VISIBLE,
CONSTRAINT `fk_ripso_servform1`
FOREIGN KEY (`servform_id`)
REFERENCES `kcson`.`servform` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`contracts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`contracts` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`contracts` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
108
`contracts` VARCHAR(45) NULL,
`ufio_id` INT UNSIGNED NOT NULL,
`dep_id` INT UNSIGNED NOT NULL,
`ripso_id` INT UNSIGNED NULL,
`blocked` TINYINT NULL DEFAULT 0,
`startdate` DATE NOT NULL COMMENT '  ',
`enddate` DATE NOT NULL,
`ippsuNum` INT(12) NULL,
`prim` VARCHAR(255) NULL,
`create` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`cr_by` INT NULL,
`upd_by` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_contract_ufio1_idx` (`ufio_id` ASC) VISIBLE,
INDEX `fk_contracts_ripso1_idx` (`ripso_id` ASC) VISIBLE,
INDEX `fk_contracts_dep1_idx` (`dep_id` ASC) VISIBLE,
CONSTRAINT `fk_contract_ufio1`
FOREIGN KEY (`ufio_id`)
REFERENCES `kcson`.`ufio` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_contracts_ripso1`
FOREIGN KEY (`ripso_id`)
REFERENCES `kcson`.`ripso` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_contracts_dep1`
FOREIGN KEY (`dep_id`)
REFERENCES `kcson`.`dep` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`roles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`roles` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`roles` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`role` VARCHAR(45) NULL,
`prim` VARCHAR(255) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`worker`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`worker` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`worker` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`worker` VARCHAR(255) NOT NULL,
`user` VARCHAR(32) NULL COMMENT 'user login',
`prim` VARCHAR(145) NULL,
`roles_id` INT UNSIGNED NOT NULL DEFAULT 1,
`dep_id` INT UNSIGNED NOT NULL DEFAULT 1,
`archive` TINYINT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE INDEX `user_UNIQUE` (`user` ASC) VISIBLE,
INDEX `fk_worker_roles1_idx` (`roles_id` ASC) VISIBLE,
INDEX `fk_worker_dep1_idx` (`dep_id` ASC) VISIBLE,
CONSTRAINT `fk_worker_roles1`
FOREIGN KEY (`roles_id`)
REFERENCES `kcson`.`roles` (`id`)
109
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_worker_dep1`
FOREIGN KEY (`dep_id`)
REFERENCES `kcson`.`dep` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`main`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`main` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`main` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`contracts_id` INT UNSIGNED NOT NULL,
`dep_id` INT UNSIGNED NOT NULL,
`ufio_id` INT UNSIGNED NOT NULL,
`serv_id` INT UNSIGNED NOT NULL,
`worker_id` INT UNSIGNED NOT NULL,
`vdate` DATE NOT NULL COMMENT '  ',
`uslnum` INT(4) NULL DEFAULT 0 COMMENT ' ',
`note` VARCHAR(255) NULL,
`create` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`ts` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`cr_by` INT NULL,
`upd_by` INT NULL,
`reported` TINYINT NULL DEFAULT 0,
`wdate` DATETIME NULL DEFAULT NULL COMMENT '  ',
`overdid` TINYINT NULL DEFAULT 0,
PRIMARY KEY (`id`),
INDEX `fk_main_serv1_idx` (`serv_id` ASC) VISIBLE,
INDEX `fk_main_dep1_idx` (`dep_id` ASC) VISIBLE,
INDEX `fk_main_ufio1_idx` (`ufio_id` ASC) VISIBLE,
INDEX `fk_main_contracts1_idx` (`contracts_id` ASC) VISIBLE,
INDEX `fk_main_worker1_idx` (`worker_id` ASC) VISIBLE,
CONSTRAINT `fk_main_serv1`
FOREIGN KEY (`serv_id`)
REFERENCES `kcson`.`serv` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_main_dep1`
FOREIGN KEY (`dep_id`)
REFERENCES `kcson`.`dep` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_main_ufio1`
FOREIGN KEY (`ufio_id`)
REFERENCES `kcson`.`ufio` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_main_contracts1`
FOREIGN KEY (`contracts_id`)
REFERENCES `kcson`.`contracts` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_main_worker1`
FOREIGN KEY (`worker_id`)
REFERENCES `kcson`.`worker` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'main table';
110
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`holiday`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`holiday` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`holiday` (
`holiday` DATE NOT NULL,
`prim` VARCHAR(255) NULL,
PRIMARY KEY (`holiday`))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`category` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`category` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`category` VARCHAR(45) NOT NULL,
`archive` TINYINT NULL DEFAULT 0,
`prim` VARCHAR(255) NULL,
`total` TINYINT NULL DEFAULT 0,
`subof` INT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `category_UNIQUE` (`category` ASC) VISIBLE);
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`tables_checksums`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`tables_checksums` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`tables_checksums` (
`id` INT NOT NULL AUTO_INCREMENT,
`table` VARCHAR(45) NOT NULL,
`checksum` VARCHAR(45) NULL,
`lastid` INT NULL,
PRIMARY KEY (`id`, `table`))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`job`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`job` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`job` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`job` VARCHAR(255) NULL,
`prim` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `kcson`.`dep_has_worker`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_has_worker` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `kcson`.`dep_has_worker` (
`id` INT NOT NULL AUTO_INCREMENT,
`worker_id` INT UNSIGNED NOT NULL,
`dep_id` INT UNSIGNED NOT NULL,
`job_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(45) NULL,
`prim` VARCHAR(145) NULL,
`archive` TINYINT NULL DEFAULT 0,
`from` DATETIME NULL DEFAULT current_TIMESTAMP,
Источник: https://baza.diplomsite.ru/previewfile/2412

Смотрите также:

_1966(~1
_Антонян Ю.М., Сексуальные преступления. Чикатило и другие
_РГР № 1 (2)
- Габариты и масса 27
'Образ–Я' и его развитие средствами диалога
"Логистическая технология JIT"
"Особенности правовой системы Российской Федерации" по дисциплине "Теория государства и права"
«Анализ ликвидности и платежеспособности предприятия» (на материалах ОАО Московская кондитерская фабрика «Красный Октябрь»)