· 8 years ago · Mar 09, 2018, 01:40 PM
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';
4
5CREATE SCHEMA IF NOT EXISTS `ThesisCollector` DEFAULT CHARACTER SET utf8 ;
6USE `ThesisCollector` ;
7
8-- -----------------------------------------------------
9-- Table `ThesisCollector`.`attachmentType`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `ThesisCollector`.`attachmentType` ;
12
13CREATE TABLE IF NOT EXISTS `ThesisCollector`.`attachmentType` (
14 `attachmentType` INT(11) NOT NULL AUTO_INCREMENT ,
15 `name` VARCHAR(100) NULL DEFAULT NULL ,
16 PRIMARY KEY (`attachmentType`) )
17ENGINE = InnoDB
18DEFAULT CHARACTER SET = utf8;
19
20
21-- -----------------------------------------------------
22-- Table `ThesisCollector`.`thesisType`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `ThesisCollector`.`thesisType` ;
25
26CREATE TABLE IF NOT EXISTS `ThesisCollector`.`thesisType` (
27 `thesisType` INT(11) NOT NULL AUTO_INCREMENT ,
28 `type` VARCHAR(100) NULL DEFAULT NULL ,
29 PRIMARY KEY (`thesisType`) )
30ENGINE = InnoDB
31DEFAULT CHARACTER SET = utf8;
32
33
34-- -----------------------------------------------------
35-- Table `ThesisCollector`.`title`
36-- -----------------------------------------------------
37DROP TABLE IF EXISTS `ThesisCollector`.`title` ;
38
39CREATE TABLE IF NOT EXISTS `ThesisCollector`.`title` (
40 `title` INT(11) NOT NULL AUTO_INCREMENT ,
41 `language` VARCHAR(100) NULL DEFAULT NULL ,
42 `name` VARCHAR(100) NULL DEFAULT NULL ,
43 PRIMARY KEY (`title`) )
44ENGINE = InnoDB
45AUTO_INCREMENT = 3
46DEFAULT CHARACTER SET = utf8;
47
48
49-- -----------------------------------------------------
50-- Table `ThesisCollector`.`author`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `ThesisCollector`.`author` ;
53
54CREATE TABLE IF NOT EXISTS `ThesisCollector`.`author` (
55 `author` INT(11) NOT NULL AUTO_INCREMENT ,
56 `prename` VARCHAR(100) NULL DEFAULT NULL ,
57 `surname` VARCHAR(100) NULL DEFAULT NULL ,
58 `loginname` VARCHAR(100) NULL DEFAULT NULL ,
59 `university` VARCHAR(100) NULL DEFAULT NULL ,
60 `study` VARCHAR(100) NULL DEFAULT NULL ,
61 `course` VARCHAR(100) NULL DEFAULT NULL ,
62 `examinationDate` DATE NULL DEFAULT NULL ,
63 `email` VARCHAR(100) NULL DEFAULT NULL ,
64 PRIMARY KEY (`author`) )
65ENGINE = InnoDB
66AUTO_INCREMENT = 8
67DEFAULT CHARACTER SET = utf8;
68
69
70-- -----------------------------------------------------
71-- Table `ThesisCollector`.`signatureThesis`
72-- -----------------------------------------------------
73DROP TABLE IF EXISTS `ThesisCollector`.`signatureThesis` ;
74
75CREATE TABLE IF NOT EXISTS `ThesisCollector`.`signatureThesis` (
76 `signatureThesis` INT(11) NOT NULL AUTO_INCREMENT ,
77 `cooporatedCompany` VARCHAR(100) NULL DEFAULT NULL ,
78 `publicAccessible` TINYINT(1) NULL DEFAULT NULL ,
79 `lockedUntil` DATE NULL DEFAULT NULL ,
80 PRIMARY KEY (`signatureThesis`) )
81ENGINE = InnoDB
82DEFAULT CHARACTER SET = utf8;
83
84
85-- -----------------------------------------------------
86-- Table `ThesisCollector`.`examinatorType`
87-- -----------------------------------------------------
88DROP TABLE IF EXISTS `ThesisCollector`.`examinatorType` ;
89
90CREATE TABLE IF NOT EXISTS `ThesisCollector`.`examinatorType` (
91 `examinatorType` INT(11) NOT NULL AUTO_INCREMENT ,
92 `name` VARCHAR(100) NULL DEFAULT NULL ,
93 PRIMARY KEY (`examinatorType`) )
94ENGINE = InnoDB
95DEFAULT CHARACTER SET = utf8;
96
97
98-- -----------------------------------------------------
99-- Table `ThesisCollector`.`examinator`
100-- -----------------------------------------------------
101DROP TABLE IF EXISTS `ThesisCollector`.`examinator` ;
102
103CREATE TABLE IF NOT EXISTS `ThesisCollector`.`examinator` (
104 `examinator` INT(11) NOT NULL AUTO_INCREMENT ,
105 `givename` VARCHAR(50) NULL DEFAULT NULL ,
106 `surname` VARCHAR(50) NULL DEFAULT NULL ,
107 `title` VARCHAR(50) NULL DEFAULT NULL ,
108 `universityMember` INT(1) NULL DEFAULT NULL ,
109 `email` VARCHAR(100) NULL DEFAULT NULL ,
110 `eximinatorType` INT(11) NULL DEFAULT NULL ,
111 PRIMARY KEY (`examinator`) ,
112 INDEX `FK_examinatorType` (`eximinatorType` ASC) ,
113 CONSTRAINT `FK_examinatorType`
114 FOREIGN KEY (`eximinatorType` )
115 REFERENCES `ThesisCollector`.`examinatorType` (`examinatorType` )
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION)
118ENGINE = InnoDB
119DEFAULT CHARACTER SET = utf8;
120
121
122-- -----------------------------------------------------
123-- Table `ThesisCollector`.`thesis`
124-- -----------------------------------------------------
125DROP TABLE IF EXISTS `ThesisCollector`.`thesis` ;
126
127CREATE TABLE IF NOT EXISTS `ThesisCollector`.`thesis` (
128 `thesisID` INT(11) NOT NULL AUTO_INCREMENT ,
129 `thesisType` INT(11) NULL DEFAULT NULL ,
130 `title` INT(11) NULL DEFAULT NULL ,
131 `author` INT(11) NULL DEFAULT NULL ,
132 `completedYear` INT(11) NULL DEFAULT NULL ,
133 `signatureThesis` INT(11) NULL DEFAULT NULL ,
134 `examinator` INT(11) NULL DEFAULT NULL ,
135 `password` VARCHAR(100) NULL ,
136 PRIMARY KEY (`thesisID`) ,
137 INDEX `FK_thesisType` (`thesisType` ASC) ,
138 INDEX `FK_title` (`title` ASC) ,
139 INDEX `FK_author` (`author` ASC) ,
140 INDEX `FK_signatureThesis` (`signatureThesis` ASC) ,
141 INDEX `FK_examinator` (`examinator` ASC) ,
142 CONSTRAINT `FK_thesisType`
143 FOREIGN KEY (`thesisType` )
144 REFERENCES `ThesisCollector`.`thesisType` (`thesisType` )
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION,
147 CONSTRAINT `FK_title`
148 FOREIGN KEY (`title` )
149 REFERENCES `ThesisCollector`.`title` (`title` )
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION,
152 CONSTRAINT `FK_author`
153 FOREIGN KEY (`author` )
154 REFERENCES `ThesisCollector`.`author` (`author` )
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `FK_signatureThesis`
158 FOREIGN KEY (`signatureThesis` )
159 REFERENCES `ThesisCollector`.`signatureThesis` (`signatureThesis` )
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION,
162 CONSTRAINT `FK_examinator`
163 FOREIGN KEY (`examinator` )
164 REFERENCES `ThesisCollector`.`examinator` (`examinator` )
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION)
167ENGINE = InnoDB
168DEFAULT CHARACTER SET = utf8;
169
170
171-- -----------------------------------------------------
172-- Table `ThesisCollector`.`attachment`
173-- -----------------------------------------------------
174DROP TABLE IF EXISTS `ThesisCollector`.`attachment` ;
175
176CREATE TABLE IF NOT EXISTS `ThesisCollector`.`attachment` (
177 `attachmentID` INT(11) NOT NULL AUTO_INCREMENT ,
178 `attachmentType` INT(11) NULL DEFAULT NULL ,
179 `thesisID` INT(11) NULL ,
180 `signatur` VARCHAR(100) NULL DEFAULT NULL ,
181 `name` VARCHAR(255) NULL DEFAULT NULL ,
182 `attachment` BLOB NULL ,
183 PRIMARY KEY (`attachmentID`) ,
184 INDEX `FK_attachmentType` (`attachmentType` ASC) ,
185 INDEX `FK_thesisID` (`thesisID` ASC) ,
186 CONSTRAINT `FK_attachmentType`
187 FOREIGN KEY (`attachmentType` )
188 REFERENCES `ThesisCollector`.`attachmentType` (`attachmentType` )
189 ON DELETE NO ACTION
190 ON UPDATE NO ACTION,
191 CONSTRAINT `FK_thesisID`
192 FOREIGN KEY (`thesisID` )
193 REFERENCES `ThesisCollector`.`thesis` (`thesisID` )
194 ON DELETE NO ACTION
195 ON UPDATE NO ACTION)
196ENGINE = InnoDB
197DEFAULT CHARACTER SET = utf8;
198
199
200
201SET SQL_MODE=@OLD_SQL_MODE;
202SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
203SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;