· 7 years ago · Jan 14, 2019, 09:04 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sun Jan 13 21:12:41 2019
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema mall
12-- -----------------------------------------------------
13CREATE SCHEMA IF NOT EXISTS `mall` DEFAULT CHARACTER SET utf8 ;
14USE `mall` ;
15
16-- -----------------------------------------------------
17-- Table `mall`.`category_tovarov`
18-- -----------------------------------------------------
19CREATE TABLE IF NOT EXISTS `mall`.`category_tovarov` (
20`idcategory_tovarov` INT(11) NOT NULL AUTO_INCREMENT,
21`name` VARCHAR(105) NOT NULL,
22PRIMARY KEY (`idcategory_tovarov`))
23ENGINE = InnoDB
24AUTO_INCREMENT = 84
25DEFAULT CHARACTER SET = utf8;
26
27-- -----------------------------------------------------
28-- Table `mall`.`type_company`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mall`.`type_company` (
31`idtype_company` INT(11) NOT NULL AUTO_INCREMENT,
32`name` VARCHAR(45) NOT NULL,
33PRIMARY KEY (`idtype_company`))
34ENGINE = InnoDB
35AUTO_INCREMENT = 5
36DEFAULT CHARACTER SET = utf8;
37-- -----------------------------------------------------
38-- Table `mall`.`arendator`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `mall`.`arendator` (
41`idarendator` INT(11) NOT NULL AUTO_INCREMENT,
42`name_company` VARCHAR(45) NOT NULL,
43`idtype_company` INT(11) NOT NULL,
44`category_tovarov` INT(11) NOT NULL,
45`phone` VARCHAR(13) NOT NULL,
46`FIO` VARCHAR(150) NOT NULL,
47`adres` VARCHAR(150) NOT NULL,
48PRIMARY KEY (`idarendator`),
49INDEX `tc_fk_idx` (`idtype_company` ASC) VISIBLE,
50INDEX `tc_fk_idx1` (`category_tovarov` ASC) VISIBLE,
51CONSTRAINT `ct_fk`
52FOREIGN KEY (`category_tovarov`)
53REFERENCES `mall`.`category_tovarov` (`idcategory_tovarov`),
54CONSTRAINT `tc_fk`
55FOREIGN KEY (`idtype_company`)
56REFERENCES `mall`.`type_company` (`idtype_company`))
57ENGINE = InnoDB
58AUTO_INCREMENT = 174
59DEFAULT CHARACTER SET = utf8;
60
61-- -----------------------------------------------------
62-- Table `mall`.`dogovor`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `mall`.`dogovor` (
65`iddogovor` INT(11) NOT NULL AUTO_INCREMENT,
66`idt_tochki` INT(11) NOT NULL,
67`idarendator` INT(11) NOT NULL,
68`tsena_arendy_kv_m` FLOAT NOT NULL,
69`data_nachala` DATE NOT NULL,
70`data_okonchaniya` DATE NOT NULL,
71PRIMARY KEY (`iddogovor`),
72INDEX `idarenda_idx` (`idarendator` ASC) VISIBLE,
73CONSTRAINT `idarenda`
74FOREIGN KEY (`idarendator`)
75REFERENCES `mall`.`arendator` (`idarendator`)
76ON DELETE CASCADE
77ON UPDATE CASCADE)
78ENGINE = InnoDB
79AUTO_INCREMENT = 185
80DEFAULT CHARACTER SET = utf8;
81
82-- -----------------------------------------------------
83-- Table `mall`.`status`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `mall`.`status` (
86`idstatus` INT(11) NOT NULL AUTO_INCREMENT,
87`name` VARCHAR(45) NOT NULL,
88PRIMARY KEY (`idstatus`))
89ENGINE = InnoDB
90AUTO_INCREMENT = 4
91DEFAULT CHARACTER SET = utf8;
92
93-- -----------------------------------------------------
94-- Table `mall`.`type`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `mall`.`type` (
97`idtype` INT(11) NOT NULL AUTO_INCREMENT,
98`name` VARCHAR(45) NOT NULL,
99PRIMARY KEY (`idtype`))
100ENGINE = InnoDB
101AUTO_INCREMENT = 3
102DEFAULT CHARACTER SET = utf8;
103
104-- -----------------------------------------------------
105-- Table `mall`.`t_tochka`
106-- -----------------------------------------------------
107CREATE TABLE IF NOT EXISTS `mall`.`t_tochka` (
108`idt_tochki` INT(11) NOT NULL AUTO_INCREMENT,
109`etazh` INT(11) NOT NULL,
110`ploshchad` FLOAT NOT NULL,
111`konditsioner` TINYINT(4) NULL DEFAULT NULL,
112`idtype` INT(11) NULL DEFAULT NULL,
113`idstatus` INT(11) NULL DEFAULT NULL,
114PRIMARY KEY (`idt_tochki`),
115INDEX `idstatus_idx` (`idstatus` ASC) VISIBLE,
116INDEX `idtype_idx` (`idtype` ASC) VISIBLE,
117CONSTRAINT `idstatus`
118FOREIGN KEY (`idstatus`)
119REFERENCES `mall`.`status` (`idstatus`)
120ON DELETE CASCADE
121ON UPDATE CASCADE,
122CONSTRAINT `idtype`
123FOREIGN KEY (`idtype`)
124REFERENCES `mall`.`type` (`idtype`)
125ON DELETE CASCADE
126ON UPDATE CASCADE)
127ENGINE = InnoDB
128AUTO_INCREMENT = 199
129DEFAULT CHARACTER SET = utf8;
130
131-- -----------------------------------------------------
132-- Table `mall`.`magazine`
133-- -----------------------------------------------------
134CREATE TABLE IF NOT EXISTS `mall`.`magazine` (
135`idmagazine` INT(11) NOT NULL AUTO_INCREMENT,
136`idt_tochki` INT(11) NOT NULL,
137`idcategory_tovarov` INT(11) NOT NULL,
138`name` VARCHAR(20) NOT NULL,
139`work_time` VARCHAR(26) NOT NULL,
140`site` VARCHAR(150) NULL DEFAULT NULL,
141PRIMARY KEY (`idmagazine`),
142INDEX `magaz_fk_t_tchk_idx` (`idt_tochki` ASC) VISIBLE,
143INDEX `idcategory_tovarov_idx` (`idcategory_tovarov` ASC) VISIBLE,
144CONSTRAINT `idcategory_tovarov`
145FOREIGN KEY (`idcategory_tovarov`)
146REFERENCES `mall`.`category_tovarov` (`idcategory_tovarov`),
147CONSTRAINT `magaz_fk_t_tchk`
148FOREIGN KEY (`idt_tochki`)
149REFERENCES `mall`.`t_tochka` (`idt_tochki`)
150ON DELETE CASCADE
151ON UPDATE CASCADE)
152ENGINE = InnoDB
153AUTO_INCREMENT = 173
154DEFAULT CHARACTER SET = utf8;
155
156USE `mall` ;
157
158-- -----------------------------------------------------
159-- Placeholder table for view `mall`.`stoimost`
160-- -----------------------------------------------------
161CREATE TABLE IF NOT EXISTS `mall`.`stoimost` (`idt_tochki` INT, `stoimost` INT);
162
163-- -----------------------------------------------------
164-- View `mall`.`stoimost`
165-- -----------------------------------------------------
166DROP TABLE IF EXISTS `mall`.`stoimost`;
167USE `mall`;
168CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mall`.`stoimost` AS select `mall`.`t_tochka`.`idt_tochki` AS `idt_tochki`,(`mall`.`t_tochka`.`ploshchad` * `mall`.`dogovor`.`tsena_arendy_kv_m`) AS `stoimost` from (`mall`.`t_tochka` join `mall`.`dogovor`) where (`mall`.`t_tochka`.`idt_tochki` = `mall`.`dogovor`.`idt_tochki`) order by `mall`.`t_tochka`.`idt_tochki`;
169USE `mall`;
170
171DELIMITER $$
172USE `mall`$$
173CREATE
174DEFINER=`root`@`localhost`
175TRIGGER `mall`.`t_tochki_BEFORE_INSERT`
176BEFORE INSERT ON `mall`.`t_tochka`
177FOR EACH ROW
178BEGIN
179IF NEW.etazh > 3 then
180SIGNAL SQLSTATE '45000'
181SET MESSAGE_TEXT = 'wrong etazh';
182end if;
183IF NEW.ploshchad <2 and NEW.ploshchad >500 then
184SIGNAL SQLSTATE '45000'
185SET MESSAGE_TEXT = 'wrong ploshchad';
186end if;
187END$$
188
189DELIMITER ;
190
191SET SQL_MODE=@OLD_SQL_MODE;
192SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
193SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;