· 6 years ago · Jun 04, 2019, 04:02 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema city_transport
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema city_transport
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `city_transport` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
18USE `city_transport` ;
19
20-- -----------------------------------------------------
21-- Table `city_transport`.`carpark`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `city_transport`.`carpark` (
24 `SerialNum` INT(11) NOT NULL,
25 `Maker` TINYTEXT NULL DEFAULT NULL,
26 `Model` TINYTEXT NULL DEFAULT NULL,
27 PRIMARY KEY (`SerialNum`))
28ENGINE = InnoDB
29DEFAULT CHARACTER SET = utf8mb4
30COLLATE = utf8mb4_0900_ai_ci;
31
32
33-- -----------------------------------------------------
34-- Table `city_transport`.`conductors`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `city_transport`.`conductors` (
37 `ITNOfConductor` INT(11) NOT NULL,
38 `Name` TINYTEXT NULL DEFAULT NULL,
39 `LastName` TINYTEXT NULL DEFAULT NULL,
40 `Age` TINYINT(4) NULL DEFAULT NULL,
41 PRIMARY KEY (`ITNOfConductor`))
42ENGINE = InnoDB
43DEFAULT CHARACTER SET = utf8mb4
44COLLATE = utf8mb4_0900_ai_ci;
45
46
47-- -----------------------------------------------------
48-- Table `city_transport`.`drivers`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `city_transport`.`drivers` (
51 `ITNOfDriver` INT(11) NOT NULL,
52 `Name` TINYTEXT NULL DEFAULT NULL,
53 `LastName` TINYTEXT NULL DEFAULT NULL,
54 `Experience` TINYINT(4) NULL DEFAULT NULL,
55 PRIMARY KEY (`ITNOfDriver`))
56ENGINE = InnoDB
57DEFAULT CHARACTER SET = utf8mb4
58COLLATE = utf8mb4_0900_ai_ci;
59
60
61-- -----------------------------------------------------
62-- Table `city_transport`.`routes`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `city_transport`.`routes` (
65 `NumOfRoute` INT(11) NOT NULL,
66 `Start` TINYTEXT NULL DEFAULT NULL,
67 `Finish` TINYTEXT NULL DEFAULT NULL,
68 `Duration` TIME NULL DEFAULT NULL,
69 PRIMARY KEY (`NumOfRoute`))
70ENGINE = InnoDB
71DEFAULT CHARACTER SET = utf8mb4
72COLLATE = utf8mb4_0900_ai_ci;
73
74
75-- -----------------------------------------------------
76-- Table `city_transport`.`transport`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `city_transport`.`transport` (
79 `idTransport` INT(11) NOT NULL AUTO_INCREMENT,
80 `routes_NumOfRoute` INT(11) NULL DEFAULT NULL,
81 `StartStop` TINYTEXT NULL DEFAULT NULL,
82 `FinishStop` TINYTEXT NULL DEFAULT NULL,
83 `Type` TINYTEXT NULL DEFAULT NULL,
84 `NameOfDriver` TINYTEXT NULL DEFAULT NULL,
85 `LastNameOfDriver` TINYTEXT NULL DEFAULT NULL,
86 `drivers_ITNOfDriver` INT(11) NULL DEFAULT NULL,
87 `NameOfConductor` TINYTEXT NULL DEFAULT NULL,
88 `LastNameOfConductor` TINYTEXT NULL DEFAULT NULL,
89 `conductors_ITNOfConductor` INT(11) NULL DEFAULT NULL,
90 `carpark_SerialNum` INT(11) NULL DEFAULT NULL,
91 PRIMARY KEY (`idTransport`),
92 INDEX `fk_transport_conductors_idx` (`conductors_ITNOfConductor` ASC) VISIBLE,
93 INDEX `fk_transport_stops1_idx` (`routes_NumOfRoute` ASC) VISIBLE,
94 INDEX `fk_transport_carpark1_idx` (`carpark_SerialNum` ASC) VISIBLE,
95 INDEX `fk_transport_drivers1_idx` (`drivers_ITNOfDriver` ASC) VISIBLE,
96 CONSTRAINT `fk_transport_carpark1`
97 FOREIGN KEY (`carpark_SerialNum`)
98 REFERENCES `city_transport`.`carpark` (`SerialNum`),
99 CONSTRAINT `fk_transport_conductors`
100 FOREIGN KEY (`conductors_ITNOfConductor`)
101 REFERENCES `city_transport`.`conductors` (`ITNOfConductor`),
102 CONSTRAINT `fk_transport_drivers1`
103 FOREIGN KEY (`drivers_ITNOfDriver`)
104 REFERENCES `city_transport`.`drivers` (`ITNOfDriver`),
105 CONSTRAINT `fk_transport_routes`
106 FOREIGN KEY (`routes_NumOfRoute`)
107 REFERENCES `city_transport`.`routes` (`NumOfRoute`))
108ENGINE = InnoDB
109AUTO_INCREMENT = 26
110DEFAULT CHARACTER SET = utf8mb4
111COLLATE = utf8mb4_0900_ai_ci;
112
113USE `city_transport` ;
114
115-- -----------------------------------------------------
116-- Placeholder table for view `city_transport`.`get_user_data`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `city_transport`.`get_user_data` (`Number` INT, `Type` INT, `Start Stop` INT, `Finish Stop` INT, `Duration` INT);
119
120-- -----------------------------------------------------
121-- procedure Autofill_conductor_LastName
122-- -----------------------------------------------------
123
124DELIMITER $$
125USE `city_transport`$$
126CREATE DEFINER=`root`@`localhost` PROCEDURE `Autofill_conductor_LastName`()
127BEGIN
128 update transport t, conductors c
129 set t.LastNameOfConductor = c.LastName
130 where t.conductors_ITNOfConductor = c.ITNOfConductor;
131END$$
132
133DELIMITER ;
134
135-- -----------------------------------------------------
136-- procedure Autofill_conductor_Name
137-- -----------------------------------------------------
138
139DELIMITER $$
140USE `city_transport`$$
141CREATE DEFINER=`root`@`localhost` PROCEDURE `Autofill_conductor_Name`()
142BEGIN
143 update transport t, conductors c
144 set t.NameOfConductor = c.Name
145 where t.conductors_ITNOfConductor = c.ITNOfConductor;
146END$$
147
148DELIMITER ;
149
150-- -----------------------------------------------------
151-- procedure Autofill_driver_LastName
152-- -----------------------------------------------------
153
154DELIMITER $$
155USE `city_transport`$$
156CREATE DEFINER=`root`@`localhost` PROCEDURE `Autofill_driver_LastName`()
157BEGIN
158 update transport t, drivers d
159 set t.LastNameOfDriver = d.LastName
160 where t.drivers_ITNOfDriver = d.ITNOfDriver;
161END$$
162
163DELIMITER ;
164
165-- -----------------------------------------------------
166-- procedure Autofill_driver_Name
167-- -----------------------------------------------------
168
169DELIMITER $$
170USE `city_transport`$$
171CREATE DEFINER=`root`@`localhost` PROCEDURE `Autofill_driver_Name`()
172BEGIN
173 update transport t, drivers d
174 set t.NameOfDriver = d.Name
175 where t.drivers_ITNOfDriver = d.ITNOfDriver;
176END$$
177
178DELIMITER ;
179
180-- -----------------------------------------------------
181-- procedure Autofill_route
182-- -----------------------------------------------------
183
184DELIMITER $$
185USE `city_transport`$$
186CREATE DEFINER=`root`@`localhost` PROCEDURE `Autofill_route`()
187BEGIN
188 update transport t, routes r
189 set t.StartStop = r.Start, t.FinishStop = r.Finish
190 where t.routes_NumOfRoute = r.NumOfRoute;
191
192END$$
193
194DELIMITER ;
195
196-- -----------------------------------------------------
197-- function get_median_duration
198-- -----------------------------------------------------
199
200DELIMITER $$
201USE `city_transport`$$
202CREATE DEFINER=`root`@`localhost` FUNCTION `get_median_duration`(dur TIME) RETURNS time
203 DETERMINISTIC
204BEGIN
205 DECLARE avg_dur TIME DEFAULT '00:00:00';
206 declare cursor1 cursor for SELECT sec_to_time(AVG(time_to_sec(Duration))) FROM routes;
207
208 open cursor1;
209 fetch cursor1 into avg_dur;
210 close cursor1;
211
212RETURN sec_to_time(abs(dur - avg_dur));
213END$$
214
215DELIMITER ;
216
217-- -----------------------------------------------------
218-- function get_transport_maker
219-- -----------------------------------------------------
220
221DELIMITER $$
222USE `city_transport`$$
223CREATE DEFINER=`root`@`localhost` FUNCTION `get_transport_maker`(tr_id int(11)) RETURNS tinytext CHARSET utf8mb4
224 DETERMINISTIC
225BEGIN
226 declare makerTC tinytext;
227 declare serialn integer;
228 select carpark_SerialNum from transport where idTransport = tr_id into serialn;
229 select Maker from carpark where SerialNum = serialn limit 1 into makerTC;
230RETURN makerTC;
231END$$
232
233DELIMITER ;
234
235-- -----------------------------------------------------
236-- function get_transport_model
237-- -----------------------------------------------------
238
239DELIMITER $$
240USE `city_transport`$$
241CREATE DEFINER=`root`@`localhost` FUNCTION `get_transport_model`(tr_id int(11)) RETURNS tinytext CHARSET utf8mb4
242 DETERMINISTIC
243BEGIN
244 declare ModelTC tinytext;
245 declare serialn integer;
246 select carpark_SerialNum from transport where idTransport = tr_id into serialn;
247 select Model from carpark where SerialNum = serialn limit 1 into ModelTC;
248RETURN ModelTC;
249END$$
250
251DELIMITER ;
252
253-- -----------------------------------------------------
254-- View `city_transport`.`get_user_data`
255-- -----------------------------------------------------
256DROP TABLE IF EXISTS `city_transport`.`get_user_data`;
257USE `city_transport`;
258CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `city_transport`.`get_user_data` AS select `city_transport`.`transport`.`routes_NumOfRoute` AS `Number`,`city_transport`.`transport`.`Type` AS `Type`,`city_transport`.`transport`.`StartStop` AS `Start Stop`,`city_transport`.`transport`.`FinishStop` AS `Finish Stop`,`city_transport`.`routes`.`Duration` AS `Duration` from (`city_transport`.`transport` join `city_transport`.`routes` on((`city_transport`.`transport`.`routes_NumOfRoute` = `city_transport`.`routes`.`NumOfRoute`)));
259USE `city_transport`;
260
261DELIMITER $$
262USE `city_transport`$$
263CREATE
264DEFINER=`root`@`localhost`
265TRIGGER `city_transport`.`carpark_BEFORE_DELETE`
266BEFORE DELETE ON `city_transport`.`carpark`
267FOR EACH ROW
268BEGIN
269 UPDATE `city_transport`.`transport` SET `carpark_SerialNum` = NULL, `StartStop` = NULL, `FinishStop` = NULL WHERE carpark_SerialNum = OLD.SerialNum;
270END$$
271
272USE `city_transport`$$
273CREATE
274DEFINER=`root`@`localhost`
275TRIGGER `city_transport`.`routes_BEFORE_DELETE`
276BEFORE DELETE ON `city_transport`.`routes`
277FOR EACH ROW
278BEGIN
279 UPDATE `city_transport`.`transport` SET `routes_NumOfRoute` = NULL, `StartStop` = NULL, `FinishStop` = NULL WHERE routes_NumOfRoute = OLD.NumOfRoute;
280END$$
281
282
283DELIMITER ;
284
285SET SQL_MODE=@OLD_SQL_MODE;
286SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
287SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;