xP4saEWV

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5-- -----------------------------------------------------
6-- Schema mydb
7-- -----------------------------------------------------
8DROP SCHEMA IF EXISTS `mydb` ;
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
14USE `mydb` ;
15
16-- -----------------------------------------------------
17-- Table `mydb`.`k_staff`
18-- -----------------------------------------------------
19DROP TABLE IF EXISTS `mydb`.`k_staff` ;
20
21CREATE TABLE IF NOT EXISTS `mydb`.`k_staff` (
22  `staff_num` INT auto_increment,
23  `staff_name` VARCHAR(45) NOT NULL,
24  `staff_post` VARCHAR(45) NULL,
25  `staff_hiredate` DATE NULL,
26  `staff_termdate` DATE NULL,
27  `K_dept_dept_num` INT,
28  PRIMARY KEY (`staff_num`),
29  INDEX `fk_k_staff_K_dept1_idx` (`K_dept_dept_num` ASC),
30  CONSTRAINT `fk_k_staff_K_dept1`
31    FOREIGN KEY (`K_dept_dept_num`)
32    REFERENCES `mydb`.`K_dept` (`dept_num`)
33    ON DELETE NO ACTION
34    ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `mydb`.`K_dept`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `mydb`.`K_dept` ;
42
43CREATE TABLE IF NOT EXISTS `mydb`.`K_dept` (
44  `dept_num` INT auto_increment,
45  `dept_full_name` VARCHAR(45) NULL,
46  `dept_short_name` VARCHAR(10) NULL,
47  `k_staff_staff_num` INT,
48  PRIMARY KEY (`dept_num`),
49  INDEX `fk_K_dept_k_staff_idx` (`k_staff_staff_num` ASC),
50  CONSTRAINT `fk_K_dept_k_staff`
51    FOREIGN KEY (`k_staff_staff_num`)
52    REFERENCES `mydb`.`k_staff` (`staff_num`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `mydb`.`k_firm`
60-- -----------------------------------------------------
61DROP TABLE IF EXISTS `mydb`.`k_firm` ;
62
63CREATE TABLE IF NOT EXISTS `mydb`.`k_firm` (
64  `firm_num` INT auto_increment,
65  `firm_name` VARCHAR(45) NOT NULL,
66  `firm_addr` VARCHAR(45) NULL,
67  `firm_phone` VARCHAR(20) NULL,
68  PRIMARY KEY (`firm_num`))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `mydb`.`k_contract`
74-- -----------------------------------------------------
75DROP TABLE IF EXISTS `mydb`.`k_contract` ;
76
77CREATE TABLE IF NOT EXISTS `mydb`.`k_contract` (
78  `contract_num` INT auto_increment,
79  `contract_date` DATE NULL,
80  `contract_type` ENUM('A', 'B', 'C') NULL,
81  `k_firm_firm_num` INT NOT NULL,
82  `k_staff_staff_num` INT NOT NULL,
83  PRIMARY KEY (`contract_num`),
84  INDEX `fk_k_contract_k_firm1_idx` (`k_firm_firm_num` ASC),
85  INDEX `fk_k_contract_k_staff1_idx` (`k_staff_staff_num` ASC),
86  CONSTRAINT `fk_k_contract_k_firm1`
87    FOREIGN KEY (`k_firm_firm_num`)
88    REFERENCES `mydb`.`k_firm` (`firm_num`)
89    ON DELETE NO ACTION
90    ON UPDATE NO ACTION,
91  CONSTRAINT `fk_k_contract_k_staff1`
92    FOREIGN KEY (`k_staff_staff_num`)
93    REFERENCES `mydb`.`k_staff` (`staff_num`)
94    ON DELETE NO ACTION
95    ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `mydb`.`k_bill`
101-- -----------------------------------------------------
102DROP TABLE IF EXISTS `mydb`.`k_bill` ;
103
104CREATE TABLE IF NOT EXISTS `mydb`.`k_bill` (
105  `bill_num` INT auto_increment,
106  `bill_date` DATE NULL,
107  `bill_sum` DECIMAL(9,2) NULL,
108  `bill_term` DATE NULL,
109  `bill_peni` DECIMAL(6,2) NULL,
110  `k_contract_contract_num` INT NOT NULL,
111  PRIMARY KEY (`bill_num`),
112  INDEX `fk_k_bill_k_contract1_idx` (`k_contract_contract_num` ASC),
113  CONSTRAINT `fk_k_bill_k_contract1`
114    FOREIGN KEY (`k_contract_contract_num`)
115    REFERENCES `mydb`.`k_contract` (`contract_num`)
116    ON DELETE NO ACTION
117    ON UPDATE NO ACTION)
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `mydb`.`k_payment`
123-- -----------------------------------------------------
124DROP TABLE IF EXISTS `mydb`.`k_payment` ;
125
126CREATE TABLE IF NOT EXISTS `mydb`.`k_payment` (
127  `payment_num` INT,
128  `payment_date` DATE NULL,
129  `payment_sum` DECIMAL(9,2) NULL,
130  `k_bill_bill_num` INT NOT NULL,
131  PRIMARY KEY (`payment_num`, `k_bill_bill_num`),
132  INDEX `fk_k_payment_k_bill1_idx` (`k_bill_bill_num` ASC),
133  CONSTRAINT `fk_k_payment_k_bill1`
134    FOREIGN KEY (`k_bill_bill_num`)
135    REFERENCES `mydb`.`k_bill` (`bill_num`)
136    ON DELETE NO ACTION
137    ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `mydb`.`k_price`
143-- -----------------------------------------------------
144DROP TABLE IF EXISTS `mydb`.`k_price` ;
145
146CREATE TABLE IF NOT EXISTS `mydb`.`k_price` (
147  `price_num` INT auto_increment,
148  `price_name` VARCHAR(45) NOT NULL,
149  `price_sum` VARCHAR(45) NULL,
150  `price_type` VARCHAR(1) NULL,
151  PRIMARY KEY (`price_num`))
152ENGINE = InnoDB;
153
154
155-- -----------------------------------------------------
156-- Table `mydb`.`k_protokol`
157-- -----------------------------------------------------
158DROP TABLE IF EXISTS `mydb`.`k_protokol` ;
159
160CREATE TABLE IF NOT EXISTS `mydb`.`k_protokol` (
161  `kolvo` INT NULL,
162  `price_sum` DECIMAL(9,2) NOT NULL,
163  `k_bill_bill_num` INT NOT NULL,
164  `k_price_price_num` INT NOT NULL,
165  PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`),
166  INDEX `fk_k_protokol_k_price1_idx` (`k_price_price_num` ASC),
167  CONSTRAINT `fk_k_protokol_k_bill1`
168    FOREIGN KEY (`k_bill_bill_num`)
169    REFERENCES `mydb`.`k_bill` (`bill_num`)
170    ON DELETE NO ACTION
171    ON UPDATE NO ACTION,
172  CONSTRAINT `fk_k_protokol_k_price1`
173    FOREIGN KEY (`k_price_price_num`)
174    REFERENCES `mydb`.`k_price` (`price_num`)
175    ON DELETE NO ACTION
176    ON UPDATE NO ACTION)
177ENGINE = InnoDB;
178
179
180SET SQL_MODE=@OLD_SQL_MODE;
181SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
182SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
183
184INSERT INTO k_firm (firm_name, firm_addr)
185VALUES('Альфа', 'Москва');
186INSERT INTO k_firm (firm_name, firm_addr)
187VALUES('Бета', 'Казань');
188INSERT INTO k_firm (firm_name, firm_addr)
189VALUES('Гамма', 'Париж');
190INSERT INTO k_firm (firm_name, firm_addr)
191VALUES('Дельта', 'Лондон');
192INSERT INTO k_firm (firm_name, firm_addr)
193VALUES('Омега', 'Токио'); 
194
195INSERT INTO k_dept (dept_short_name, dept_full_name)
196VALUES('Sales', 'Отдел продаж');
197INSERT INTO k_dept (dept_short_name, dept_full_name)
198VALUES('Mart', 'Отдел маркетинга');
199INSERT INTO k_dept (dept_short_name, dept_full_name)
200VALUES('Cust', 'Отдел гарантийного обслуживания');
201
202INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
203VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
204INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
205VALUES('Петров', 2, '2010-10-13','Менеджер');
206INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
207VALUES('Сидоров', 3, '2005-12-01','Менеджер');
208INSERT INTO k_staff (staff_name, staff_hiredate, staff_post)
209VALUES('Семенов', '1990-01-01','Директор');
210INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
211VALUES('Григорьев', 3, '2008-12-19','Программист');
212
213INSERT INTO k_contract
214(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 1, 1,'2011-11-01');
215INSERT INTO k_contract
216(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 1, 2,'2011-10-01');
217INSERT INTO k_contract
218(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 1, 1,'2011-09-01');
219INSERT INTO k_contract
220(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 2, 2,'2011-11-15');
221INSERT INTO k_contract
222(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 2, 2,'2011-08-01');
223INSERT INTO k_contract
224(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 3, 1,'2011-07-15');
225INSERT INTO k_contract
226(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 4, 1,'2011-11-12');
227
228UPDATE k_dept SET k_staff_staff_num=2
229WHERE dept_short_name='Mart';
230UPDATE k_dept SET k_staff_staff_num=3
231WHERE dept_short_name='Cust';
232UPDATE k_dept SET k_staff_staff_num=1
233WHERE dept_short_name='Sales';
234--
235-- Перед выполнением этого запроса необходимо снять галочку в настройках
236-- edit -> preferences -> sql editor проматываем вниз и снимаем галочку с последнего пункта(safe updates)
237-- Затем выходим и заходим снова в workbench. Пробуем выполнить запросы и всё работает
238--
239DELETE FROM k_firm WHERE firm_num=5;