· 5 years ago · Sep 11, 2020, 03:38 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Sep 11 23:28:58 2020
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 mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`person`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`person` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `first_name` VARCHAR(45) NULL,
26 `last_name` VARCHAR(45) NULL,
27 `suffix` VARCHAR(45) NULL,
28 `nationality` VARCHAR(45) NULL,
29 `birthday` DATE NULL,
30 `gender` VARCHAR(45) NULL,
31 `civil_status` VARCHAR(45) NULL,
32 PRIMARY KEY (`id`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `mydb`.`address`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `mydb`.`address` (
40 `house_no` INT NULL,
41 `lot` VARCHAR(45) NULL,
42 `barangay` VARCHAR(45) NULL,
43 `street` VARCHAR(45) NULL,
44 `building` VARCHAR(45) NULL,
45 `municipality` VARCHAR(45) NULL,
46 `city` VARCHAR(45) NULL,
47 `province` VARCHAR(45) NULL,
48 `region` VARCHAR(45) NULL,
49 `person_id` INT NOT NULL,
50 INDEX `fk_address_person_idx` (`person_id` ASC) VISIBLE,
51 CONSTRAINT `fk_address_person`
52 FOREIGN KEY (`person_id`)
53 REFERENCES `mydb`.`person` (`id`)
54 ON DELETE NO ACTION
55 ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `mydb`.`employment`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `mydb`.`employment` (
63 `work_phone` VARCHAR(45) NULL,
64 `employ_status` VARCHAR(45) NULL,
65 `employer` VARCHAR(45) NULL,
66 `employment_name` VARCHAR(45) NULL,
67 `person_id` INT NOT NULL,
68 INDEX `fk_employment_person1_idx` (`person_id` ASC) VISIBLE,
69 CONSTRAINT `fk_employment_person1`
70 FOREIGN KEY (`person_id`)
71 REFERENCES `mydb`.`person` (`id`)
72 ON DELETE NO ACTION
73 ON UPDATE NO ACTION)
74ENGINE = InnoDB;
75
76
77-- -----------------------------------------------------
78-- Table `mydb`.`contact_detail`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `mydb`.`contact_detail` (
81 `mobile_phone` VARCHAR(45) NULL,
82 `home_phone` VARCHAR(45) NULL,
83 `email_address` VARCHAR(45) NULL,
84 `person_id` INT NOT NULL,
85 INDEX `fk_contact_detail_person1_idx` (`person_id` ASC) VISIBLE,
86 CONSTRAINT `fk_contact_detail_person1`
87 FOREIGN KEY (`person_id`)
88 REFERENCES `mydb`.`person` (`id`)
89 ON DELETE NO ACTION
90 ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `mydb`.`disease`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `mydb`.`disease` (
98 `id` INT NOT NULL AUTO_INCREMENT,
99 `name` VARCHAR(45) NULL,
100 PRIMARY KEY (`id`))
101ENGINE = InnoDB;
102
103
104-- -----------------------------------------------------
105-- Table `mydb`.`person_disease`
106-- -----------------------------------------------------
107CREATE TABLE IF NOT EXISTS `mydb`.`person_disease` (
108 `datetime` DATETIME NOT NULL,
109 `disease_id` INT NOT NULL,
110 `person_id` INT NOT NULL,
111 PRIMARY KEY (`datetime`),
112 INDEX `fk_person_disease_disease1_idx` (`disease_id` ASC) VISIBLE,
113 INDEX `fk_person_disease_person1_idx` (`person_id` ASC) VISIBLE,
114 CONSTRAINT `fk_person_disease_disease1`
115 FOREIGN KEY (`disease_id`)
116 REFERENCES `mydb`.`disease` (`id`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION,
119 CONSTRAINT `fk_person_disease_person1`
120 FOREIGN KEY (`person_id`)
121 REFERENCES `mydb`.`person` (`id`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table `mydb`.`classification`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `mydb`.`classification` (
131 `id` INT NOT NULL AUTO_INCREMENT,
132 `name` VARCHAR(45) NULL,
133 PRIMARY KEY (`id`))
134ENGINE = InnoDB;
135
136
137-- -----------------------------------------------------
138-- Table `mydb`.`disease_classification`
139-- -----------------------------------------------------
140CREATE TABLE IF NOT EXISTS `mydb`.`disease_classification` (
141 `disease_id` INT NOT NULL,
142 `classification_id` INT NOT NULL,
143 INDEX `fk_disease_classification_disease1_idx` (`disease_id` ASC) VISIBLE,
144 INDEX `fk_disease_classification_classification1_idx` (`classification_id` ASC) VISIBLE,
145 CONSTRAINT `fk_disease_classification_disease1`
146 FOREIGN KEY (`disease_id`)
147 REFERENCES `mydb`.`disease` (`id`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION,
150 CONSTRAINT `fk_disease_classification_classification1`
151 FOREIGN KEY (`classification_id`)
152 REFERENCES `mydb`.`classification` (`id`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION)
155ENGINE = InnoDB;
156
157
158-- -----------------------------------------------------
159-- Table `mydb`.`location`
160-- -----------------------------------------------------
161CREATE TABLE IF NOT EXISTS `mydb`.`location` (
162 `id` INT NOT NULL AUTO_INCREMENT,
163 `house_no` INT NULL,
164 `lot` VARCHAR(45) NULL,
165 `barangay` VARCHAR(45) NULL,
166 `street` VARCHAR(45) NULL,
167 `building` VARCHAR(45) NULL,
168 `municipality` VARCHAR(45) NULL,
169 `city` VARCHAR(45) NULL,
170 `province` VARCHAR(45) NULL,
171 `region` VARCHAR(45) NULL,
172 `longitude` VARCHAR(45) NULL,
173 `latitude` VARCHAR(45) NULL,
174 PRIMARY KEY (`id`))
175ENGINE = InnoDB;
176
177
178-- -----------------------------------------------------
179-- Table `mydb`.`event`
180-- -----------------------------------------------------
181CREATE TABLE IF NOT EXISTS `mydb`.`event` (
182 `id` INT NOT NULL AUTO_INCREMENT,
183 `name` VARCHAR(45) NULL,
184 `duration` INT NULL,
185 `type` VARCHAR(45) NULL,
186 `location_id` INT NOT NULL,
187 PRIMARY KEY (`id`),
188 INDEX `fk_event_location1_idx` (`location_id` ASC) VISIBLE,
189 CONSTRAINT `fk_event_location1`
190 FOREIGN KEY (`location_id`)
191 REFERENCES `mydb`.`location` (`id`)
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION)
194ENGINE = InnoDB;
195
196
197-- -----------------------------------------------------
198-- Table `mydb`.`person_event`
199-- -----------------------------------------------------
200CREATE TABLE IF NOT EXISTS `mydb`.`person_event` (
201 `datetime` DATETIME NULL,
202 `temperature` VARCHAR(45) NULL,
203 `person_id` INT NOT NULL,
204 `event_id` INT NOT NULL,
205 INDEX `fk_person_event_person1_idx` (`person_id` ASC) VISIBLE,
206 INDEX `fk_person_event_event1_idx` (`event_id` ASC) VISIBLE,
207 CONSTRAINT `fk_person_event_person1`
208 FOREIGN KEY (`person_id`)
209 REFERENCES `mydb`.`person` (`id`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION,
212 CONSTRAINT `fk_person_event_event1`
213 FOREIGN KEY (`event_id`)
214 REFERENCES `mydb`.`event` (`id`)
215 ON DELETE NO ACTION
216 ON UPDATE NO ACTION)
217ENGINE = InnoDB;
218
219
220-- -----------------------------------------------------
221-- Table `mydb`.`establishment_category`
222-- -----------------------------------------------------
223CREATE TABLE IF NOT EXISTS `mydb`.`establishment_category` (
224 `id` INT NOT NULL AUTO_INCREMENT,
225 `name` VARCHAR(45) NULL,
226 PRIMARY KEY (`id`))
227ENGINE = InnoDB;
228
229
230-- -----------------------------------------------------
231-- Table `mydb`.`establishment`
232-- -----------------------------------------------------
233CREATE TABLE IF NOT EXISTS `mydb`.`establishment` (
234 `id` INT NOT NULL AUTO_INCREMENT,
235 `name` VARCHAR(45) NULL,
236 `duration` INT NULL,
237 `is_airconditioned` TINYINT NULL,
238 `location_id` INT NOT NULL,
239 `establishment_category_id` INT NOT NULL,
240 PRIMARY KEY (`id`),
241 INDEX `fk_establishment_location1_idx` (`location_id` ASC) VISIBLE,
242 INDEX `fk_establishment_establishment_category1_idx` (`establishment_category_id` ASC) VISIBLE,
243 CONSTRAINT `fk_establishment_location1`
244 FOREIGN KEY (`location_id`)
245 REFERENCES `mydb`.`location` (`id`)
246 ON DELETE NO ACTION
247 ON UPDATE NO ACTION,
248 CONSTRAINT `fk_establishment_establishment_category1`
249 FOREIGN KEY (`establishment_category_id`)
250 REFERENCES `mydb`.`establishment_category` (`id`)
251 ON DELETE NO ACTION
252 ON UPDATE NO ACTION)
253ENGINE = InnoDB;
254
255
256-- -----------------------------------------------------
257-- Table `mydb`.`establishment_category_type`
258-- -----------------------------------------------------
259CREATE TABLE IF NOT EXISTS `mydb`.`establishment_category_type` (
260 `id` INT NOT NULL AUTO_INCREMENT,
261 `name` VARCHAR(45) NULL,
262 `establishment_category_id` INT NOT NULL,
263 PRIMARY KEY (`id`),
264 INDEX `fk_establishment_category_type_establishment_category1_idx` (`establishment_category_id` ASC) VISIBLE,
265 CONSTRAINT `fk_establishment_category_type_establishment_category1`
266 FOREIGN KEY (`establishment_category_id`)
267 REFERENCES `mydb`.`establishment_category` (`id`)
268 ON DELETE NO ACTION
269 ON UPDATE NO ACTION)
270ENGINE = InnoDB;
271
272
273-- -----------------------------------------------------
274-- Table `mydb`.`transportation`
275-- -----------------------------------------------------
276CREATE TABLE IF NOT EXISTS `mydb`.`transportation` (
277 `id` INT NOT NULL AUTO_INCREMENT,
278 `name` VARCHAR(45) NULL,
279 `type` VARCHAR(45) NULL,
280 `person_type` VARCHAR(45) NULL,
281 `transportation_no` VARCHAR(45) NULL,
282 `estimate_no_person` INT NULL,
283 `is_airconditioned` TINYINT NULL,
284 PRIMARY KEY (`id`))
285ENGINE = InnoDB;
286
287
288-- -----------------------------------------------------
289-- Table `mydb`.`route`
290-- -----------------------------------------------------
291CREATE TABLE IF NOT EXISTS `mydb`.`route` (
292 `id` INT NOT NULL AUTO_INCREMENT,
293 `location_id` INT NOT NULL,
294 PRIMARY KEY (`id`),
295 INDEX `fk_route_location1_idx` (`location_id` ASC) VISIBLE,
296 CONSTRAINT `fk_route_location1`
297 FOREIGN KEY (`location_id`)
298 REFERENCES `mydb`.`location` (`id`)
299 ON DELETE NO ACTION
300 ON UPDATE NO ACTION)
301ENGINE = InnoDB;
302
303
304-- -----------------------------------------------------
305-- Table `mydb`.`transportation_route`
306-- -----------------------------------------------------
307CREATE TABLE IF NOT EXISTS `mydb`.`transportation_route` (
308 `route_id` INT NOT NULL,
309 `transportation_id` INT NOT NULL,
310 INDEX `fk_transportation_route_route1_idx` (`route_id` ASC) VISIBLE,
311 INDEX `fk_transportation_route_transportation1_idx` (`transportation_id` ASC) VISIBLE,
312 CONSTRAINT `fk_transportation_route_route1`
313 FOREIGN KEY (`route_id`)
314 REFERENCES `mydb`.`route` (`id`)
315 ON DELETE NO ACTION
316 ON UPDATE NO ACTION,
317 CONSTRAINT `fk_transportation_route_transportation1`
318 FOREIGN KEY (`transportation_id`)
319 REFERENCES `mydb`.`transportation` (`id`)
320 ON DELETE NO ACTION
321 ON UPDATE NO ACTION)
322ENGINE = InnoDB;
323
324
325-- -----------------------------------------------------
326-- Table `mydb`.`person_establishment`
327-- -----------------------------------------------------
328CREATE TABLE IF NOT EXISTS `mydb`.`person_establishment` (
329 `datetime` DATETIME NULL,
330 `temperature` VARCHAR(45) NULL,
331 `person_id` INT NOT NULL,
332 `establishment_id` INT NOT NULL,
333 INDEX `fk_person_establishment_person1_idx` (`person_id` ASC) VISIBLE,
334 INDEX `fk_person_establishment_establishment1_idx` (`establishment_id` ASC) VISIBLE,
335 CONSTRAINT `fk_person_establishment_person1`
336 FOREIGN KEY (`person_id`)
337 REFERENCES `mydb`.`person` (`id`)
338 ON DELETE NO ACTION
339 ON UPDATE NO ACTION,
340 CONSTRAINT `fk_person_establishment_establishment1`
341 FOREIGN KEY (`establishment_id`)
342 REFERENCES `mydb`.`establishment` (`id`)
343 ON DELETE NO ACTION
344 ON UPDATE NO ACTION)
345ENGINE = InnoDB;
346
347
348-- -----------------------------------------------------
349-- Table `mydb`.`person_transportation`
350-- -----------------------------------------------------
351CREATE TABLE IF NOT EXISTS `mydb`.`person_transportation` (
352 `datetime` DATETIME NULL,
353 `temperature` VARCHAR(45) NULL,
354 `person_id` INT NOT NULL,
355 `transportation_id` INT NOT NULL,
356 INDEX `fk_person_transportation_person1_idx` (`person_id` ASC) VISIBLE,
357 INDEX `fk_person_transportation_transportation1_idx` (`transportation_id` ASC) VISIBLE,
358 CONSTRAINT `fk_person_transportation_person1`
359 FOREIGN KEY (`person_id`)
360 REFERENCES `mydb`.`person` (`id`)
361 ON DELETE NO ACTION
362 ON UPDATE NO ACTION,
363 CONSTRAINT `fk_person_transportation_transportation1`
364 FOREIGN KEY (`transportation_id`)
365 REFERENCES `mydb`.`transportation` (`id`)
366 ON DELETE NO ACTION
367 ON UPDATE NO ACTION)
368ENGINE = InnoDB;
369
370
371SET SQL_MODE=@OLD_SQL_MODE;
372SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
373SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
374