· 5 years ago · May 01, 2020, 02:50 AM
1-- MySQL Workbench Synchronization
2-- Generated: 2020-04-30 16:31
3-- Model: New Model
4-- Version: 1.0
5-- Project: Name of the project
6-- Author: Валентин
7
8SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
9SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
10SET @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';
11
12CREATE SCHEMA IF NOT EXISTS `empl_service` DEFAULT CHARACTER SET utf8 ;
13
14CREATE TABLE IF NOT EXISTS `empl_service`.`unemployed` (
15 `id` INT(11) NOT NULL AUTO_INCREMENT,
16 `surname` VARCHAR(45) NOT NULL,
17 `name` VARCHAR(45) NOT NULL,
18 `patronymic` VARCHAR(45) NULL DEFAULT NULL,
19 `birth_date` DATE NOT NULL,
20 `phone` VARCHAR(45) NOT NULL,
21 `education` VARCHAR(45) NOT NULL,
22 `address` VARCHAR(45) NULL DEFAULT NULL,
23 `status` VARCHAR(45) NULL DEFAULT NULL,
24 `empl_book_id` VARCHAR(16) NULL DEFAULT NULL,
25 PRIMARY KEY (`id`),
26 INDEX `fk_unemployed_empl_book1_idx` (`empl_book_id` ASC) VISIBLE,
27 CONSTRAINT `fk_unemployed_empl_book1`
28 FOREIGN KEY (`empl_book_id`)
29 REFERENCES `empl_service`.`empl_book` (`empl_book_id`)
30 ON DELETE NO ACTION
31 ON UPDATE NO ACTION)
32ENGINE = InnoDB
33DEFAULT CHARACTER SET = utf8;
34
35CREATE TABLE IF NOT EXISTS `empl_service`.`request` (
36 `fk_unemployed_id` INT(11) NOT NULL,
37 `fk_profession_id` INT(11) NOT NULL,
38 `req_salary` INT(11) NULL DEFAULT NULL,
39 `empl_type` VARCHAR(45) NOT NULL,
40 `publ_date` DATE NOT NULL,
41 PRIMARY KEY (`fk_unemployed_id`, `fk_profession_id`),
42 INDEX `fk_request_profession1_idx` (`fk_profession_id` ASC) VISIBLE,
43 CONSTRAINT `fk_request_unemployed1`
44 FOREIGN KEY (`fk_unemployed_id`)
45 REFERENCES `empl_service`.`unemployed` (`id`)
46 ON DELETE NO ACTION
47 ON UPDATE NO ACTION,
48 CONSTRAINT `fk_request_profession1`
49 FOREIGN KEY (`fk_profession_id`)
50 REFERENCES `empl_service`.`profession` (`profession_id`)
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION)
53ENGINE = InnoDB
54DEFAULT CHARACTER SET = utf8;
55
56CREATE TABLE IF NOT EXISTS `empl_service`.`employer` (
57 `employer_id_INN` INT(10) ZEROFILL NOT NULL,
58 `employer_id_KPP` INT(9) ZEROFILL NULL DEFAULT NULL,
59 `name` VARCHAR(100) NOT NULL,
60 `type` ENUM("частный", "государственный") NOT NULL,
61 `OKVED` VARCHAR(100) NULL DEFAULT NULL,
62 `address` VARCHAR(100) NULL DEFAULT NULL,
63 `contact` VARCHAR(200) NOT NULL,
64 PRIMARY KEY (`employer_id_INN`))
65ENGINE = InnoDB
66DEFAULT CHARACTER SET = utf8;
67
68CREATE TABLE IF NOT EXISTS `empl_service`.`vacancy` (
69 `vacancy_id` INT(11) NOT NULL AUTO_INCREMENT,
70 `employer_id_INN` INT(10) ZEROFILL NOT NULL,
71 `profession_id` INT(11) NOT NULL,
72 `speciality_id` INT(11) NULL DEFAULT NULL,
73 `empl_type` VARCHAR(45) NOT NULL,
74 `salary` INT(11) NULL DEFAULT NULL,
75 `req_exp` INT(11) NULL DEFAULT NULL,
76 `benefits` VARCHAR(200) NULL DEFAULT NULL,
77 `publ_date` DATE NOT NULL,
78 PRIMARY KEY (`vacancy_id`),
79 INDEX `fk_vacancy_employer1_idx` (`employer_id_INN` ASC) VISIBLE,
80 INDEX `fk_vacancy_profession1_idx` (`profession_id` ASC) VISIBLE,
81 INDEX `fk_vacancy_speciality1_idx` (`speciality_id` ASC) VISIBLE,
82 CONSTRAINT `fk_vacancy_employer1`
83 FOREIGN KEY (`employer_id_INN`)
84 REFERENCES `empl_service`.`employer` (`employer_id_INN`)
85 ON DELETE NO ACTION
86 ON UPDATE NO ACTION,
87 CONSTRAINT `fk_vacancy_profession1`
88 FOREIGN KEY (`profession_id`)
89 REFERENCES `empl_service`.`profession` (`profession_id`)
90 ON DELETE NO ACTION
91 ON UPDATE NO ACTION,
92 CONSTRAINT `fk_vacancy_speciality1`
93 FOREIGN KEY (`speciality_id`)
94 REFERENCES `empl_service`.`speciality` (`speciality_id`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION)
97ENGINE = InnoDB
98DEFAULT CHARACTER SET = utf8;
99
100CREATE TABLE IF NOT EXISTS `empl_service`.`profession` (
101 `profession_id` INT(11) NOT NULL AUTO_INCREMENT,
102 `name` VARCHAR(100) NOT NULL,
103 PRIMARY KEY (`profession_id`))
104ENGINE = InnoDB
105DEFAULT CHARACTER SET = utf8;
106
107CREATE TABLE IF NOT EXISTS `empl_service`.`speciality` (
108 `speciality_id` INT(11) NOT NULL AUTO_INCREMENT,
109 `name` VARCHAR(45) NOT NULL,
110 PRIMARY KEY (`speciality_id`))
111ENGINE = InnoDB
112DEFAULT CHARACTER SET = utf8;
113
114CREATE TABLE IF NOT EXISTS `empl_service`.`skill` (
115 `skill_id` INT(11) NOT NULL AUTO_INCREMENT,
116 `fk_speciality_id` INT(11) NOT NULL,
117 `name` VARCHAR(200) NOT NULL,
118 INDEX `fk_skills_speciality1_idx` (`fk_speciality_id` ASC) VISIBLE,
119 PRIMARY KEY (`skill_id`),
120 CONSTRAINT `fk_skills_speciality1`
121 FOREIGN KEY (`fk_speciality_id`)
122 REFERENCES `empl_service`.`speciality` (`speciality_id`)
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION)
125ENGINE = InnoDB
126DEFAULT CHARACTER SET = utf8;
127
128CREATE TABLE IF NOT EXISTS `empl_service`.`record` (
129 `record_num` TINYINT(4) NOT NULL AUTO_INCREMENT,
130 `fk_empl_book_id` VARCHAR(16) NOT NULL,
131 `profession_id` INT(11) NOT NULL,
132 `empl_date` DATE NOT NULL,
133 `dism_date` DATE NULL DEFAULT NULL,
134 `dism_reason` VARCHAR(100) NULL DEFAULT NULL,
135 INDEX `fk_experience_profession1_idx` (`profession_id` ASC) VISIBLE,
136 PRIMARY KEY (`record_num`, `fk_empl_book_id`),
137 CONSTRAINT `fk_experience_profession1`
138 FOREIGN KEY (`profession_id`)
139 REFERENCES `empl_service`.`profession` (`profession_id`)
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION,
142 CONSTRAINT `fk_experience_empl_book1`
143 FOREIGN KEY (`fk_empl_book_id`)
144 REFERENCES `empl_service`.`empl_book` (`empl_book_id`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION)
147ENGINE = InnoDB
148DEFAULT CHARACTER SET = utf8;
149
150CREATE TABLE IF NOT EXISTS `empl_service`.`empl_book` (
151 `empl_book_id` VARCHAR(16) NOT NULL,
152 `profession_id` INT(11) NOT NULL,
153 `speciality_id` INT(11) NOT NULL,
154 PRIMARY KEY (`empl_book_id`),
155 INDEX `fk_empl_book_profession1_idx` (`profession_id` ASC) VISIBLE,
156 INDEX `fk_empl_book_speciality1_idx` (`speciality_id` ASC) VISIBLE,
157 CONSTRAINT `fk_empl_book_profession1`
158 FOREIGN KEY (`profession_id`)
159 REFERENCES `empl_service`.`profession` (`profession_id`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION,
162 CONSTRAINT `fk_empl_book_speciality1`
163 FOREIGN KEY (`speciality_id`)
164 REFERENCES `empl_service`.`speciality` (`speciality_id`)
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION)
167ENGINE = InnoDB
168DEFAULT CHARACTER SET = utf8;
169
170CREATE TABLE IF NOT EXISTS `empl_service`.`unemployed_has_skill` (
171 `fk_unemployed_id` INT(11) NOT NULL,
172 `fk_skill_id` INT(11) NOT NULL,
173 PRIMARY KEY (`fk_unemployed_id`, `fk_skill_id`),
174 INDEX `fk_unemployed_has_skill_skill1_idx` (`fk_skill_id` ASC) VISIBLE,
175 INDEX `fk_unemployed_has_skill_unemployed1_idx` (`fk_unemployed_id` ASC) VISIBLE,
176 CONSTRAINT `fk_unemployed_has_skill_unemployed1`
177 FOREIGN KEY (`fk_unemployed_id`)
178 REFERENCES `empl_service`.`unemployed` (`id`)
179 ON DELETE NO ACTION
180 ON UPDATE NO ACTION,
181 CONSTRAINT `fk_unemployed_has_skill_skill1`
182 FOREIGN KEY (`fk_skill_id`)
183 REFERENCES `empl_service`.`skill` (`skill_id`)
184 ON DELETE NO ACTION
185 ON UPDATE NO ACTION)
186ENGINE = InnoDB
187DEFAULT CHARACTER SET = utf8;
188
189
190SET SQL_MODE=@OLD_SQL_MODE;
191SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
192SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;