db.sql (5983B)
1 SET FOREIGN_KEY_CHECKS=0; 2 DROP TABLE IF EXISTS `consult_letter`; 3 4 CREATE TABLE `consult_letter` ( 5 `consult_letter_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 6 `employee_id` smallint(6) UNSIGNED NOT NULL, 7 `proposal_id` smallint(6) UNSIGNED NOT NULL, 8 PRIMARY KEY (`consult_letter_id`), 9 KEY `employee_id` (`employee_id`), 10 KEY `proposal_id` (`proposal_id`), 11 CONSTRAINT `consult_letter_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`), 12 CONSTRAINT `consult_letter_ibfk_2` FOREIGN KEY (`proposal_id`) REFERENCES `proposal` (`proposal_id`) 13 ); 14 15 DROP TABLE IF EXISTS `consult_letter_revision`; 16 17 CREATE TABLE `consult_letter_revision` ( 18 `consult_letter_id` smallint(6) UNSIGNED NOT NULL, 19 `consult_letter_file_path` varchar(255) NOT NULL, 20 `consult_letter_datetime` datetime NOT NULL, 21 PRIMARY KEY (`consult_letter_id`, `consult_letter_datetime`), 22 KEY `consult_letter_id` (`consult_letter_id`), 23 CONSTRAINT `consult_letter_revision_ibfk_1` FOREIGN KEY (`consult_letter_id`) REFERENCES `consult_letter` (`consult_letter_id`) 24 ); 25 26 DROP TABLE IF EXISTS `assessement_form`; 27 28 CREATE TABLE `assessement_form` ( 29 `assessement_form_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 30 PRIMARY KEY (`assessement_form_id`) 31 ); 32 33 DROP TABLE IF EXISTS `assessement_form_revision`; 34 35 CREATE TABLE `assessement_form_revision` ( 36 `assessement_form_id` smallint(6) UNSIGNED NOT NULL, 37 `assessement_form_file_path` varchar(255) NOT NULL, 38 `assessement_form_datetime` datetime NOT NULL, 39 PRIMARY KEY (`assessement_form_id`, `assessement_form_datetime`), 40 KEY `assessement_form_id` (`assessement_form_id`), 41 CONSTRAINT `assessement_form_revision_ibfk_1` FOREIGN KEY (`assessement_form_id`) REFERENCES `assessement_form` (`assessement_form_id`) 42 ); 43 44 DROP TABLE IF EXISTS `employee`; 45 46 CREATE TABLE `employee` ( 47 `employee_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 48 `first_name` varchar(20) NOT NULL, 49 `middle_name` varchar(20) DEFAULT NULL, 50 `last_name` varchar(20) NOT NULL, 51 `email` varchar(30) NOT NULL, 52 `department` varchar(20) NOT NULL, 53 `title` varchar(20) NOT NULL, 54 `curricular_consultant` tinyint(4) UNSIGNED NOT NULL, 55 PRIMARY KEY (`employee_id`) 56 ); 57 58 DROP TABLE IF EXISTS `library_form`; 59 60 CREATE TABLE `library_form` ( 61 `library_form_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 62 PRIMARY KEY (`library_form_id`) 63 ); 64 65 DROP TABLE IF EXISTS `library_form_revision`; 66 67 CREATE TABLE `library_form_revision` ( 68 `library_form_id` smallint(6) UNSIGNED NOT NULL, 69 `library_form_file_path` varchar(255) NOT NULL, 70 `library_form_datetime` datetime NOT NULL, 71 PRIMARY KEY (`library_form_id`, `library_form_datetime`), 72 KEY `library_form_id` (`library_form_id`), 73 CONSTRAINT `library_form_revision_ibfk_1` FOREIGN KEY (`library_form_id`) REFERENCES `library_form` (`library_form_id`) 74 ); 75 76 DROP TABLE IF EXISTS `proposal`; 77 78 CREATE TABLE `proposal` ( 79 `proposal_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 80 `assessement_form_id` smallint(6) UNSIGNED DEFAULT NULL, 81 `library_form_id` smallint(6) UNSIGNED DEFAULT NULL, 82 `supporting_document_id` smallint(6) UNSIGNED DEFAULT NULL, 83 `program_guide_id` smallint(6) UNSIGNED DEFAULT NULL, 84 `proposal_datetime` datetime NOT NULL, 85 `revision_note` varchar(255) DEFAULT NULL, 86 `proposal_title` varchar(50) NOT NULL, 87 `proposal_description` varchar(255) DEFAULT NULL, 88 `need_library_form` tinyint(4) UNSIGNED NOT NULL, 89 `need_program_guide` tinyint(4) UNSIGNED NOT NULL, 90 `proposal_type` char(1) NOT NULL, 91 PRIMARY KEY (`proposal_id`), 92 KEY `assessement_form_id` (`assessement_form_id`), 93 KEY `supporting_document_id` (`supporting_document_id`), 94 KEY `library_form_id` (`library_form_id`), 95 CONSTRAINT `proposal_ibfk_1` FOREIGN KEY (`assessement_form_id`) REFERENCES `assessement_form` (`assessement_form_id`), 96 CONSTRAINT `proposal_ibfk_2` FOREIGN KEY (`supporting_document_id`) REFERENCES `supporting_document` (`supporting_document_id`), 97 CONSTRAINT `proposal_ibfk_3` FOREIGN KEY (`library_form_id`) REFERENCES `library_form` (`library_form_id`) 98 ); 99 100 DROP TABLE IF EXISTS `sponsor`; 101 102 CREATE TABLE `sponsor` ( 103 `proposal_id` smallint(6) UNSIGNED NOT NULL, 104 `employee_id` smallint(6) UNSIGNED NOT NULL, 105 `is_author` tinyint(4) UNSIGNED NOT NULL, 106 PRIMARY KEY (`proposal_id`, `employee_id`), 107 KEY `proposal_id` (`proposal_id`), 108 KEY `employee_id` (`employee_id`), 109 CONSTRAINT `sponsor_ibfk_1` FOREIGN KEY (`proposal_id`) REFERENCES `proposal` (`proposal_id`), 110 CONSTRAINT `sponsor_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`) 111 ); 112 113 DROP TABLE IF EXISTS `supporting_document`; 114 115 CREATE TABLE `supporting_document` ( 116 `supporting_document_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 117 PRIMARY KEY (`supporting_document_id`) 118 ); 119 120 DROP TABLE IF EXISTS `supporting_document_revision`; 121 122 CREATE TABLE `supporting_document_revision` ( 123 `supporting_document_id` smallint(6) UNSIGNED NOT NULL, 124 `supporting_document_file_path` varchar(255) NOT NULL, 125 `supporting_document_datetime` datetime NOT NULL, 126 PRIMARY KEY (`supporting_document_id`, `supporting_document_datetime`), 127 KEY `supporting_document_id` (`supporting_document_id`), 128 CONSTRAINT `supporting_document_revision_ibfk_1` FOREIGN KEY (`supporting_document_id`) REFERENCES `supporting_document` (`supporting_document_id`) 129 ); 130 131 DROP TABLE IF EXISTS `program_guide`; 132 133 CREATE TABLE `program_guide` ( 134 `program_guide_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT, 135 PRIMARY KEY (`program_guide_id`) 136 ); 137 138 DROP TABLE IF EXISTS `program_guide_revision`; 139 140 CREATE TABLE `program_guide_revision` ( 141 `program_guide_id` smallint(6) UNSIGNED NOT NULL, 142 `program_guide_file_path` varchar(255) NOT NULL, 143 `program_guide_datetime` datetime NOT NULL, 144 PRIMARY KEY (`program_guide_id`, `program_guide_datetime`), 145 KEY `program_guide_id` (`program_guide_id`), 146 CONSTRAINT `program_guide_revision_ibfk_1` FOREIGN KEY (`program_guide_id`) REFERENCES `program_guide` (`program_guide_id`) 147 );