GNZQniMV

1-- MySQL Script generated by MySQL Workbench
2-- Вс 20 янв 2019 11:32:57
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema campus
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema campus
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `campus` DEFAULT CHARACTER SET utf8 ;
18SHOW WARNINGS;
19USE `campus` ;
20
21-- -----------------------------------------------------
22-- Table `campus`.`Services`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `campus`.`Services` (
25  `idService` INT NOT NULL AUTO_INCREMENT,
26  `name` VARCHAR(45) NOT NULL,
27  `description` VARCHAR(45) NOT NULL,
28  `timetable` VARCHAR(45) NOT NULL,
29  PRIMARY KEY (`idService`))
30ENGINE = InnoDB;
31
32SHOW WARNINGS;
33
34-- -----------------------------------------------------
35-- Table `campus`.`State`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `campus`.`State` (
38  `idState` INT NOT NULL AUTO_INCREMENT,
39  `description` VARCHAR(45) NOT NULL,
40  `type` INT NOT NULL,
41  PRIMARY KEY (`idState`))
42ENGINE = InnoDB;
43
44SHOW WARNINGS;
45
46-- -----------------------------------------------------
47-- Table `campus`.`Role`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `campus`.`Role` (
50  `idRole` INT NOT NULL AUTO_INCREMENT,
51  `name` VARCHAR(45) NOT NULL,
52  PRIMARY KEY (`idRole`),
53  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
54ENGINE = InnoDB;
55
56SHOW WARNINGS;
57
58-- -----------------------------------------------------
59-- Table `campus`.`Users`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `campus`.`Users` (
62  `idUser` INT NOT NULL AUTO_INCREMENT,
63  `username` VARCHAR(45) NOT NULL,
64  `first_name` VARCHAR(45) NOT NULL,
65  `second_name` VARCHAR(45) NOT NULL,
66  `campus` INT NULL,
67  `room` INT NULL,
68  `Role_idRole` INT NOT NULL,
69  `e_mail` VARCHAR(45) NOT NULL,
70  `create_time` VARCHAR(45) NULL,
71  `auth` INT NOT NULL DEFAULT 0,
72  `last_time` DATETIME NOT NULL DEFAULT '2019-01-01 00:00:00',
73  `must_logout` INT NOT NULL DEFAULT 0,
74  `last_activity` INT NOT NULL DEFAULT '2019-01-01 00:00:00',
75  PRIMARY KEY (`idUser`, `username`),
76  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
77  UNIQUE INDEX `idUser_UNIQUE` (`idUser` ASC),
78  CONSTRAINT `fk_User_Role1`
79    FOREIGN KEY (`Role_idRole`)
80    REFERENCES `campus`.`Role` (`idRole`)
81    ON DELETE NO ACTION
82    ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85SHOW WARNINGS;
86
87-- -----------------------------------------------------
88-- Table `campus`.`Tickets`
89-- -----------------------------------------------------
90CREATE TABLE IF NOT EXISTS `campus`.`Tickets` (
91  `idTicket` INT NOT NULL AUTO_INCREMENT,
92  `Service_idService` INT NOT NULL,
93  `State_idState` INT NOT NULL,
94  `Users_idUser` INT NOT NULL,
95  `description` VARCHAR(255) NOT NULL,
96  `idExecutor` INT NULL,
97  `result_description` VARCHAR(255) NULL,
98  `responce` VARCHAR(255) NULL,
99  `mark` INT NULL,
100  PRIMARY KEY (`idTicket`, `Service_idService`, `Users_idUser`),
101  CONSTRAINT `fk_Tiket_Service`
102    FOREIGN KEY (`Service_idService`)
103    REFERENCES `campus`.`Services` (`idService`)
104    ON DELETE NO ACTION
105    ON UPDATE NO ACTION,
106  CONSTRAINT `fk_Tiket_State1`
107    FOREIGN KEY (`State_idState`)
108    REFERENCES `campus`.`State` (`idState`)
109    ON DELETE NO ACTION
110    ON UPDATE NO ACTION,
111  CONSTRAINT `fk_Ticket_User1`
112    FOREIGN KEY (`Users_idUser`)
113    REFERENCES `campus`.`Users` (`idUser`)
114    ON DELETE NO ACTION
115    ON UPDATE NO ACTION)
116ENGINE = InnoDB;
117
118SHOW WARNINGS;
119
120-- -----------------------------------------------------
121-- Table `campus`.`Model`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `campus`.`Model` (
124  `idModel` INT NOT NULL AUTO_INCREMENT,
125  `name` VARCHAR(45) NOT NULL,
126  PRIMARY KEY (`idModel`),
127  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
128ENGINE = InnoDB;
129
130SHOW WARNINGS;
131
132-- -----------------------------------------------------
133-- Table `campus`.`Mashines`
134-- -----------------------------------------------------
135CREATE TABLE IF NOT EXISTS `campus`.`Mashines` (
136  `idMashine` INT NOT NULL AUTO_INCREMENT,
137  `Model_idModel` INT NOT NULL,
138  `serial_number` VARCHAR(45) NOT NULL,
139  `number` INT NOT NULL,
140  `campus` INT NULL,
141  `State_idState` INT NOT NULL,
142  `time_begin` VARCHAR(45) NULL,
143  PRIMARY KEY (`idMashine`),
144  UNIQUE INDEX `serial_number_UNIQUE` (`serial_number` ASC),
145  CONSTRAINT `fk_Mashine_Model1`
146    FOREIGN KEY (`Model_idModel`)
147    REFERENCES `campus`.`Model` (`idModel`)
148    ON DELETE NO ACTION
149    ON UPDATE NO ACTION,
150  CONSTRAINT `fk_Mashine_State1`
151    FOREIGN KEY (`State_idState`)
152    REFERENCES `campus`.`State` (`idState`)
153    ON DELETE NO ACTION
154    ON UPDATE NO ACTION)
155ENGINE = InnoDB;
156
157SHOW WARNINGS;
158
159-- -----------------------------------------------------
160-- Table `campus`.`Mode`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `campus`.`Mode` (
163  `idMode` INT NOT NULL AUTO_INCREMENT,
164  `name` VARCHAR(45) NOT NULL,
165  `duration` INT NOT NULL,
166  PRIMARY KEY (`idMode`),
167  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
168ENGINE = InnoDB;
169
170SHOW WARNINGS;
171
172-- -----------------------------------------------------
173-- Table `campus`.`Positions`
174-- -----------------------------------------------------
175CREATE TABLE IF NOT EXISTS `campus`.`Positions` (
176  `idPosition` INT NOT NULL AUTO_INCREMENT,
177  `Mashines_idMashine` INT NOT NULL,
178  `State_idState` INT NOT NULL,
179  `Mode_idMode` INT NOT NULL,
180  `User_idUser` INT NOT NULL,
181  PRIMARY KEY (`idPosition`, `Mashines_idMashine`, `State_idState`, `Mode_idMode`, `User_idUser`),
182  CONSTRAINT `fk_Position_Mashine1`
183    FOREIGN KEY (`Mashines_idMashine`)
184    REFERENCES `campus`.`Mashines` (`idMashine`)
185    ON DELETE NO ACTION
186    ON UPDATE NO ACTION,
187  CONSTRAINT `fk_Position_State1`
188    FOREIGN KEY (`State_idState`)
189    REFERENCES `campus`.`State` (`idState`)
190    ON DELETE NO ACTION
191    ON UPDATE NO ACTION,
192  CONSTRAINT `fk_Position_Mode1`
193    FOREIGN KEY (`Mode_idMode`)
194    REFERENCES `campus`.`Mode` (`idMode`)
195    ON DELETE NO ACTION
196    ON UPDATE NO ACTION,
197  CONSTRAINT `fk_Position_User1`
198    FOREIGN KEY (`User_idUser`)
199    REFERENCES `campus`.`Users` (`idUser`)
200    ON DELETE NO ACTION
201    ON UPDATE NO ACTION)
202ENGINE = InnoDB;
203
204SHOW WARNINGS;
205
206-- -----------------------------------------------------
207-- Table `campus`.`Mashine_has_Mode`
208-- -----------------------------------------------------
209CREATE TABLE IF NOT EXISTS `campus`.`Mashine_has_Mode` (
210  `Mashine_idMashine` INT NOT NULL AUTO_INCREMENT,
211  `Mode_idMode` INT NOT NULL,
212  PRIMARY KEY (`Mashine_idMashine`, `Mode_idMode`),
213  CONSTRAINT `fk_Mashine_has_Mode_Mashine1`
214    FOREIGN KEY (`Mashine_idMashine`)
215    REFERENCES `campus`.`Mashines` (`idMashine`)
216    ON DELETE NO ACTION
217    ON UPDATE NO ACTION,
218  CONSTRAINT `fk_Mashine_has_Mode_Mode1`
219    FOREIGN KEY (`Mode_idMode`)
220    REFERENCES `campus`.`Mode` (`idMode`)
221    ON DELETE NO ACTION
222    ON UPDATE NO ACTION)
223ENGINE = InnoDB;
224
225SHOW WARNINGS;
226
227SET SQL_MODE=@OLD_SQL_MODE;
228SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
229SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;