· 8 years ago · Jun 24, 2018, 10:04 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sun Jun 24 23:40:56 2018
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 rech_system
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema rech_system
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `rech_system` DEFAULT CHARACTER SET utf8 ;
18USE `rech_system` ;
19
20-- -----------------------------------------------------
21-- Table `rech_system`.`Faculty`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `rech_system`.`Faculty` ;
24
25CREATE TABLE IF NOT EXISTS `rech_system`.`Faculty` (
26 `faculty_name` VARCHAR(50) NOT NULL,
27 `faculty_info` TEXT NULL,
28 PRIMARY KEY (`faculty_name`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `rech_system`.`Department`
34-- -----------------------------------------------------
35DROP TABLE IF EXISTS `rech_system`.`Department` ;
36
37CREATE TABLE IF NOT EXISTS `rech_system`.`Department` (
38 `department_name` VARCHAR(50) NOT NULL,
39 `faculty_name` VARCHAR(50) NOT NULL,
40 PRIMARY KEY (`department_name`, `faculty_name`),
41 CONSTRAINT `fk_Department_Faculty`
42 FOREIGN KEY (`faculty_name`)
43 REFERENCES `rech_system`.`Faculty` (`faculty_name`)
44 ON DELETE CASCADE
45 ON UPDATE CASCADE)
46ENGINE = InnoDB;
47
48CREATE INDEX `fk_Department_Faculty_idx` ON `rech_system`.`Department` (`faculty_name` ASC);
49
50
51-- -----------------------------------------------------
52-- Table `rech_system`.`Person`
53-- -----------------------------------------------------
54DROP TABLE IF EXISTS `rech_system`.`Person` ;
55
56CREATE TABLE IF NOT EXISTS `rech_system`.`Person` (
57 `user_email` VARCHAR(100) NOT NULL,
58 `user_password_hash` VARCHAR(50) NOT NULL,
59 `user_firstname` VARCHAR(50) NULL,
60 `user_lastname` VARCHAR(50) NULL,
61 `user_gender` CHAR(1) NULL,
62 `current_degree_level` VARCHAR(20) NULL,
63 `contact_number_mobile` VARCHAR(15) NULL,
64 `person_type` VARCHAR(10) NULL,
65 `contact_office_telephone` VARCHAR(15) NULL,
66 `office_address` VARCHAR(40) NULL,
67 `faculty_name` VARCHAR(50) NULL,
68 `department_name` VARCHAR(50) NULL,
69 `Faculty_faculty_name` VARCHAR(50) NOT NULL,
70 `Department_department_name` VARCHAR(50) NOT NULL,
71 `Department_faculty_name` VARCHAR(50) NOT NULL,
72 PRIMARY KEY (`user_email`, `Department_faculty_name`, `Department_department_name`, `Faculty_faculty_name`),
73 CONSTRAINT `fk_Faculty`
74 FOREIGN KEY (`Faculty_faculty_name`)
75 REFERENCES `rech_system`.`Faculty` (`faculty_name`)
76 ON DELETE CASCADE
77 ON UPDATE CASCADE,
78 CONSTRAINT `fk_Department`
79 FOREIGN KEY (`Department_department_name` , `Department_faculty_name`)
80 REFERENCES `rech_system`.`Department` (`department_name` , `faculty_name`)
81 ON DELETE CASCADE
82 ON UPDATE CASCADE)
83ENGINE = InnoDB
84DEFAULT CHARACTER SET = utf8;
85
86CREATE UNIQUE INDEX `user_email_UNIQUE` ON `rech_system`.`Person` (`user_email` ASC);
87
88CREATE INDEX `fk_Person_Faculty1_idx` ON `rech_system`.`Person` (`Faculty_faculty_name` ASC);
89
90CREATE INDEX `fk_Person_Department1_idx` ON `rech_system`.`Person` (`Department_department_name` ASC, `Department_faculty_name` ASC);
91
92
93-- -----------------------------------------------------
94-- Table `rech_system`.`Ethics_Application`
95-- -----------------------------------------------------
96DROP TABLE IF EXISTS `rech_system`.`Ethics_Application` ;
97
98CREATE TABLE IF NOT EXISTS `rech_system`.`Ethics_Application` (
99 `application_type` CHAR(1) NOT NULL,
100 `application_year` YEAR NOT NULL,
101 `application_department` VARCHAR(50) NOT NULL,
102 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
103 `is_submitted` TINYINT NULL DEFAULT '0',
104 `date_submitted` VARCHAR(45) NULL DEFAULT NULL,
105 `date_approved` VARCHAR(45) NULL DEFAULT NULL,
106 `pi_id` VARCHAR(100) NOT NULL,
107 `pi_approved_date` VARCHAR(45) NULL DEFAULT NULL,
108 `prp_id` VARCHAR(100) NOT NULL,
109 `prp_approved_date` VARCHAR(45) NULL DEFAULT NULL,
110 `hod_id` VARCHAR(100) NULL DEFAULT NULL,
111 `hod_approved` VARCHAR(45) NULL DEFAULT NULL,
112 `rti_id` VARCHAR(100) NULL DEFAULT NULL,
113 `rti_approved` VARCHAR(45) NULL DEFAULT NULL,
114 `liaison_id` VARCHAR(100) NULL DEFAULT NULL,
115 PRIMARY KEY (`application_type`, `application_year`, `application_department`, `application_number`),
116 CONSTRAINT `fk_Person_user_email_pi`
117 FOREIGN KEY (`pi_id`)
118 REFERENCES `rech_system`.`Person` (`user_email`)
119 ON DELETE CASCADE
120 ON UPDATE CASCADE,
121 CONSTRAINT `fk_Person_user_email_prp`
122 FOREIGN KEY (`prp_id`)
123 REFERENCES `rech_system`.`Person` (`user_email`)
124 ON DELETE CASCADE
125 ON UPDATE CASCADE,
126 CONSTRAINT `fk_Person_user_email_rti`
127 FOREIGN KEY (`rti_id`)
128 REFERENCES `rech_system`.`Person` (`user_email`)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE,
131 CONSTRAINT `fk_Person_user_email_hod`
132 FOREIGN KEY (`hod_id`)
133 REFERENCES `rech_system`.`Person` (`user_email`)
134 ON DELETE CASCADE
135 ON UPDATE CASCADE,
136 CONSTRAINT `fk_Person_user_email_liaison`
137 FOREIGN KEY (`liaison_id`)
138 REFERENCES `rech_system`.`Person` (`user_email`)
139 ON DELETE CASCADE
140 ON UPDATE CASCADE)
141ENGINE = InnoDB
142DEFAULT CHARACTER SET = utf8;
143
144CREATE INDEX `fk_Person_user_email_pi_idx` ON `rech_system`.`Ethics_Application` (`pi_id` ASC);
145
146CREATE INDEX `fk_Person_user_email_prp_idx` ON `rech_system`.`Ethics_Application` (`prp_id` ASC);
147
148CREATE INDEX `fk_Person_user_email_rti_idx` ON `rech_system`.`Ethics_Application` (`rti_id` ASC);
149
150CREATE INDEX `fk_Person_user_email_hod_idx` ON `rech_system`.`Ethics_Application` (`hod_id` ASC);
151
152CREATE INDEX `fk_Person_user_email_liaison_idx` ON `rech_system`.`Ethics_Application` (`liaison_id` ASC);
153
154
155-- -----------------------------------------------------
156-- Table `rech_system`.`Message`
157-- -----------------------------------------------------
158DROP TABLE IF EXISTS `rech_system`.`Message` ;
159
160CREATE TABLE IF NOT EXISTS `rech_system`.`Message` (
161 `message_date` VARCHAR(45) NOT NULL,
162 `message` TEXT NULL DEFAULT NULL,
163 `user_email_sender` VARCHAR(100) NOT NULL,
164 `application_type` CHAR(1) NOT NULL,
165 `application_year` YEAR NOT NULL,
166 `application_department` VARCHAR(50) NOT NULL,
167 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
168 `user_email_receiver` VARCHAR(100) NOT NULL,
169 PRIMARY KEY (`message_date`, `application_number`, `application_department`, `application_year`, `application_type`, `user_email_sender`, `user_email_receiver`),
170 CONSTRAINT `fk_Person_Sender`
171 FOREIGN KEY (`user_email_sender`)
172 REFERENCES `rech_system`.`Person` (`user_email`)
173 ON DELETE RESTRICT
174 ON UPDATE CASCADE,
175 CONSTRAINT `fk_Ethics_Application`
176 FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
177 REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
178 ON DELETE CASCADE
179 ON UPDATE CASCADE,
180 CONSTRAINT `fk_Person_Receiver`
181 FOREIGN KEY (`user_email_receiver`)
182 REFERENCES `rech_system`.`Person` (`user_email`)
183 ON DELETE CASCADE
184 ON UPDATE CASCADE)
185ENGINE = InnoDB
186DEFAULT CHARACTER SET = utf8;
187
188CREATE INDEX `application_idx` ON `rech_system`.`Message` ();
189
190CREATE INDEX `fk_Person_Sender_idx` ON `rech_system`.`Message` (`user_email_sender` ASC);
191
192CREATE INDEX `fk_Ethics_Application_idx` ON `rech_system`.`Message` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);
193
194CREATE INDEX `fk_Person_Receiver_idx` ON `rech_system`.`Message` (`user_email_receiver` ASC);
195
196
197-- -----------------------------------------------------
198-- Table `rech_system`.`Meeting`
199-- -----------------------------------------------------
200DROP TABLE IF EXISTS `rech_system`.`Meeting` ;
201
202CREATE TABLE IF NOT EXISTS `rech_system`.`Meeting` (
203 `meeting_date` DATETIME NOT NULL,
204 PRIMARY KEY (`meeting_date`))
205ENGINE = InnoDB;
206
207
208-- -----------------------------------------------------
209-- Table `rech_system`.`AgendaItem`
210-- -----------------------------------------------------
211DROP TABLE IF EXISTS `rech_system`.`AgendaItem` ;
212
213CREATE TABLE IF NOT EXISTS `rech_system`.`AgendaItem` (
214 `resolution` TEXT NULL,
215 `application_status` TINYINT NULL,
216 `application_type` CHAR(1) NOT NULL,
217 `application_year` YEAR NOT NULL,
218 `application_department` VARCHAR(50) NOT NULL,
219 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
220 `meeting_date` DATETIME NOT NULL,
221 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `meeting_date`),
222 CONSTRAINT `fk_Ethics_Application`
223 FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
224 REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
225 ON DELETE CASCADE
226 ON UPDATE CASCADE,
227 CONSTRAINT `fk_Meeting`
228 FOREIGN KEY (`meeting_date`)
229 REFERENCES `rech_system`.`Meeting` (`meeting_date`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION)
232ENGINE = InnoDB;
233
234CREATE INDEX `fk_Ethics_Application_idx` ON `rech_system`.`AgendaItem` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);
235
236CREATE INDEX `fk_Meeting_idx` ON `rech_system`.`AgendaItem` (`meeting_date` ASC);
237
238
239-- -----------------------------------------------------
240-- Table `rech_system`.`Component`
241-- -----------------------------------------------------
242DROP TABLE IF EXISTS `rech_system`.`Component` ;
243
244CREATE TABLE IF NOT EXISTS `rech_system`.`Component` (
245 `section_id` SMALLINT(2) NOT NULL,
246 `component_id` SMALLINT(2) NOT NULL,
247 `question` VARCHAR(255) NULL,
248 `application_type` CHAR(1) NOT NULL,
249 `application_year` YEAR NOT NULL,
250 `application_department` VARCHAR(50) NOT NULL,
251 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
252 PRIMARY KEY (`section_id`, `component_id`, `application_type`, `application_year`, `application_department`, `application_number`),
253 CONSTRAINT `fk_Ethics_Application`
254 FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
255 REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
256 ON DELETE CASCADE
257 ON UPDATE CASCADE)
258ENGINE = InnoDB;
259
260CREATE INDEX `fk_Component_Ethics_Application_idx` ON `rech_system`.`Component` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);
261
262
263-- -----------------------------------------------------
264-- Table `rech_system`.`ComponentVersion`
265-- -----------------------------------------------------
266DROP TABLE IF EXISTS `rech_system`.`ComponentVersion` ;
267
268CREATE TABLE IF NOT EXISTS `rech_system`.`ComponentVersion` (
269 `version` TINYINT NOT NULL AUTO_INCREMENT,
270 `is_submitted` TINYINT NULL,
271 `date_submitted` DATETIME NULL,
272 `date_last_edited` DATETIME NULL,
273 `response_type` VARCHAR(8) NULL,
274 `text_value` TEXT NULL,
275 `bool_value` TINYINT NULL,
276 `document_name` VARCHAR(100) NULL,
277 `document_description` VARCHAR(255) NULL,
278 `document_blob` BLOB NULL,
279 `section_id` SMALLINT(2) NOT NULL,
280 `component_id` SMALLINT(2) NOT NULL,
281 `application_type` CHAR(1) NOT NULL,
282 `application_year` YEAR NOT NULL,
283 `application_department` VARCHAR(50) NOT NULL,
284 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
285 PRIMARY KEY (`version`, `section_id`, `component_id`, `application_type`, `application_year`, `application_department`, `application_number`),
286 CONSTRAINT `fk_ComponentVersion_Component`
287 FOREIGN KEY (`section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
288 REFERENCES `rech_system`.`Component` (`section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
289 ON DELETE CASCADE
290 ON UPDATE CASCADE)
291ENGINE = InnoDB;
292
293CREATE INDEX `fk_Component_idx` ON `rech_system`.`ComponentVersion` (`section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);
294
295CREATE INDEX `fk_Application_idx` ON `rech_system`.`ComponentVersion` (`application_number` ASC, `application_department` ASC, `application_year` ASC, `application_type` ASC);
296
297
298-- -----------------------------------------------------
299-- Table `rech_system`.`ReviewerFeedback`
300-- -----------------------------------------------------
301DROP TABLE IF EXISTS `rech_system`.`ReviewerFeedback` ;
302
303CREATE TABLE IF NOT EXISTS `rech_system`.`ReviewerFeedback` (
304 `feedback_date` DATETIME NULL,
305 `application_assigned_date` DATETIME NULL,
306 `user_email` VARCHAR(100) NOT NULL,
307 `version` TINYINT NOT NULL,
308 `section_id` SMALLINT(2) NOT NULL,
309 `component_id` SMALLINT(2) NOT NULL,
310 `application_type` CHAR(1) NOT NULL,
311 `application_year` YEAR NOT NULL,
312 `application_department` VARCHAR(50) NOT NULL,
313 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
314 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`),
315 CONSTRAINT `fk_ReviewerFeedback_Person`
316 FOREIGN KEY (`user_email`)
317 REFERENCES `rech_system`.`Person` (`user_email`)
318 ON DELETE CASCADE
319 ON UPDATE CASCADE,
320 CONSTRAINT `fk_ReviewerFeedback_ComponentVersion`
321 FOREIGN KEY (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
322 REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
323 ON DELETE CASCADE
324 ON UPDATE CASCADE)
325ENGINE = InnoDB;
326
327CREATE INDEX `fk_Reviewer_idx` ON `rech_system`.`ReviewerFeedback` (`user_email` ASC);
328
329CREATE INDEX `fk_ComponentVersion_idx` ON `rech_system`.`ReviewerFeedback` (`version` ASC, `section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);
330
331
332-- -----------------------------------------------------
333-- Table `rech_system`.`ReviewerComponentFeedback`
334-- -----------------------------------------------------
335DROP TABLE IF EXISTS `rech_system`.`ReviewerComponentFeedback` ;
336
337CREATE TABLE IF NOT EXISTS `rech_system`.`ReviewerComponentFeedback` (
338 `component_feedback` VARCHAR(255) NULL,
339 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
340 `application_department` VARCHAR(50) NOT NULL,
341 `application_year` YEAR NOT NULL,
342 `application_type` CHAR(1) NOT NULL,
343 `component_id` SMALLINT(2) NOT NULL,
344 `section_id` SMALLINT(2) NOT NULL,
345 `version` TINYINT NOT NULL,
346 `user_email` VARCHAR(100) NOT NULL,
347 `ComponentVersion_version` TINYINT NOT NULL,
348 `ComponentVersion_section_id` SMALLINT(2) NOT NULL,
349 `ComponentVersion_component_id` SMALLINT(2) NOT NULL,
350 `ComponentVersion_application_type` CHAR(1) NOT NULL,
351 `ComponentVersion_application_year` YEAR NOT NULL,
352 `ComponentVersion_application_department` VARCHAR(50) NOT NULL,
353 `ComponentVersion_application_number` SMALLINT(5) UNSIGNED NOT NULL,
354 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`, `ComponentVersion_version`, `ComponentVersion_section_id`, `ComponentVersion_component_id`, `ComponentVersion_application_type`, `ComponentVersion_application_year`, `ComponentVersion_application_department`, `ComponentVersion_application_number`),
355 CONSTRAINT `fk_ReviewerFeedback`
356 FOREIGN KEY (`application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
357 REFERENCES `rech_system`.`ReviewerFeedback` (`application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
358 ON DELETE CASCADE
359 ON UPDATE CASCADE,
360 CONSTRAINT `fk_ComponentVersion`
361 FOREIGN KEY (`ComponentVersion_version` , `ComponentVersion_section_id` , `ComponentVersion_component_id` , `ComponentVersion_application_type` , `ComponentVersion_application_year` , `ComponentVersion_application_department` , `ComponentVersion_application_number`)
362 REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
363 ON DELETE CASCADE
364 ON UPDATE CASCADE)
365ENGINE = InnoDB;
366
367CREATE INDEX `fk_ComponentVersion_idx` ON `rech_system`.`ReviewerComponentFeedback` (`ComponentVersion_version` ASC, `ComponentVersion_section_id` ASC, `ComponentVersion_component_id` ASC, `ComponentVersion_application_type` ASC, `ComponentVersion_application_year` ASC, `ComponentVersion_application_department` ASC, `ComponentVersion_application_number` ASC);
368
369
370-- -----------------------------------------------------
371-- Table `rech_system`.`LiaisonFeedback`
372-- -----------------------------------------------------
373DROP TABLE IF EXISTS `rech_system`.`LiaisonFeedback` ;
374
375CREATE TABLE IF NOT EXISTS `rech_system`.`LiaisonFeedback` (
376 `feedback_date` DATETIME NOT NULL,
377 `application_assigned_date` DATETIME NULL,
378 `user_email` VARCHAR(100) NOT NULL,
379 `version` TINYINT NOT NULL,
380 `section_id` SMALLINT(2) NOT NULL,
381 `component_id` SMALLINT(2) NOT NULL,
382 `application_type` CHAR(1) NOT NULL,
383 `application_year` YEAR NOT NULL,
384 `application_department` VARCHAR(50) NOT NULL,
385 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
386 PRIMARY KEY (`feedback_date`, `application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`),
387 CONSTRAINT `fk_LiaisonFeedback_Person`
388 FOREIGN KEY (`user_email`)
389 REFERENCES `rech_system`.`Person` (`user_email`)
390 ON DELETE CASCADE
391 ON UPDATE CASCADE,
392 CONSTRAINT `fk_LiaisonFeedback_ComponentVersion`
393 FOREIGN KEY (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
394 REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
395 ON DELETE CASCADE
396 ON UPDATE CASCADE)
397ENGINE = InnoDB;
398
399CREATE INDEX `fk_Liaison_idx` ON `rech_system`.`LiaisonFeedback` (`user_email` ASC);
400
401CREATE INDEX `fk_ComponenVersion_idx` ON `rech_system`.`LiaisonFeedback` (`version` ASC, `section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);
402
403
404-- -----------------------------------------------------
405-- Table `rech_system`.`LiaisonComponentFeedback`
406-- -----------------------------------------------------
407DROP TABLE IF EXISTS `rech_system`.`LiaisonComponentFeedback` ;
408
409CREATE TABLE IF NOT EXISTS `rech_system`.`LiaisonComponentFeedback` (
410 `component_feedback` VARCHAR(255) NULL,
411 `feedback_date` DATETIME NOT NULL,
412 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
413 `application_department` VARCHAR(50) NOT NULL,
414 `application_year` YEAR NOT NULL,
415 `application_type` CHAR(1) NOT NULL,
416 `component_id` SMALLINT(2) NOT NULL,
417 `section_id` SMALLINT(2) NOT NULL,
418 `version` TINYINT NOT NULL,
419 `user_email` VARCHAR(100) NOT NULL,
420 `Component_version` TINYINT NOT NULL,
421 `Component_section_id` SMALLINT(2) NOT NULL,
422 `ComponentVersion_component_id` SMALLINT(2) NOT NULL,
423 `ComponentVersion_application_type` CHAR(1) NOT NULL,
424 `ComponentVersion_application_year` YEAR NOT NULL,
425 `ComponentVersion_application_department` VARCHAR(50) NOT NULL,
426 `ComponentVersion_application_number` SMALLINT(5) UNSIGNED NOT NULL,
427 PRIMARY KEY (`feedback_date`, `application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`, `Component_version`, `Component_section_id`, `ComponentVersion_component_id`, `ComponentVersion_application_type`, `ComponentVersion_application_year`, `ComponentVersion_application_department`, `ComponentVersion_application_number`),
428 CONSTRAINT `fk_LiaisonFeedback_idx`
429 FOREIGN KEY (`feedback_date` , `application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
430 REFERENCES `rech_system`.`LiaisonFeedback` (`feedback_date` , `application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
431 ON DELETE CASCADE
432 ON UPDATE CASCADE,
433 CONSTRAINT `fk_ComponentVersion`
434 FOREIGN KEY (`Component_version` , `Component_section_id` , `ComponentVersion_component_id` , `ComponentVersion_application_type` , `ComponentVersion_application_year` , `ComponentVersion_application_department` , `ComponentVersion_application_number`)
435 REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
436 ON DELETE CASCADE
437 ON UPDATE CASCADE)
438ENGINE = InnoDB;
439
440CREATE INDEX `fk_LiaisonFeedback_idx` ON `rech_system`.`LiaisonComponentFeedback` (`feedback_date` ASC, `application_number` ASC, `application_department` ASC, `application_year` ASC, `application_type` ASC, `component_id` ASC, `section_id` ASC, `version` ASC, `user_email` ASC);
441
442CREATE INDEX `fk_ComponentVersionFeedback_idx` ON `rech_system`.`LiaisonComponentFeedback` (`Component_version` ASC, `Component_section_id` ASC, `ComponentVersion_component_id` ASC, `ComponentVersion_application_type` ASC, `ComponentVersion_application_year` ASC, `ComponentVersion_application_department` ASC, `ComponentVersion_application_number` ASC);
443
444
445SET SQL_MODE=@OLD_SQL_MODE;
446SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
447SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;